⚡ @trap_stevo/liveql

Supercharge your database workflow with a visually clean, ultra-intuitive SQL layer.
Chain elegant queries, trigger instant real-time events, and manage schemas effortlessly — all without ORM overhead, blending raw SQL power with modern developer ergonomics to make database interactions fast, fluid, and future-proof.

🚀 Features

• 🖋 Elegant Query Chaining – Build clean, composable SQL queries with zero boilerplate
• 🔗 Joins & Aliases – join/leftJoin/rightJoin/fullJoin + on/andOn/orOn/onVal/onRaw
• 🔄 Full DML + DDL Support – Insert, update, delete, and alter schemas in one unified API
• 📡 Real-Time Database Events – Subscribe to DML and (Postgres) DDL changes
• 🛠 Schema Builder – Create, drop, and alter tables with expressive syntax
• 🔍 Schema Inspector – Introspect tables, columns, PKs, FKs, and indexes
• 🧮 Index Builder – Create/drop regular or unique indexes with ifNotExists safety
• 🌐 Multi-Dialect Ready – Works with PostgreSQL, MySQL, SQLite
• 📜 Migrations – Run SQL files from a folder, track applied migrations
• ⚡ No ORM Bloat – Keep full control of raw SQL with modern ergonomic tooling

⚙️ System Requirements

| Requirement | Version | |------------|------------------------| | Node.js| ≥ 19.x | | npm | ≥ 9.x (recommended) | | OS | Windows, macOS, Linux | | Database | PostgreSQL, MySQL, SQLite |

API Specifications

⚡ LiveQL API

The root class for all LiveQL operations.
Instantiated with an existing database client (PostgreSQL, MySQL, or SQLite).

const LiveQL = require("@trap_stevo/liveql");
const db = new LiveQL(client);

| Method / Getter | Signature | Async | Description | |---|---|:--:|---| | table | table(name: string) | ❌ | Returns a QueryBuilder instance for building and running queries on a specific table. | | schema | schema(name: string) | ❌ | Returns a SchemaBuilder instance for DDL operations on a table. | | inspector | inspector | ❌ | Exposes the SchemaInspector API for table/column/index metadata. | | migrator | migrator | ❌ | Exposes { run, runFromFolder } for migrations. | | realtime | realtime | ❌ | Exposes the Realtime dispatcher (LISTEN/NOTIFY on PG, polling elsewhere). | | indexes | indexes | ❌ | IndexBuilder helper (create/drop, unique, guards). | | raw | raw(sql: string, values?: any[]) | ❌ | Returns a SQL fragment usable within insert() / update() or join predicates. Also available as LiveQL.raw. | | query | query(sql: string, params?: any[]) | ✅ | Pass-through to driver (client.query). | | enableRealtime | enableRealtime(table: string, events?: ("insert"\|"update"\|"delete")[], options?: {...}) | ✅ | Enables realtime listeners for a table. | | disableRealtime | disableRealtime(table: string, events?: string[]) | ✅ | Disables listeners for a table. | | enableDDLEvents | enableDDLEvents() | ✅ | Postgres: installs DDL event trigger and starts listening on ddl__event. | | disableDDLEvents | disableDDLEvents() | ✅ | Drops the DDL trigger/function and unlistens channel. | | setupRealtime | setupRealtime({ tables?: string[], events?: string[], ddl?: boolean, pollOptions?: {...} }) | ✅ | Convenience method to enable multiple tables/events at once and optionally DDL events. |

Constructor

new LiveQL(client);

Example

const { Client } = require("pg");
const LiveQL = require("@trap_stevo/liveql");

const client = new Client({ /* ... */ });
await client.connect();

const db = new LiveQL(client);

// Create a table
await db.schema("users").create([
     { name: "id", type: "SERIAL", primaryKey: true },
     { name: "name", type: "TEXT", notNull: true }
]);

// Insert + select
await db.table("users").insert({ name: "Alice" }).run();
const rows = await db.table("users").select("*").run();
console.log(rows);

🖋 Live Query

db.table(name)

