A very simple tool to help generate postgres queries. "" will be added to table/field names.

.toParams() returns the following object, which could be used in node-postgres (https://www.npmjs.com/package/pg) directly.

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

Install

npm install o2sql

Usage

Basic

const o2sql = new (require('o2sql'))();
const params = o2sql.select(['id', 'name'])
  .from('user')
  .where(1)
  .toParams();

Then params will be:

{
  sql: 'select "id", "name" from "user" where "id" = $1',
  values: [4],
}

toParams()

Everying inherits from ast, and can be transformed to {sql, values} by calling toParams().

API

identifier / i

o2sql.identifier(name:string):IdentifierAst

Parse an identifier to ast.

o2sql.i('user.name').toParams();

{ sql: '"user"."name"', values: [] }

ValueAst.op(op:string, right: string|number|Ast):ExprAst

o2sql.i('age').op('+', 5).toParams();

{ sql: '"age" + $1', values: [ 5 ] }

ValueAst.and(right: string|number|Ast):ExprAst

Equals ValueAst.op('and', right:any):ExprAst

o2sql.i('show').and(o2sql.i('top')).toParams();

{ sql: '"show" AND "top"', values: [] }

ValueAst.or(right: string|number|Ast):ExprAst

Equals ValueAst.op('or', right:any):ExprAst

o2sql.i('show').or(o2sql.i('top')).toParams();

{ sql: '"show" OR "top"', values: [] }

function / f

o2sql.function(
  name:string,  // function name
  ...params:?string|number|Ast  // params
):FunctionAst

Parse a function to ast. First argument is funciton name, and rest for arguments.

o2sql.f('foo', 1, 'abc').toParams();

{ sql: '"foo"($1,$2)', values: [ 1, 'abc' ] }

FunctionAst.op(op:string, right: string|number|Ast):ExprAst

o2sql.f('foo', 3).op('+', 5).toParams();

{ sql: '"foo"($1) + $2', values: [ 3, 5 ] }

FunctionAst.and(right: string|number|Ast):ExprAst

Equals FunctionAst.op('and', right:any):ExprAst

o2sql.f('foo',o2sql.i('show')).and(o2sql.i('top')).toParams();

{ sql: '"foo"("show") AND "top"', values: [] }

FunctionAst.or(right: string|number|Ast):ExprAst

Equals FunctionAst.op('or', right:any):ExprAst

o2sql.f('foo',o2sql.i('show')).or(o2sql.i('top')).toParams();

{ sql: '"foo"("show") OR "top"', values: [] }

value / v

o2sql.value(value:string|number):ValueAst

Make a value ast.

o2sql.v(5).toParams();

{ sql: '$1', values: [ 5 ] }

ValueAst.op(op:string, right: string|number|Ast):ExprAst

o2sql.v(3).op('+', 5).toParams();

{ sql: '$1 + $2', values: [ 3, 5 ] }

ValueAst.and(right: string|number|Ast):ExprAst

Equals ValueAst.op('and', right:any):ExprAst

o2sql.v(true).and(o2sql.i('top')).toParams();

{ sql: '$1 AND "top"', values: [ true ] }

ValueAst.or(right: string|number|Ast):ExprAst

Equals ValueAst.op('or', right:any):ExprAst

o2sql.v(true).or(o2sql.i('top')).toParams();

{ sql: '$1 OR "top"', values: [ true ] }

expr / e

o2sql.expr(
  left:string|number|Ast,
  op:string,
  right:string|number|Ast
):ExprAst

Make an expression ast. This is very useful in UPDATE.

o2sql.e(o2sql.i('count'), '+', 1).toParams();

{ sql: '"count" + $1', values: [ 1 ] }

This equals to:

o2sql.i('count').op('+', 1).toParams();

ExprAst.op(op:string, right:string|number|Ast):ExprAst

o2sql.e(5, '+', 6).op('*', 7).toParams();

{ sql: '($1 + $2) * $3', values: [ 5, 6, 7 ] }

o2sql.e(5, '+', 6).op('*', o2sql.i('rank')).toParams();

{ sql: '($1 + $2) * "rank"', values: [ 5, 6 ] }

ExprAst.and(right: string|number|Ast):ExprAst

Equals ValueAst.op('and', right:any):ExprAst

o2sql.e(o2sql.i('rank'), '=', 5).and(o2sql.i('top')).toParams();

{ sql: '"rank" = $1 AND "top"', values: [ 5 ] }

ExprAst.or(right: string|number|Ast):ExprAst

Equals ValueAst.op('or', right:any):ExprAst

o2sql.e(o2sql.i('rank'), '=', 5).or(o2sql.i('top')).toParams();

{ sql: '"rank" = $1 OR "top"', values: [ 5 ] }

table / t

o2sql.table(table:string|array|object):TableAst

table(table:string):TableAst

o2sql.t('user').toParams();

{ sql: '"user"', values: [] }

table([table:string, alias:string]:array):TableAst

o2sql.t(['user', 'U']).toParams();

{ sql: '"user" "U"', values: [] }

table({table:string, alias:string}:object):TableAst

o2sql.t({table:'user', alias:'U'}).toParams();

{ sql: '"user" "U"', values: [] }

About how to join tables, please see join of Select.

Select

o2sql.select(columns:array)
  .distinct(distinct:string|array)
  .from(table:string|object)
  .innerJoin(table:string|array|tableAst|object,on:array|object)
  .leftJoin(table:string|array|tableAst|object,on:array|object)
  .rightJoin(table:string|array|tableAst|object,on:array|object)
  .fullJoin(table:string|array|tableAst|object,on:array|object)
  .crossJoin(table:string|array|tableAst|object,on:array|object)
  .default(table:string)
  .where(where:object)
  .groupby(groupby:string|array)
  .orderby(orderby:string|array)
  .having(having:object)
  .limit(limit:number)
  .skip(skip:number)
  .union(union:Select)

paginate(page:number, pageSize:number)
// short for .limit(limit).skip(skip)

select

o2sql.select(columns:array):Select

Basic

column name

o2sql.select(['id', 'name', 'dept.name']).toParams()

{ sql: 'SELECT "id","name","dept"."name"', values: [] }

alias name

o2sql.select(['deptId', ['dept.name', 'deptName']).toParams();

{ sql: 'SELECT "deptId","dept"."name" "deptName"', values: [] }

cast value

o2sql.select(['id', ['age', 'userAge', 'int']]).toParams();

{
  sql: 'SELECT "id",CAST("age" AS INTEGER) "userAge"',
  values: []
}

function / expr

o2sql.select([
  [o2sql.f('foo', o2sql.i('col1'), o2sql.i('col2'), 5), 'total'],
  [o2sql.e(o2sql.i('col3'), '+', '_append_string'), 'appendedString', 'string'],
])
  .toParams();

{
  sql: 'SELECT "foo"("col1","col2",$1) "total",CAST("col3" + $2 AS VARCHAR) "appendedString"',
  values: [ 5, '_append_string' ]
}

sub query

o2sql.select([
  [o2sql.select(['name']).from('group').where({id: o2sql.i('user.groupId')}), 'groupName']
])
  .from('user')
  .toParams();

{
  sql: 'SELECT (SELECT "name" FROM "group" WHERE "id" = "user"."groupId") "groupName" FROM "user"',
  values: []
}

Multi table

o2sql.select([
  {
    table: 'user',
    fields: ['id', 'name', 'gender'],
  },
  {
    table: 'group',
    fields: ['id', 'name', ['category', 'kind']],
    prefix: 'group',
  },
  {
    table: 'company',
    fields: ['id', 'name'],
    prefix: 'company',
    separator: '_',
  }
]).toParams();

{
  sql: 'SELECT "user"."id" "userId","user"."name" "userName","user"."gender" "userGender","group"."id" "groupId","group"."name" "groupName","category" "groupKind","company"."id" "company_id","company"."name" "company_name"',
  values: []
}

Mixed usage is also supported, but you need to make sure every plain field is unique.

o2sql.select([
  'firstName',
  'lastName',
  {
    table: 'group',
    fields: ['id', 'name', ['category', 'kind']],
    prefix: 'group',
  }
]).toParams();

{
  sql: 'SELECT "firstName","lastName","group"."id" "groupId","group"."name" "groupName","category" "groupKind"',
  values: []
}

distinct

.distinct(distinct:?array):Select

distinct all

o2sql.select(['id', 'name', 'groupId'])
  .distinct()
  .toParams();

{ sql: 'SELECT DISTINCT "id","name",groupId"', values: [] }

disinct on

o2sql.select(['id', 'name', 'groupId'])
  .from('user')
  .distinct(['groupId'])
  .toParams();

{
  sql: 'SELECT DISTINCT ON ("groupId") "id","name","groupId" FROM "user"',
  values: []
}

from

Select.from(table:string|array|TableAst|object):Select

See [table / t](##table / t) for param details.

o2sql.select(['id'])
  .from(o2sql.t('user'))
  .toParams();

{ sql: 'SELECT "id" FROM "user"', values: [] }

o2sql.select(['id'])
  .from(o2sql.t('user').innerJoin('dept', ['user.deptId', 'dept.id']))
  .toParams();

{
  sql: 'SELECT "id" FROM "user" INNER JOIN "dept" ON "user"."deptId" = "dept"."id"',
  values: []
}

join

Select.innerJoin(table:string|array|tableAst|object,on:array|object):Select
Select.leftJoin(table:string|array|tableAst|object,on:array|object):Select
Select.rightJoin(table:string|array|tableAst|object,on:array|object):Select
Select.fullJoin(table:string|array|tableAst|object,on:array|object):Select
Select.crossJoin(table:string|array|tableAst|object,on:array|object):Select

table:string|array|object

See [table / t](##table / t) for param details.

table:TableAst

o2sql.select(['id'])
  .from('user')
  .innerJoin(
    o2sql.table('dept')
      .innerJoin(
        'org',
        ['dept.orgId','org.id']
       ),
    ['user.deptId', 'dept.id']
  ).toParams();

{
  sql: 'SELECT "id" FROM "user" INNER JOIN ("dept" INNER JOIN "org" ON "dept"."orgId" = "org"."id" ON "user"."deptId" = "dept"."id")',
  values: []
}

on:array

o2sql.select(['id'])
  .from('user')
  .join('group', ['groupId', 'group.id'])
  .toParams();

{
  sql: 'SELECT "id" FROM "user" INNER JOIN "group" ON "groupId" = "group"."id"',
  values: []
}

on:object

o2sql.select(['id'])
  .from('user')
  .rightJoin('group', {
    left: o2sql.i('groupId'),
    op: '=',
    right: o2sql.i('group.id'),
  })
  .toParams();

{
  sql: 'SELECT "id" FROM "user" RIGHT JOIN "group" ON "groupId" = "group"."id"',
  values: []
}

on:ExprAst Advanced usage.

o2sql
  .select(['id'])
  .from('user')
  .join(
    'group',
    o2sql.i('groupId').op('=', o2sql.i('group.id'))
      .and(o2sql.i('group.kind').op('=', 'admin'))
  )
  .toParams();

{
  sql: 'SELECT "id" FROM "user" INNER JOIN "group" ON "groupId" = "group"."id" AND "group"."kind" = $1',
  values: [ 'admin' ]
}

default

Set default table/alias prefix before field names.

o2sql
  .select(['id', 'name', ['dept.name', 'deptName']])
  .from('user')
  .innerJoin('dept', ['deptId', 'dept.id'])
  .default('user')
  .where({
    orgId: 3,
  })
  .orderby(['deptName']);
  .toParams();
{
  sql:
   'SELECT "user"."id","user"."name","dept"."name" "deptName" FROM "user" INNER JOIN "dept" ON "user"."deptId" = "dept"."id" WHERE "user"."orgId" = $1 ORDER BY "deptName" ASC',
  values: [ 3 ]
}
```
### where:

```javascript
where(where:string|number|array|object):Select

Number/String

where(id:string|number):Select
// equals to
where({
  id:string|number
}):Select

o2sql.select(['id']).from('user').where(1).toParams();

{
  sql: 'SELECT "id" FROM "user" WHERE "id" = $1',
  values: [ 1 ]
}

AND

o2sql.select(['id'])
  .from('user')
  .where({
    groupId: 3,
    gender: 'M',
  })
  .toParams();

{
  sql: 'SELECT "id" FROM "user" WHERE "groupId" = $1 AND "gender" = $2',
  values: [ 3, 'M' ]
}

OR

where(where:array)

o2sql.select(['id'])
  .from('user')
  .where([
    {
      groupId: 3
    },
    {
      groupId: 4
    }
  ]).toParams();

{
  sql: 'SELECT "id" FROM "user" WHERE "groupId" = $1 OR "groupId" = $2',
  values: [ 3, 4 ]
}

OR in AND

If the name of an attribute startsWith '$' and value is an array (this feature will be removed in the next main version).
OR the name of an attribute is a Symbol and value is an array.

o2sql.select(['id'])
  .from('user')
  .where({
    gender: 'M',
    [Symbol()]:[
      {
        groupId: 3
      },
      {
        groupId: 4,
        rank: 2,
      }
    ]
  }).toParams();

{
  sql: 'SELECT "id" FROM "user" WHERE "gender" = $1 AND ("groupId" = $2 OR "groupId" = $3 AND "rank" = $4)',
  values: [ 'M', 3, 4 ]
}

Other operators

o2sql.select(['id'])
  .from('user')
  .where({
    groupId: 3,
    gender: 'M',
    vip: false,
    address: {
      '<>': null,
    },
    grade: null,
    age: {
      '>=': 18,
      '<': 60
    },
    role: ['user', 'admin'],
    name: {
      ILIKE: '%Mia%'
    },
    sectors: {
      '&&': ['a', 'b', 'c'],
      '@>': ['a', 'b'],
    }
  })
  .toParams();

{
  sql: 'SELECT "id" FROM "user" WHERE "groupId" = $1 AND "gender" = $2 AND "vip" = $3 AND "address" IS NOT NULL AND "grade" IS NULL AND "age" >= $4 AND "age" < $5 AND "role"=ANY(ARRAY[$6,$7]::VARCHAR[]) AND "name" ILIKE $8 AND "sectors" && ARRAY[$9,$10,$11]::VARCHAR[] AND "sectors" @> ARRAY[$12,$13]::VARCHAR[]',
  values:
   [ 3,
     'M',
     false,
     18,
     60,
     'user',
     'admin',
     '%Mia%',
     'a',
     'b',
     'c',
     'a',
     'b' ]
}

Subquery

o2sql.select(['id', 'name'])
  .from('user')
  .where({
    groupId: {
      IN: o2sql.select(['id']).from('group').where({
        groupKind: 'a',
      }),
    }
  })
  .toParams();

{
  sql: 'SELECT "id","name" FROM "user" WHERE "groupId"=ANY(SELECT "id" FROM "group" WHERE "groupKind" = $1)',
  values: [ 'a' ]
}

Free mode

o2sql
  .select(['id'])
  .from('user')
  .where({
    [Symbol()]: {
      $left: o2sql.f('foo'),
      $op: '>=',
      $right: o2sql.i('age'),
    },
    [Symbol()]: {
      $op: 'EXISTS',
      $right: o2sql
        .select(['deptId'])
        .from('userDept')
        .where({
          userId: o2sql.i('user.deptId'),
        }),
    },
    [Symbol()]: {
      $right: o2sql.f(
        'NOT EXISTS',
        o2sql
          .select(['deptId'])
          .from('userDept')
          .where({
            userId: o2sql.i('user.deptId'),
          })
      ),
    },
    [Symbol()]: o2sql.f(
      'EXISTS',
      o2sql
        .select(['groupId'])
        .from('userGroup')
        .where({
          userId: o2sql.i('user.groupId'),
        })
    ),
  })
  .toParams();

{
  sql: 'SELECT "id" FROM "user" WHERE "foo"() >= "age" AND  EXISTS (SELECT "deptId" FROM "userDept" WHERE "userId" = "user"."deptId") AND NOT EXISTS((SELECT "deptId" FROM "userDept" WHERE "userId" = "user"."deptId")) AND EXISTS((SELECT "groupId" FROM "userGroup" WHERE "userId" = "user"."groupId"))',
  values: []
}

** For EXISTS / NOT EXISTS, **

o2sql
  .select(['id'])
  .from('user')
  .where(o2sql.e(o2sql.i('age'), '>', 18))
  .toParams();

{
  sql: 'SELECT "id" FROM "user" WHERE "age" > $1',
  values: [18]
}

groupby

groupby(groupby:string|array):Select

o2sql.select(['role', [o2sql.f('count', o2sql.i('id')), 'count']])
  .from('user')
  .groupby(['role'])
  // .groupby('role')
  .toParams();

{
  sql: 'SELECT "role","count"("id") "count" FROM "user" GROUP BY "role"',
  values: []
}

orderby

orderby(order:string|array):Select

o2sql.select(['id', 'name'])
  .from('user')
  .orderby(['id', '-name'])
  // .orderby(['id', ['name', 'DESC']])
  // .orderby('id')
  .toParams();

{
  sql: 'SELECT "id","name" FROM "user" ORDER BY "id" ASC,"name" DESC',
  values: []
}

having

having(having::string|number|array|object):Select

Same as where

paginate, limit and skip

limit(limit:int).skip(skip:int):Select

paginate(page:int, pageSize:int):Select
// equals
limit(pageSize).skip(pageSize * (page - 1))

o2sql
  .select(['id', 'name'])
  .from('user')
  .paginate(2, 10)
  .toParams();

{
  sql: 'SELECT "id","name" FROM "user" LIMIT $1 OFFSET $2',
  values: [ 10, 10 ]
}

union

o2sql
  .select(['id', 'name'])
  .from('dept1')
  .where({ orgId: 5 })
  .union(
    o2sql
    .select(['id', 'name'])
    .from('dept2')
    .where({ orgId: 3 })
  )
  .toParams();

{
  sql: 'SELECT "id","name" FROM "dept1" WHERE "orgId" = $1 UNION ALL SELECT "id","name" FROM "dept2" WHERE "orgId" = $2',
  values: [ 5, 3 ]
}

Get

o2sql.get(columns:array)
  .distinct(distinct:string|array)
  .from(table:string|object)
  .where(where:object)
  .groupby(groupby:string|array)
  .orderby(orderby:string|array)
  .having(having:object)
  .skip(skip:number)

Get inherits from Select, and set limit(1) automatically. There's no limit and union method, others are the same with select.

o2sql.get(['id', 'name'])
  .from('user')
  .toParams();

{
  sql: 'SELECT "id","name" FROM "user" LIMIT $1',
  values: [ 1 ]
}

Count

o2sql.count((table: string)).where((where: object));
o2sql
  .count((columns: array))
  .distinct((distinct: string | array))
  .from((table: string | object))
  .where((where: object))
  .groupby((groupby: string | array))
  .orderby((orderby: string | array))
  .having((having: object));

o2sql
  .count('user')
  .where({
     groupId: 1,
   })
   .toParams();

{
  sql: 'SELECT COUNT(*)::INTEGER AS count FROM "user" WHERE "groupId" = $1',
  values: [ 1 ]
}

o2sql
  .count(['companyId'])
  .from('user')
  .where({
    groupd: 1,
  })
  .distinct()
   .toParams();
// OR
o2sql
  .count('user')
  .select(['companyId'])
  .where({
    groupd: 1,
  })
  .distinct()
   .toParams();

{
  sql: 'SELECT DISTINCT COUNT("companyId")::INTEGER AS count FROM "user" WHERE "groupd" = $1',
  values: [ 1 ]
}

Insert

o2sql.insert(values:object|array)
  .into(table:string);
  .returning(columns:array);

o2sql.insertInto(table:name)
  values(values:object|array)
  .returning(columns:array);

o2sql.insertInto('user')
  .values({
    name: 'Echo',
    age: 35,
  })
  .returning(['id', 'name'])
  .toParams();

{
  sql: 'INSERT INTO "user"("name","age") VALUES ($1,$2) RETURNING "id","name"',
  values: [ 'Echo', 35 ]
}

Update

o2sql
  .update((table: string))
  .set((value: object))
  .where((where: object));

o2sql
  .update('user')
  .set({
    name: 'Echo',
    age: 34,
    count: o2sql.i('count').op('+', 1),
    favs: o2sql.count('userFav').where({
      userId: o2sql.i('user.id'),
    }),
  })
  .where({
    id: 1,
  })
  .toParams();

{
  sql: 'UPDATE "user" SET "name"=$1,"age"=$2,"count"="count" + $3,"favs"=(SELECT COUNT(*)::INTEGER AS count FROM "userFav" WHERE "userId" = "user"."id") WHERE "id" = $4',
  values: [ 'Echo', 34, 1, 1 ]
}

innerJoin, leftJoin, rightJoin, fullJoin also supported.

Delete

o2sql.delete((table: string)).where((where: object));

o2sql.delete('user').where(2).toParams();

{ sql: 'DELETE FROM "user" WHERE "id" = $1', values: [ 2 ] }

Integrate with pg

The easiest way is to use o2sql-pg.

const O2sqlPg = require('o2sql-pg');
const config = {
  user: '** user **',
  host: '** host **',
  database: '** dbname **',
  password: '** pass **',
  port: 5432,
};
const o2sql = O2sqlPg(config);
let rows = await o2sql.select(['id', 'name']).from('user').where(1).execute();

For more details (multi connections, transactions, etc.), please refer to o2sql-pg.

o2sql

Install

Usage

Basic

toParams()

API

identifier / i

function / f

value / v

expr / e

table / t

Select

select

Basic

Multi table

distinct

from

join

on:array

default

Number/String

AND

OR

Other operators

Subquery

Free mode

groupby

orderby

having

paginate, limit and skip

union

Get

Count

Insert

Update

Delete

Integrate with pg

Readme

Keywords

Package Sidebar

Install

Repository

Homepage

DownloadsWeekly Downloads

Version

License

Unpacked Size

Total Files

Last publish

Collaborators

Weekly Downloads