@a2lix/schemql NPM

SchemQl

SchemQl simplifies database interactions by allowing you to write advanced SQL queries that fully leverage the features of your DBMS, while providing type safety through the use of schemas and offering convenient execution methods.

Key features:

Database agnostic: Compatible with any DBMS.
SQL-first: Write SQL with precise type checks on literals like tables, columns (JSON fields & some JSONPath included), and parameters.
Flexible parameters Supports single objects, arrays of objects, and asynchronous generators for parameters.
Schema-agnostic: Use any validation library implementing Standard Schema (Zod, ArkType, Effect, etc.) to validate and parse parameters and query results.
Iterative Execution Process large datasets efficiently using asynchronous generators.

Screenshot

Installation

To install SchemQl, use:

npm i @a2lix/schemql

Usage

Here's a basic example of how to use SchemQl:

If using JSON data, leverage the built-in parseJsonPreprocessor.

With Zod:

import { parseJsonPreprocessor } from '@a2lix/schemql'
import { z } from 'zod'

export const zUserDb = z.object({
  id: z.string(),
  email: z.string(),
  metadata: z.preprocess(
    parseJsonPreprocessor,   // ! Zod handles JSON parsing for this JSON columns 'metadata'
    z.object({
      role: z.enum(['user', 'admin']).default('user'),
    })
  ),
  created_at: z.int(),
  disabled_at: z.int().nullable(),
})

type UserDb = z.infer<typeof zUserDb>

With ArkType:

import { type } from 'arktype'

export const userDb = type({
  id: 'string',
  email: 'string',
  metadata: type("string.json.parse").to({
    role: "'user' | 'admin' = 'user'",
  }),
  created_at: 'number.epoch',
  disabled_at: 'number.epoch | null',
})

type UserDb = typeof userDb.infer

// ...

export interface DB {
  users: UserDb
  // ...other mappings
}

import { SchemQl } from '@a2lix/schemql'
import { BetterSqlite3Adapter } from '@a2lix/schemql/adapters/better-sqlite3'
import type { DB } from '@/schema'

const schemQl = new SchemQl<DB>({
  adapter: new BetterSqlite3Adapter('sqlite.db'),
  stringifyObjectParams: true,   // Optional. Automatically stringify objects (useful for JSON)
})

const allUsers = await schemQl.all({
  resultSchema: zUserDb.array(),
})(`
  SELECT *
  FROM users
`)

More advanced example

const firstUser = await schemQl.first({
  params: { id: 'uuid-1' },
  paramsSchema: zUserDb.pick({ id: true }),
  resultSchema: z.object({ user_id: zUserDb.shape.id, length_id: z.number() }),
})((s) => s.sql`
  SELECT
    ${'@users.id'} AS ${'$user_id'},
    LENGTH(${'@users.id'}) AS ${'$length_id'}
  FROM ${'@users'}
  WHERE
    ${'@users.id'} = ${':id'}
`);

const allUsersLimit = await schemQl.all({
  params: { limit: 10 },
  resultSchema: zUserDb.array(),   // ! Note the array() use for .all() case
})((s) => s.sql`
  SELECT
    ${'@users.*'}
  FROM ${'@users'}
  LIMIT ${':limit'}
`)

const allUsersPaginated = await schemQl.all({
  params: {
    limit: data.query.limit + 1,
    cursor: data.query.cursor,
    dir: data.query.dir,
  },
  paramsSchema: zRequestQuery,
  resultSchema: zUserDb.array(),   // ! Note the array() use for .all() case
})((s) => s.sql`
  SELECT
    ${'@users.*'}
  FROM ${'@users'}
  ${s.sqlCond(
    !!data.query.cursor,
    s.sql`WHERE ${'@users.id'} ${s.sqlRaw(data.query.dir === 'next' ? '>' : '<')} ${':cursor'}`
  )}
  ORDER BY ${'@users.id'} ${s.sqlCond(data.query.dir === 'prev', 'DESC', 'ASC')}
  LIMIT ${':limit'}
`)

Automatically stringify JSON params 'metadata' (by schemQl if enabled) and get parsed JSON metadata, as well (if your schema preprocess is set rightly)

const firstSession = await schemQl.firstOrThrow({
  params: {
    id: uuidv4(),
    user_id: 'uuid-1',
    metadata: {
      account: 'credentials',
    },
    expiresAtAdd: 10000,
  },
  paramsSchema: zSessionDb.pick({ id: true, user_id: true, metadata: true }).and(z.object({
    expiresAtAdd: z.number().int(),
  })),
  resultSchema: zSessionDb,
})((s) => s.sql`
  INSERT INTO
    ${{ sessions: ['id', 'user_id', 'metadata', 'expires_at'] }}
  VALUES
    (
      ${':id'}
      , ${':user_id'}
      , JSON(${':metadata'})
      , STRFTIME('%s', 'now') + ${':expiresAtAdd'}
    )
  RETURNING *
`)

Handle iteration when required

const iterResults = await schemQl.first({
  params: [
    { id: 'uuid-1' },
    { id: 'uuid-2' }
  ],
  paramsSchema: zUserDb.pick({ id: true }).array(),  // ! Note the array() use when array of params
  resultSchema: zUserDb,
})((s) => s.sql`
  SELECT *
  FROM ${'@users'}
  WHERE
    ${'@users.id'} = ${':id'}
`)

const iterResults = await schemQl.first({
  params: function* () {
    yield { id: 'uuid-1' }
    yield { id: 'uuid-2' }
  },
  paramsSchema: zUserDb.pick({ id: true }),
  resultSchema: zUserDb,
})((s) => s.sql`
  SELECT *
  FROM ${'@users'}
  WHERE
    ${'@users.id'} = ${':id'}
`)

const iterResults = await schemQl.iterate({
  resultSchema: zUserDb,
})((s) => s.sql`
  SELECT *
  FROM ${'@users'}
  LIMIT 10
`)

Literal String SQL Helpers

Helper Syntax	Raw SQL Result	Description
`${'@table1'}`	`table1`	Table Selection: Prefix `@` eases table selection/validation
`${'@table1.col1'}`	`table1.col1`	Column Selection: Use `@` for table and column validation
`${'@table1.col1-'}`	`col1`	Final `-` excludes table name (useful if table is aliased)
`${'@table1.col1 ->jsonpath1'}`	`table1.col1 ->'jsonpath1'`	JSON Field Selection: Use `->` for JSON paths
`${'@table1.col1 ->>jsonpath1'}`	`table1.col1 ->>'jsonpath1'`	JSON field (raw) with `->>` syntax
`${'@table1.col1 $.jsonpath1'}`	`'$.jsonpath1'`	JSONPath with `$` prefix
`${'$resultCol1'}`	`resultCol1`	Result Selection: `$` prefix targets resultSchema fields
`${':param1'}`	`:param1`	Parameter Selection: `:` prefix eases parameter validation
`${{ table1: ['col1', 'col2'] }}`	`table1 (col1, col2)`	Batch Column Selection: Object syntax useful for INSERT
`${s.sqlCond(1, 'ASC', 'DESC')}`	`ASC`	Conditional SQL: `s.sqlCond` for conditional clauses
`${s.sqlRaw(var)}`	`var`	Raw SQL: Use `s.sqlRaw` for unprocessed SQL fragments