xql
v1.4.12
Published
Extensible and dependency free SQL builder that provides an expression tree and supports multiple database dialects.
Downloads
168
Maintainers
Readme
xql.js
Extensible and dependency free SQL builder and expression tree for node.js.
Disclaimer
This library is used in production, but it doesn't contain all possible features of all available DB engines. It started initially with only PostgreSQL support, but now also MySQL, MSSQL, and SQLite dialects are available.
Be prepared for some minor API changes before the library stabilizes.
Introduction
xql.js is a library designed to build SQL queries programmatically. It provides SQL expression tree that is created by high level API calls which mimic SQL syntax. It's a tool that helps to create the SQL expression tree that can be compiled into a single query string at the end of the building phase. The library has been designed primarily for DAO/DB layers, but use-cases are nearly unlimited.
There are several reasons why xql.js has been developed:
- Full support and focus on PostgreSQL (PG is the primary engine, but xql is getting support for MySQL and SQLite3 as well).
- High performance and low memory footprint, see jsstuff/xql-bench that compares with other engines.
- Schemaless by design, but allows to specify type-mapping so the input data can be properly escaped.
- Control of SQL parameters and the way they are formatted / escaped.
- Construction of SQL query shouldn't require RAW expressions to be written, but it should be easy to use RAW expressions in case they are needed.
- No more legacy JS (xql.js is based on ES6 classes), however, it doesn't dictate you how to write your own code.
There are several node.js libraries that focus on SQL query building, but none has satisfied all the needs. The closest library and huge inspiration for xql.js was Python's SqlAlchemy, which is much more advanced compared to any node.js SQL framework at the moment. However, xql.js is just a query builder that has a type-mapping feature, which is used describe column types for serialization, but they are not used to describe relations or anything else. There are no plans to add ORM support to xql.js in any future release.
To simplify the library design and use cases, xql.js itself doesn't implement any functionality to talk to a real database - is just a query builder. There is another project in preparation that will bridge xql.js with node.js SQL drivers, but since there are so many libraries that can be used (including libraries for SQL connection pooling) there was no real work done to create another library for this purpose yet.
At the beginning, xql.js has been designed to work primarily with PostgreSQL, but other dialects are already in-progress and some code that brings initial support for MySQL and SQLite3 has landed.
Basic Usage
To use xql.js in node.js add "xql"
library to your package.json
and then require("xql")
it. You need to create a context before you compile your expressions:
const xql = require("xql");
// Create your context - context is used to hold database dialect and some
// options. It doesn't hold any intermediate data. It's perfectly fine to
// use one context for all your queries (and it's designed this way).
const ctx = xql.dialect.newContext({ dialect: "pgsql" /* [more options]*/ });
// Create some query.
var query = xql.SELECT("*")
.FROM("cities")
.WHERE("population", ">=", 1000000) // 3 form WHERE.
.WHERE("capital", true); // 2 form WHERE, implicit equality.
// Use context to compile the query.
console.log(query.compileStatement(ctx));
// SELECT * FROM "cities" WHERE "population" >= 1000000 AND "capital" = TRUE;
If you plan to pretty-print your queries for debugging purposes, use pretty
and optionally indentation
(default 2) option:
const xql = require("xql");
const ctx = xql.dialect.newContext({
dialect: "pgsql"
pretty: true
});
var query = xql.SELECT("*")
.FROM("cities")
.WHERE("population", ">=", 1000000)
.WHERE("capital", true);
console.log(query.compileStatement(ctx));
// SELECT
// *
// FROM
// "cities"
// WHERE
// "population" >= 1000000 AND "capital" = TRUE;
If you ask yourself why all SQL constructs are UPPERCASED the explanation is very simple: in the past xql.js supported both conventions (UPPERCASED and camelCased), but it led to confusion and ambiguity. The new API follows a very simple rule: if any function creates a new SQL expression or modifies an existing one based on SQL semantics it's name is always UPPERCASED, otherwise it's camelCased (utility functions, etc). This way it's very simple to visually distinguish between SQL building blocks and other logic in your own code. Please open an issue if you would like to discuss other possibilities.
API Overview
xql.js library consists of several nested namespaces, however, they are rarely used outside of xql
implementation:
Namespace | Description
:-------------------------- | :------------------------------------
xql
| High-level SQL builder interface targeting end-users
xql.error
| Namespace that provides custom errors used by xql.js
xql.misc
| SQL utilities made public, contains also a VERSION
member in a "major.minor.patch"
form
xql.node
| SQL expression tree, contains xql.node.Node
and all nodes that inherit from it
Error classes:
Error | Description
:-------------------------- | :------------------------------------
xql.error.ValueError
| Error thrown if data is wrong
xql.error.CompileError
| Error thrown if query is wrong
Expression tree:
Node | Description
:-------------------------- | :------------------------------------
xql.node.Node
| Base node, all SQL nodes inherit from it, it's safe to use instanceof
operator to check whether an object is a xql.node.Node
xql.node.NodeArray
| Contains array of SQL nodes or values
xql.node.Raw
| Raw SQL expression intended to be used unescaped (the only way to pass something, which will not be escaped)
xql.node.Value
| SQL value base class
xql.node.Identifier
| SQL identifier, like table or column
xql.node.Unary
| SQL unary node (can contain a single child)
xql.node.Binary
| SQL binary node (can contain two children, left and right)
xql.node.Func
| SQL function or aggregate
xql.node.Case
| SQL CASE
construct
xql.node.When
| SQL WHEN
construct
xql.node.Logical
| Logical operator like AND
and OR
, which is based on NodeArray
and can contain more than two expressions
xql.node.ConditionalMap
| Special node that contains key/value interface that can be used to construct WHERE
like expressions without constructing xql.node.Logical
nodes.
xql.node.Join
| SQL JOIN
construct
xql.node.Sort
| SQL ORDER BY
construct
xql.node.With
| Expression representing "identifier" AS (SELECT ...)
part of WITH clause.
xql.node.Statement
| Base class representing a single SQL statement, which should end with semicolon
xql.node.QueryStatement
| Base class used by SELECT
, INSERT
, UPDATE
, and DELETE
statements
xql.node.SelectStatement
| SQL SELECT
statement
xql.node.InsertStatement
| SQL INSERT
statement
xql.node.UpdateStatement
| SQL UPDATE
statement
xql.node.DeleteStatement
| SQL DELETE
statement
xql.node.CompoundStatement
| SQL UNION
, INTERSECT
, and EXCEPT
operators that can be used to combine multiple query statements
High-level SQL builder concepts:
SQL-Builder API | Description
:-------------------------- | :------------------------------------
xql.TABLE(...)
| Create a xql.node.Identifier
wrapping a table name
xql.COLUMN(...)
| Create a xql.node.Identifier
wrapping a column name (in a format "column"
or "table"."column"
or "namespace"."table"."column"
)
xql.COL(...)
| Alias to xql.COLUMN
xql.VALUE(...)
| Create a xql.node.Value
wrapping a value like null
, boolean
, number
, or string
xql.VAL(...)
| Alias to xql.VALUE
.
xql.VALUES(...)
| Create a xql.node.Value
wrapping an array into SQL VALUES
xql.DATE(...)
| Create a xql.node.Value
wrapping a DATE
value
xql.TIME(...)
| Create a xql.node.Value
wrapping a TIME
value
xql.TIMESTAMP(...)
| Create a xql.node.Value
wrapping a TIMESTAMP
value
xql.TIMESTAMPTZ(...)
| Create a xql.node.Value
wrapping a TIMESTAMPTZ
value
xql.INTERVAL(...)
| Create a xql.node.Value
wrapping a INTERVAL
value
xql.ARRAY(...)
| Create a xql.node.Value
wrapping an ARRAY
value
xql.JSON_(...)
| Create a xql.node.Value
wrapping a JSON
value
xql.RAW(s, bindings)
| Create a RAW query xql.node.Raw
node based on query string s
and optional bindings
xql.OP(...)
| Create a xql.node.Unary
or xql.node.Binary
node depending on the count of parameters. The most used form is a 3 operand form, which is used to describe a binary expression. For example OP(COL("salary"), "+", 500).AS("newSalary")
can be used to describe an expression like "salary" + 500 AS "newSalary"
. Please note that AND
and OR
operators should always use xql.node.Logical
as xql.js can construct queries containing multiple AND
and OR
leaves
xql.EQ(a, b)
| Create a xql.node.Binary
node describing a = b
expression
xql.NE(a, b)
| Create a xql.node.Binary
node describing a <> b
expression
xql.LT(a, b)
| Create a xql.node.Binary
node describing a < b
expression
xql.LE(a, b)
| Create a xql.node.Binary
node describing a <= b
expression
xql.GT(a, b)
| Create a xql.node.Binary
node describing a > b
expression
xql.GE(a, b)
| Create a xql.node.Binary
node describing a >= b
expression
xql.IS(a, b)
| Create a xql.node.Binary
node describing a IS b
expression (you can use EQ as well which would detect IS case)
xql.IS_DISTINCT_FROM(a, b)
| Create a xql.node.Binary
node describing a IS DISTINCT FROM b
expression
xql.LIKE(a, b)
| Create a xql.node.Binary
node describing a LIKE b
expression
xql.ILIKE(a, b)
| Create a xql.node.Binary
node describing a ILIKE b
expression
xql.SIMILAR_TO(a, b)
| Create a xql.node.Binary
node describing a SIMILAR TO b
expression
xql.IN(a, b)
| Create a xql.node.Binary
node describing a IN (b)
expression
xql.NOT_IN(a, b)
| Create a xql.node.Binary
node describing a NOT IN (b)
expression
xql.BETWEEN(x, a, b)
| Create a xql.node.Func
node describing x BETWEEN a AND b
expression
xql.NOT_BETWEEN(x, a, b)
| Create a xql.node.Func
node describing x NOT BETWEEN a AND b
expression
xql.FUNCTION_NAME(...)
| Create a xql.node.Func
node describing FUNCTION_NAME(...)
expression. Note that FUNCTION_NAME
has to be replaced by the name of the function to be used, for example xql.SIN(...)
describes SIN()
function and xql.COUNT(...)
describes COUNT()
aggregate
xql.AND(...)
| Create a xql.node.Logical
expression describing AND
expression
xql.OR(...)
| Create a xql.node.Logical
expression describing OR
expression
xql.SELECT(...)
| Create a xql.node.SelectStatement
and pass optional arguments to the SelectStatement.FIELD(...)
method
xql.INSERT(...)
| Create a xql.node.InsertStatement
and use an optional first argument as a table name (FROM
clause) if it's a string or an identifier, and pass all other arguments to SelectStatement.FIELD(...)
method
xql.UPDATE(...)
| Create a xql.node.UpdateStatement
and use an optional first argument as a table name (UPDATE ...
clause) if it's a string or an identifier, and pass all other arguments to UpdateStatement.FIELD(...)
method
xql.DELETE(...)
| Create a xql.node.DeleteStatement
and use an optional first argument as a table name
xql.EXCEPT(...)
| Create a xql.node.CompoundStatement
describing EXCEPT
expression
xql.EXCEPT_ALL(...)
| Create a xql.node.CompoundStatement
describing EXCEPT ALL
query
xql.INTERSECT(...)
| Create a xql.node.CompoundStatement
describing INTERSECT
query
xql.INTERSECT_ALL(...)
| Create a xql.node.CompoundStatement
describing INTERSECT ALL
query
xql.UNION(...)
| Create a xql.node.CompoundStatement
describing UNION
query
xql.UNION_ALL(...)
| Create a xql.node.CompoundStatement
describing UNION ALL
query
xql.SORT(c, sort, nulls)
| Create a xql.node.Sort
node wrapping an ORDER BY
clause
Generic Interface
Since every node that is used to describe various constructs inherits directly or indirectly from xql.node.Node
all nodes share a common interface:
xql.node.Node | Description
:------------------------- | :------------------------------------
.getType()
| Get the node type {String}. For example a xql.node.SelectStatement
is a SELECT
type, logical operator is AND
or OR
type, etc...
.setType(type)
| Set the node type (used internally)
.getLabel()
| Get the node label that is rendered as AS "label"
in SQL
.setLabel(label)
| Set the node label
.canExecute()
| Can be used to check whether the node can be executed by SQL engine. Only SELECT
, INSERT
, UPDATE
, and DELETE
queries and UNION
, INTERSECT
, and EXCEPT
operators can be executed.
.compileNode(ctx)
| Compile the node into a string. The ctx
argument is currently not used, but it's designed in a way to pass an additional information to the compiler so multiple dialects can be used in the future.
.compileStatement(ctx?)
| Compile the query, it's basically a compileNode()
call with semicolon ";"
at the end. This method should be used to return the query to be executed by your DB engine. It's provided by all query nodes.
.AS(label)
| Alias to setLabel()
.
.EQ(b)
| Returns this = b
expression.
.NE(b)
| Returns this <> b
expression.
.LT(b)
| Returns this < b
expression.
.LE(b)
| Returns this <= b
expression.
.GT(b)
| Returns this > b
expression.
.GE(b)
| Returns this >= b
expression.
.IN(b)
| Returns this IN b
expression.
.NOT_IN(b)
| Returns this NOT IN b
expression.
For example COL("a").EQ(1)
yields the same tree as OP(COL("a"), "=", 1)
The xql.node.Unary
interface:
xql.node.Unary | Description
:------------------------- | :------------------------------------
.getValue()
| Get the child node or value
.setValue(value)
| Set the child node or value
The xql.node.Binary
interface:
xql.node.Binary | Description
:------------------------- | :------------------------------------
.getLeft()
| Get the left node or value
.setLeft(left)
| Set the left node or value
.getRight()
| Get the right node or value
.setRight(right)
| Set the right node or value
.addLeft(left)
|
.addRight(right)
| Helpers, can only be used if the target value is an array, in such case the value left
or right
is pushed into it.
SELECT
Select query is described by xql.node.SelectStatement
node and wrapped by xql.SELECT(...)
. It accepts arguments that are passed to the FIELD()
method making the SELECT(...)
, SELECT([...])
and SELECT().FIELD(...)
constructs equivalent.
The xql.node.SelectStatement
implements the following interface:
xql.node.SelectStatement | Description
:------------------------- | :------------------------------------
.FIELD(...)
|
.FIELD([...])
| Add a field or expression to be selected. It accepts a xql.node.Node
, column name, or a dictionary defining columns and their expressions. The FIELD()
calls are usually chained. For example FIELD("a").FIELD("b")
calls are the same as FIELD("a", "b")
, FIELD(["a", "b"])
, and FIELD({ a: true, b: true })
.DISTINCT(...)
| Add a DISTINCT
or DISTINCT ON
(if arguments are provided) clause to the query.
.FROM(...)
|
.FROM([...])
| Add FROM
clause to the query. The method accepts multiple arguments or a list of arguments. Most of the time FROM
is used with a single argument describing the table to select from, however, multiple arguments forming an implicit CROSS JOIN
construct, which matches the SQL specification, are allowed. For example FROM(a)
construct will generate SELECT ... FROM "a"
query, while FROM(a, b)
construct will generate SELECT ... FROM "a", "b"
or SELECT ... FROM "a" CROSS JOIN "b"
(these are equivalent, xql.js can generate any of these depending on the version and implementation changes)
.CROSS_JOIN(with, cond)
|
.INNER_JOIN(...)
|
.LEFT_JOIN(...)
|
.RIGHT_JOIN(...)
|
.FULL_JOIN(...)
| Add a JOIN
clause to the query. Joins always join the current query with a new table. For example FROM("a").INNER_JOIN("b").LEFT_JOIN("c")
construct will generate SELECT ... FROM "a" INNER JOIN "b" LEFT OUTER JOIN "c"
query
.WHERE(node)
|
.WHERE(a, b)
|
.WHERE(a, op, b)
| Add a WHERE
clause node
, WHERE a = b
, or WHERE a op b
to the query (implicitly AND
ed with other WHERE
clauses if present)
.OR_WHERE(node)
|
.OR_WHERE(a, b)
|
.OR_WHERE(a, op, b)
| Add a WHERE
clause node
, WHERE a = b
, or WHERE a op b
to the query (implicitly OR
ed with other WHERE
clauses if present)
.GROUP_BY(...)
|
.GROUP_BY([...])
| Add a GROUP BY
clause to the query. Group by can be specified as a column or a xql.node.Node
.HAVING(node)
|
.HAVING(a, b)
|
.HAVING(a, op, b)
| Add a HAVING
clause node
, HAVING a = b
, or HAVING a op b
to the query (implicitly AND
ed with other HAVING
clauses if present)
.OR_HAVING(node)
|
.OR_HAVING(a, b)
|
.OR_HAVING(a, op, b)
| Add a HAVING
clause node
, HAVING a = b
, or HAVING a op b
to the query (implicitly OR
ed with other HAVING
clauses if present)
.ORDER_BY(col, dir, nulls)
| Add an ORDER BY
expression of the form "col" [ASC/DESC] [NULLS FIRST/LAST]
. If col
is an array the builder will insert multiple sort clauses with the same dir
and nulls
order
.OFFSET(offset)
| Add an OFFSET
clause to the query
.LIMIT(limit)
| Add a LIMIT
clause to the query
Sample SQL selects:
Complex SQL selects are possible by combining various SQL expressions together:
var query = SELECT()
.FIELD("name")
.FIELD(
SELECT(MAX(COL("pop")))
.FROM("cities")
.WHERE(COL("cities.state"), "=", COL("states.name"))
.AS("population"))
.FROM("states");
yields to:
SELECT
"name",
(SELECT MAX("pop") FROM "cities" WHERE "cities"."state" = "states"."name")
FROM
"states";
INSERT
Insert query is described by xql.node.InsertStatement
node and wrapped by xql.INSERT(...)
. Note that INSERT(...)
accepts parameters that can describe a target table and data to be inserted.
The xql.node.InsertStatement
implements the following interface:
xql.node.InsertStatement | Description
:------------------------- | :------------------------------------
.TABLE(table)
|
.INTO(table)
| Specify a target table
.VALUES(data)
| Specify a data to be inserted. The data
argument can be both array or object. If an array is passed each element describes one row (it has to be array of objects), of an object is passed, it describes only one row. If VALUES()
is called multiple times it pushes more rows to be inserted by the query
.RETURNING(...)
| Specify a RETURNING
clause, uses the same syntax as SELECT()
Sample SQL insert:
// INSERT("tasks", {...}).RETURNING(...) would also work.
var query = INSERT()
.INTO("tasks")
.VALUES({
title: "Try xql.js",
duration: 5
})
.RETURNING("id");
yields to:
INSERT INTO
"tasks" ("title", "duration")
VALUES
('Try xql.js', 5)
RETURNING
"id";
UPDATE
Update query is described by xql.node.UpdateStatement
node and wrapped by xql.UPDATE(...)
. Please note that UPDATE(...)
accepts parameters that can describe a target table and data to be updated.
The xql.node.UpdateStatement
implements the following interface:
xql.node.UpdateStatement | Description
:------------------------- | :------------------------------------
.TABLE(table)
| Specify a target table
.FROM(...)
| Specify a FROM
clause, uses the same syntax as FROM()
defined by SELECT
query
.WHERE(node)
|
.WHERE(a, b)
|
.WHERE(a, op, b)
| Add a WHERE
clause node
, WHERE a = b
, or WHERE a op b
to the query (implicitly AND
ed with other WHERE
clauses if present)
.OR_WHERE(node)
|
.OR_WHERE(a, b)
|
.OR_WHERE(a, op, b)
| Add a WHERE
clause node
, WHERE a = b
, or WHERE a op b
to the query (implicitly OR
ed with other WHERE
clauses if present)
.RETURNING(...)
| Specify a RETURNING
clause, uses the same syntax as FIELD()
defined by SELECT
query
Sample SQL update:
var query = UPDATE("users")
.VALUES({
address: "Friedrichstrasse 50, Berlin",
addressChanged: OP(COL("addressChanged"), "+", 1)
})
.WHERE("userId", "=", 1);
yields to:
UPDATE
"users"
SET
"address" = 'Friedrichstrasse 50, Berlin',
"addressChanged" = "addressChanged" + 1
WHERE
"userId" = 1;
DELETE
Delete query is described by xql.node.DeleteStatement
node and wrapped by xql.DELETE(...)
.
The xql.node.DeleteStatement
implements the following interface:
xql.node.DeleteStatement | Description
:------------------------- | :------------------------------------
.TABLE(table)
|
.FROM(table)
| Specify a target table
.USING(...)
| Specify a USING
clause, uses the same syntax as FROM()
defined by SELECT
query
.WHERE(node)
|
.WHERE(a, b)
|
.WHERE(a, op, b)
| Add a WHERE
clause node
, WHERE a = b
, or WHERE a op b
to the query (implicitly AND
ed with other WHERE
clauses if present)
.OR_WHERE(node)
|
.OR_WHERE(a, b)
|
.OR_WHERE(a, op, b)
| Add a WHERE
clause node
, WHERE a = b
, or WHERE a op b
to the query (implicitly OR
ed with other WHERE
clauses if present)
.RETURNING(...)
| Specify a RETURNING
clause, uses the same syntax as FIELD()
defined by SELECT
query.
Sample SQL delete:
var query = DELETE().FROM("tasks").WHERE("completed", "=", true)
yields to:
DELETE FROM "tasks" WHERE "completed" = TRUE;
Type Mapping
xql.js has a feature called TypeMapping
, which allows to override a default serialization of data used by INSERT
and UPDATE
. The type mapping is an object where a key/value defines a column/data-type pair. It can be set by setTypeMapping()
and get by getTypeMapping()
methods of the query object.
The following example illustrates how type mapping may affect data serialization:
var typeMapping = {
tagsArray: "ARRAY",
tagsJson : "JSON"
};
var query = UPDATE("users")
.VALUES({
tagsArray : ["accounting", "customer support"],
tagsJson : ["accounting", "customer support"]
})
.WHERE("userId", "=", 1)
.setTypeMapping(typeMapping);
UPDATE
"users"
SET
"tagsArray" = ARRAY['accounting', 'customer support'], -- Using PG ARRAY syntax.
"tagsJson" = '["accounting", "customer support"]'::json -- Using PG JSON syntax.
WHERE
"userId" = 1;
More Examples
There is a project called xql-fiddle, which can be used to explore xql.js possibilities by playing with it online. It contains more snippets and tries to teach by examples.