sqlex

sqlex is a typed ORM, migration toolkit, and relation-aware database API for PostgreSQL, MySQL, and SQLite. It supports declarative record classes, checksum-protected migrations, nested relational reads and writes, bulk import/export, and graph-aware persistence without hiding the underlying SQL model.

Use the typed ORM for new applications, or introspect an existing database and work directly with the lower-level Table API.

Requirements

• Node.js 24.12 or newer
• One database driver: pg, mysql2, or sqlite3

npm install sqlex sqlite3

Typed ORM

Define records once and use the same metadata for TypeScript inference, relations, and migrations:

import { Database, defineRecord, field } from 'sqlex';

class User extends defineRecord({
  table: 'app_user',
  fields: {
    id: field.id(),
    email: field.string({ maxLength: 254, unique: true }),
    active: field.boolean({ default: true }),
  },
}) {}

const db = new Database({
  dialect: 'sqlite3',
  connection: { database: 'app.db' },
});
const models = db.bind({ User });

const [user, created] = await models.User.getOrCreate({
  email: '[email protected]',
});
const activeUsers = await models.User
  .filter({ active: true })
  .orderBy('email');

console.log({ user, created, activeUsers });
await db.end();

The ORM and migrations quickstart covers a complete SQLite application, including model definitions, generated migrations, relations, and queries.

Migrations

Migration files contain structured, reversible operations and a schema snapshot. Applied migrations are tracked with checksums.

npx sqlex migration make initial
npx sqlex migration sql
npx sqlex migration up
npx sqlex migration status

See Migrations for configuration, rollback, baselining an existing database, manual operations, and dialect limitations.

Table API

The Table API works from an introspected database and does not require record classes:

import { Database } from 'sqlex';

const db = new Database({
  dialect: 'postgres',
  connection: process.env.DATABASE_URL!,
});

await db.buildSchema();

const orders = await db.table('order').select({
  user: '*',
  orderItems: { fields: { product: '*' } },
}, {
  where: { status_in: [10, 20] },
});

await db.end();

Start with Getting started with the Table API when adopting sqlex around an existing schema or when you want direct, relation-aware table operations.

Which API?

| Use case | Start here | | --- | --- | | New TypeScript application | ORM quickstart | | Generated and reversible schema changes | Migrations | | Existing database with no model declarations | Table API | | Nested relational import/export | Import and export | | Trees backed by closure tables | Hierarchical data | | Parameterized SQL with named placeholders | Raw SQL | | REST API and OpenAPI over an existing database | REST API | | One API serving many tenants | Multi-tenancy |

Features

• Typed record fields, managers, immutable query sets, and reverse relations
• PostgreSQL, MySQL, and SQLite migration compilation
• Explicit nested relation selection and mutation
• Identity mapping and graph-aware persistence for connected records
• Schema introspection for existing databases
• JSON-path filtering and configurable filter operator syntax
• Closure-table tree traversal and cloning
• Read-only REST API and OpenAPI 3.1 generation from a declared policy
• Bulk loading, export, serialization, views, and aggregates
• Parameterized raw SQL with positional and named placeholders

Documentation

Start here

• ORM and migrations quickstart
• Getting started with the Table API
• Upgrading to v4

ORM and schema

• ORM records
• Migrations
• Connecting and schema
• TypeScript

Queries and writes

• Querying
• Filtering
• Mutations
• Unit of work

Advanced

• REST API
• Multi-tenancy
• Views and aggregates
• Hierarchical data
• Import and export
• Raw SQL
• Testing utilities

Community

See CONTRIBUTING.md for local setup, tests, and pull request guidance. Please report security issues through the process in SECURITY.md.

sqlex is released under the MIT License.