@neogroup/database-connector

A lightweight, fluent TypeScript library for interacting with relational databases. It provides a chainable query builder, connection/transaction management, and pluggable data sources for PostgreSQL, MySQL, and SQLite — with a clean architecture that makes it easy to add new engines.

Table of contents

• Installation
• Data sources
  • PostgreSQL
  • MySQL
  • SQLite
  • Multiple sources
• Querying with DataTable
  • SELECT — basic
  • Filtering — WHERE
  • Conditional clauses — when
  • Sorting — ORDER BY
  • Pagination — LIMIT & OFFSET
  • Grouping — GROUP BY & HAVING
  • Joins
  • Distinct
  • Field aliases & functions
  • Table alias
• INSERT, UPDATE, DELETE
• Raw queries
• Advanced queries with SelectQuery
  • UNION / UNION ALL
  • Subqueries
• Connections & transactions
• Debug mode
• Extending the library
• Contact

Installation

npm install @neogroup/database-connector

Depending on the database engine you use, install the corresponding driver:

| Engine | Driver | |------------|-------------------------| | PostgreSQL | npm install pg | | MySQL | npm install mysql2 | | SQLite | built-in (Node ≥ 22.5) |

Data sources

A DataSource represents a configured connection to a database engine. Register it once at startup with DB.register() and use DB anywhere in your code from that point on.

PostgreSQL

import { DB, PostgresDataSource } from '@neogroup/database-connector'

const source = new PostgresDataSource()
source.setHost('localhost')
source.setPort(5432)               // default: 5432
source.setDatabaseName('mydb')
source.setUsername('admin')
source.setPassword('secret')

DB.register(source)

MySQL

import { DB, MysqlDataSource } from '@neogroup/database-connector'

const source = new MysqlDataSource()
source.setHost('localhost')
source.setPort(3306)               // default: 3306
source.setDatabaseName('mydb')
source.setUsername('admin')
source.setPassword('secret')

DB.register(source)

MySQL identifiers (table names, column names) are automatically quoted with backticks to avoid conflicts with reserved words.

SQLite

import { DB, SqliteDataSource } from '@neogroup/database-connector'

const source = new SqliteDataSource()         // in-memory database
// source.setFilename('./data.db')            // or a file path

DB.register(source)

SQLite uses Node's built-in node:sqlite module — no native compilation required.

Multiple sources

You can register multiple sources and switch between them by name:

DB.register('primary', primarySource)
DB.register('reporting', reportingSource)

// Use a specific source
const rows = await DB.source('reporting').table('analytics').find()

When no name is given, the first registered source is the default and DB.table(...) targets it automatically.

Querying with DataTable

DB.table('name') returns a DataTable — a chainable query builder scoped to a single table. All methods return the same DataTable instance so you can chain them freely.

SELECT — basic

// SELECT * FROM users
const users = await DB.table('users').find()

// SELECT * FROM users LIMIT 1
const user = await DB.table('users').first()   // returns null if no rows match

Select specific columns:

// SELECT id, name, email FROM users
const users = await DB.table('users')
  .select('id', 'name', 'email')
  .find()

Filtering — WHERE

Simple equality:

// WHERE name = 'Alice'
await DB.table('users').where('name', 'Alice').find()

Comparison operators (=, <>, <, >, <=, >=):

await DB.table('users').where('age', '>', 18).find()
await DB.table('users').where('age', '<>', 30).find()

Multiple conditions (AND by default):

// WHERE active = 1 AND age > 18
await DB.table('users').where('active', 1).where('age', '>', 18).find()

OR conditions:

// WHERE name = 'Alice' OR name = 'Bob'
await DB.table('users').where('name', 'Alice').orWhere('name', 'Bob').find()

IN / NOT IN:

// WHERE role IN ('admin', 'editor')
await DB.table('users').whereIn('role', ['admin', 'editor']).find()

// WHERE id NOT IN (1, 2, 3)
await DB.table('users').whereNotIn('id', [1, 2, 3]).find()

Also available as orWhereIn / orWhereNotIn.

BETWEEN / NOT BETWEEN:

// WHERE age BETWEEN 18 AND 65
await DB.table('users').whereBetween('age', [18, 65]).find()

// WHERE created_at NOT BETWEEN '2024-01-01' AND '2024-12-31'
await DB.table('orders').whereNotBetween('created_at', ['2024-01-01', '2024-12-31']).find()

Also available as orWhereBetween / orWhereNotBetween.

LIKE / NOT LIKE:

await DB.table('users').whereLike('email', '%@gmail.com').find()
await DB.table('users').whereNotLike('name', 'A%').find()

Also available as orWhereLike / orWhereNotLike.

NULL checks:

// WHERE deleted_at IS NULL
await DB.table('users').whereNull('deleted_at').find()

// WHERE deleted_at IS NOT NULL
await DB.table('users').whereNotNull('deleted_at').find()

Also available as orWhereNull / orWhereNotNull.

Grouped conditions (parentheses):

Pass a callback to where() to create a parenthesized group. Inside the callback the full where* / orWhere* API is available:

