hajdas-orm

A lightweight, fully type-safe ORM for MySQL/MariaDB, PostgreSQL, and MongoDB — with migrations, seeders, chainable queries, eager-loading relations, lifecycle hooks, connection retry, and streaming support. Built on mysql2, pg, and mongodb.

Table of Contents

1. Why hajdas-orm?
2. Installation
3. Quick start
4. Connecting
   • MySQL / MariaDB
   • PostgreSQL
   • MongoDB
   • Connection strings
   • Connection retry
5. CRUD methods
6. WHERE filters
   • Object notation
   • Array notation
   • OR groups
   • Raw WHERE
7. Chainable query builder
8. Aggregates
9. Row helpers
10. Numeric helpers
11. Soft delete
12. Lifecycle hooks
13. Streaming large result sets
14. Model layer
    • Define a model
    • Schema validation
    • Relations & eager loading
    • Pagination
15. Migrations
16. Seeder
17. Table & index management
18. Transactions
19. API reference
20. Driver compatibility matrix

Why hajdas-orm?

| Feature | hajdas-orm | |---|---| | MySQL, PostgreSQL, MongoDB — single API | ✅ | | Fully typed (TypeScript-first, ESM) | ✅ | | Chainable fluent query builder | ✅ | | OR / AND / BETWEEN / IN / LIKE conditions | ✅ | | Lifecycle hooks (before/after every CRUD op) | ✅ | | Connection retry with exponential backoff | ✅ | | Async streaming via async generators | ✅ | | Migrations with batch tracking & rollback | ✅ | | Seeder with factory support | ✅ | | Eager-loading relations (hasMany, belongsTo …) | ✅ | | Runtime schema validation | ✅ | | Zero extra runtime dependencies beyond drivers | ✅ |

Installation

# MySQL / MariaDB (always required)
npm install hajdas-orm mysql2

# PostgreSQL (optional)
npm install pg

# MongoDB (optional)
npm install mongodb

Quick start

import { ConnectMysql } from 'hajdas-orm';

const db = new ConnectMysql({ host: 'localhost', user: 'root', database: 'myapp' });

const users = await db.get('users', { where: { active: 1 }, limit: 20 });
await db.post('users', { name: 'Alice', email: '[email protected]', active: 1 });
await db.patch('users', { active: 0 }, { id: 42 });
await db.delete('users', { id: 42 });

Connecting

MySQL / MariaDB

import { ConnectMysql } from 'hajdas-orm';

// Config object (mysql2 PoolOptions)
const db = new ConnectMysql({
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: 'secret',
  database: 'myapp',
  connectionLimit: 10,
});

// Lifecycle hooks + retry options (optional second argument)
const db = new ConnectMysql(config, {
  hooks: { onError: (err, op, table) => logger.error({ err, op, table }) },
  retry: { maxAttempts: 3, delayMs: 300, factor: 2 },
});

await db.connect(); // optional — pool is lazy
await db.ping();    // returns true or throws
await db.close();

Connect and DbConnect are kept as backward-compatible aliases for ConnectMysql.

PostgreSQL

import { ConnectPostgresql } from 'hajdas-orm';

const db = new ConnectPostgresql({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'secret',
  database: 'myapp',
  max: 10,
  ssl: false,
});

MongoDB

import { ConnectMongodb } from 'hajdas-orm';

const db = new ConnectMongodb({
  uri: 'mongodb://localhost:27017',
  database: 'myapp',
});

// Access underlying driver when needed
const client = db.getNativeClient();
const nativeDb = db.getNativeDb();

Connection strings

All three adapters accept a URL string instead of a config object:

const mysql = new ConnectMysql('mysql://root:secret@localhost:3306/myapp');
const pg    = new ConnectPostgresql('postgresql://postgres:secret@localhost:5432/myapp');
const mongo = new ConnectMongodb('mongodb://localhost:27017/myapp');

Utility functions are also exported for manual parsing:

import { parseMysqlUrl, parsePgUrl, parseMongoUrl } from 'hajdas-orm';

Connection retry

