用 javascript 写 SQL
介绍
基于 node-postgreSql 的 sql 客户端
什么时候需要用 javascript 写 SQL？
避免重复
一些 SQL 可以省去多余的代码，例如根据条件查询数据
const getFooIdByBar = (connection: DatabaseConnection, bar: string): Promise<DatabaseRecordIdType> => {
return connection.oneFirst(sql`
SELECT id
FROM foo
WHERE bar = ${bar}
`);
};静态检查
例如数据操作是不是符合规范：
// fooId is array
const fooId = await connection.many(sql`
SELECT id
FROM foo
WHERE bar = ${bar}
`);
// throw error
await connection.query(sql`
DELETE FROM baz
WHERE foo_id = ${fooId}
`);应用
释放资源
当 SQL 产生错误及时释放资源：
const main = () => {
return pool.connect((connection) => {
return connection.query(sql`SELECT foo()`);
});
};connection总是会释放资源，无论是resolve 还是 reject，因此也就不用使用 try...catch。
防止 SQL 注入
使用
连接数据库
// postgresql://[user[:password]@][host[:port]][/database name][?name=value[&...]]
import {
createPool,
} from 'slonik';
const pool = await createPool('postgres://');查询
import {
createPool,
sql,
} from 'slonik';
const pool = await createPool('postgres://');
const main = async () => {
// 查询连接状态
pool.getPoolState();
// {
// activeConnectionCount: 0,
// ended: false,
// idleConnectionCount: 0,
// waitingClientCount: 0,
// }
await pool.connect(() => {
pool.getPoolState();
// {
// activeConnectionCount: 1,
// ended: false,
// idleConnectionCount: 0,
// waitingClientCount: 0,
// }
});
await pool.query(sql`
SELECT 1
`);
await pool.end();
};
main();mock
import {
createMockPool,
createMockQueryResult,
} from 'slonik';
type OverridesType =
query: (sql: string, values: PrimitiveValueExpression[],) => Promise<QueryResult<QueryResultRow>>,
};
createMockPool(overrides: OverridesType): DatabasePool;
createMockQueryResult(rows: QueryResultRow[]): QueryResult<QueryResultRow>;参考