| Method | Signature | Async | Description | |---|---|:--:|---| | as | as(alias: string) | ❌ | Set table alias for joins/projections. | | select | select(...fields: string[]) | ❌ | Set columns ("*" if omitted). Accepts arrays and raw fragments. | | where | where(field: string, value: any) or where(field: string, op: string, value: any) | ❌ | WHERE clause. Supports =, >, <, >=, <=, <>, etc. | | whereRaw | whereRaw(text: string, values?: any[]) | ❌ | Inject a raw WHERE fragment with bound parameters (chained with AND). | | and | and(field: string, valueOrOp: any, valueIfOp?: any) | ❌ | Chain AND condition (alias to where with AND). | | or | or(field: string, valueOrOp: any, valueIfOp?: any) | ❌ | Chain OR condition. | | like | like(field: string, pattern: string) | ❌ | LIKE clause (dialect-appropriate params). | | in | in(field: string, values: any[]) | ❌ | IN (...) clause with bound params. | | orderBy | orderBy(field: string, dir?: "ASC"\|"DESC") | ❌ | Sorting. | | limit | limit(n: number) | ❌ | Limit rows. | | join | join(table: string, alias?: string) | ❌ | INNER JOIN. | | leftJoin | leftJoin(table: string, alias?: string) | ❌ | LEFT JOIN. | | rightJoin | rightJoin(table: string, alias?: string) | ❌ | RIGHT JOIN. | | fullJoin | fullJoin(table: string, alias?: string) | ❌ | FULL OUTER JOIN (Postgres). | | on | on(left: string, op: string, right: string) | ❌ | Join predicate (column-to-column). | | andOn | andOn(left: string, op: string, right: string) | ❌ | Additional AND predicate for join. | | orOn | orOn(left: string, op: string, right: string) | ❌ | Additional OR predicate for join. | | onVal | onVal(left: string, op: string, value: any) | ❌ | Join predicate (column-to-value). | | onRaw | onRaw(text: string, values?: any[]) | ❌ | Raw ON clause fragment. | | insert | insert(data: object \| object[]) | ❌ | Prepare an INSERT (single or bulk). | | update | update(data: object) | ❌ | Prepare an UPDATE (use with where). | | delete | delete() | ❌ | Prepare a DELETE (use with where). | | raw | raw(sql: string, values?: any[]) | ❌ | Create a raw SQL fragment for embedding inside insert()/update() or onRaw(). | | run | run() | ✅ | Execute the built SQL with bound params. Returns driver result (rows on PG). |

Notes

• Placeholders are dialect-aware: $1, $2, ... (PostgreSQL) vs ? (MySQL/SQLite).
• Parameter numbering resets on each .run().
• Plain objects/arrays in INSERT/UPDATE auto-cast to ::jsonb on Postgres.

Examples

// Select with AND/OR/IN/LIMIT
await db.table("users")
     .select("id", "name")
     .where("active", true)
     .and("age", ">", 18)
     .or("email", "LIKE", "%@example.com")
     .in("role", ["admin", "editor"])
     .orderBy("name", "ASC")
     .limit(20)
     .run();

// Update with raw SQL fragment (Postgres JSONB)
await db.table("users")
     .update({
          data : db.raw(
               "jsonb_set(coalesce(data, '{}'::jsonb), '{lastSeen}', to_jsonb($1))",
               [Date.now()]
          )
     })
     .where("id", 1)
     .run();

// Use raw WHERE fragments
await db.table("events")
     .where("type", "=", "click")
     .whereRaw("(data ->> 'elementId') = $1", ["buy-button"])
     .select("id", "type")
     .run();

// Combined raw and standard predicates
await db.table("orders").as("o")
     .where("o.status", "=", "paid")
     .or("o.status", "=", "shipped")
     .whereRaw("(o.total - coalesce(o.discount, 0)) >= $1", [50])
     .select("o.id", "o.total", "o.status")
     .orderBy("o.created_at", "DESC")
     .limit(25)
     .run();

// Joined select + mixed ON conditions
const res = await db
     .table("orders").as("o")
     .leftJoin("users", "u")
     .on("o.user_id", "=", "u.id")
     .andOn("o.status", "=", "u.last_order_status")
     .onVal("o.archived", "=", false)
     .onRaw("u.email LIKE $1", ["%@example.com"])
     .select("o.id", "o.total", "u.name", "u.email")
     .orderBy("o.created_at", "DESC")
     .limit(25)
     .run();

// Fetch the 25 most recent PAID orders in a date window,
// with the buyer’s display name (first + last), username, primary email,
// and a couple of product fields from the line items.
//
// Tables (example names, all lowercase for portability):
//   users u, firstnames fn, lastnames ln, usernames un, emails e,
//   orders o, order_items oi, products p

