轻松生成PostgreSQL查询：o2sql工具简介

发表时间: 2019-03-16 14:25

o2sql是一个简单、灵活、快速生成postgreSQL查询语句的工具。配合 node-postgres 模块，可以以最直接的方式操作数据库。

o2sql以链式调用的方式构建查询。工具内部先将o2sql查询转换成抽象语法树AST，然后再生成最终的查询参数：SQL语句和值列表。SQL语句会自动将表名、字段名等用 "" 包裹。

安装

npm i o2sql

.toParams() 生成查询参数

返回对象结构如下：

{ sql: '....', values: [...],}

示例：

o2sql.select(['id', 'name']) .from('user') .where(4) .toParams();结果： { sql: 'select "id", "name" from "user" where "id" = ', values: [4],}

execute(client) 执行查询

配置setOnExecuteHandler之后中，可以直接调用execute，获得查询结果。无需先使用 toParams 生成查询参数。具体示例请参照后面的 setOnExecuteHandler 部分。

parse 查询字符串转抽象语法树AST

将SQL查询字符串转换成抽象语法树，以获得更复杂的SQL语句结构，尤其适合函数和嵌套的情况。但这个方式类似于AST的Hack，应尽量避免使用。

o2sql.parse('count + my_func(p1, p2, 4)')

select 生成select查询

用法：

o2sql.select(columns) .from(table) .distinct(distinct) .where(conditions) .groupby(groupby) .orderby(orderby) .having(having) .limit(limit) .skip(skip) .paginate(page, pageSize)

columns 字段

基本

['id', 'gender', ['name', 'userName']]// "id", "gender", "name" AS "userName"

多表join的情况