// WHERE (name = 'Alice' OR name = 'Bob') AND active = 1
await DB.table('users')
  .where((group) => group.where('name', 'Alice').orWhere('name', 'Bob'))
  .where('active', 1)
  .find()

// WHERE (age IN (25, 30)) AND active = 1
await DB.table('users')
  .where((q) => q.whereIn('age', [25, 30]))
  .where('active', 1)
  .find()

// WHERE (age BETWEEN 18 AND 40 OR nickname IS NULL)
await DB.table('users')
  .where((q) => q.whereBetween('age', [18, 40]).orWhereNull('nickname'))
  .find()

Nest groups as deep as needed:

// WHERE (role = 'admin' OR (role = 'editor' AND verified = 1))
await DB.table('users')
  .where((group) =>
    group
      .where('role', 'admin')
      .orWhere((inner) => inner.where('role', 'editor').where('verified', 1))
  )
  .find()

orWhere() also accepts a callback, producing an OR-connected group.

Column comparisons — whereColumn / orWhereColumn:

Compare two columns against each other instead of against a scalar value:

// WHERE updated_at > created_at
await DB.table('users').whereColumn('updated_at', '>', 'created_at').find()

// WHERE col_a = col_b  (operator defaults to =)
await DB.table('items').whereColumn('col_a', 'col_b').find()

// OR variant
await DB.table('items').whereColumn('a', 'b').orWhereColumn('a', '>', 'b').find()

whereColumn / orWhereColumn are also available inside grouped-condition callbacks:

await DB.table('items')
  .where((q) => q.whereColumn('a', 'b').orWhereColumn('a', '>', 'b'))
  .find()

Conditional clauses — when

when(condition, callback) applies query modifications only when condition is truthy. This keeps dynamic query building readable without if statements scattered through the chain:

const onlyActive = true
const minAge = 18

const users = await DB.table('users')
  .when(onlyActive, (q) => q.where('active', 1))
  .when(minAge != null, (q) => q.where('age', '>=', minAge))
  .find()

When condition is falsy the callback is skipped and the chain continues unchanged. when works on both DataTable and SelectQuery.

Sorting — ORDER BY

// ORDER BY name ASC
await DB.table('users').orderBy('name').find()

// ORDER BY age DESC
import { OrderByDirection } from '@neogroup/database-connector'
await DB.table('users').orderBy('age', OrderByDirection.DESC).find()

// Multiple sort fields
await DB.table('users')
  .orderBy('country', OrderByDirection.ASC)
  .orderBy('age', OrderByDirection.DESC)
  .find()

Pagination — LIMIT & OFFSET

// LIMIT 10
await DB.table('users').limit(10).find()

// LIMIT 10 OFFSET 20  (page 3)
await DB.table('users').limit(10).offset(20).find()

// OFFSET only (SQLite emits LIMIT -1 automatically)
await DB.table('users').offset(5).find()

Grouping — GROUP BY & HAVING

// SELECT country, COUNT(*) AS total FROM users GROUP BY country
await DB.table('users')
  .select('country', 'COUNT(*) AS total')
  .groupBy('country')
  .find()

// GROUP BY + HAVING
// HAVING COUNT(*) > 5
await DB.table('users')
  .select('country', 'COUNT(*) AS total')
  .groupBy('country')
  .having('COUNT(*)', '>', 5)
  .find()

having() and orHaving() accept the same signatures as where().

Joins

// INNER JOIN orders ON users.id = orders.user_id
await DB.table('users')
  .innerJoin('orders', 'users.id', 'orders.user_id')
  .find()

// LEFT JOIN
await DB.table('users')
  .leftJoin('orders', 'users.id', 'orders.user_id')
  .find()

Fields follow the 'table.field' notation — the builder parses and quotes each component according to the engine (e.g. backticks in MySQL). Object form is also accepted for finer control:

.innerJoin('orders', { name: 'id', table: 'users' }, { name: 'user_id', table: 'orders' })

Available join methods: join(), innerJoin(), leftJoin(), rightJoin(), outerJoin(), crossJoin().

Join with table alias:

await DB.table('users')
  .innerJoin('orders', 'users.id', 'orders.user_id', 'o')
  .select('users.name', 'o.total')
  .find()

Distinct

// SELECT DISTINCT country FROM users
await DB.table('users').distinct().select('country').find()

Field aliases & functions

Raw string fields support 'table.field' notation and 'FUNC(table.field)' — the builder parses and quotes each component using the engine's rules:

// SELECT users.name, COUNT(orders.id) AS order_count FROM ...
await DB.table('users')
  .select('users.name', 'COUNT(orders.id) AS order_count')
  .innerJoin('orders', 'users.id', 'orders.user_id')
  .groupBy('users.id')
  .find()

Object form is also available for precise control:

// SELECT COUNT(*) AS total FROM users
await DB.table('users')
  .select({ name: '*', function: 'COUNT', alias: 'total' })
  .find()

// SELECT MAX(age) AS max_age FROM users
await DB.table('users')
  .select({ name: 'age', function: 'MAX', alias: 'max_age' })
  .find()

Table alias

// FROM users AS u
await DB.table('users').alias('u').find()

