Sequel.js - AST

@sequeljs/ast is a SQL AST manager for JavaScript.

Installation

npm install --save @sequeljs/ast
Copy

A Gentle Introduction

Generating a query with Sequel.js AST is simple. For example, in order to produce

SELECT * FROM "users"
Copy

you construct a table relation and convert it to SQL:

let users = new Table('users')
users = users.project('*')
users.toSQL()
Copy

More Sophisticated Queries

Here is a whirlwind tour through the most common SQL operators. These will probably cover 80% of all interaction with the database.

First is the 'restriction' operator, where:

users.project('*').where(users.get('name').eq('amy'))
// SELECT * FROM "users" WHERE "users"."name" = 'amy'
Copy

What would, in SQL, be part of the SELECT clause is called in Sequel.js AST a projection:

users.project(users.get('id'))
// SELECT "users"."id" FROM "users"
Copy

Comparison operators =, !=, <, >, <=, >=, IN:

users.where(users.get('age').eq(10)).project('*')
// SELECT * FROM "users" WHERE "users"."age" = 10

users.where(users.get('age').notEq(10)).project('*')
// SELECT * FROM "users" WHERE "users"."age" != 10

users.where(users.get('age').lt(10)).project('*')
// SELECT * FROM "users" WHERE "users"."age" < 10

users.where(users.get('age').gt(10)).project('*')
// SELECT * FROM "users" WHERE "users"."age" > 10

users.where(users.get('age').lteq(10)).project('*')
// SELECT * FROM "users" WHERE "users"."age" <= 10

users.where(users.get('age').gteq(10)).project('*')
// SELECT * FROM "users" WHERE "users"."age" >= 10

users.where(users.get('age').inVal([20, 16, 17])).project('*')
// SELECT * FROM "users" WHERE "users"."age" IN (20, 16, 17)
Copy

Bitwise operators &, |, ^, <<, >>:

users.where((users.get('bitmap').bitwiseAnd(16)).gt(0)).project('*')
// SELECT * FROM "users" WHERE ("users"."bitmap" & 16) > 0

users.where((users.get('bitmap').bitwiseOr(16)).gt(0)).project('*')
// SELECT * FROM "users" WHERE ("users"."bitmap" | 16) > 0

users.where((users.get('bitmap').bitwiseXor(16)).gt(0)).project('*')
// SELECT * FROM "users" WHERE ("users"."bitmap" ^ 16) > 0

users.where((users.get('bitmap').bitwiseShiftLeft(1)).gt(0)).project('*')
// SELECT * FROM "users" WHERE ("users"."bitmap" << 1) > 0

users.where((users.get('bitmap').bitwiseShiftRight(1)).gt(0)).project('*')
// SELECT * FROM "users" WHERE ("users"."bitmap" >> 1) > 0

users.where((users.get('bitmap').bitwiseNot()).gt(0)).project('*')
// SELECT * FROM "users" WHERE  ~ "users"."bitmap" > 0
Copy

Joins resemble SQL strongly:

users.join(photos).on(users.get('id').eq(photos.get('user_id'))).project('*')
// SELECT * FROM "users" INNER JOIN "photos" ON "users"."id" = "photos"."user_id"
Copy

Left joins:

users.join(photos, OuterJoin).on(users.get('id').eq(photos.get('user_id'))).project('*')
// SELECT * FROM "users" LEFT OUTER JOIN "photos" ON "users"."id" = "photos"."user_id"
Copy

What are called LIMIT and OFFSET in SQL are called take and skip in Sequel.js AST:

users.take(5).project('*') // SSELECT * FROM "users" LIMIT 5
users.skip(4).project('*') // SELECT * FROM "users" OFFSET 4
Copy

GROUP BY is called group:

users.project(users.get('name')).group(users.get('name'))
// SELECT "users"."name" FROM "users" GROUP BY "users"."name"
Copy

The best property of Sequel.js AST is its "composability," or closure under all operations. For example, to restrict AND project, just "chain" the method invocations:

users
  .where(users.get('name').eq('amy'))
  .project(users.get('id'))
// SELECT "users"."id" FROM "users" WHERE "users"."name" = 'amy'
Copy

All operators are chainable in this way, and they are chainable any number of times, in any order.

users.where(users.get('name').eq('bob')).where(users.get('age').lt(25))
Copy

The OR operator works like this:

users.where(users.get('name').eq('bob').or(users.get('age').lt(25)))
Copy

