Queriable

A type-safe, database-agnostic SQL query builder for TypeScript with built-in connectors for MySQL, PostgreSQL, and SQLite.

Installation

npm install queriable

Install the connector for your database:

npm install mysql2         # MySQL
npm install pg             # PostgreSQL
npm install better-sqlite3 # SQLite

Quick Start

import { createDB, SQLiteConnector } from 'queriable';

// 1. Define your schema
interface Schema {
  users: { id: number; name: string; email: string; age: number };
  orders: { id: number; user_id: number; total: number; status: string };
}

// 2. Create a typed DB with a connector
const db = createDB<Schema>(new SQLiteConnector({ filename: 'app.db' }));

// 3. Full type safety — table names autocomplete, columns validate, results are typed
const users = await db.table('users')
  .select('id', 'name')
  .where('age', 18, '>=')
  .getAll();

users[0].id;   // number
users[0].name; // string

Table of Contents

• Schema & Connectors
• Select
• Insert
• Update
• Delete
• Aggregates
• Find
• Pagination
• Joins
• Where Clauses
• Conditional Clauses
• Ordering, Grouping, Limit & Offset
• Having
• Union
• Increment & Decrement
• Truncate
• Clone
• Transactions
• Raw Queries
• SQL Output

Schema & Connectors

Define your tables as a TypeScript interface:

interface Schema {
  users: { id: number; name: string; email: string; age: number };
  orders: { id: number; user_id: number; total: number; status: string };
}

This gives you:

• Table name autocomplete on .table()
• Column name validation on .select(), .where(), .orderBy(), .groupBy(), etc.
• Type-safe .insert() and .update() data
• Typed query results from .get() and .getAll()

MySQL

import { createDB, MySQLConnector } from 'queriable';

const db = createDB<Schema>(new MySQLConnector({
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: 'secret',
  database: 'myapp',
  pool: { max: 10 },
}));

PostgreSQL

import { createDB, PostgresConnector } from 'queriable';

const db = createDB<Schema>(new PostgresConnector({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'secret',
  database: 'myapp',
  pool: { max: 10 },
  ssl: false,
}));

SQLite

import { createDB, SQLiteConnector } from 'queriable';

const db = createDB<Schema>(new SQLiteConnector({
  filename: 'app.db',    // or ':memory:' for in-memory
}));

Without a connector (SQL generation only)

const db = createDB<Schema>();

const sql = db.table('users')
  .select('id', 'name')
  .where('age', 18, '>=')
  .toSQL();
// SELECT id, name FROM users WHERE age >= 18

Select

// Get all rows
const users = await db.table('users')
  .select('id', 'name', 'age')
  .getAll();
// { id: number; name: string; age: number }[]

// Get a single row (LIMIT 1, returns null if not found)
const user = await db.table('users')
  .select('id', 'name')
  .where('id', 1)
  .get();
// { id: number; name: string } | null

Distinct

db.table('users').distinct().select('name').toSQL();
// SELECT DISTINCT name FROM users

selectRaw

db.table('users').selectRaw('SELECT COUNT(*) as total').toSQL();
// SELECT COUNT(*) as total FROM users

Insert

Executes the query and returns { affectedRows, insertId }:

const result = await db.table('users')
  .insert({ name: 'John', email: '[email protected]', age: 30 });

result.insertId;     // 1
result.affectedRows; // 1

Bulk insert

await db.table('users').insert([
  { name: 'John', email: '[email protected]', age: 30 },
  { name: 'Jane', email: '[email protected]', age: 25 },
]);

Update

Executes the query and returns { affectedRows }:

const result = await db.table('users')
  .where('id', 1)
  .update({ name: 'John', age: 31 });

result.affectedRows; // 1

Delete

Executes the query and returns { affectedRows }:

const result = await db.table('users')
  .where('id', 1)
  .delete();

result.affectedRows; // 1

Aggregates

All aggregate methods execute immediately and return a number:

await db.table('users').count('id');          // 5
await db.table('users').sum('age');           // 150
await db.table('users').avg('age');           // 30
await db.table('users').min('age');           // 18
await db.table('users').max('age');           // 65

Aggregates respect WHERE clauses:

await db.table('users').where('status', 'active').count('id');  // 3

Find

Shorthand for .where(column, value).get():

const user = await db.table('users')
  .select('id', 'name')
  .find('id', 1);
// { id: number; name: string } | null

Pagination

Returns paginated results with metadata:

const page = await db.table('users')
  .select('id', 'name')
  .orderBy('name')
  .paginate(1, 10);

page.data;     // { id: number; name: string }[]
page.total;    // 50  (total matching rows)
page.page;     // 1
page.perPage;  // 10
page.lastPage; // 5

Joins

Methods: leftJoin, rightJoin, innerJoin, crossJoin, joinRaw, join

db.table('users')
  .select('id', 'name')
  .leftJoin('orders', 'users.id', 'orders.user_id')
  .toSQL();
// SELECT id, name FROM users LEFT JOIN orders ON users.id = orders.user_id

With alias

.leftJoin('orders', 'users.id', 'orders.user_id', 'o')
// LEFT JOIN orders AS o ON users.id = orders.user_id

Other join types

.rightJoin('orders', 'users.id', 'orders.user_id')
.innerJoin('orders', 'users.id', 'orders.user_id')
.crossJoin('roles')
.joinRaw('LEFT JOIN orders o ON users.id = o.user_id')

Advanced join with conditions

db.table('users')
  .join(qb => {
    qb.leftJoin('orders')
      .on('users.id', 'orders.user_id')
      .and('orders.status', 'active')
      .or('orders.priority', 'high');
  })
  .toSQL();

