@gibme/sql

Simple SQL Helpers for MySQL, MariaDB, SQLite, & Postgres

A TypeScript database abstraction library providing a unified interface for MySQL, MariaDB, PostgreSQL, and SQLite. It wraps driver-specific behavior behind a common abstract Database class so consumers use the same API regardless of backend.

Node >= 22 required.

Documentation

https://gibme-npm.github.io/sql/

Installation

yarn add @gibme/sql

Quick Start

Factory Function

Use createConnection() to instantiate the correct driver based on Database.Type:

import { createConnection, Database } from '@gibme/sql';

const client = createConnection(Database.Type.SQLITE, {
    filename: ':memory:'
});

await client.createTable('test', [
    { name: 'id', type: 'integer' },
    { name: 'value', type: 'varchar(255)' }
], ['id']);

const [rows, meta] = await client.query('SELECT * FROM test');

When no arguments are provided, createConnection() reads from environment variables (see Environment Variables below) and defaults to an in-memory SQLite database.

Direct Imports

Import specific drivers directly to avoid bundling unused drivers:

import MySQL from '@gibme/sql/mysql';
import MariaDB from '@gibme/sql/mariadb';
import Postgres from '@gibme/sql/postgres';
import SQLite from '@gibme/sql/sqlite';
import { Database } from '@gibme/sql/database';

Driver Configuration

MySQL

import MySQL from '@gibme/sql/mysql';

const client = new MySQL({
    host: 'localhost',       // default: '127.0.0.1'
    port: 3306,              // default: 3306
    user: 'someuser',        // default: ''
    password: 'somepassword',
    database: 'somedatabase',
    connectTimeout: 30_000,  // default: 30000 ms
    useSSL: false,           // default: false
    rejectUnauthorized: false // default: false
});

The MySQL driver accepts all mariadb.PoolConfig options in addition to the ones listed above.

The second constructor argument accepts a table options string used when creating tables (default: 'ENGINE=InnoDB PACK_KEYS=1 ROW_FORMAT=COMPRESSED').

MariaDB

MariaDB extends the MySQL driver and accepts the same configuration. The only difference is the UPSERT dialect used internally.

import MariaDB from '@gibme/sql/mariadb';

const client = new MariaDB({
    host: 'localhost',
    port: 3306,
    user: 'someuser',
    password: 'somepassword',
    database: 'somedatabase'
});

Postgres

import Postgres from '@gibme/sql/postgres';

const client = new Postgres({
    host: 'localhost',       // default: '127.0.0.1'
    port: 5432,              // default: 5432
    user: 'someuser',        // default: ''
    password: 'somepassword',
    database: 'somedatabase',
    ssl: false,              // default: false
    rejectUnauthorized: false // default: false
});

The Postgres driver accepts all pg.PoolConfig options in addition to the ones listed above.

SQLite

import SQLite from '@gibme/sql/sqlite';

const client = new SQLite({
    filename: './data.db', // default: ':memory:'
    readonly: false,       // default: false
    foreignKeys: true,     // default: true (enables PRAGMA foreign_keys)
    WALmode: true          // default: true (enables PRAGMA journal_mode=WAL)
});

SQLite instances are managed as singletons per filename, so multiple SQLite instances pointing to the same file share the underlying connection. All operations are serialized through a mutex for thread safety.

PRAGMA Support

Read and set PRAGMA values on SQLite connections:

const walMode = await client.getPragma('journal_mode');
await client.setPragma('foreign_keys', true);

The following PRAGMAs are supported through the dedicated methods: quick_check, integrity_check, incremental_vacuum, foreign_key_check, foreign_key_list, index_info, index_list, index_xinfo, table_info, table_xinfo, and optimize. Other PRAGMAs can be executed directly via query().

Query Results

All queries return a [rows, metadata, query] tuple:

const [rows, meta, query] = await client.query<{
    column1: string,
    column2: number
}>('SELECT * FROM test WHERE column1 = ?', 'value');

// rows  - RecordType[] array of result rows
// meta  - { changedRows, affectedRows, insertId?, length }
// query - { query, values? } the executed query

