用 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 注入

Read more

使用

连接数据库

// 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>;

参考

• slonik | Github