npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

sql-dialect

v5.0.1

Published

SQL Builder

Downloads

131

Readme

sql-dialect

Build Status Coverage Status

This library provides query builders independent of any particular database connection library.

Install

npm install sql-dialect

Main Features

  • Supports MySQL, PostgreSQL and Sqlite
  • Uses the prefix notation for building queries
  • Supports SELECT, UPDATE, INSERT and DELETE
  • Supports CREATE TABLE and DROP TABLE

Documentation

Dialect Classes

Dialect classes are used to generate SQL queries. The available Dialect classes are:

  • sql/dialect/MySql: MySql dialect.
  • sql/dialect/PostgreSql: PostgreSql dialect.
  • sql/dialect/Sqlite: Sqlite dialect.
  • sql/Dialect: the generic SQL one

Let's start by instantiating a MySql dialect class:

var SQL = require('sql-dialect');

var dialect = new SQL.MySql();

Once instantiated, it's possible to use the dialect's .statement() method to create query instances like in the following:

var select = dialect.statement('select');
var insert = dialect.statement('insert');
var update = dialect.statement('update');
var delete = dialect.statement('delete');

var createTable = dialect.statement('create table');
var dropTable = dialect.statement('drop table');

Then to generate the corresponding SQL, we need to use either toString():

select.from('mytable');
console.log(select.toString()); // SELECT * FROM "mytable"
console.log(String(select));    // SELECT * FROM "mytable"

Since the SQL generated is independent of any particular database connection library so you can any other database connection library of your choice, to execute the query.

Quoting

By default string values are automatically quoted, however some database connections provide their own built-in quoting method. If you are not using PDO you will probably want to override the default quoting handler. To do so, you'ss need to inject your handler to the dialect instance like in the following:

var pg = require('pg-promise')();
var SQL = require('sql-dialect');

var connection = pg({user: 'postgres', password: 'password', database: 'mytable'});
var dialect = new SQL.PostgreSql({ quoter: function(string) {
    return pgp.as.text(string);
} });

Note: to avoid SQL injections, table/field names are also escaped by default.

SELECT

Example of SELECT query:

select
  .distinct()                   // SELECT DISTINCT
  .fields([
    'id',                       // a field name
    { fielname: 'alias' }       // a aliased field name
  ])
  .from('table')                // FROM
  .join(                        // JOIN
    'other',                    // a table name
    [{ 'other.table_id': {      // join conditions, (more information on
      ':name': 'table.id'       // { ':name': ... } in the Prefix Notation section)
    } }],
    'LEFT'                      // type of join
  )
  .where([                      // WHERE `fieldname` === 'value'
    { fielname: 'value' }
  ])
  .group('foo')                 // GROUP BY
  .having([                     // HAVING `fieldname` === 'value'
    { fielname: 'value' }
  ])
  .order('bar')                 // ORDER BY
  .limit(10, 40)                // LIMIT AND OFFSET AS SECOND PARAMETER
  .forUpdate()                  // FOR UPDATE

Prefix Notation (or polish notation)

To be able to write complex SQL queries, the prefix notation has been choosed instead of relying on an exhaustive API with a lot of methods (i.e. methods like orWhere, andWhere, whereNull(), etc.) which generally ends up to have missing methods anyway.

Infix notation is the most populare arithmetical notation. It's characterized by the placement of operators between operands (e.g. 3 + 4). With the prefix notation, the operator is placed to the left of their operands (e.g. + 3 4).

For a developper this notation is pretty intuitive since it's very similar to how functions are defined. + is in a way the function name and 3 and 4 are the arguments.

So to be able to build complex queries without being limitated by the API, this library allows prefix notation like in the following:

select.fields({ '*': [
  { '+': [1, 2] }, 3
] });
console.log(select.toString());            // SELECT 1 + 2 * 3

select.fields({ '*': [
  { ':()': { '+': [1, 2] } }, 3
] });
console.log(select.toString());            // SELECT (1 + 2) * 3

You probably had to read the example twice. But if it looks quite confusing at a first glance, prefix notation has a couple of advantages:*

  • it's can represent any kind of expression by definition.
  • it's not SQL dedicated and can be used in a higher level of abstraction.
  • it's simpler to deal with programmatically than parsing/unparsing SQL strings, since it's a mathematical abstraction.

Note: all named operators are prefixed by a colon : (e.g :or, :and, :like, ':in', etc.). However mathematical symbol like +, -, <=, etc. doesn't requires a colon.

Formatters

Formatters are used to deal with the three different types of values present in SQL:

  1. the table/field names which need to be escaped.
  2. the values which needs to be quotes like string values.
  3. the plain expressions.

So, to be able to choose the formatting, the following formatters has been introduced:

  • ':name': escapes a table/field names.
  • ':value': quotes string values.
  • ':plain': doesn't do anything (warning: ':plain' is subject to SQL injection)