Query parameters use ? placeholders across all drivers. The Postgres driver automatically converts these to $1, $2, ... numbered parameters internally.

Table Management

Creating Tables

await client.createTable('users', [
    { name: 'id', type: 'integer', nullable: false },
    { name: 'name', type: 'varchar(255)' },
    { name: 'email', type: 'varchar(255)', unique: true },
    { name: 'role', type: 'varchar(50)', default: 'user' },
    { name: 'score', type: 'float', nullable: true }
], ['id']); // primary key columns

Tables are created with IF NOT EXISTS. Columns marked unique: true automatically get a unique index.

Column Options

Option	Type	Default	Description
name	string	required	Column name
type	string	required	SQL type (e.g., varchar(255), integer, float)
nullable	boolean	true	Whether the column allows NULL values
default	string | number | boolean	—	Default value for the column
unique	boolean	false	Creates a unique index on this column
foreignKey	ForeignKey	—	Foreign key relationship (see below)

Column types are validated against the pattern /^[a-zA-Z][a-zA-Z0-9 (),]*$/.

Foreign Key Constraints

await client.createTable('orders', [
    { name: 'id', type: 'integer' },
    {
        name: 'user_id',
        type: 'integer',
        foreignKey: {
            table: 'users',
            column: 'id',
            onDelete: Database.Table.ForeignKeyConstraint.CASCADE,
            onUpdate: Database.Table.ForeignKeyConstraint.CASCADE
        }
    }
], ['id']);

Available constraints: RESTRICT, CASCADE, NULL (SET NULL), DEFAULT (SET DEFAULT), NA (NO ACTION).

Indexes

// Standard index
await client.createIndex('users', ['email']);

// Unique index
await client.createIndex('users', ['email'], Database.Table.IndexType.UNIQUE);

Other Table Operations

// List all tables
const tables = await client.listTables();

// Drop tables
await client.dropTable('users');
await client.dropTable(['temp1', 'temp2']);

// Truncate tables
await client.truncate('users');

// Switch database (MySQL/MariaDB/Postgres)
await client.use('other_database');

Transactions

All drivers support transactions via transaction():

const results = await client.transaction([
    { query: 'INSERT INTO users (name) VALUES (?)', values: ['Alice'] },
    { query: 'INSERT INTO users (name) VALUES (?)', values: ['Bob'] }
]);

// results is an array of [rows, metadata, query] tuples, one per query

Set noError: true on individual queries to ignore their failures without aborting the transaction (MySQL/MariaDB/Postgres only — SQLite transactions are atomic and roll back entirely on any failure).

await client.transaction([
    { query: 'INSERT INTO users (name) VALUES (?)', values: ['Alice'] },
    { query: 'INSERT INTO users (name) VALUES (?)', values: ['duplicate'], noError: true },
    { query: 'INSERT INTO users (name) VALUES (?)', values: ['Charlie'] }
]);

Bulk Operations

Bulk Insert

await client.multiInsert('test', ['col1', 'col2'], [
    ['a', 1],
    ['b', 2],
    ['c', 3]
]);

Bulk Upsert

Insert rows or update them if they conflict on the primary key:

await client.multiUpdate('test', ['col1'], ['col1', 'col2'], [
    ['a', 10],  // updates existing row
    ['d', 40]   // inserts new row
]);

Both operations accept an optional useTransaction parameter (default: true) to control whether the bulk operation is wrapped in a transaction.

Prepared Queries

Generate query objects without executing them, useful for combining with other operations or inspecting the generated SQL:

const queries = client.prepareMultiInsert('test', ['col1', 'col2'], [
    ['a', 1],
    ['b', 2]
]);

const createQueries = client.prepareCreateTable('test', [
    { name: 'id', type: 'integer' }
], ['id']);

Connection Pool

MySQL/MariaDB use the mariadb driver's native connection pool. PostgreSQL uses pg's pool. SQLite uses a singleton instance with mutex-based concurrency.

Monitor pool status via getters:

console.log(client.idleConnections); // connections available in pool
console.log(client.totalConnections); // total pool size