const start = new Date("2025-10-01T00:00:00Z").toISOString();
const end   = new Date("2025-11-01T00:00:00Z").toISOString();

const rows = await db
     .table("users").as("u")

     // Join the user's name parts (column-to-column ONs)
     .leftJoin("firstnames", "fn").on("u.first_name_id", "=", "fn.id")
     .leftJoin("lastnames",  "ln").on("u.last_name_id",  "=", "ln.id")

     // Username + primary email
     .leftJoin("usernames", "un").on("un.user_id", "=", "u.id")
     .leftJoin("emails",    "e").on("e.user_id",    "=", "u.id")
                                .onVal("e.is_primary", "=", true) // value in ON

     // Orders for the user
     .join("orders", "o").on("o.user_id", "=", "u.id")

     // Order line items and products
     .leftJoin("order_items", "oi").on("oi.order_id", "=", "o.id")
     .leftJoin("products",    "p").on("p.id",        "=", "oi.product_id")

     // ---- WHEREs (values belong in WHERE, not ON) ----
     .where("o.status", "=", "paid")
     .and("o.created_at", ">=", start)
     .and("o.created_at", "<=", end)
     .in("p.category", ["electronics", "appliances"])

     // Small raw predicate with no parameters (portable)
     .whereRaw("(coalesce(e.email, '') <> '')")

     // Projection
     .select(
          "o.id",
          "o.created_at",
          "o.total",
          "u.id",
          "un.username",
          "e.email",
          "fn.first_name",
          "ln.last_name",
          "p.id",
          "p.name",
          "p.category"
     )

     // Sort + limit
     .orderBy("o.created_at", "DESC")
     .limit(25)

     .run();

console.log(rows);

🛠 Schema Builder

db.schema(name)

| Method | Signature | Async | Description | |---|---|:--:|---| | create | create(columns: Array<{ name, type, primaryKey?, autoIncrement?, notNull?, unique?, default? }>, options?: { ifNotExists?: boolean }) | ✅ | Create table with provided columns. | | drop | drop(options?: { ifExists?: boolean }) | ✅ | Drop the table. | | rename | rename(newName: string) | ✅ | Rename table. | | addColumn | addColumn(column: string, type: string, options?: {...}) | ✅ | Add a column. | | dropColumn | dropColumn(column: string) | ✅ | Drop a column. | | alterColumn | alterColumn(column: string, type?: string, options?: { notNull?: boolean, default?: any }) | ✅ | Change type / nullability / default. | | addPrimaryKey | addPrimaryKey(columns: string \| string[]) | ✅ | Add primary key constraint. | | addForeignKey | addForeignKey(column: string, refTable: string, refColumn: string, options?: { onDelete?, onUpdate? }) | ✅ | Add foreign key. | | addIndex | addIndex(indexName: string, columns: string \| string[], options?: { unique?: boolean, ifNotExists?: boolean }) | ✅ | Create (unique) index. | | dropIndex | dropIndex(indexName: string) | ✅ | Drop index (adds CASCADE on PG). |

Column Type Hints

• Auto-increment: PG GENERATED ALWAYS AS IDENTITY, MySQL AUTO_INCREMENT
• default accepts raw SQL strings like "CURRENT_TIMESTAMP"

Example

await db.schema("products").create([
     { name: "id", type: "SERIAL", primaryKey: true },
     { name: "name", type: "TEXT", notNull: true },
     { name: "price", type: "NUMERIC", default: 0 }
], { ifNotExists: true });

await db.schema("products").addColumn("stock", "INT", { default: 0 });

🔍 Schema Inspector

db.inspector

| Method | Signature | Async | Description | |---|---|:--:|---| | listTables | listTables() | ✅ | List table names. | | getColumns | getColumns(table: string) | ✅ | Column metadata. | | getPrimaryKeys | getPrimaryKeys(table: string) | ✅ | Primary key columns. | | getIndexes | getIndexes(table: string) | ✅ | Index metadata. | | getForeignKeys | getForeignKeys(table: string) | ✅ | FK metadata. |

Supports PostgreSQL / MySQL / SQLite (using information_schema or PRAGMA).

🧮 Index Builder

db.indexes

| Method | Signature | Async | Description | |---|---|:--:|---| | create | create(table: string, indexName: string, columns: string[], options?: { unique?: boolean, ifNotExists?: boolean }) | ✅ | Create regular or unique index with safety guards. | | drop | drop(table: string, indexName: string, options?: { ifExists?: boolean }) | ✅ | Drop an index. |

