sqlex

sqlex helps you retrieve, update, import and export data from a relational database with minimal ceremony. Alongside the usual one-to-one, many-to-one and many-to-many relations, it has built-in support for hierarchical data (trees) via closure tables — including cloning a tree rooted at a given node — and a parameterised raw-SQL layer with positional (?) and named (:id) placeholders.

Supported databases: MySQL, PostgreSQL, SQLite.

Install

npm install sqlex
# plus a driver: mysql2 | pg | sqlite3

Quick example

import { Database } from 'sqlex';

const db = new Database({
  dialect: 'mysql',
  connection: { user: 'root', password: 'secret', database: 'example' },
});

await db.buildSchema(); // read tables & relations from the database

// create an order linked to an existing user
const order = await db.table('order').create({
  user: { connect: { email: '[email protected]' } },
  code: 'order-1',
});

// read it back with the user and each item's product expanded
const orders = await db.table('order').select({
  user: '*',
  orderItems: { fields: { product: '*' } },
});

await db.end();

New here? Start with the Getting started guide.

Documentation

Getting started

• Getting started — install, connect, first query and write

Guides

• Connecting & schema — connection options, pooling, schema introspection
• Querying — selecting rows and related data, ordering, pagination, counting
• Filtering — operators, and/or/not, nested and dotted-path filters
• Mutations — create/update/upsert/modify/delete and nested relation writes
• Unit of work — append/flush, cyclic references, replaceRecordsIn

Advanced

• Views & aggregates — joins, aggregate functions, groupBy/having
• Hierarchical data — closure-table trees, ancestors/descendants, cloning
• Import & export — bulk load, xselect, surrogate keys, serialisers
• Raw SQL — parameterised queries with ? and :named placeholders

TypeScript & tooling

• TypeScript — typed tables, the type-map generator, returning, JSON columns
• Testing utilities — mock() / cleanup() fixtures
• Command line interface — schema dump and code/type generation

Development

Testing

# SQLite
DB_TYPE=sqlite3 npm test

# PostgreSQL
DB_TYPE=postgres DB_USER=postgres npm test

# MySQL
DB_TYPE=mysql DB_USER=root DB_PASS=secret npm test

# generic driver
DB_TYPE=generic SQLEX_DRIVER=/path/to/sqlex.node npm test