Skip to main content

Quick Start

This walks through SQB in the order most projects grow into it: build a query, run it against a real database, model your tables as entities, and use transactions. Every step here only needs what the previous step installed — see Installation if you haven't set that up yet.

1. Build a query​

@sqb/builder composes SQL statements as plain JS/TS objects — no driver, no connection:

import { Select, Eq } from '@sqb/builder';

const query = Select('id', 'given_name', 'family_name')
  .from('customers')
  .where(Eq('active', true))
  .orderBy('id')
  .limit(10);

You can serialize it to SQL text for a specific dialect without ever connecting to a database — import the matching dialect plugin once (a side-effect import) so .generate() knows that dialect's SQL rules:

import '@sqb/postgres-dialect';

const { sql, params } = query.generate({ dialect: 'postgres' });

See The Dialect Plugin System for why that import matters, Select Statement, and Operators and conditions for the full query-builder API.

2. Connect and execute​

Add @sqb/connect and a database adapter (here, @sqb/postgres) to run that query against a real database. Importing the adapter registers it automatically — nothing else to wire up:

import '@sqb/postgres';
import { SqbClient } from '@sqb/connect';
import { Select, Eq } from '@sqb/builder';

const client = new SqbClient({
  dialect: 'postgres',
  host: 'localhost',
  database: 'mydb',
});

const query = Select('id', 'given_name').from('customers').where(Eq('active', true));
const result = await client.execute(query);
console.log(result.rows);

SqbClient manages a connection pool for you: execute() acquires a connection, runs the query, and releases the connection back to the pool. See Creating a client and Executing queries.

3. Define entities​

@sqb/connect also includes a decorator-based ORM on top of the query builder and connection layer. Model your tables as classes once, then read and write them through a typed Repository instead of hand-writing SQL for everyday CRUD:

import { BaseEntity, Column, Entity, Link, PrimaryKey } from '@sqb/connect';

@Entity('countries')
export class Country extends BaseEntity {
  @PrimaryKey()
  @Column()
  declare code: string;

  @Column()
  declare name: string;
}

@Entity('customers')
export class Customer extends BaseEntity {
  @PrimaryKey()
  @Column({ autoGenerated: 'increment' })
  declare id?: number;

  @Column({ fieldName: 'given_name' })
  declare givenName: string;

  @Column({ fieldName: 'country_code' })
  declare countryCode: string;

  @Column({ default: true })
  declare active: boolean;

  @(Link().toOne(Country, { sourceKey: 'countryCode', targetKey: 'code' }))
  declare readonly country?: Country;
}

@Link relations are only fetched when a query actually asks for them (via projection), so adding one never adds an implicit join to every query that touches the entity. See Defining Models, Data Columns, and Associations for the rest of the decorators (@Embedded, @Index, @ForeignKey, lifecycle hooks, and more).

4. Use a repository​

const repo = client.getRepository(Customer);

// Find many rows, with a filter and an eager-loaded relation
const customers = await repo.findMany({
  filter: { active: true },
  projection: ['id', 'givenName', 'country'],
  sort: ['givenName'],
  limit: 20,
});

// Find a single row by primary key
const customer = await repo.findById(1);

// Create
const created = await repo.create({ givenName: 'Jane', countryCode: 'US' });

// Update
await repo.update(1, { givenName: 'Janet' });

// Delete
await repo.delete(1);

Repository also has findOne, count, exists, and bulk updateMany/deleteMany variants that act on a filter instead of a single key. Filters accept either a plain object ({ city: 'Istanbul' }) or @sqb/builder operators (Eq, In, And, Or, ...) for anything more complex. See Repositories.

5. Transactions​

Acquire a single connection to group several operations into one transaction:

await client.acquire(async connection => {
  const repo = connection.getRepository(Customer);
  await connection.startTransaction();
  try {
    await repo.update(1, { active: false });
    await repo.create({ givenName: 'New Customer', countryCode: 'US' });
    await connection.commit();
  } catch (e) {
    await connection.rollback();
    throw e;
  }
});

See Transactions for savepoints and more detail, and Cursors and streaming for working with large result sets without buffering everything in memory.

Where to go next​