Events

The Database class extends EventEmitter. Available events vary by driver:

MySQL / MariaDB:

Event	Description
error	Connection error
acquire	Connection acquired from pool
connection	New connection created
enqueue	Connection request queued (pool exhausted)
release	Connection released back to pool

Postgres:

Event	Description
connect	New connection created
acquire	Connection acquired from pool
remove	Connection removed from pool
error	Connection error

client.on('error', (err) => {
    console.error('Database connection error:', err);
});

Utility Methods

// Escape a string value for safe SQL interpolation
const safe = client.escape(userInput);

// Escape an identifier (table/column name)
const id = client.escapeId('column name');

// Check the driver type
if (client.type === Database.Type.POSTGRES) { /* ... */ }
console.log(client.typeName); // 'MySQL', 'MariaDB', 'Postgres', or 'SQLite'

Escaping is driver-aware: Postgres uses pg-format, all others use sqlstring.

TLS / SSL

MySQL/MariaDB and Postgres disable TLS certificate validation by default (rejectUnauthorized: false) for development convenience. Set rejectUnauthorized: true in production:

// MySQL/MariaDB
const client = new MySQL({
    host: 'prod-host',
    useSSL: true,
    rejectUnauthorized: true
});

// Postgres
const client = new Postgres({
    host: 'prod-host',
    ssl: true,
    rejectUnauthorized: true
});

Environment Variables

createConnection() reads these environment variables when options are not provided directly:

Variable	Description	Default
SQL_TYPE	Database type enum value (0=MySQL, 1=Postgres, 2=SQLite, 4=MariaDB)	2 (SQLite)
SQL_HOST	Database host	127.0.0.1
SQL_PORT	Database port	Driver default
SQL_USERNAME	Database user	''
SQL_PASSWORD	Database password
SQL_DATABASE	Database name
SQL_SSL	Enable SSL (true/false)	false
SQL_FILENAME	SQLite database file path	:memory:

A .env file is loaded automatically via dotenv.

Type Reference

// Database type enum
enum Database.Type {
    MYSQL = 0,
    POSTGRES = 1,
    SQLITE = 2,
    MARIADB = 4
}

// Query result tuple
type Database.Query.Result<T> = [T[], Database.Query.MetaData, Database.Query]

// Query metadata
type Database.Query.MetaData = {
    changedRows: number;
    affectedRows: number;
    insertId?: number;
    length: number;
}

// Query definition (for transactions and prepared queries)
type Database.Query = {
    query: string;
    values?: any[];
    noError?: boolean;
}

// Column definition
type Database.Table.Column = {
    name: string;
    type: string;
    nullable?: boolean;
    default?: string | number | boolean;
    unique?: boolean;
    foreignKey?: Database.Table.ForeignKey;
}

// Foreign key definition
type Database.Table.ForeignKey = {
    table: string;
    column: string;
    onUpdate?: Database.Table.ForeignKeyConstraint;
    onDelete?: Database.Table.ForeignKeyConstraint;
}

// Foreign key constraint actions
enum Database.Table.ForeignKeyConstraint {
    RESTRICT = 'RESTRICT',
    CASCADE = 'CASCADE',
    NULL = 'SET NULL',
    DEFAULT = 'SET DEFAULT',
    NA = 'NO ACTION'
}

// Index types
enum Database.Table.IndexType {
    NONE = '',
    UNIQUE = 'UNIQUE'
}

Known Limitations

• Postgres ? placeholders: The library automatically converts ? placeholders to $1, $2, ... for Postgres. This conflicts with PostgreSQL JSON operators (?, ?|, ?&). Use the native $1, $2 syntax directly for queries involving JSON operators.

• SQLite transaction atomicity: SQLite transactions use better-sqlite3's native transaction() callback, which is all-or-nothing. Individual query noError flags cannot be honored per-query — if any query fails, the entire transaction rolls back.

• Column type validation: Column types in createTable are validated against the pattern /^[a-zA-Z][a-zA-Z0-9 (),]*$/. Types must start with a letter and can only contain letters, digits, spaces, parentheses, and commas.