Since most of queries relies on the following kind of condition: field = value, you don't need to specify the formatting everywhere. For example you can simply write your select conditions like the following:

select.from('table').where([
  { field1: 'value1' },
  { field2: 'value2' }
]);
console.log(select.toString());
// SELECT * FROM `table` WHERE `field1` = 'value1' AND `field2` = 'value2'

which can be rewrited as:

select.from('table').where([
  { field: { ':value': 'value' }
]);

which can also be rewrited as:

select.from('table').where([
    { '=': [{ ':name': 'field' }, { ':value': 'value' }] }
]);

So most of the time the { field: 'value' } syntax will fit prefectly well to your need. However if you wan't to make a field1 = field2 condition where both part must be escaped, the prefix notation can save your day:

select.from('table').where([
  { field1: { ':name': 'field2' }
]);

Common Operators

Bellow an exhaustive list of common operators which work for both MySQL and PostgreSQL:

  • '='
  • '<=>'
  • '<'
  • '>'
  • '<='
  • '>='
  • '!='
  • '<>'
  • '-'
  • '+'
  • '*'
  • '/'
  • '%'
  • '>>'
  • '<<'
  • ':='
  • '&'
  • '|'
  • ':mod'
  • ':div'
  • ':like'
  • ':not like'
  • ':is'
  • ':is not'
  • ':distinct'
  • '~'
  • ':between'
  • ':not between'
  • ':in'
  • ':not in'
  • ':exists'
  • ':not exists'
  • ':all'
  • ':any'
  • ':some'
  • ':as'
  • ':not'
  • ':and'
  • ':or'
  • ':xor'
  • '()'

It's also possible to use some "free" operators. All used operators which are not present in the list above will be considered as SQL functions like :concat, :sum, :min, :max, etc. and will be generated as FUNCTION(...).

For example:

select
  .fields([
    {':sum()': [{':name': 'x'}, 2])
  ])
  .from('table')

// SELECT SUM("x", 2) from "table"

MySQL Dedicated Operators

  • '#'
  • ':regex'
  • ':rlike'
  • ':sounds like'
  • ':union'
  • ':union all'
  • ':minus'
  • ':except'

PostgreSQL Dedicated Operators

  • ':regex'
  • ':regexi'
  • ':not regex'
  • ':not regexi'
  • ':similar to'
  • ':not similar to'
  • ':square root'
  • ':cube root'
  • ':fact'
  • '|/'
  • '||/'
  • '!!'
  • ':concat'
  • ':pow'
  • '#'
  • '@'
  • '<@'
  • '@>'
  • ':union'
  • ':union all'
  • ':except'
  • ':except all'
  • ':intersect'
  • ':intersect all'

Custom Dedicated Operators

It's also possible to create your own operators with handlers to build them.

Example:

var dialect = new SQL.PostgreSql({
  builders: {
    braces: function ($operator, $parts) {
      return "{" . array_shift($parts)  ."}";
    }
  },
  operators: {
    '{}': { builder: 'braces' }
    // Note: { format: '{%s}' } would also be enough here.
  }
});

var select = dialect.statement('select');
select.fields([{ '{}': [1] }]); // SELECT {1}
]]);

The example above allows to use '{}' as operator and provides the following formatting '{%s}'.

Subqueries

To use a subquery inside another query or doing some algebraic operations on queries (i.e. UNION, INTERSECT, etc.), you can simply mix them together:

Example of JOIN on a subquery:

var subquery = dialect.statement('select')
subquery.from('table2').alias('t2');

select.from('table').join($subquery);

select.toString();
// SELECT * FROM "table" LEFT JOIN (SELECT * FROM "table2") AS "t2"

Example of UNION query:

var select1 = dialect.statement('select').from('table1');
var select2 = dialect.statement('select').from('table2');

dialect.conditions([
  { ':union': [select1, select2] }
]);
// SELECT * FROM `table1` UNION SELECT * FROM `table2`

INSERT

Example of INSERT query:

var insert = dialect.statement('insert');