[{ table: 'user', fields: ['id', 'name', 'gender'],}, { table: 'group', fields: ['id', 'name', ['category', 'kind'], prefix: 'group', // 会在每个字段前附加前缀，如name 会变成 groupName}, { table: 'company', fields: ['id', 'name'], prefix: 'company', separator: '_', // 配合prefix，会在每个字段前附加前缀，如id 会变成 company_id}]//"id", "name", "gender", "groupId", "groupName", "groupKind", "company_id", "company_name"

上述两种用法是可以混用的：

['firstName', 'lastName', { table: 'group', fields: ['id', 'name', ['category', 'kind'], prefix: 'group',}]

distinct:

o2sql.select(['id', 'group']) .from('user') .distinct()// select distinct "id", "group" from "user"o2sql.select(['id', 'group', 'name']) .from('user') .distinct(['id', 'group']);// SELECT distinct on ( "id", "group" ) "id", "group", "name" FROM "user"

from 表

基本用法:

'user'

join:

{ left: { name: 'user', key: 'groupId', }, right: { name: 'group', key: 'id', },}// "user" INNER JOIN "group" ON "user"."groupId"="group"."id"{ left: { name: 'user', alias: 'U', }, right: { name: 'group', alias: 'G', }, join: 'LEFT JOIN', on: 'U.groupId=G.id'}// "user" "U" LEFT JOIN "group" "G" ON "U"."groupId" = "G"."id"{ right: { left: { name: 'user', alias: 'U', }, right: { name: 'group', alias: 'G', }, join: 'LEFT JOIN', on: 'U.groupId=G.id', key: 'U.companyId', }, right: { name: 'company', key: 'id', },}// "user" "U" LEFT JOIN "group" "G" ON "U"."groupId" = "G"."id" INNER JOIN "company" ON "U"."companyId" = "company"."id"{ left: { name: 'user', alias: 'U', }, right: { left: { name: 'group', alias: 'G', key: 'groupKindId', }, right: { name: 'groupKind', alias: 'GK', key: 'id', }, }, join: 'LEFT JOIN', on: 'U.groupId=G.id',}// "user" "U" LEFT JOIN ("group" "G" INNER JOIN "groupKind" "GK" ON "G"."groupKindId" = "GK"."id") ON "U"."groupId" = "G"."id"

where 条件:

Number/String

where(8) 是 where({ id: 8 }) 的简写

{ groupId: 3, gender: 'M',}// "groupId" =  AND "gender" =

$1, $2 的值会被push到toParams返回对象的 values

{ $or: { groupId: 3, gender: 'M', }}// ("groupId" =  OR "gender" = )

基本操作

{ name: { "IS NOT": null, }, title: { LIKE: '%abc', }, age: [22, 23, 24], sector: { '&&': ['a', 'b', 'c'], },}// "name" IS NOT NULL AND "title" like  AND "age" IN (,,) AND "sector" && ARRAY[,,]::VARCHAR[]

基本的操作符都是支持的，如 >=, ILIKE, ...

自由模式

{ $$: 'id=ANY(1,2,3)', $:`(gender='M' OR "groupKind"=3)`, $: { left: o2sql.parse('my_function1(field1)'), op: '>=', right: o2sql.parse('my_function2(field2, field3)'), },}// "id" = ANY(, , ) AND ("gender" =  OR "groupKind" = ) AND my_function1("field1") >= my_function2("field2", "field3")

** 只要以 $$ 开头，后面的逻辑表达式可以随意定义。

子查询

{ groupId: { IN: o2sql.select(['id']).from('group').where({ groupKind: 'a', }), }}// "groupId" IN (SELECT "id" FROM "group" WHERE "groupKind" = )

groupby

groupby(['user.groupId', 'user.kind'])

orderby

order(['id', '-name', ['gender', 'desc']])

-name 是 ['name', 'desc'] 的简写

having

having(having)

having的结构和 where 一致

分页

limit(10).skip(20)

或

paginate(page, pageSize)// 这是下面写法的简写limit(pageSize).skip(pageSize * (page - 1))

get 类似select，但只获取第一行数据。

get 不需要设置limit, skip, paginate，其它与 select 相同

o2sql.get(columns) .from(table) .distinct(distinct) .where(conditions) .groupby(groupby) .orderby(orderby) .having(having)

count 统计结果数

各函数设置和 select 相同

o2sql.count(table) .where(where) .distinct(distinct) .where(conditions) .groupby(groupby) .orderby(orderby) .having(having)

insert

o2sql.insert(values) .into(table); .returning(columns);或o2sql.insertInto(table) values(values) .returning(columns);

如果values是对象，那么就插入一行。如果是数据，那么就插入多行。，

示例.

o2sql.insertInto('user') .values([{ name: 'Echo', age: 34, likes: o2sql.count('ul').where({ tt: 3, }) }, { name: 'Echo', age: 34, likes: 5, }]) .returning(['id', 'name']);// toParams { sql: 'INSERT INTO "user" ("name","age","likes") VALUES (,,(SELECT COUNT(*)::int AS "count" FROM "ul" WHERE "tt" = )),(,,) RETURNING "id", "name"', values: [ 'Echo', 34, 3, 'Echo', 34, 5 ], }

update

o2sql.update(table) .set(value) .where(where)

示例

o2sql.update('user') .set({ name: 'Echo', age: 34, count: o2sql.parse('"count" + 1'), likes: o2sql.count('ul').where({ tt: 3, }) }) .where({ id: 1, });// toParams{ sql: 'UPDATE "user" SET "name"=, "age"=, "count"="count" + , "likes"=(SELECT COUNT(*)::int AS "count" FROM "ul" WHERE "tt" = ) WHERE "id" = ', values: [ 'Echo', 34, 1, 3, 1 ],}

delete

o2sql.delete('user').where(2)o2sql.delete('user') .where({ id: 1, })

where 的格式和 select 的 where 相同，但是where参数必须设置，以防止删除整个表。

setOnExecuteHandler 设置执行函数

通过以下设置，可以很好的通过 pg 模块操作数据库。

// set execute handler when you init your appconst { Pool } = require('pg')const pool = new Pool(config);const o2sql = require('o2sql');o2sql.setOnExecuteHandler(async function({ sql: text, values }, client) { const result = await (client ? client : pool).query({ text, values }); if (this.command === 'select') { if (this.isGet) { return result.rows.length > 0 ? result.rows[0] : null; } else if (this.isCount) { return result.rows[0].count; } return result.rows;  } else if (this.command === 'insert') { return result.rows[0]; } else if (this.command === 'update') { return result.rows[0]; } else if (this.command === 'delete') { return result; }});

执行查询：

const user = await o2sql.get(['name', 'age']) .from('user') .where({ id: 1, }) .execute(client);

其中 client 如果不设置，或是null，则执行单独的查询。如果client可用，则使用client的query进行查询。这可以实现存储过程支持。

存储过程

具体请参照 pg 模块中关于存储过程的说明
https://node-postgres.com/features/transactions.

const { Pool } = require('pg');const pool = new Pool();(async () => { try { await client.query('BEGIN'); await o2sql.get(['name', 'age']) .from('user') .where({ id: 1, }) .execute(client); await o2sql.delete('user') .where({ id: 1, }) .execute(client); await client.query('COMMIT'); } catch (e) { await client.query('ROLLBACK'); throw e; } finally { client.release(); }})().catch(e => console.error(e.stack));