const db = new ConnectMysql(config, {
  retry: {
    maxAttempts: 5,   // total attempts (default: 1 — no retry)
    delayMs: 500,     // initial delay in ms (default: 500)
    factor: 2,        // exponential backoff multiplier (default: 2)
  },
});

Every CRUD operation is wrapped in the retry logic. Retry is transparent — no API changes.

CRUD methods

All three adapters share an identical interface (IConnect).

// SELECT — returns Row[] or Row | null (quantity=1)
await db.get(table, filters?, quantity?)

// INSERT — returns { insertId, affectedRows }
await db.post(table, data)             // single row
await db.post(table, [row1, row2])     // bulk insert

// UPDATE — returns { affectedRows, changedRows }
await db.patch(table, data, where)

// DELETE — returns { affectedRows }
await db.delete(table, where)

WHERE filters

Object notation

Simple equality and IN checks using a plain object:

// WHERE active = 1 AND role = 'admin'
await db.get('users', { where: { active: 1, role: 'admin' } });

// WHERE id IN (1, 2, 3)
await db.get('users', { where: { id: [1, 2, 3] } });

// WHERE deleted_at IS NULL
await db.get('users', { where: { deleted_at: null } });

Array notation

Full control via WhereClause[] — supports all operators:

await db.get('products', {
  where: [
    { column: 'price',  operator: '>=',      value: 10    },
    { column: 'price',  operator: '<=',      value: 100   },
    { column: 'name',   operator: 'LIKE',    value: '%oo%' },
    { column: 'stock',  operator: 'BETWEEN', value: [5, 50] },
    { column: 'cat_id', operator: 'IN',      value: [1, 2, 3] },
    { column: 'deleted_at', operator: 'IS NULL' },
  ],
});

Supported operators: =, !=, <>, <, <=, >, >=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, BETWEEN.

OR groups

Wrap conditions in { OR: [...] } to produce (a = 1 OR b = 2) SQL fragments. OR groups are AND-chained with the surrounding conditions:

await db.get('users', {
  where: [
    { OR: [
        { column: 'role', operator: '=', value: 'admin'  },
        { column: 'role', operator: '=', value: 'editor' },
    ]},
    { column: 'active', operator: '=', value: 1 },
  ],
});
// WHERE (role = 'admin' OR role = 'editor') AND active = 1

Raw WHERE

Append a raw SQL fragment (SQL adapters only):

await db.get('orders', {
  rawWhere: 'total > (SELECT AVG(total) FROM orders)',
  where: { status: 'paid' },
});

Chainable query builder

Obtain a QueryChain via db.from(table):

const users = await db
  .from('users')
  .select('id', 'name', 'email')
  .where('active', 1)
  .where('role', '!=', 'banned')
  .orderBy('name', 'ASC')
  .limit(20)
  .offset(40)
  .get();

// First matching row
const user = await db.from('users').where({ email: '[email protected]' }).first();

// Aggregates
const total = await db.from('orders').where('status', 'paid').sum('amount');
const pages = await db.from('articles').where('published', 1).paginate(2, 10);

// Joins
const result = await db
  .from('orders')
  .innerJoin('users', 'orders.user_id = users.id')
  .leftJoin('coupons', 'orders.coupon_id = coupons.id')
  .select('orders.id', 'users.name', 'coupons.code')
  .where('orders.status', 'pending')
  .get();

// Group by / having
const stats = await db
  .from('sales')
  .select('category')
  .groupBy('category')
  .having('SUM(amount) > 1000')
  .get();

QueryChain terminal methods: get(), first(), count(), exists(), sum(col), avg(col), min(col), max(col), paginate(page, perPage).

Aggregates

await db.count('users', { where: { active: 1 } });
await db.sum('orders',  'amount',  { where: { status: 'paid' } });
await db.avg('reviews', 'rating');
await db.min('products', 'price');
await db.max('products', 'price');
await db.exists('users', { email: '[email protected]' });

Row helpers

// Clone a row (optionally override columns)
await db.cloneRow('products', { id: 5 }, { name: 'Copy of Widget' });

// Touch updated_at (or a custom column)
await db.touch('sessions', { id: 99 });
await db.touch('sessions', { id: 99 }, 'last_seen_at');

