关于前端:用-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 arrayconst fooId = await connection.many(sql`  SELECT id  FROM foo  WHERE bar = ${bar}`);// throw errorawait 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>;

参考

slonik | Github