Squilt

A lightweight, zero-dependency TypeScript library for building SQL queries through a fluent AST API. Constructs type-safe query objects and serializes them to clean SQL strings—no database interaction, just pure query building for SQLite (and beyond).

Installation

npm install @shaxpir/squilt

Usage

Using the Shorthand API

The shorthand API provides a concise, SQL-like syntax for building queries:

import { SELECT, FROM, COLUMN, EQ, JOIN, ORDER_BY } from '@shaxpir/squilt';
import { OrderByDirection } from '@shaxpir/squilt';

const query = SELECT(
  FROM('users').as('u'),
  COLUMN('u', 'id'),
  COLUMN('u', 'name'),
  JOIN('orders', 'o', EQ(COLUMN('u', 'id'), COLUMN('o', 'user_id')))
)
.where(EQ(COLUMN('u', 'active'), true))
.orderBy(COLUMN('u', 'name'), OrderByDirection.ASC)
.limit(10);

console.log(query.toSQL());
// SELECT
//   u.id,
//   u.name
// FROM users u
// INNER JOIN orders o ON (u.id = o.user_id)
// WHERE (u.active = 1)
// ORDER BY u.name ASC
// LIMIT 10

Using the QueryBuilder API

For more control, use the fluent QueryBuilder API:

import { QueryBuilder, Column, TableFrom, Alias } from '@shaxpir/squilt';

const query = QueryBuilder.select()
  .from(new Alias(new TableFrom('users'), 'u'))
  .column(new Column('u', 'id'))
  .column(new Column('u', 'name'))
  .limit(10);

Rendering Options

Squilt supports both compact and indented rendering:

import { CompactQueryRenderer, IndentedQueryRenderer } from '@shaxpir/squilt';

// Compact output (single line)
query.toSQL(new CompactQueryRenderer());
// SELECT u.id, u.name FROM users u LIMIT 10

// Indented output (multi-line, 2-space indent)
query.toSQL(new IndentedQueryRenderer(2));
// SELECT
//   u.id,
//   u.name
// FROM users u
// LIMIT 10

Delete Queries

Build DELETE statements with optional WHERE clauses:

import { DELETE_FROM, EQ, COLUMN, PARAM, AND, LT } from '@shaxpir/squilt';

// Simple delete
const deleteAll = DELETE_FROM('temp_files');
console.log(deleteAll.toSQL());
// DELETE FROM temp_files

// Delete with conditions
const deleteOld = DELETE_FROM('logs')
  .where(AND(
    EQ(COLUMN('level'), 'debug'),
    LT(COLUMN('created_at'), PARAM('cutoffDate'))
  ));
console.log(deleteOld.toSQL());
// DELETE FROM logs WHERE ((level = 'debug') AND (created_at < ?))

DELETE queries support subqueries in WHERE clauses:

import { DELETE_FROM, SELECT, FROM, COLUMN, IN, EXISTS, EQ } from '@shaxpir/squilt';

// Delete using IN with subquery
const bannedUsers = SELECT(FROM('banned_users'), COLUMN('id'));
const deleteComments = DELETE_FROM('comments')
  .where(IN(COLUMN('user_id'), bannedUsers));
console.log(deleteComments.toSQL());
// DELETE FROM comments WHERE (user_id IN (SELECT id FROM banned_users))

// Delete using EXISTS with correlated subquery
const deleteOrders = DELETE_FROM('orders')
  .where(EXISTS(
    SELECT(FROM('refunds'), COLUMN('*'))
      .where(EQ(COLUMN('refunds', 'order_id'), COLUMN('orders', 'id')))
  ));
console.log(deleteOrders.toSQL());
// DELETE FROM orders WHERE EXISTS (SELECT * FROM refunds WHERE (refunds.order_id = orders.id))

Update Queries

Build UPDATE statements with SET clauses and optional WHERE:

import { UPDATE, EQ, COLUMN, PARAM } from '@shaxpir/squilt';
import { StringLiteral, NumberLiteral } from '@shaxpir/squilt';

// Simple update
const updateStatus = UPDATE('users')
  .set('status', new StringLiteral('active'))
  .set('last_login', PARAM('loginTime'))
  .where(EQ(COLUMN('id'), PARAM('userId')));