insert.into('table')               // INTO
      .values([                    // (field1, ...) VALUES (value1, ...)"
        { field1: 'value1', field2: 'value2', anotherField: 'value3' }
      );

The values() method allows you to pass an object of key-value pairs where the key is the field name and value the field value.

Common Table Expressions

To use common table expressions, you use the with function on any statement instance (INSERT, UPDATE, DELETE SELECT). Pass an object mapping key names to any other query


var update = dialect
  .statement('update')
  .table('foo')
  .values([
    { field1: 'value1' },
    { field2: 'value2' }
  ])
  .where({ id: 123 })
  .returning(['id']);

select
  .with({foo_cte: update})
  .from('table')
  .join('foo', [
    {'foo_cte.id': {':name': 'table.foo_id'}}
  ], 'LEFT');

select.toString();
// WITH foo_cte AS (
//   UPDATE foo
//   SET
//      "field1" = 'value1',
//      "field2" = 'value2'
//    WHERE "id" = '123'
//    RETURNING id
//  )
// SELECT *
// FROM "table"
//  LEFT JOIN "foo_cte"."id" ON "foo_cte"."id" = "table"."foo_id"

UPDATE

Example of UPDATE query:

var update = dialect.statement('update');

$update.table('table')              // TABLE
       .values([                    // (field1, ...) VALUES (value1, ...)"
         { field1: 'value1' },
         { field2: 'value2' }
       ])
       .where({ id: 123 });         // WHERE

The values() method allows you to pass an array of key-value pairs where the key is the field name and value the field value.

DELETE

Example of DELETE query:

var delete = dialect.statement('delete');

delete.from('table')                 // FROM
      .where({ id: 123 });           // WHERE

CREATE TABLE

Example of CREATE TABLE query:

var createTable = dialect.statement('create table');
createTable
    .table('table')                 // TABLE
    .columns([])                    // columns definition
    .meta([])                       // table meta definition
    .constraints([]);               // constraints definition

Bellow an example of a MySQL table creation:

var createTable = dialect.statement('create table');
createTable
    .table('table')
    .columns([
        { id: { type: 'serial' } },
        { table_id: { type: 'integer' } },
        { published: {
          { type: 'datetime' },
          { null: false },
          { 'default': { ':plain': 'CURRENT_TIMESTAMP' } }
        },
        { decimal: {
          type: 'float',
          length: 10,
          precision: 2
        } },
        { integer: {
          type: 'integer',
          use: 'numeric',
          length: 10,
          precision: 2
        } },
        { date: {
          type: 'date',
          null: false,
        } },
        { text: {
          type: 'text',
          null: false,
        } }
    ])
    .meta({
      charset: 'utf8',
      collate: 'utf8_unicode_ci',
      engine: 'InnoDB'
    })
    .constraints([
      {
        type: 'check',
        expr: {
          'integer': { '<': 10 }
        }
      },
      {
        type: 'foreign key',
        foreignKey: 'table_id',
        to: 'other_table',
        primaryKey: 'id',
        on: 'DELETE NO ACTION'
      }
    ]);

this.create.toString();
// CREATE TABLE `table` (
// `id` int NOT NULL AUTO_INCREMENT,
// `table_id` int,
// `published` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
// `decimal` decimal(10,2),
// `integer` numeric(10,2),
// `date` date NOT NULL,
// `text` text NOT NULL,
// CHECK (`integer` < 10),
// FOREIGN KEY (`table_id`) REFERENCES `other_table` (`id`) ON DELETE NO ACTION,
// PRIMARY KEY (`id`))
// DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci ENGINE InnoDB

Abstract types

Databases uses different naming conventions for types which can be missleading. To be the most generic possible, columns definition can be done using some abstract 'type' definitions. Out of the box the following types are supported:

  • 'id': foreign key ID
  • 'serial': autoincremented serial primary key
  • 'string': string value
  • 'text': text value
  • 'integer': integer value
  • 'boolean': boolean value
  • 'float': foat value
  • 'decimal': decimal value with 2 decimal places
  • 'date': date value
  • 'time': time value
  • 'datetime': datetime value
  • 'binary': binary value

For example with MySQL the 'serial' type will generate the following query:

var createTable = dialect.statement('create table');
createTable.table('table')
           .columns([
             { id:  { type: 'serial' } }
           ]);

this.create.toString();
// CREATE TABLE `table` (`id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`))

And PostgreSQL will generate:

var createTable = dialect.statement('create table');
createTable.table('table')
           .columns([
             { id:  { type: 'serial' } }
           ]);

this.create.toString();
// CREATE TABLE "table" ("id" serial NOT NULL, PRIMARY KEY ("id"))

However it's possible to add your own abstract types. For example to make 'uuid' to stand for char(30) columns, we can write:

var dialect = new SQL.MySql();
dialect.type('uuid', { use: 'char', length: 30 });

If you don't want to deal with abstract types you can use directly 'use' instead of 'type' to define a column:

var createTable = dialect.statement('create table');
createTable.table('table')
           .columns([
             { id: { type: 'serial' } },
             { data: { use: 'blob' } }
           ]);

Abstract types autodetection

When you are using abstract types, it would be interesting to be able to map databases type to their corresponding abstract type.

Example:

dialect.map('tinyint', 'boolean', { length: 1 });
dialect.map('tinyint', 'integer');

dialect.mapped('tinyint'); // integer
dialect.mapped({           // boolean
  use: 'tinyint'
  length: 1
});

Note: for databases like SQLite this won't help much since types are not discriminative enough but this feature can be useful for PostgreSQL or MySQL.

DROP TABLE

Example of DROP TABLE query:

var dropTable = dialect.statement('drop table');

dropTable.table('table');         // TABLE

Testing

The spec suite can be runned with:

cd sql-dialect
npm install
npm test