@carusox/sqorm

Type-safe SQL query builder for TypeScript. SQLAlchemy-inspired, zero codegen, Postgres-first.

class User extends Base {
  __tablename__ = "users";
  name = Column(Text);
  email = Column(Text, { unique: true });
  bio = Column(Text, { nullable: true });
  active = Column(Boolean, { default: true });
}

const Users = new User();

const db = createDB(); // compile-only, no pool needed for toSQL()
const { sql, params } = db.query(Users)
  .where(Users.active.eq(true), Users.bio.isNotNull())
  .orderBy(desc(Users.name))
  .limit(10)
  .toSQL();
// sql:    SELECT * FROM "users" WHERE ... ORDER BY ... LIMIT $1
// params: [10]

Features

• Class-based schema with full type inference — no codegen, no decorators
• Immutable query builder — every .where(), .join(), .limit() returns a new query
• Conditional filters — undefined values are silently skipped (no more if/else chains)
• SQL-first — you think in SQL, not an abstracted DSL
• Engine-agnostic core — Postgres today, pluggable Dialect interface for others
• 186 tests — unit tests + integration tests against real Postgres via testcontainers

Install

npm install @carusox/sqorm
# or
pnpm add @carusox/sqorm

Quick Start

Define your schema

import { Model, Column, Serial, Text, Integer, Boolean, TimestampTZ, now } from "@carusox/sqorm";

// Shared base — all tables get an auto-increment id
class Base extends Model {
  id = Column(Serial, { primaryKey: true });
}

class UserDef extends Base {
  __tablename__ = "users";
  name = Column(Text);
  email = Column(Text, { unique: true });
  bio = Column(Text, { nullable: true });
  active = Column(Boolean, { default: true });
  created_at = Column(TimestampTZ, { default: now() });
}

class PostDef extends Base {
  __tablename__ = "posts";
  user_id = Column(Integer);
  title = Column(Text);
  body = Column(Text, { nullable: true });
  published = Column(Boolean, { default: false });
}

// Instantiate to get table objects with typed column references
const User = new UserDef();
const Post = new PostDef();

Type inference

import type { InferSelect, InferInsert } from "@carusox/sqorm";

type UserRow = InferSelect<typeof User>;
// { id: number; name: string; email: string; bio: string | null; active: boolean; created_at: Date }

type NewUser = InferInsert<typeof User>;
// { name: string; email: string; bio?: string | null; active?: boolean; id?: number; created_at?: Date }
//   ^^^^^^^^^^^^^^^^^^^^^^^^^ required    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ optional

Columns are NOT NULL by default. Use { nullable: true } to allow nulls. Columns with { primaryKey: true } or { default: ... } become optional on insert.

SELECT

import { createDB, and_, or_, desc, count } from "@carusox/sqorm";

const db = createDB(); // compile-only, no pool needed for toSQL()

// Basic
db.query(User).toSQL();
// SELECT * FROM "users"

// WHERE with parameters
db.query(User).where(User.id.eq(1)).toSQL();
// SELECT * FROM "users" WHERE ("users"."id" = $1)  params: [1]

// Conditional filters — undefined is a no-op
const nameFilter: string | undefined = getFilter();
db.query(User).where(
  nameFilter ? User.name.eq(nameFilter) : undefined,
  User.active.eq(true),
);
// Only non-undefined conditions are included

// Joins
db.query(User)
  .join(Post, Post.user_id.eq(User.id))
  .where(Post.published.eq(true));

// Aggregates
db.query(User)
  .select(User.name, count())
  .join(Post, Post.user_id.eq(User.id))
  .groupBy(User.name);

// Ordering, pagination
db.query(User)
  .orderBy(desc(User.created_at))
  .limit(10)
  .offset(20);

// Combinators
db.query(User).where(
  or_(
    and_(User.active.eq(true), User.bio.isNotNull()),
    User.name.like("admin%"),
  ),
);

INSERT

// Single row
db.insert(User)
  .values({ name: "Alice", email: "[email protected]" })
  .toSQL();

// Bulk
db.insert(User)
  .values([
    { name: "Alice", email: "[email protected]" },
    { name: "Bob", email: "[email protected]" },
  ])
  .toSQL();