// Find or create
const { row, created } = await db.firstOrCreate(
  'categories',
  { slug: 'nodejs' },
  { name: 'Node.js', slug: 'nodejs' },
);

Numeric helpers

await db.increment('posts',   'views',  { id: 1 });
await db.increment('wallets', 'balance', { user_id: 7 }, 50);
await db.decrement('stock',   'qty',    { sku: 'ABC' }, 3);

Soft delete

// Mark as deleted (sets deleted_at = NOW())
await db.softDelete('users', { id: 42 });
await db.softDelete('users', { id: 42 }, { column: 'removed_at' });

// Restore
await db.restore('users', { id: 42 });

Lifecycle hooks

Pass a HookMap in the options object. Hooks may be async.

const db = new ConnectMysql(config, {
  hooks: {
    beforeGet:    (table, filters)       => console.log('SELECT', table),
    afterGet:     (table, result)        => console.log('got', result),
    beforePost:   (table, data)          => console.log('INSERT', table),
    afterPost:    (table, result)        => console.log('inserted', result.insertId),
    beforePatch:  (table, data, where)   => console.log('UPDATE', table),
    afterPatch:   (table, result)        => console.log('updated', result.changedRows),
    beforeDelete: (table, where)         => console.log('DELETE', table),
    afterDelete:  (table, result)        => console.log('deleted', result.affectedRows),
    onError:      (err, operation, table) => logger.error({ err, operation, table }),
  },
});

onError is called for every thrown error but does not suppress it — the error continues to propagate.

Streaming large result sets

stream() is an async generator that fetches rows in configurable batches, keeping memory usage constant:

for await (const user of db.stream('users', { where: { active: 1 } }, 500)) {
  await processUser(user);
}

Also available via the chainable builder:

for await (const order of db.stream('orders', { orderBy: { column: 'id', direction: 'ASC' } })) {
  await sendInvoice(order);
}

Model layer

Define a model

import { ConnectMysql, type TableSchema } from 'hajdas-orm';

const db = new ConnectMysql(config);

const schema: TableSchema = {
  id:         { type: 'INT',     primaryKey: true, autoIncrement: true },
  name:       { type: 'VARCHAR', length: 255, notNull: true },
  email:      { type: 'VARCHAR', length: 255, notNull: true, unique: true },
  role:       { type: 'VARCHAR', length: 50, default: 'user' },
  active:     { type: 'TINYINT', default: 1 },
  created_at: { type: 'DATETIME' },
  updated_at: { type: 'DATETIME' },
};

const User = db.model('users', schema, {
  timestamps: true,  // auto-manages created_at / updated_at
  softDelete: true,  // hides deleted rows; use restore() to bring them back
  primaryKey: 'id',
  validate: true,    // enable runtime schema validation
});

// Sync (create table if it doesn't exist)
await User.sync();

Model CRUD:

await User.create({ name: 'Alice', email: '[email protected]', role: 'admin' });
await User.find({ where: { active: 1 }, orderBy: { column: 'name' } });
await User.findOne({ email: '[email protected]' });
await User.findById(1);
await User.update({ id: 1 }, { role: 'editor' });
await User.updateById(1, { active: 0 });
await User.remove({ id: 1 });       // soft-deletes if softDelete: true
await User.removeById(1);
await User.restore({ id: 1 });
await User.count({ where: { active: 1 } });

Schema validation

When validate: true is set on the model, create() and update() automatically validate the data row against the schema's validate rules. A ValidationError is thrown if any rule fails.

const schema: TableSchema = {
  email: {
    type: 'VARCHAR', length: 255,
    validate: { email: true, maxLength: 255 },
  },
  age: {
    type: 'INT',
    validate: { min: 0, max: 130 },
  },
  role: {
    type: 'VARCHAR', length: 20,
    validate: { enum: ['user', 'admin', 'editor'] },
  },
  username: {
    type: 'VARCHAR', length: 50,
    validate: {
      minLength: 3,
      maxLength: 50,
      pattern: /^[a-z0-9_]+$/,
    },
  },
  website: {
    type: 'VARCHAR',
    validate: { url: true },
  },
  score: {
    type: 'INT',
    validate: {
      custom: (v) => (Number(v) % 2 === 0 ? true : 'Score must be even'),
    },
  },
};