INSERT, UPDATE, DELETE

INSERT:

// INSERT INTO users (name, email, age) VALUES ('Alice', '[email protected]', 30)
const rowsAffected = await DB.table('users')
  .set('name', 'Alice')
  .set('email', '[email protected]')
  .set('age', 30)
  .insert()

UPDATE:

// UPDATE users SET active = 0 WHERE id = 7
const rowsAffected = await DB.table('users')
  .where('id', 7)
  .set('active', 0)
  .update()

DELETE:

// DELETE FROM users WHERE active = 0
const rowsAffected = await DB.table('users').where('active', 0).delete()

⚠️ Omitting where() on update() and delete() will affect all rows in the table.

Raw queries

When you need full control over the SQL string:

// Raw SELECT
const rows = await DB.source('primary').query(
  'SELECT * FROM users WHERE age > ? AND active = ?',
  [18, 1]
)

// Raw execute (INSERT / UPDATE / DELETE / DDL)
const affected = await DB.source('primary').execute(
  'UPDATE users SET active = ? WHERE last_login < ?',
  [0, '2023-01-01']
)

Advanced queries with SelectQuery

For queries that go beyond a single table — unions, subqueries, or complex joins — build a SelectQuery directly and pass it to source.query().

UNION / UNION ALL

import { DB } from '@neogroup/database-connector'

// SELECT name FROM users WHERE active = 1
// UNION
// SELECT name FROM users WHERE age > 50
const query = DB.selectQuery('users')
  .select('name')
  .where('active', 1)
  .union(
    DB.selectQuery('users').select('name').where('age', '>', 50)
  )

const rows = await DB.source('primary').query(query)

union() removes duplicates (standard SQL UNION). Use unionAll() to keep them:

const query = DB.selectQuery('orders')
  .select('user_id')
  .where('status', 'paid')
  .unionAll(
    DB.selectQuery('orders').select('user_id').where('status', 'refunded')
  )

Chain multiple unions:

const query = DB.selectQuery('table_a').select('id')
  .union(DB.selectQuery('table_b').select('id'))
  .union(DB.selectQuery('table_c').select('id'))

Subqueries

A SelectQuery can be used as a value in a where() condition:

const activeUserIds = DB.selectQuery('users').select('id').where('active', 1)

const orders = await DB.source('primary').query(
  DB.selectQuery('orders').where('user_id', 'IN', activeUserIds)
)

Connections & transactions

Obtain an explicit connection for multi-step operations or transactions:

const conn = await DB.source('primary').getConnection()

try {
  await conn.beginTransaction()

  await conn.execute('INSERT INTO accounts (user_id, balance) VALUES (?, ?)', [1, 1000])
  await conn.execute('UPDATE accounts SET balance = balance - ? WHERE user_id = ?', [200, 2])

  await conn.commitTransaction()
} catch (err) {
  await conn.rollbackTransaction()
} finally {
  await conn.close()
}

The helper executeTransaction() handles begin/commit/rollback automatically:

const conn = await DB.source('primary').getConnection()

await conn.executeTransaction(async (tx) => {
  await tx.execute('INSERT INTO logs (event) VALUES (?)', ['login'])
  await tx.execute('UPDATE users SET last_login = NOW() WHERE id = ?', [userId])
  // throws → automatic rollback; resolves → automatic commit
})

await conn.close()

Debug mode

Enable SQL logging on a data source to print every statement and its bindings to the console:

source.setDebugEnabled(true)

Example output:

SQL: SELECT * FROM users WHERE active = ? AND age > ?;   ["1", 18]

Read-only mode prevents any write operations from reaching the database — useful for testing:

source.setReadonly(true)

Extending the library

Custom data source

Implement DataSource to connect any database engine:

import { Connection, DataSource } from '@neogroup/database-connector'

class MyConnection implements Connection {
  async query(sql: string, bindings?: any[]): Promise<any[]> { /* ... */ }
  async execute(sql: string, bindings?: any[]): Promise<number> { /* ... */ }
  async beginTransaction(): Promise<void> { /* ... */ }
  async commitTransaction(): Promise<void> { /* ... */ }
  async rollbackTransaction(): Promise<void> { /* ... */ }
  async close(): Promise<void> { /* ... */ }
}

class MyDataSource extends DataSource {
  protected async requestConnection(): Promise<Connection> {
    return new MyConnection(/* ... */)
  }
  async close(): Promise<void> { /* ... */ }
}

Custom query builder

Override DefaultQueryBuilder to adapt SQL generation for your engine:

import { DefaultQueryBuilder, Statement, Table } from '@neogroup/database-connector'

class MyQueryBuilder extends DefaultQueryBuilder {
  protected buildTable(table: Table, statement: Statement) {
    // e.g. wrap table names in double-brackets for SQL Server
    statement.sql += typeof table === 'string' ? `[${table}]` : `[${table.name}]`
  }
}

Pass your custom builder to the data source constructor:

class MyDataSource extends DataSource {
  constructor() {
    super(new MyQueryBuilder())
  }
  // ...
}

Contact

For bugs or feature requests open an issue on GitHub or contact the author at [email protected].