@keza/BetterSQL

A minimal high performance ORM for Node.js using better-sqlite3.

Thin and SQL first with no decorators, no reflection, no runtime magic. Built directly on better-sqlite3 with cached prepared statements, transaction support, and strong TypeScript inference.

Installation

npm install @keza/bettersql better-sqlite3

Quick Start

import { BetterSQL } from '@keza/bettersql'

const ql = new BetterSQL({ filename: ':memory:' })

const User = ql.define('user', {
  id: { type: 'INTEGER', primary: true, autoIncrement: true },
  email: { type: 'TEXT', unique: true },
  username: { type: 'TEXT' },
  created_at: { type: 'INTEGER' }
})

// Insert a new user
const user = User.insert({
  email: '[email protected]',
  username: 'alice',
  created_at: Date.now()
})

// Query users
const found = User.find({ email: '[email protected]' }).first()
const all = User.find().all()
const byId = User.findById(1)

// Fluent query builder
const active = User.query().where('status', '=', 'active').orderBy('created_at', 'DESC').limit(10).all()

// Chainable methods on results
const contact = User.query().where('id', '=', 1).first()
contact?.delete()
contact?.update({ username: 'new_name' })

// Raw SQL queries
const results = User.query("SELECT * FROM user WHERE status = 'active'")
const paramResults = User.query('SELECT * FROM user WHERE id = ?', [1])

Features

• Type-safe: Full TypeScript support with inferred types
• Fluent API: Chainable query builders for SELECT, INSERT, UPDATE, DELETE
• Cached statements: Prepared statements are automatically cached
• Transactions: Full transaction support with automatic rollback
• Result proxies: Query results have chainable methods like .delete(), .update()
• WAL mode: Enabled by default for better concurrent performance

API Reference

BetterSQL

const ql = new BetterSQL({
  filename: string,  // Database file path or ":memory:"
  wal?: boolean,     // Enable WAL mode (default: true)
});

ql.define(tableName, schema)     // Define a model
ql.getModel(tableName)           // Get existing model
ql.exec(sql)                     // Execute raw SQL
ql.query(sql, params?)           // Query with results
ql.transaction(callback)         // Run in transaction
ql.close()                       // Close connection

Model

Model.insert(data)               // Insert record
Model.insertBuilder(data)        // Fluent insert builder
Model.find(where?)               // Query builder
Model.findById(id)               // Find by primary key
Model.update(where, data)        // Update records
Model.delete(where)              // Delete records
Model.count(where?)              // Count records
Model.all()                      // Get all records
Model.upsert(data)               // Insert or replace

Query Builder

Model.find()
  .where(conditions) // AND conditions
  .where(col, op, val) // Comparison operator
  .orWhere(conditions) // OR conditions
  .orderBy(column, direction) // ORDER BY
  .limit(count) // LIMIT
  .offset(count) // OFFSET
  .select(...columns) // Select columns
  .distinct() // DISTINCT
  .groupBy(...columns) // GROUP BY
  .having(conditions) // HAVING
  .all() // Execute and get all
  .first() // Get first result
  .count() // Get count
  .exists() // Check existence

License

MIT