关于前端:用-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