The Runtime-First ORM for TypeScript — Zero code generation. Zero build step. Full type safety.
PawQL is a modern, type-safe database query builder that infers types directly from your runtime schema definition. No CLI tools, no .prisma files, no generated code.
| Feature | Prisma | Drizzle | PawQL |
|---|---|---|---|
| Code Generation | ✅ Required | ⚠ Optional | ❌ Not needed |
| Build Step | ✅ Required | ⚠ Sometimes | ❌ Not needed |
| Runtime Schema | ❌ | ❌ | ✅ Yes |
| Type Safety | ✅ | ✅ | ✅ Native inference |
| Schema Definition | .prisma file |
TypeScript schema | Plain JS objects |
| Learning Curve | Medium | Medium | Low |
- 🚀 Runtime-First — Define schema using plain JavaScript objects
- 🔒 Native Type Inference — End-to-end TypeScript support without code generation
- 🛠️ Zero Build Step — No CLI, no schema files, no generated clients
- ⚡ Lightweight Core — Minimal abstraction, ideal for serverless & edge
- 📦 Modern — ESM-first, works with Node.js and Bun
- 🔌 Multi-Database — Native adapters for PostgreSQL, MySQL, and SQLite
- CRUD:
SELECT,INSERT,UPDATE,DELETE - Filtering:
WHERE,OR,IN,LIKE,BETWEEN,IS NULL, comparison operators - ORDER BY: Single/multiple column sorting with ASC/DESC
- LIMIT / OFFSET: Pagination support
- Joins:
INNER,LEFT,RIGHT,FULLJOIN with type inference - Transactions: Atomic operations with auto-rollback
- Data Types:
String,Number,Boolean,Date,JSON,UUID,Enum,Array,varchar,text,bigint,decimal - DDL: Auto-generate tables from schema with
db.createTables() - Controllable RETURNING: Choose which columns to return from mutations
- Shortcuts:
.first()for single row,.count()for counting - Migrations: Programmatic
MigrationRunnerfor safe schema transitions - Raw SQL:
db.raw(sql, params)— escape hatch for custom queries - Upsert:
INSERT ... ON CONFLICT DO UPDATE / DO NOTHING - Batch Inserts: Transparent chunking optimization for bulk array insertions
- GROUP BY + HAVING: Aggregation query support
- Subqueries: Subqueries in WHERE and FROM clauses
- Logger / Debug Mode:
.toString()dump capability to inspect raw injected SQL queries - Streaming: Native AsyncGenerator
.stream()iteration functionality for massive chunks - Plugins: Extensible community adapter system setup (
PawQLPlugin) - Pool Management: Exposed connection pool options (max, idle timeout, etc.)
- JSDoc: Complete documentation for all public APIs
- Soft Delete: Native
deleted_athandling (.softDelete(),.restore(),.withTrashed(),.onlyTrashed()) - Integration Tests: Comprehensive tests with real PostgreSQL via Docker
- Seeders:
seed()andcreateSeeder()for populating initial data with validation - Parameter Validation: Runtime
validateRow()andassertValid()checks against schema types - Query Timeout:
.timeout(ms)support withPawQLTimeoutErrorfor canceling long-running queries - Hooks / Middleware:
db.hook()forbeforeInsert,afterUpdate, etc. with data mutation support - Relations:
hasMany,belongsTo,hasOnewith.with()auto-joins - Multi-Database: Use
PostgresAdapter,MysqlAdapter, orSqliteAdapterinterchangeably - Introspection:
introspectDatabase()runtime API safely reverse-engineers legacy DB schemas without forcing CLI templates.
Use PawQL if you:
- Prefer runtime schema over DSL files
- Want type inference without code generation
- Need a lightweight alternative to heavy ORMs
- Work in serverless or edge environments
- Prefer clean, minimal APIs
# Core
npm install pawql
# Pick your database driver:
npm install pg # PostgreSQL
npm install mysql2 # MySQL / MariaDB
npm install better-sqlite3 # SQLite (Node.js) — Bun has built-in supportInstall only the driver you need. PawQL will auto-detect the runtime (Node.js or Bun) for SQLite.
import { connect } from 'pawql';
// 1. Define your schema using standard JavaScript constructors or custom PawQL markers
// 2. Pass in the Connection Dialect String directly.
const db = await connect({
users: {
id: { type: Number, primaryKey: true },
name: String,
isActive: { type: Boolean, default: true },
createdAt: Date,
}
}, 'postgres://user:pass@localhost:5432/mydb');
// 3. Optional: Sync tables dynamically to DB (great for prototyping)
await db.createTables();
// 3. Insert data
await db.query('users')
.insert({ id: 1, name: 'Alice', email: 'alice@example.com', age: 28 })
.execute();
// 4. Query with full type inference
const activeUsers = await db.query('users')
.select('id', 'name')
.where({ isActive: true })
.orderBy('name', 'ASC')
.limit(10)
.execute();
// 5. Get a single row
const user = await db.query('users')
.where({ id: 1 })
.first(); // Returns single object or null
// 6. Count rows
const total = await db.query('users')
.where({ isActive: true })
.count(); // Returns numberPawQL supports native soft delete — mark records as deleted instead of removing them:
const db = createDB(schema, adapter, {
softDelete: {
tables: ['users', 'posts'], // Enable for specific tables
column: 'deleted_at', // Optional, default
},
});
// Soft delete (sets deleted_at = NOW())
await db.query('users').where({ id: 1 }).softDelete().execute();
// Default queries automatically exclude soft-deleted rows
const users = await db.query('users').execute(); // Only non-deleted
// Include soft-deleted rows
const all = await db.query('users').withTrashed().execute();
// Only soft-deleted rows
const deleted = await db.query('users').onlyTrashed().execute();
// Restore a soft-deleted row
await db.query('users').where({ id: 1 }).restore().execute();See Soft Delete Guide for full details.
PawQL includes a built-in seeder to populate your database with initial or test data:
import { seed } from 'pawql';
await seed(db, {
users: [
{ id: 1, name: 'Alice', email: 'alice@example.com', age: 28 },
{ id: 2, name: 'Bob', email: 'bob@example.com', age: 32 },
],
posts: [
{ id: 1, userId: 1, title: 'Hello World', content: '...' },
],
}, {
truncate: true, // Clear tables first
validate: true, // Validate against schema
transaction: true, // Atomic operation
});See Seeders Guide for full details.
Catch type mismatches before they hit the database:
import { validateRow, assertValid, PawQLValidationError } from 'pawql';
// Non-throwing validation
const result = validateRow({ id: 'wrong', name: 123 }, schema.users);
console.log(result.valid); // false
console.log(result.errors); // [{ column: 'id', message: '...', ... }]
// Throwing validation
try {
assertValid(data, schema.users, 'users');
} catch (e) {
if (e instanceof PawQLValidationError) {
console.log(e.table); // 'users'
console.log(e.details); // structured error details
}
}Supports all types: Number, String, Boolean, Date, UUID, Enum, Array, JSON — including nested array element validation.
See Parameter Validation Guide for full details.
Prevent long-running queries from blocking your application:
import { PawQLTimeoutError } from 'pawql';
try {
const users = await db.query('users')
.timeout(5000) // 5 seconds
.execute();
} catch (e) {
if (e instanceof PawQLTimeoutError) {
console.log(`Query timed out after ${e.timeoutMs}ms`);
}
}See Query Timeout Guide for full details.
Use .stream() to retrieve rows natively using Generator bounds, avoiding Memory OOM:
for await (const chunk of db.query('users').stream(100)) {
console.log(`Processing batch of ${chunk.length}`);
}See Streaming Guide for full details.
Register lifecycle hooks for cross-cutting concerns:
// Auto-add timestamps
db.hook('users', 'beforeInsert', (ctx) => {
if (ctx.data && !Array.isArray(ctx.data)) {
ctx.data.createdAt = new Date();
}
});
// Global audit logging
db.hook('*', 'afterInsert', (ctx) => {
console.log(`Inserted into ${ctx.table}`);
});
// Block dangerous operations
db.hook('admin_settings', 'beforeDelete', () => {
throw new Error('Cannot delete admin settings!');
});See Hooks Guide for full details.
Define relationships for auto-joins with .with():
import { defineRelations, hasMany, belongsTo, hasOne } from 'pawql';
const relations = defineRelations({
users: {
posts: hasMany('posts', 'userId'),
profile: hasOne('profiles', 'userId'),
},
posts: {
author: belongsTo('users', 'userId'),
},
});
const db = createDB(schema, adapter, { relations });
// Auto-join — no manual join columns!
const usersWithPosts = await db.query('users')
.with('posts')
.with('profile')
.execute();
// → SELECT * FROM "users"
// LEFT JOIN "posts" ON "users"."id" = "posts"."userId"
// LEFT JOIN "profiles" ON "users"."id" = "profiles"."userId"See Relations Guide for full details.
import { createDB, uuid, json, enumType, arrayType, varchar, decimal } from 'pawql';
const db = createDB({
events: {
id: uuid, // UUID
name: varchar(255), // VARCHAR(255)
type: enumType('conference', 'meetup'), // TEXT + CHECK constraint
tags: arrayType(String), // TEXT[]
fee: decimal(5, 2), // DECIMAL(5, 2)
details: json<{ location: string }>(), // JSONB with TypeScript generic
createdAt: Date, // TIMESTAMP
}
}, adapter);PawQL includes a migration system that stays true to its runtime-first philosophy — no code generation, just plain TypeScript migration files using the same schema types you already know.
# Create a new migration file
npx pawql migrate:make create_users
# Run all pending migrations
npx pawql migrate:up
# Rollback the last batch
npx pawql migrate:downMigration files use PawQL's runtime schema types:
import type { MigrationRunner } from 'pawql';
export default {
async up(runner: MigrationRunner) {
await runner.createTable('users', {
id: { type: Number, primaryKey: true },
name: String,
email: { type: String, nullable: true },
});
},
async down(runner: MigrationRunner) {
await runner.dropTable('users');
},
};See Migrations Guide for full details.
For complete documentation, see the docs/ directory:
- Getting Started — Installation, setup, first query, logger & pool config
- Schema Definition — Defining tables, columns, and types
- Querying — SELECT, WHERE, ORDER BY, joins, GROUP BY, HAVING, subqueries, raw SQL
- Mutations — INSERT, UPDATE, DELETE, RETURNING, upsert (ON CONFLICT)
- Transactions — Atomic operations
- Migrations — Database migrations with CLI
- Soft Delete — Soft delete with
.withTrashed(),.onlyTrashed() - Seeders — Populate initial data with
seed()andcreateSeeder() - Parameter Validation — Runtime type validation with
validateRow()andassertValid() - Query Timeout — Cancel long-running queries with
.timeout(ms) - Hooks / Middleware — Lifecycle hooks:
beforeInsert,afterUpdate, etc. - Relations —
hasMany,belongsTo,hasOnewith.with()auto-joins - Multi-Database — PostgreSQL, MySQL/MariaDB, SQLite native adapters
- Introspection — programmatic reverse-engineering of schemas
- Testing — Using DummyAdapter for unit tests + Docker integration tests
- Streaming — Async execution arrays using native Generator streams.
- Plugins — Hook community integrations globally via
PawQLPlugin. - API Reference — Complete API listing (logger, pool, all methods)
Most ORMs require a separate schema definition language or complex build steps. PawQL takes a different approach: your schema is just JavaScript. TypeScript infers everything at compile time, giving you full autocomplete and type checking without any extra tooling.
MIT — Ahmat Fauzi