// Upsert
db.insert(User)
  .values({ name: "Alice", email: "[email protected]" })
  .onConflictDoUpdate([User.email], { name: "Alice (updated)" })
  .toSQL();

// RETURNING
db.insert(User)
  .values({ name: "Alice", email: "[email protected]" })
  .returning(User.id, User.email)
  .toSQL();

UPDATE / DELETE

db.update(User)
  .set({ name: "Bob" })
  .where(User.id.eq(1))
  .toSQL();

db.delete(User)
  .where(User.active.eq(false))
  .returning()
  .toSQL();

Execute against Postgres

import { createDB, createPool } from "@carusox/sqorm";

const db = createDB({
  host: "localhost",
  port: 5432,
  database: "mydb",
  user: "postgres",
  password: "secret",
});

// SELECT
const users = await db.query(User).where(User.active.eq(true)).list();

// INSERT with RETURNING
const [newUser] = await db.insert(User)
  .values({ name: "Alice", email: "[email protected]" })
  .returning()
  .execute();

// Transaction
await db.transaction(async (tx) => {
  const [user] = await tx.insert(User)
    .values({ name: "Alice", email: "[email protected]" })
    .returning()
    .execute();
  await tx.insert(Post)
    .values({ user_id: user.id, title: "Hello" })
    .execute();
});

await db.end();

Column Types

| Type | TypeScript | SQL | |------|-----------|-----| | Serial | number | SERIAL (auto-increment) | | Integer / Int | number | INTEGER | | BigInteger | string | BIGINT | | Float | number | FLOAT | | Numeric | string | NUMERIC | | Text | string | TEXT | | Varchar | string | VARCHAR | | Boolean / Bool | boolean | BOOLEAN | | TimestampTZ / DateTime | Date | TIMESTAMPTZ | | Timestamp | Date | TIMESTAMP | | UUID | string | UUID | | Json | unknown | JSON | | Jsonb | unknown | JSONB | | Bytea | Buffer | BYTEA |

Column Options

Column(Text)                          // NOT NULL, no default
Column(Text, { nullable: true })      // nullable
Column(Text, { unique: true })        // NOT NULL + UNIQUE
Column(Text, { default: "hello" })    // NOT NULL + DEFAULT
Column(Serial, { primaryKey: true })  // NOT NULL + PRIMARY KEY + DEFAULT
Column(Integer, { references: User.id })

Operators

| Method | SQL | |--------|-----| | .eq(value) | = $1 | | .neq(value) | != $1 | | .gt(value) | > $1 | | .gte(value) | >= $1 | | .lt(value) | < $1 | | .lte(value) | <= $1 | | .in_([...]) | IN ($1, $2, ...) | | .notIn([...]) | NOT IN (...) | | .like(pattern) | LIKE $1 | | .ilike(pattern) | ILIKE $1 | | .isNull() | IS NULL | | .isNotNull() | IS NOT NULL | | .between(a, b) | BETWEEN $1 AND $2 | | .asc() | ASC | | .desc() | DESC |

Functions

import { count, sum, avg, min, max, coalesce, func, now } from "@carusox/sqorm";

count()                    // COUNT(*)
count(User.id)             // COUNT("users"."id")
sum(Post.views)            // SUM("posts"."views")
coalesce(User.bio, "N/A")  // COALESCE("users"."bio", $1)
func.lower(User.name)      // LOWER("users"."name")
now()                      // NOW()
func.abs(User.score)       // ABS("users"."score")

Query Immutability

Every method returns a new query. The original is never mutated:

const base = db.query(User);
const filtered = base.where(User.active.eq(true));
const sorted = base.orderBy(desc(User.name));

// base, filtered, sorted are three independent queries

Engine Abstraction

@carusox/sqorm is Postgres-first but the compiler is dialect-agnostic. All SQL generation goes through a Dialect interface:

import type { Dialect } from "@carusox/sqorm";

const myDialect: Dialect = {
  name: "mysql",
  param: (i) => "?",           // MySQL uses ? placeholders
  quoteIdentifier: (n) => `\`${n}\``,  // MySQL uses backticks
  supportsReturning: false,
};

query(User).toSQL(myDialect);

License

MIT