@jay-chauhan/hybridb

One fluent query builder — table().select().where()... — that runs against MySQL, Postgres, or MongoDB, chosen by a single driver string. Built as a follow-up to @morphsync/mysql-db, fixing its known bugs and generalizing the interface across SQL and NoSQL.

Install

npm install @jay-chauhan/hybridb
# plus whichever driver(s) you actually use:
npm install mysql2      # for driver: 'mysql'
npm install pg          # for driver: 'postgres'
npm install mongodb     # for driver: 'mongo'

Quick Start

const { DB } = require('@jay-chauhan/hybridb');

// MySQL
const db = new DB('mysql', { host: 'localhost', port: 3306, user: 'root', password: '', database: 'app' });

// Postgres — same shape, swap the driver string
// const db = new DB('postgres', { host: 'localhost', port: 5432, user: 'postgres', password: '', database: 'app' });

// MongoDB — config is { uri, database } instead of host/user/pass
// const db = new DB('mongo', { uri: 'mongodb://localhost:27017', database: 'app' });

await db.connect();

const users = await db.table('users')
  .select('id', 'name', 'email')
  .where('status', 'active')
  .orderBy('created_at', 'DESC')
  .limit(10)
  .get();

await db.disconnect();

Same chain, same method names, regardless of driver. db.table(...) also works for Mongo (aliased to collection(...) if you prefer that name there).

API

