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.
• Zod integration Use Zod schemas to validate and parse parameters and query results. (JSON fields included).
• Iterative Execution Process large datasets efficiently using asynchronous generators.

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.

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 JSON columns
    z.object({
      role: z.enum(['user', 'admin']).default('user'),
    })
  ),
  created_at: z.number().int(),
  disabled_at: z.number().int().nullable(),
})

type UserDb = z.infer<typeof zUserDb>

// ...

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

Here the 4 methods first/firstOrThrow/all/iterate are defined at the instance level, but you can define them at the query level if you prefer. You can also ignore some methods if you don't need them.

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

const db = new SQLite('sqlite.db')

const schemQl = new SchemQl<DB>({
  queryFns: {    // Optional at this level, but simplifies usage
    first: (sql) => {
      const stmt = db.prepare(sql)
      return (params) => {
        return stmt.get(params)
      }
    },
    firstOrThrow: (sql) => {
      const stmt = db.prepare(sql)
      return (params) => {
        const first = stmt.get(params)
        if (first === undefined) {
          throw new Error('No result found')
        }
        return first
      }
    },
    all: (sql) => {
      const stmt = db.prepare(sql)
      return (params) => {
        return params ? stmt.all(params) : stmt.all()
      }
    },
    iterate: (sql) => {
      const stmt = db.prepare(sql)
      return (params) => {
        return stmt.iterate(params)
      }
    },
  },
  shouldStringifyObjectParams: true,   // Optional. Automatically stringify objects (useful for JSON)
})

You can also keep initialization simple if your move some logic in you own custom class

const sqliteDb = new SQLiteDb()  // Custom class with better error handling

const schemQl = new SchemQl<DB>({
  queryFns: {
    first: sqliteDb.queryFirst.bind(db),
    firstOrThrow: sqliteDb.queryFirstOrThrow.bind(db),
    all: sqliteDb.queryAll.bind(db),
  },
  shouldStringifyObjectParams: true,
})

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 Zod preprocess 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 |

Contributing

Contributions are welcome! This library aims to remain lightweight and focused, so please keep PRs concise and aligned with this goal.

This library relies solely on Zod, but it could also include support for other schema libraries, such as @effect/schema.

License

This project is licensed under the MIT License.