You can also run validation manually:

import { validateRow, ValidationError } from 'hajdas-orm';

const failures = validateRow(data, schema);
if (failures.length > 0) throw new ValidationError(failures);

Relations & eager loading

Define relations in model options:

const User = db.model('users', userSchema, {
  relations: {
    posts:    { type: 'hasMany',       relatedTable: 'posts',    foreignKey: 'user_id' },
    profile:  { type: 'hasOne',        relatedTable: 'profiles', foreignKey: 'user_id' },
    company:  { type: 'belongsTo',     relatedTable: 'companies', foreignKey: 'company_id' },
    roles:    {
      type: 'belongsToMany',
      relatedTable: 'roles',
      foreignKey: 'user_id',
      pivotTable: 'user_roles',
      pivotRelatedKey: 'role_id',
    },
  },
});

Eager-load relations using with():

// Load users with posts and profile
const users = await User.with('posts', 'profile').find({ where: { active: 1 } });

// Single record
const user = await User.with('posts', 'company').findById(1);

// Paginated with relations
const page = await User.with('roles').paginate(1, 15);

Loaded relations are attached directly to each record under the relation name:

user.posts    // Row[]
user.profile  // Row | null
user.company  // Row | null
user.roles    // Row[]

Supported relation types:

| Type | Description | |---|---| | hasMany | One-to-many: this table's PK matches foreign key on related table | | hasOne | One-to-one: same as hasMany, returns single row | | belongsTo | This table holds the foreign key pointing to the related table | | belongsToMany | Many-to-many via a pivot table |

Pagination

const result = await User.paginate(2, 15, { where: { active: 1 } });
// {
//   data: Row[],
//   total: 142,
//   page: 2,
//   perPage: 15,
//   lastPage: 10,
// }

Migrations

import { ConnectMysql, Migrator, type MigrationDefinition } from 'hajdas-orm';

const db = new ConnectMysql(config);

const migrations: MigrationDefinition[] = [
  {
    id: '001_create_users',
    async up(db) {
      await db.tableCreate('users', {
        id:    { type: 'INT', primaryKey: true, autoIncrement: true },
        email: { type: 'VARCHAR', length: 255, notNull: true, unique: true },
      }, { ifNotExists: true });
    },
    async down(db) {
      await db.tableDrop('users');
    },
  },
  {
    id: '002_add_role_to_users',
    async up(db) {
      // raw SQL for ALTER TABLE
      await (db as any).raw('ALTER TABLE `users` ADD COLUMN `role` VARCHAR(50) DEFAULT "user"');
    },
  },
];

const migrator = new Migrator(db);

await migrator.run(migrations);              // run all pending
await migrator.status(migrations);           // MigrationStatus[]
await migrator.rollback(migrations, 1);      // roll back last batch
await migrator.reset(migrations);            // roll back everything

The migrator tracks state in a _migrations table it creates automatically. Each run() call is a single batch.

Seeder

import { ConnectMysql, Seeder } from 'hajdas-orm';

const db     = new ConnectMysql(config);
const seeder = new Seeder(db);

// Seed from a static array
await seeder.seed('categories', [
  { name: 'Electronics' },
  { name: 'Books' },
], { truncate: true });

// Seed from a factory (generates 100 unique rows)
await seeder.seedFactory('users', 100, () => ({
  name:  `User ${Math.random().toString(36).slice(2, 8)}`,
  email: `${Math.random().toString(36).slice(2)}@example.com`,
  role:  'user',
  active: 1,
}));

truncate: true clears the table before seeding.

Table & index management

await db.tableCreate('products', {
  id:    { type: 'INT',     primaryKey: true, autoIncrement: true },
  name:  { type: 'VARCHAR', length: 255, notNull: true },
  price: { type: 'DECIMAL', length: 10, notNull: true },
  stock: { type: 'INT',     default: 0 },
}, { ifNotExists: true, engine: 'InnoDB', charset: 'utf8mb4' });