The AND operator behaves similarly. Here is an example of the DISTINCT operator:

const posts = new Table('posts')
posts.project(posts.get('title')).distinct()
posts.toSQL() // SELECT DISTINCT "posts"."title" FROM "posts"
Copy

Aggregate functions AVG, SUM, COUNT, MIN, MAX, HAVING:

photos.group(photos.get('user_id')).having(photos.get('id').count().gt(5))
// SELECT FROM "photos" GROUP BY "photos"."user_id" HAVING COUNT("photos"."id") > 5

users.project(users.get('age').sum())
// SELECT SUM("users"."age") FROM "users"

users.project(users.get('age').average())
// SELECT AVG("users"."age") FROM "users"

users.project(users.get('age').maximum())
// SELECT MAX("users"."age") FROM "users"

users.project(users.get('age').minimum())
// SELECT MIN("users"."age") FROM "users"

users.project(users.get('age').count())
// SELECT COUNT("users"."age") FROM "users"
Copy

Aliasing Aggregate Functions:

users.project(users.get('age').average().as("mean_age"))
// SELECT AVG("users"."age") AS mean_age FROM "users"
Copy

The Advanced Features

The examples above are fairly simple and other libraries match or come close to matching the expressiveness of Sequel.js AST.

Inline math operations

Suppose we have a table products with prices in different currencies. And we have a table currency_rates, of constantly changing currency rates. In Sequel.js AST:

products = new Table('products')
// Attributes: ['id', 'name', 'price', 'currency_id']

currency_rates = new Table('currency_rates')
// Attributes: ['from_id', 'to_id', 'date', 'rate']
Copy

Now, to order products by price in user preferred currency simply call:

products
  .join('currency_rates')
  .on(products.get('currency_id').eq(currencyRates.get('from_id')))
  .where(currencyRates.get('to_id').eq(userPreferredCurrency))
  .where(currencyRates.get('date').eq(new Date()))
  .order(products.get('price').multiply(currencyRates.get('rate')))
Copy

Complex Joins

Alias

Where Sequel.js AST really shines is in its ability to handle complex joins and aggregations. As a first example, let's consider an "adjacency list", a tree represented in a table. Suppose we have a table comments, representing a threaded discussion:

comments = new Table('comments')
Copy

And this table has the following attributes:

// ['id', 'body', 'parent_id']
Copy

The parent_id column is a foreign key from the comments table to itself. Joining a table to itself requires aliasing in SQL. This aliasing can be handled from Sequel.js AST as below:

replies = comments.alias()
commentsWithReplies = comments
  .join(replies)
  .on(replies.get('parent_id').eq(comments.get('id')))
  .where(comments.get('id').eq(1))
  .project('*')

// SELECT * FROM "comments" INNER JOIN "comments" "comments_2"
// ON "comments_2"."parent_id" = "comments"."id"
// WHERE "comments"."id" = 1
Copy

This will return the reply for the first comment.

CTE

Common Table Expressions (CTE) support via:

Create a CTE

cteTable = new Table('cte_table')
composedCte = new Nodes.As(cteTable, photos.where(photos.get('created_at').gt(new Date('2021-01-01'))))
Copy

Use the created CTE:

users
  .join(cteTable)
  .on(users.get('id').eq(cteTable.get('user_id')))
  .project(users.get('id'), cteTable.get('click').sum())
  .with(composedCte)

// WITH "cte_table" AS (SELECT FROM "photos" WHERE "photos"."created_at" > '2021-01-01 00:00:00')
// SELECT "users"."id", SUM("cte_table"."click")
// FROM "users" INNER JOIN "cte_table" ON "users"."id" = "cte_table"."user_id"
Copy

Write SQL strings

When your query is too complex for Sequel.js AST, you can use Nodes.SQLLiteral:

photoClicks = new Nodes.SQLLiteral(`
  CASE WHEN condition1 THEN calculation1
  WHEN condition2 THEN calculation2
  WHEN condition3 THEN calculation3
  ELSE default_calculation END
  `)
photos.project(photoClicks.as("photo_clicks"))

// SELECT
// CASE WHEN condition1 THEN calculation1
// WHEN condition2 THEN calculation2
// WHEN condition3 THEN calculation3
// ELSE default_calculation END
//   AS photo_clicks FROM "photos"
Copy

Contributing

Feel free to contribute with this library. For more information, take a look at our contributing guide.

License

MIT

Inspiration

@sequeljs/ast is basically a rewrite of Rails' Arel library in TypeScript.