❤
npm
Sign UpSign In

o2sql

4.0.20 • Public • Published

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.

Versions

Current Tags

VersionDownloads (Last 7 Days)Tag
4.0.201latest

Version History

VersionDownloads (Last 7 Days)Published
4.0.201
4.0.191
4.0.181
4.0.171
4.0.161
4.0.151
4.0.141
4.0.131
4.0.121
4.0.111
4.0.101
4.0.91
4.0.81
4.0.71
4.0.61
4.0.51
4.0.41
4.0.31
4.0.21
4.0.11
4.0.01
3.0.51
3.0.41
3.0.31
3.0.22
3.0.11
3.0.01
2.0.81
2.0.72
2.0.61
2.0.51
2.0.41
2.0.31
2.0.21
2.0.11
2.0.01
1.0.271
1.0.261
1.0.251
1.0.241
1.0.231
1.0.221
1.0.211
1.0.201
1.0.191
1.0.181
1.0.171
1.0.161
1.0.151
1.0.141
1.0.131
1.0.121
1.0.111
1.0.101
1.0.81
1.0.71
1.0.61
1.0.51
1.0.41
1.0.31
1.0.21
1.0.11
1.0.01

Package Sidebar

Install

npm i o2sql

Repository

github.com/qdechochen/o2sql

Homepage

github.com/qdechochen/o2sql#readme

Weekly Downloads

65

Version

4.0.20

License

ISC

Unpacked Size

60.1 kB

Total Files

6

Last publish

Collaborators

  • echochen
Try on RunKit
Report malware