| Method | SQL adapters | Mongo adapter | |---|---|---| | .table(name) / .collection(name) | table | collection | | .select(...cols) | SELECT cols | projection | | .where(col, val, op='=') | AND-ed condition | AND-ed condition | | .orWhere(col, val, op='=') | OR-ed condition | OR-ed condition | | .whereIn(col, arr) / .whereNotIn(col, arr) | IN / NOT IN | $in / $nin | | .whereNull(col) / .whereNotNull(col) | IS NULL / IS NOT NULL | {col: null} / {col: {$ne: null}} | | .rawWhere(sql) | raw SQL fragment | not supported — throws (no safe Mongo equivalent); use .raw() | | .join(table, localField, foreignField, type) | JOIN ... ON t.localField = table.foreignField — localField can be otherTable.col to chain off an earlier join instead of the root table | $lookup aggregation stage | | .groupBy(...cols) | GROUP BY | $group aggregation stage (best-effort — see caveat below) | | .having(col, val, op='=') | HAVING, applied after GROUP BY | post-$group $match — col is 'count' (group size) or a grouped field (see caveat below) | | .orderBy(col, dir) | ORDER BY — dir must be 'ASC'/'DESC' (throws otherwise) | .sort() — same validation | | .limit(n) / .offset(n) | LIMIT / OFFSET — must be a non-negative integer (throws otherwise; see Security below) | .limit() / .skip() — same validation | | .get() / .first() / .count() | rows / first row / count | docs / first doc / count | | .insert(data) — object or array | INSERT ... VALUES, batched | insertOne / insertMany | | .update(data) | UPDATE ... SET | updateMany with $set | | .delete() | DELETE FROM | deleteMany | | .raw(query, params) (SQL) / .raw(pipeline \| fn) (Mongo) | parameterized raw query | aggregation pipeline, or (db) => ... for full native access | | db.startTransaction() | returns a transaction object bound to its own connection | returns one bound to its own session (needs a replica set — standalone Mongo doesn't support multi-doc transactions) | | trx.table(name) / trx.commit() / trx.rollback() | run queries against trx, not db, for the duration of the transaction | same | | db.transaction(fn) | runs fn(trx), auto-commit/rollback | same |

Transactions

startTransaction() returns a transaction object, not undefined — build and run queries against that, not against db, so the queries land on the dedicated connection/session instead of the shared pool. db itself never holds transaction state, so it's safe to have multiple transactions in flight at once on the same shared db — each startTransaction() call gets its own connection/session.

Prefer db.transaction(fn) — it commits on success and rolls back and releases the connection/session if fn throws:

await db.transaction(async trx => {
  await trx.table('accounts').where('id', 1).update({ balance: 90 });
  await trx.table('accounts').where('id', 2).update({ balance: 110 });
});

The manual form is still available for flows that can't be expressed as one callback (e.g. spanning multiple request handlers), but it's on you to release the connection on every code path — an uncaught error between startTransaction() and commit()/rollback() leaks the connection back to nothing, and enough leaks exhaust the pool:

const trx = await db.startTransaction();
try {
  await trx.table('accounts').where('id', 1).update({ balance: 90 });
  await trx.commit();
} catch (err) {
  await trx.rollback();
  throw err;
}

Observability

Pass onQuery({ query, params, durationMs, error }) in the config to log/measure every query (SQL and Mongo both call it, with params omitted for Mongo since its "query" is already a filter/pipeline object). MySQL and Postgres also accept onError(err), called if the connection pool itself errors (e.g. a dropped idle connection) — without one, an unhandled 'error' event on the pool would crash the process; a default console.error logger is used if you don't provide one. Mongo doesn't need this — the driver surfaces failures through the rejected operation promise instead of a pool-level event.

const db = new DB('mysql', {
  host: 'localhost', database: 'app', user: 'root', password: '',
  onQuery: ({ query, durationMs, error }) => {
    if (durationMs > 200) logger.warn('slow query', { query, durationMs });
    if (error) logger.error('query failed', { query, error });
  },
  onError: err => logger.error('pool error', err)
});

What changed vs. @morphsync/mysql-db v1.1.3

Fixed while building this:

• Shared mutable query state. .table() used to mutate the adapter instance itself and return this, so a single pooled db reused across concurrent requests (the normal pattern in a server) could have one request's .where()/.join()/etc. leak into another's in-flight query. .table()/.collection() now return a dedicated query object per call, holding its own state; only the connection pool (SQL) or client/db handle (Mongo) is shared. Safe to keep one db instance for the life of the process and call .table(...) per request.
• Shared mutable transaction state. Same bug, in the transaction methods: startTransaction() used to stash the dedicated connection/session on the adapter instance (db.txConn/db.txClient/db.session), so two concurrent transactions on the same db — or a transaction racing a non-transactional query — could bleed into each other's connection. startTransaction() now returns a transaction object carrying its own connection/session; run queries against that object (trx.table(...)), not against db. See "Transactions" below. This is a breaking change — code that did db.startTransaction(); await db.table(...)...; await db.commit(); must change to capture and use the returned transaction object.
• SELECT with no .select() call now correctly defaults to SELECT * (v1 produced invalid SQL: SELECT FROM table).
• .where().orWhere() grouping now wraps each side in parentheses — WHERE (a AND b) OR (c) — instead of the ungrouped WHERE a AND b OR c, which silently evaluated with the wrong operator precedence.
• Bulk .insert([...]) now actually batches; v1's insert() only handled a single object despite the README documenting array input.
• Added the methods the old README documented but the code didn't implement: whereNotIn, whereNull, whereNotNull, count(), a real parameterized raw(), offset().
• Switched MySQL/Postgres to connection pooling (createPool) instead of a single connection, for concurrent request handling.
• LIMIT/OFFSET/ORDER BY direction were SQL-injectable. They're string-interpolated (SQL can't parameterize them), and nothing validated what went in — .limit(req.query.limit) on unsanitized pagination input was a direct injection vector. .limit()/.offset() now require a non-negative integer and .orderBy()'s direction must be 'ASC'/'DESC'; anything else throws instead of reaching the query string.
• .insert(data)/.update(data) object keys were unescaped column names. db.table('users').insert(req.body) let an attacker-controlled key land directly in the SQL. Keys are now validated as plain identifiers (letters/digits/underscore) before being used, both for insert() and the shared update() builder.
• .whereIn(col, []) silently dropped the filter instead of matching nothing. IN () is invalid SQL, so the old code just skipped adding the condition — turning an empty allowlist (e.g. "IDs this user is allowed to see," computed as []) into "no filter at all," a potential authorization bypass. It's now translated to a condition that actually matches zero rows (SQL: 1 = 0; Mongo: $in: [], which already does this natively).
• Pool errors could crash the process. mysql2/pg pools are EventEmitters; an unhandled 'error' event on one (e.g. from a dropped idle connection) throws by default and takes the whole process down. Both adapters now register a pool error handler (config.onError, defaulting to console.error).
• Manual transactions could leak a pooled connection forever if application code threw between startTransaction() and commit()/rollback(). Added db.transaction(fn), which always releases the connection/session, success or failure.
• Added .having() (SQL: real HAVING; Mongo: best-effort $match after $group) and multi-hop .join() chains (.join('c', 'b.field', 'c.field') joins off the previous join instead of always the root table).
• Added an onQuery hook for query-level logging/metrics, since there was previously no way to observe what the builder was actually sending.