console.log(updateStatus.toSQL());
// UPDATE users SET status = 'active', last_login = ? WHERE (id = ?)

// Update multiple columns
const bulkUpdate = UPDATE('products')
  .set('price', new NumberLiteral(99))
  .set('on_sale', new NumberLiteral(1))
  .set('discount', new NumberLiteral(10))
  .where(EQ(COLUMN('category'), 'electronics'));
console.log(bulkUpdate.toSQL());
// UPDATE products SET price = 99, on_sale = 1, discount = 10 WHERE (category = 'electronics')

RETURNING Clause (SQLite 3.35+)

Get back affected rows from INSERT, UPDATE, and DELETE statements:

import { INSERT, UPDATE, DELETE_FROM, COLUMN, PARAM, EQ, ALIAS, FN } from '@shaxpir/squilt';

// INSERT with RETURNING
const createUser = INSERT('users', ['name', 'email'], [PARAM('name'), PARAM('email')])
  .returning(COLUMN('id'), COLUMN('created_at'));
console.log(createUser.toSQL());
// INSERT INTO users (name, email) VALUES (?, ?) RETURNING id, created_at

// UPDATE with RETURNING
const updateUser = UPDATE('users')
  .set('status', 'active')
  .where(EQ(COLUMN('id'), PARAM('userId')))
  .returning(COLUMN('id'), COLUMN('status'), COLUMN('updated_at'));
console.log(updateUser.toSQL());
// UPDATE users SET status = 'active' WHERE (id = ?) RETURNING id, status, updated_at

// DELETE with RETURNING
const deleteInactive = DELETE_FROM('users')
  .where(EQ(COLUMN('status'), 'inactive'))
  .returning(COLUMN('id'), COLUMN('email'));
console.log(deleteInactive.toSQL());
// DELETE FROM users WHERE (status = 'inactive') RETURNING id, email

// RETURNING with aliased expressions
const insertWithAlias = INSERT('orders', ['total'], [PARAM('total')])
  .returning(
    COLUMN('id'),
    ALIAS(FN('DATETIME', COLUMN('created_at')), 'order_time')
  );
console.log(insertWithAlias.toSQL());
// INSERT INTO orders (total) VALUES (?) RETURNING id, DATETIME(created_at) AS order_time

INSERT ... SELECT

Insert rows from a query result:

import { INSERT_INTO, SELECT, FROM, COLUMN, EQ, LT, PARAM } from '@shaxpir/squilt';

// Basic INSERT ... SELECT
const archiveOrders = INSERT_INTO('archive_orders')
  .columns('id', 'user_id', 'total')
  .fromSelect(
    SELECT(FROM('orders'), COLUMN('id'), COLUMN('user_id'), COLUMN('total'))
      .where(LT(COLUMN('created_at'), PARAM('cutoff')))
  );
console.log(archiveOrders.toSQL());
// INSERT INTO archive_orders (id, user_id, total)
// SELECT id, user_id, total FROM orders WHERE (created_at < ?)

// INSERT OR REPLACE ... SELECT
const syncData = INSERT_INTO('local_cache')
  .orReplace()
  .columns('id', 'data')
  .fromSelect(
    SELECT(FROM('remote_data'), COLUMN('id'), COLUMN('data'))
  );
console.log(syncData.toSQL());
// INSERT OR REPLACE INTO local_cache (id, data) SELECT id, data FROM remote_data

// With RETURNING clause
const copyWithIds = INSERT_INTO('new_table')
  .columns('name', 'value')
  .fromSelect(SELECT(FROM('old_table'), COLUMN('name'), COLUMN('value')))
  .returning(COLUMN('id'));
console.log(copyWithIds.toSQL());
// INSERT INTO new_table (name, value) SELECT name, value FROM old_table RETURNING id

Set Operations: UNION, INTERSECT, EXCEPT

Combine query results with set operations:

import { SELECT, FROM, COLUMN, EQ } from '@shaxpir/squilt';

// UNION - combine results from multiple queries (already supported)
const allEmails = SELECT(FROM('customers'), COLUMN('email'))
  .union(SELECT(FROM('subscribers'), COLUMN('email')));
