SQLQueryHelperjs

Control how your database evolves — without losing SQL control.

SQLQueryHelperjs is a runtime database evolution engine for SQL systems.

It allows you to define your schema in code, reflect it at runtime, and safely synchronize database structure — while still writing real SQL.

The published package supports both ESM import and CommonJS require(...) consumers.

🚀 What makes it different?

SQLQueryHelperjs combines capabilities that are usually split across multiple tools:

• Schema definition (like ORMs)
• Schema evolution (like migration tools)
• Full SQL query control (like query builders)
• Reverse engineering of existing databases

All in a single system.

No migrations. No schema drift. Full control.

✨ Core Features

• Code-first schema (classes → tables)
• Runtime schema reflection (db.reflect(...))
• Automatic schema synchronization
• Safe schema evolution (ALTER or rebuild with data preservation)
• Fluent SQL builder (joins, CTEs, subqueries, window functions)
• Built-in validation to prevent invalid SQL
• Legacy database inspection (generate classes from existing DB)
• Entity-aware query helpers
• Runtime engines for SQLite, PostgreSQL, and MySQL

🔄 How it works

1. Define your schema using classes
2. Call db.reflect(...)
3. The system inspects your database
4. Applies safe schema updates
5. Query your data using fluent SQL

🚀 Quick Start

import { SqliteReflector, column, entity, primaryKey } from "sqlqueryhelperjs";

class User {
  static entity = entity({
    table: "users",
    columns: {
      id: column.integer(),
      email: column.text({ nullable: false }),
    },
    primaryKey: primaryKey("id", { autoIncrement: true }),
  });
}

const db = new SqliteReflector({ filename: "app.db" });

db.reflect(User);

const rows = db.select(["id", "email"]).from("users").all();

CommonJS usage is also supported:

const { SqliteReflector, column, entity, primaryKey } = require("sqlqueryhelperjs");

🧭 Legacy Database Support

Already have a database?

You can inspect it and generate entity classes automatically:

sqlh inspect SQLITE "./legacy.db" "./generated/entities.ts"

🧩 Fluent SQL Builder

Supports:

• joins, subqueries, CTEs
• window functions
• set operations (union, intersect, except)
• aggregation
• pagination

🛡️ Safety by default

• prevents invalid SQL chains
• blocks unsafe multi-statements
• requires approval for destructive schema changes
• supports audit logging and change tracking

🎯 Use Cases

• Local-first applications
• Internal tools with evolving schemas
• Rapid prototyping without migrations
• Systems requiring SQL-level control

🐘 Engine Overview

Current implemented runtimes:

• SQLite
• PostgreSQL
• MySQL

Advanced engine features include:

• runtime schema planning (planReflect)
• controlled destructive changes
• schema inspection and entity generation
• views, triggers, and stored routines
• dependency-aware rebuild safety

PostgreSQL remains the most advanced engine for orchestration and governance features.

MySQL now includes query builder, returning(...) for DML, reflect/sync, dependency orchestration, schema change history, audit hooks, routines, triggers, views, and live-tested runtime support.

Benchmark snapshots for SQLite, PostgreSQL, and MySQL are now tracked in the benchmark guide and summarized in the engine matrix.

👉 See full details in DATABASE_ENGINE.md

👉 See benchmark details in docs/benchmarks.md

📚 HTML Documentation

The repository includes a searchable static HTML documentation site powered by TypeDoc.

Read the published documentation at:

👉 https://jgabocc.github.io/SQLQueryHelperJs/

Generate it locally with:

npm run docs

The generated site is written to docs-site/.

If you want the site to rebuild while you document the public API, use:

npm run docs:watch

The docs combine guide pages from docs/ with generated API reference from src/index.ts, so the function and type search stays aligned with the real exported surface.

The editorial guides are separated by engine:

• SQLite
• PostgreSQL
• MySQL
• SQL Server

SQLite, PostgreSQL, and MySQL are implemented runtime engines. SQL Server remains a separated documentation track for future support.

🧭 Roadmap

• ✅ SQLite (production-ready)
• ✅ PostgreSQL (advanced runtime support)
• ✅ MySQL (advanced runtime support)
• 🔜 SQL Server
• 🔜 Cloud sync / multi-instance control

📦 Installation

npm install sqlqueryhelperjs

📄 License

MIT