Example

await db.indexes.create("users", "users_email_uq", ["email"], { unique: true, ifNotExists: true });

📂 Live Migration

db.migrator

| Method | Signature | Async | Description | |---|---|:--:|---| | run | run({ up?: string[], down?: string[] }) | ✅ | Run SQL strings directly (in order). | | runFromFolder | runFromFolder(dir: string, direction?: "up"\|"down") | ✅ | Load *.up.sql/*.down.sql from a folder, track in migrations, run/rollback in order. |

Example

await db.migrator.runFromFolder("./migrations", "up");
// ...
await db.migrator.runFromFolder("./migrations", "down");

📡 Live Time

db.realtime

| Method | Signature | Async | Description | |---|---|:--:|---| | onEvent | onEvent(table: string, eventType: "insert"\|"update"\|"delete", handler: (payload) => void) | ❌ | Listen to table events; internally uses event name ${table}__${eventType}. | | listen | listen(channel: string) | ✅ | (PG) LISTEN channel. | | unlisten | unlisten(channel: string) | ✅ | (PG) UNLISTEN channel. | | startPolling | startPolling(table, eventType, interval, fetchFn, { idColumn?, updatedAtColumn? }) | ✅ | Start polling fallback for non-PG. | | stopPolling | stopPolling(table, eventType) | ❌ | Stop polling. |

Enable DML events

db.realtime.onEvent("users", "insert", (row) => console.log("New user:", row));
db.enableRealtime("users", ["insert", "update", "delete"], {
     pollInterval: 1500,
     updatedAtColumn: "updated_at",
     idColumn: "id"
});

Enable DDL events (Postgres)

• Channel: ddl__event
• Payload fields: tag (e.g., CREATE TABLE, ALTER TABLE), schema, table, type

await db.enableDDLEvents();
// ... handle notifications via your RealTime dispatcher’s notification hook
await db.disableDDLEvents();

🔀 Dialect Behavior Summary

| Capability | Postgres | MySQL | SQLite | |---|---|---|---| | Placeholders | $1, $2, ... | ? | ? | | Realtime DML | LISTEN/NOTIFY + triggers | Polling fallback | Polling fallback | | DDL Events | ✅ (event trigger → ddl__event) | ❌ | ❌ | | ::jsonb auto-cast for object/array values | ✅ | ❌ (pass strings/raw) | ❌ (pass strings/raw) | | FULL OUTER JOIN | ✅ | ❌ (emulate via UNION) | ❌ |

📦 Installation

    npm install @trap_stevo/liveql

⚡ Quick Start (PostgreSQL)

    const { Client } = require("pg");
    const LiveQL = require("@trap_stevo/liveql");

    (async () => {
      const client = new Client({
        host: "localhost",
        user: "postgres",
        password: "pass123",
        database: "testdb",
        port: 5432
      });

      await client.connect();

      const db = new LiveQL(client);

      // Fresh table
      await db.schema("users").drop({ ifExists: true });
      await db.schema("users").create([
        { name: "id", type: "SERIAL", primaryKey: true },
        { name: "name", type: "TEXT", notNull: true },
        { name: "email", type: "TEXT", unique: true },
        { name: "created_at", type: "TIMESTAMP", default: "CURRENT_TIMESTAMP" }
      ], { ifNotExists: true });

      // Realtime listeners
      db.realtime.onEvent("users", "insert", (row) => console.log("INSERT:", row));
      db.realtime.onEvent("users", "update", (row) => console.log("UPDATE:", row));
      db.realtime.onEvent("users", "delete", (row) => console.log("DELETE:", row));
      db.enableRealtime("users", ["insert", "update", "delete"]);

      // Insert
      await db.table("users").insert({ name: "John", email: "[email protected]" }).run();
      await db.table("users").insert({ name: "Jane", email: "[email protected]" }).run();

      // Update
      await db.table("users").update({ name: "Johnathan" }).where("id", 1).run();

      // Delete
      await db.table("users").delete().where("id", 2).run();

      // Select
      const result = await db.table("users").select("id", "name", "email", "created_at").run();
      console.log("Users:", result.rows);
    })();

📜 License

See License in LICENSE.md

⚡ SQL Power. Live Updates. Zero Bloat.

LiveQL gives you the raw speed of SQL with the flexibility of a modern query API — and real-time events baked right in. Build, evolve, and react to your database like never before.