await db.tableExists('products');           // boolean
await db.tableList();                       // string[]
await db.tableColumns('products');          // Row[] (column info)
await db.tableRename('products', 'items');
await db.tableTruncate('products');
await db.tableDrop('products', true);       // true = IF EXISTS

await db.indexCreate('products', 'idx_name', ['name'], { unique: false });
await db.indexDrop('products', 'idx_name');

Transactions

MySQL

await db.transaction(async (conn) => {
  await conn.execute('INSERT INTO accounts (user_id, balance) VALUES (?, ?)', [1, 1000]);
  await conn.execute('UPDATE wallets SET balance = balance - ? WHERE user_id = ?', [100, 1]);
});

PostgreSQL

await db.transaction(async (client) => {
  await client.query('INSERT INTO accounts (user_id, balance) VALUES ($1, $2)', [1, 1000]);
  await client.query('UPDATE wallets SET balance = balance - $1 WHERE user_id = $2', [100, 1]);
});

MongoDB

await db.transaction(async (session) => {
  await db.getNativeDb().collection('accounts').insertOne({ user_id: 1, balance: 1000 }, { session });
});

API reference

Common methods (all adapters)

| Method | Description | |---|---| | connect() | Open connection / pool | | ping() | Verify connectivity | | close() | Close pool / client | | get(table, filters?, qty?) | SELECT rows | | post(table, data) | INSERT row(s) | | patch(table, data, where) | UPDATE rows | | delete(table, where) | DELETE rows | | count(table, filters?) | COUNT(*) | | sum/avg/min/max(table, col, filters?) | Aggregate functions | | exists(table, where) | Boolean existence check | | cloneRow(table, where, overrides?) | Duplicate a row | | touch(table, where, col?) | Update a timestamp column | | softDelete(table, where, opts?) | Set deleted_at | | restore(table, where, opts?) | Clear deleted_at | | firstOrCreate(table, where, defaults?) | Find or insert | | increment(table, col, where, amount?) | Atomic increment | | decrement(table, col, where, amount?) | Atomic decrement | | from(table) | Return a QueryChain | | stream(table, filters?, batchSize?) | Async generator | | model(table, schema, opts?) | Return a Model | | tableCreate/Drop/Exists/Truncate/Rename/Columns/List(…) | DDL helpers | | indexCreate/indexDrop(…) | Index management |

Filters object

interface Filters {
  select?:          string[];
  where?:           WhereInput;
  orderBy?:         OrderByClause | OrderByClause[] | string;
  groupBy?:         string | string[];
  having?:          string;
  havingParams?:    SqlValue[];
  joins?:           JoinClause[];
  limit?:           number;
  offset?:          number;
  rawWhere?:        string;      // SQL adapters only
  rawWhereParams?:  SqlValue[];  // SQL adapters only
}

Driver compatibility matrix

| Feature | MySQL | PostgreSQL | MongoDB | |---|:---:|:---:|:---:| | CRUD | ✅ | ✅ | ✅ | | Aggregates | ✅ | ✅ | ✅ (pipeline) | | WHERE operators | ✅ | ✅ | ✅ | | OR groups | ✅ | ✅ | ✅ ($or) | | Raw WHERE | ✅ | ✅ | ❌ | | Joins | ✅ | ✅ | ❌ | | GROUP BY / HAVING | ✅ | ✅ | ❌ | | Transactions | ✅ | ✅ | ✅ (sessions) | | Hooks | ✅ | ✅ | ✅ | | Retry | ✅ | ✅ | ✅ | | Streaming | ✅ | ✅ | ✅ | | Chainable builder | ✅ | ✅ | ✅ | | Migrations | ✅ | ✅ | ✅ | | Seeder | ✅ | ✅ | ✅ | | Relations | ✅ | ✅ | ✅ | | Schema validation | ✅ | ✅ | ✅ | | Table DDL | ✅ | ✅ | ✅ (collections) | | Index management | ✅ | ✅ | ✅ | | Connection string | ✅ | ✅ | ✅ | | raw() | ✅ | ✅ | ❌ |

License

MIT