console.log(allEmails.toSQL());
// SELECT email FROM customers UNION SELECT email FROM subscribers

// INTERSECT - rows that appear in both queries
const commonEmails = SELECT(FROM('subscribers'), COLUMN('email'))
  .intersect(SELECT(FROM('customers'), COLUMN('email')));
console.log(commonEmails.toSQL());
// SELECT email FROM subscribers INTERSECT SELECT email FROM customers

// EXCEPT - rows in first query but not in second
const activeUsers = SELECT(FROM('all_users'), COLUMN('id'))
  .except(SELECT(FROM('banned_users'), COLUMN('user_id')));
console.log(activeUsers.toSQL());
// SELECT id FROM all_users EXCEPT SELECT user_id FROM banned_users

// Combine multiple set operations
const complexSet = SELECT(FROM('set_a'), COLUMN('id'))
  .union(SELECT(FROM('set_b'), COLUMN('id')))
  .intersect(SELECT(FROM('set_c'), COLUMN('id')))
  .except(SELECT(FROM('set_d'), COLUMN('id')));

UPSERT (ON CONFLICT) (SQLite 3.24+)

Insert-or-update operations using SQLite's upsert syntax:

import { INSERT, PARAM, COLUMN, EQ } from '@shaxpir/squilt';

// ON CONFLICT DO UPDATE - update existing rows on conflict
const upsertUser = INSERT('users', ['id', 'name', 'email'], [PARAM('id'), PARAM('name'), PARAM('email')])
  .onConflict('id')
  .doUpdate({ name: PARAM('name'), email: PARAM('email') });
console.log(upsertUser.toSQL());
// INSERT INTO users (id, name, email) VALUES (?, ?, ?)
// ON CONFLICT (id) DO UPDATE SET name = ?, email = ?

// ON CONFLICT DO NOTHING - silently ignore conflicts
const insertIfNotExists = INSERT('events', ['id', 'data'], [PARAM('id'), PARAM('data')])
  .onConflict('id')
  .doNothing();
console.log(insertIfNotExists.toSQL());
// INSERT INTO events (id, data) VALUES (?, ?) ON CONFLICT (id) DO NOTHING

// Multiple conflict columns
const upsertRole = INSERT('user_roles', ['user_id', 'role_id', 'assigned_at'], [PARAM('userId'), PARAM('roleId'), PARAM('now')])
  .onConflict('user_id', 'role_id')
  .doUpdate({ assigned_at: PARAM('now') });
console.log(upsertRole.toSQL());
// INSERT INTO user_roles (user_id, role_id, assigned_at) VALUES (?, ?, ?)
// ON CONFLICT (user_id, role_id) DO UPDATE SET assigned_at = ?

// With WHERE clause on conflict (conditional upsert)
const conditionalUpsert = INSERT('users', ['id', 'name'], [PARAM('id'), PARAM('name')])
  .onConflict('id')
  .onConflictWhere(EQ(COLUMN('active'), true))
  .doUpdate({ name: PARAM('name') });
console.log(conditionalUpsert.toSQL());
// INSERT INTO users (id, name) VALUES (?, ?)
// ON CONFLICT (id) WHERE (active = 1) DO UPDATE SET name = ?

// With RETURNING clause
const upsertWithReturn = INSERT('counters', ['counter_key', 'value'], [PARAM('key'), PARAM('value')])
  .onConflict('counter_key')
  .doUpdate({ value: PARAM('value') })
  .returning(COLUMN('counter_key'), COLUMN('value'));
console.log(upsertWithReturn.toSQL());
// INSERT INTO counters (counter_key, value) VALUES (?, ?)
// ON CONFLICT (counter_key) DO UPDATE SET value = ?
// RETURNING counter_key, value

CREATE TABLE

Create tables with columns, constraints, and SQLite options:

import { CREATE_TABLE } from '@shaxpir/squilt';

// Simple table with primary key
const createUsers = CREATE_TABLE('users')
  .column('id', 'INTEGER', { primaryKey: true })
  .column('name', 'TEXT', { notNull: true })
  .column('email', 'TEXT', { unique: true })
  .ifNotExists();