Breaking change from v1: .join(table, condition, type) took a raw SQL condition string ('users.id = profiles.user_id'), which has no Mongo translation. It's now .join(table, localField, foreignField, type) — plain field names — so the same call can become a SQL JOIN or a Mongo $lookup.

Known limitations (read before relying on these)

• Postgres insert() assumes an id primary key column by default — pass the real column name as a second argument, .insert(data, 'uuid'), if your table's PK is named differently.
• .groupBy()/.having() on Mongo are best-effort, not a general GROUP BY ... HAVING ... translator. $group produces {_id: <grouped fields>, count, docs: [...]}; .having() can only filter on 'count' or one of the grouped fields (as _id.<field> under the hood). Anything past that — real aggregate expressions, HAVING SUM(x) > y — needs .raw(pipeline) with a hand-written pipeline. Also: $group's docs: {$push: '$$ROOT'} collects every matched document into memory per group with no cap, and neither $group nor $lookup set allowDiskUse, so large groups/joins can hit MongoDB's in-memory aggregation limit (~100MB/stage) and fail outright. Use .raw(pipeline) with allowDiskUse: true for heavy aggregate reporting.
• .join() on Mongo is always effectively a left-outer $lookup — Mongo has no native inner-join semantics; the type argument is accepted for API symmetry but doesn't change behavior there. It's also always single-hop: unlike the SQL adapters' dot-qualified chaining, Mongo $lookup can't reference a field from an earlier $lookup without restructuring the pipeline (let/pipeline + $unwind) — use .raw(pipeline) for multi-hop joins on Mongo.
• Identifiers are not escaped/allowlisted everywhere — .table(), .select(), .orderBy()'s column, .groupBy(), and .join()'s table/field arguments are still interpolated as-is (intentionally, since .select() in particular is expected to carry expressions like 'COUNT(*) as total'). Only values, LIMIT/OFFSET/ORDER BY direction, and insert()/update() object keys are validated. Don't build the former from unsanitized user input.
• No table aliasing — no self-joins, no joining the same table twice, no shortening long table names in generated SQL.
• OFFSET-based pagination degrades at depth — OFFSET 500000 gets slow on large tables regardless of indexing. There's no built-in keyset/cursor pagination; for deep pagination, build it yourself with .where() on an indexed cursor column instead of .offset().
• No automatic retry/backoff on failed queries or lost connections — intentionally not added, since blindly retrying a non-idempotent write (e.g. an INSERT that actually succeeded but the acknowledgment was lost) can silently duplicate data. Any retry policy needs to know which operations are safe to retry, which is an application-level decision.
• Mongo transactions require a replica set or mongos — they'll throw against a standalone mongod.
• This is a builder for common CRUD, not a full ORM — no migrations, no schema validation, no relationship loading beyond a single $lookup/JOIN per call.

Tests

test/sql-build.test.js and test/mongo-build.test.js check query/filter generation against expected output without needing a live database (Mongo tests stub the collection driver). Run with:

node test/sql-build.test.js
node test/mongo-build.test.js