@trap_stevo/liveql

⚡ @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", "[e-mail]")
     .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", ["[e-mail]"])
     .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: "[e-mail]" }).run();
      await db.table("users").insert({ name: "Jane", email: "[e-mail]" }).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.