Where Clauses

Where / orWhere

db.table('users').where('age', 18, '>=').toSQL();
// WHERE age >= 18

db.table('users').where('age', 18, '>=').where('status', 'active').toSQL();
// WHERE age >= 18 AND status = 'active'

db.table('users').where('status', 'active').orWhere('status', 'pending').toSQL();
// WHERE status = 'active' OR status = 'pending'

whereColumn

Compare two columns without value escaping:

db.table('users').whereColumn('users.id', 'orders.user_id').toSQL();
// WHERE users.id = orders.user_id

whereIn / whereNotIn

db.table('users').whereIn('id', [1, 2, 3]).toSQL();
// WHERE id IN (1, 2, 3)

db.table('users').whereNotIn('status', ['banned', 'suspended']).toSQL();
// WHERE status NOT IN ('banned', 'suspended')

whereBetween / whereNotBetween

db.table('users').whereBetween('age', 18, 65).toSQL();
// WHERE age BETWEEN 18 AND 65

whereLike / whereNotLike

db.table('users').whereLike('name', 'john').toSQL();
// WHERE name LIKE '%john%'

db.table('users').whereLike('name', 'john%').toSQL();
// WHERE name LIKE 'john%'

whereIsNull / whereIsNotNull

db.table('users').whereIsNull('deleted_at').toSQL();
// WHERE deleted_at IS NULL

whereExists / whereNotExists

db.table('users')
  .select('id', 'name')
  .whereExists((qb) => {
    qb.table('orders').select('id').whereRaw('WHERE orders.user_id = users.id');
  })
  .toSQL();
// WHERE EXISTS (SELECT id FROM orders WHERE orders.user_id = users.id)

Where Subquery

db.table('users')
  .whereIn('id', (qb) => {
    qb.table('orders').select('user_id').where('total', 100, '>');
  })
  .toSQL();
// WHERE id IN (SELECT user_id FROM orders WHERE total > 100)

Where Nested

db.table('users')
  .where('age', 18, '>=')
  .orWhere((qb) => {
    qb.where('name', 'Admin').where('email', '[email protected]');
  })
  .toSQL();
// WHERE age >= 18 OR (name = 'Admin' AND email = '[email protected]')

whereRaw

db.table('users').whereRaw('WHERE created_at > %s', '2024-01-01').toSQL();
// WHERE created_at > '2024-01-01'

Conditional Clauses

Add clauses only when a condition is truthy:

const filterByAge = true;
const minAge = 18;

const users = await db.table('users')
  .select('id', 'name')
  .when(filterByAge, (qb) => qb.where('age', minAge, '>='))
  .getAll();

Ordering, Grouping, Limit & Offset

db.table('users')
  .select('id', 'name')
  .orderBy('name')
  .orderBy('age', 'DESC')
  .limit(10)
  .offset(20)
  .toSQL();
// ORDER BY name ASC, age DESC LIMIT 10 OFFSET 20

orderByRaw

db.table('users').orderByRaw('RANDOM()').toSQL();
// ORDER BY RANDOM()

groupBy / groupByRaw

db.table('orders').select('status').groupBy('status').toSQL();
// GROUP BY status

db.table('orders').groupByRaw('YEAR(created_at)').toSQL();
// GROUP BY YEAR(created_at)

Having

Methods: having, orHaving, havingCount, havingSum, havingAvg, havingMin, havingMax (+ or variants), havingRaw

db.table('orders')
  .select('user_id')
  .groupBy('user_id')
  .havingCount('id', '>', 5)
  .toSQL();
// HAVING COUNT(id) > 5

db.table('orders')
  .select('user_id')
  .groupBy('user_id')
  .havingSum('total', '>', 1000)
  .orHavingAvg('total', '>', 200)
  .toSQL();
// HAVING SUM(total) > 1000 OR AVG(total) > 200

Union

const archived = db.table('orders').select('id', 'total');

db.table('orders')
  .select('id', 'total')
  .union(archived)
  .toSQL();
// SELECT id, total FROM orders UNION SELECT id, total FROM orders

Increment & Decrement

Update a numeric column by a given amount:

await db.table('users').where('id', 1).increment('age');       // age = age + 1
await db.table('users').where('id', 1).increment('age', 5);    // age = age + 5
await db.table('users').where('id', 1).decrement('age', 3);    // age = age - 3

Truncate

await db.table('users').truncate();
// TRUNCATE TABLE users

Clone

Create an independent copy of a query builder to build variants:

const base = db.table('users').select('id', 'name');

const active = base.clone().where('status', 'active');
const admins = base.clone().where('role', 'admin');

// base, active, and admins are independent queries

Transactions

Use the callback pattern for automatic commit/rollback:

await db.transaction(async (trx) => {
  await trx.execute(db.table('users').insert({ name: 'Alice', email: '[email protected]' }));
  await trx.execute(db.table('orders').insert({ user_id: 1, total: 99, status: 'pending' }));
  // auto-commits on success
});
// auto-rollbacks if an error is thrown

You can also use raw SQL inside transactions:

await db.transaction(async (trx) => {
  await trx.raw("INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')");
});

Raw Queries

Execute arbitrary SQL:

const result = await db.raw<{ cnt: number }>('SELECT COUNT(*) as cnt FROM users');
result.rows[0].cnt; // number

SQL Output

Generate the SQL string without executing:

const sql = db.table('users')
  .select('id', 'name')
  .where('age', 18, '>=')
  .toSQL();
// SELECT id, name FROM users WHERE age >= 18

Closing the Connection

await db.close();