console.log(createUsers.toSQL());
// CREATE TABLE IF NOT EXISTS users (
//   id INTEGER PRIMARY KEY,
//   name TEXT NOT NULL,
//   email TEXT UNIQUE
// )

// Table with foreign key
const createOrders = CREATE_TABLE('orders')
  .column('id', 'INTEGER', { primaryKey: true })
  .column('user_id', 'INTEGER')
  .column('total', 'REAL')
  .foreignKey(['user_id'], { table: 'users', column: 'id' });

CREATE INDEX

Create indexes with support for expressions:

import { CREATE_INDEX, COLUMN, FN, EQ } from '@shaxpir/squilt';
import { StringLiteral } from '@shaxpir/squilt';

// Simple column index
const simpleIndex = CREATE_INDEX('idx_users_email')
  .on('users', 'email');
// CREATE INDEX idx_users_email ON users (email)

// Composite index
const compositeIndex = CREATE_INDEX('idx_orders_user_date')
  .on('orders', ['user_id', 'created_at']);
// CREATE INDEX idx_orders_user_date ON orders (user_id, created_at)

// Expression index (JSON fields)
const jsonIndex = CREATE_INDEX('idx_data_field')
  .on('docs', FN('json_extract', COLUMN('data'), new StringLiteral('$.type')));
// CREATE INDEX idx_data_field ON docs (json_extract(data, '$.type'))

// Expression index (case-insensitive)
const lowerIndex = CREATE_INDEX('idx_email_lower')
  .on('users', FN('LOWER', COLUMN('email')));
// CREATE INDEX idx_email_lower ON users (LOWER(email))

// Mixed columns and expressions
const mixedIndex = CREATE_INDEX('idx_composite')
  .on('users', ['id', FN('LOWER', COLUMN('name'))]);
// CREATE INDEX idx_composite ON users (id, LOWER(name))

// Unique index
const uniqueIndex = CREATE_INDEX('idx_email')
  .on('users', 'email')
  .unique();
// CREATE UNIQUE INDEX idx_email ON users (email)

// Partial index (filtered)
const partialIndex = CREATE_INDEX('idx_active_users')
  .on('users', 'email')
  .where(EQ(COLUMN('active'), true));
// CREATE INDEX idx_active_users ON users (email) WHERE (active = 1)

DROP TABLE and DROP INDEX

Remove tables and indexes from the database:

import { DROP_TABLE, DROP_INDEX } from '@shaxpir/squilt';

// Simple DROP TABLE
const dropUsers = DROP_TABLE('users');
console.log(dropUsers.toSQL());
// DROP TABLE users

// DROP TABLE IF EXISTS
const dropCache = DROP_TABLE('cache').ifExists();
console.log(dropCache.toSQL());
// DROP TABLE IF EXISTS cache

// Simple DROP INDEX
const dropIndex = DROP_INDEX('idx_users_email');
console.log(dropIndex.toSQL());
// DROP INDEX idx_users_email

// DROP INDEX IF EXISTS
const dropOldIndex = DROP_INDEX('idx_old').ifExists();
console.log(dropOldIndex.toSQL());
// DROP INDEX IF EXISTS idx_old

Range Queries with BETWEEN

Use BETWEEN for range comparisons:

import { SELECT, FROM, COLUMN, BETWEEN, NOT_BETWEEN, PARAM, AND } from '@shaxpir/squilt';

// Price range query
const priceFilter = SELECT(FROM('products'), COLUMN('*'))
  .where(BETWEEN(COLUMN('price'), 10, 100));
console.log(priceFilter.toSQL());
// SELECT * FROM products WHERE (price BETWEEN 10 AND 100)

// Date range with parameters
const dateFilter = SELECT(FROM('orders'), COLUMN('*'))
  .where(BETWEEN(COLUMN('created_at'), PARAM('startDate'), PARAM('endDate')));
console.log(dateFilter.toSQL());
// SELECT * FROM orders WHERE (created_at BETWEEN ? AND ?)

// Exclude a range with NOT BETWEEN
const excludeRange = SELECT(FROM('employees'), COLUMN('*'))
  .where(NOT_BETWEEN(COLUMN('salary'), 50000, 100000));
console.log(excludeRange.toSQL());
// SELECT * FROM employees WHERE (salary NOT BETWEEN 50000 AND 100000)

Parameterized Queries

Use named parameters for safe value binding:

import { SELECT, FROM, COLUMN, EQ, PARAM, ParamCollectingVisitor } from '@shaxpir/squilt';

const query = SELECT(FROM('users'), COLUMN('*'))
  .where(EQ(COLUMN('id'), PARAM('userId')));

const sql = query.toSQL();  // SELECT * FROM users WHERE (id = ?)

// Collect parameter values in order
const params = query.accept(new ParamCollectingVisitor({ userId: 42 }));
// params = [42]

Features

Type-safe AST: Build queries programmatically with full TypeScript support
Fluent API: Chain methods for readable query construction
Two rendering modes: Compact for production, indented for debugging
SQLite-focused: Supports SQLite-specific features like json_each
Query validation: Detect errors before execution
Zero dependencies: No external runtime dependencies

API Reference

Shorthand Functions

Function	Description
`SELECT(...args)`	Create a SELECT query
`SELECT_DISTINCT(...args)`	Create a SELECT DISTINCT query
`FROM(table)`	Create a FROM clause
`COLUMN(name)` or `COLUMN(table, name)`	Reference a column
`ALIAS(expr, name)`	Create an aliased expression
`EQ`, `NOT_EQ`, `GT`, `LT`, `GTE`, `LTE`	Comparison operators
`BETWEEN`, `NOT_BETWEEN`	Range operators
`AND`, `OR`, `NOT`	Logical operators
`LIKE`, `IN`, `NOT_IN`	Pattern matching
`JOIN`, `LEFT_JOIN`, `CROSS_JOIN`	Join clauses
`FN(name, ...args)`	Function calls
`CASE([...cases])`	CASE expressions
`WITH(name, query)`	Common Table Expressions
`UNION`, `INTERSECT`, `EXCEPT`	Set operations
`INSERT`, `INSERT_INTO`, `INSERT_OR_REPLACE`	Insert statements
`UPDATE(table)`	Update statements
`DELETE_FROM(table)`	Delete statements
`DROP_TABLE(table)`	Drop table statements
`DROP_INDEX(index)`	Drop index statements

Renderers

CompactQueryRenderer: Single-line output
IndentedQueryRenderer(spaces): Multi-line output with configurable indentation

Validators

CommonQueryValidator: General SQL validation
SQLiteQueryValidator: SQLite-specific validation

License

Apache 2.0

Name		Name	Last commit message	Last commit date
Latest commit History 23 Commits
docs		docs
src		src
tests		tests
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
jest.config.js		jest.config.js
package.json		package.json
tsconfig.json		tsconfig.json

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

Squilt

Installation

Usage

Using the Shorthand API

Using the QueryBuilder API

Rendering Options

Delete Queries

Update Queries

RETURNING Clause (SQLite 3.35+)

INSERT ... SELECT

Set Operations: UNION, INTERSECT, EXCEPT

UPSERT (ON CONFLICT) (SQLite 3.24+)

CREATE TABLE

CREATE INDEX

DROP TABLE and DROP INDEX

Range Queries with BETWEEN

Parameterized Queries

Features

API Reference

Shorthand Functions

Renderers

Validators

License

About

Uh oh!

Releases

Packages

Uh oh!

Contributors

Uh oh!

Languages

Folders and files

Latest commit

History

Repository files navigation

Squilt

Installation

Usage

Using the Shorthand API

Using the QueryBuilder API

Rendering Options

Delete Queries

Update Queries

RETURNING Clause (SQLite 3.35+)

INSERT ... SELECT

Set Operations: UNION, INTERSECT, EXCEPT

UPSERT (ON CONFLICT) (SQLite 3.24+)

CREATE TABLE

CREATE INDEX

DROP TABLE and DROP INDEX

Range Queries with BETWEEN

Parameterized Queries

Features

API Reference

Shorthand Functions

Renderers

Validators

License

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

Packages