miniORM

A lightweight Object-Relational Mapping (ORM) library for Node.js with MySQL support and automatic connection pool management.

Features

• Immutable Builder Pattern: Each query method returns a new instance, preserving immutability
• Fluent Chainable API: Readable methods like model.fromTable('users').select('*')
• SELECT DISTINCT Support: Get unique values with the new distinct() method ✨
• Field-based IN/NOT IN: Enhanced whereField() with in() and notIn() operators ✨
• Promise-like Interface: Await queries directly or call .done() explicitly
• Singleton Connection Pool: Single shared pool across all miniORM instances with automatic cleanup
• Flexible WHERE Conditions: Support for complex conditions with operators, IN/NOT IN, NULL checks, and BETWEEN ranges
• Logical Operators: Chain conditions with AND/OR operators, orWhere(), andWhere() methods, and grouping with andGroup()/orGroup()
• Field-based Conditions: Use whereField() for specialized operations like isNull(), isBetween(), in(), notIn(), etc.
• Type Casting: Automatic type conversion for query values
• Auto Resource Management: Connection pool automatically closes on process exit (SIGINT, SIGTERM)
• Debug Support: Built-in debugging with configurable namespaces via the debug package
• ES6 Module Support: Full ESM compatibility

Installation

npm install mysql2 debug @dotenvx/dotenvx

Environment Setup

Create a .env file in your project root with your database configuration:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=your_database
DB_PORT=3306
CONNECTION_LIMIT=10

Quick Start

import miniORM from './miniORM.js'

const model = new miniORM()

// Select all users (can await directly without .done())
const allUsers = await model.fromTable('users').selectAll()

// Select specific columns with WHERE condition
const activeUsers = await model
  .fromTable('users')
  .select('id', 'name', 'email')
  .where('status', '=', 'active')

// NEW: Get unique values with distinct()
const uniqueEmails = await model
  .fromTable('users')
  .select()
  .distinct('email')

// NEW: Use whereField().in() for cleaner syntax
const featuredPosts = await model
  .fromTable('posts')
  .select('*')
  .whereField('author')
  .in(['John Doe', 'Jane Smith', 'Bob Wilson'])

// Insert a new user
const insertResult = await model
  .fromTable('users')
  .insert({
    name: 'John Doe',
    email: '[email protected]',
    status: 'active'
  })

// Complex conditions with AND/OR
const adminUsers = await model
  .fromTable('users')
  .select('id', 'name')
  .where('status', '=', 'active')
  .andWhere('role', '=', 'admin')

// Using grouped conditions with new in() operator
const complexUsers = await model
  .fromTable('users')
  .select('id', 'name', 'role')
  .where('status', '=', 'active')
  .andGroup((builder) => {
    return builder
      .whereField('role').in(['admin', 'moderator'])
      .orWhere('department', '=', 'IT')
  })

// Update records
const updateResult = await model
  .fromTable('users')
  .update({ status: 'inactive', updated_at: new Date() })
  .where('last_login', '<', '2025-01-01')

What's New ✨

Version 1.0.0 Update (2025)

New Features:

• ✨ distinct(...columns) - Get unique values from columns
• ✨ in(list) - Field operator for IN conditions after whereField()
• ✨ notIn(list) - Field operator for NOT IN conditions after whereField()
• ✨ select() enhanced - Now supports being called without arguments

Examples:

// Get unique departments
await model.fromTable('users').select().distinct('department')

// Filter by multiple authors (more readable)
await model
  .fromTable('posts')
  .select('*')
  .whereField('author')
  .in(['John', 'Jane', 'Bob'])

// Exclude banned users
await model
  .fromTable('users')
  .select('*')
  .whereField('status')
  .notIn(['banned', 'deleted', 'suspended'])

API Reference

Core Methods

new miniORM(options?)

Create a new miniORM instance with optional database configuration.

// Use environment variables
const model = new miniORM()

// Override with custom options
const model = new miniORM({
  host: 'custom-host',
  user: 'custom-user',
  password: 'custom-password',
  database: 'custom-database',
  port: 3306,
  connectionLimit: 10
})

fromTable(tableName)

Sets the table and returns a new instance. Must be called first in the chain.

const users = await model.fromTable('users').selectAll()
const posts = await model.fromTable('posts').select('id', 'title')

setTable(tableName)

Internal method to set the table name. Used with persistent model instances.

model.setTable('users')
const all = await model.selectAll().done()
const active = await model.select('id', 'name').where('status', '=', 'active').done()

done()

Executes the query and returns results. Queries can also be awaited directly without calling .done().

const results = await model.fromTable('users').selectAll().done()
// Or simply:
const results = await model.fromTable('users').selectAll()

Query Building Methods

select(...columns) ✨ Enhanced

Selects specific columns from the table. Now supports being called without arguments.

// Single column
const names = await model.fromTable('users').select('name')

// Multiple columns
const userInfo = await model.fromTable('users').select('id', 'name', 'email')

// No arguments (for use with distinct() or other modifiers)
const query = await model.fromTable('users').select().distinct('email')

distinct(...columns) ✨ NEW

Returns unique values from specified columns. Must be chained after select().

// Get unique email addresses
const uniqueEmails = await model
  .fromTable('users')
  .select()
  .distinct('email')

// Get unique combinations
const uniqueCombos = await model
  .fromTable('orders')
  .select()
  .distinct('customer_id', 'product_id')

// With WHERE conditions
const activeDepts = await model
  .fromTable('users')
  .select()
  .distinct('department')
  .where('status', '=', 'active')

Rules:

• Must be chained after select()
• Requires at least one column
• No empty, null, or undefined column names allowed

selectAll()

Selects all columns (equivalent to SELECT *).

const allUsers = await model.fromTable('users').selectAll()

countRecords()

Returns the count of records in the table.

const count = await model.fromTable('users').countRecords()
// Returns: [{ recordsCount: 42 }]

insert(details)

Inserts a new record into the table.

const result = await model
  .fromTable('users')
  .insert({
    name: 'John Doe',
    email: '[email protected]',
    role: 'admin',
    created_at: new Date()
  })

console.log(result.insertId) // Auto-increment ID

update(details)

Updates records in the table.

// Update with WHERE condition
const result = await model
  .fromTable('users')
  .update({
    status: 'inactive',
    updated_at: new Date()
  })
  .where('last_login', '<', '2025-01-01')

// Bulk update with complex conditions
const result = await model
  .fromTable('users')
  .update({ view_count: 0 })
  .where('status', '=', 'archived')
  .andWhere('created_at', '<', '2024-01-01')

delete()

Deletes records from the table.

// Delete with WHERE condition
const result = await model
  .fromTable('users')
  .delete()
  .where('status', '=', 'banned')

// Delete excluding certain values (using new notIn())
const result = await model
  .fromTable('sessions')
  .delete()
  .whereField('user_id')
  .notIn([1, 2, 3]) // Protect admin sessions

limit(number)

Limits the number of results returned.

const topTen = await model
  .fromTable('products')
  .select('*')
  .where('in_stock', '=', true)
  .limit(10)

offset(number)

Skips a specified number of results. Must be chained after limit().

// Get page 3 (assuming 20 items per page)
const page3 = await model
  .fromTable('products')
  .select('*')
  .limit(20)
  .offset(40)

WHERE Clause Methods

where(column, operator, value)

Primary WHERE clause method. Supports multiple operators and type casting.

// Basic usage
await model.fromTable('users').select('*').where('status', '=', 'active')

// With comparison operators
await model.fromTable('products').select('*').where('price', '>', 100)
await model.fromTable('users').select('*').where('age', '>=', 18)

// With LIKE operator
await model.fromTable('users').select('*').where('name', 'LIKE', '%John%')

// With type casting
await model.fromTable('users').select('*').where('age', '>', { value: '18', type: 'number' })
await model.fromTable('users').select('*').where('is_verified', '=', { value: 'true', type: 'boolean' })

Supported Operators:

• =, !=, <> (not equal)
• >, >=, <, <= (comparison)
• LIKE, NOT LIKE (pattern matching)

Type Casting: Pass value as object with value and type properties:

• type: 'string' - Cast to string
• type: 'number' - Cast to number
• type: 'boolean' - Cast to boolean

andWhere(column, operator, value)

Adds an AND condition to the query.

const activeAdmins = await model
  .fromTable('users')
  .select('id', 'name')
  .where('status', '=', 'active')
  .andWhere('role', '=', 'admin')
// SQL: WHERE status = 'active' AND role = 'admin'

orWhere(column, operator, value)

Adds an OR condition to the query.

const privilegedUsers = await model
  .fromTable('users')
  .select('id', 'name', 'role')
  .where('role', '=', 'admin')
  .orWhere('role', '=', 'moderator')
// SQL: WHERE role = 'admin' OR role = 'moderator'

whereIn(column, list)

Checks if column value is in the provided list.

const privilegedUsers = await model
  .fromTable('users')
  .select('id', 'name', 'role')
  .whereIn('role', ['admin', 'moderator', 'editor'])
// SQL: WHERE role IN ('admin', 'moderator', 'editor')

whereNotIn(column, list)

Checks if column value is NOT in the provided list.

const activeUsers = await model
  .fromTable('users')
  .select('id', 'name', 'status')
  .whereNotIn('status', ['banned', 'deleted', 'suspended'])
// SQL: WHERE status NOT IN ('banned', 'deleted', 'suspended')

whereField(column)

Initiates a field-based condition chain. Must be followed by field operators.

// Check for NULL
await model.fromTable('users').select('*').whereField('email_verified_at').isNull()

// Check for NOT NULL
await model.fromTable('users').select('*').whereField('deleted_at').isNotNull()

// BETWEEN
await model.fromTable('products').select('*').whereField('price').isBetween(10, 100)

// NOT BETWEEN
await model.fromTable('users').select('*').whereField('age').isNotBetween(13, 17)

// IN - NEW!
await model.fromTable('posts').select('*').whereField('author').in(['John', 'Jane', 'Bob'])

// NOT IN - NEW!
await model.fromTable('users').select('*').whereField('status').notIn(['banned', 'deleted'])

Field Operators (After whereField())

isNull()

Checks if field is NULL.

const unverifiedUsers = await model
  .fromTable('users')
  .select('id', 'name')
  .whereField('email_verified_at')
  .isNull()
// SQL: WHERE email_verified_at IS NULL

isNotNull()

Checks if field is NOT NULL.

const verifiedUsers = await model
  .fromTable('users')
  .select('id', 'name')
  .whereField('email_verified_at')
  .isNotNull()
// SQL: WHERE email_verified_at IS NOT NULL

isBetween(start, end)

Checks if field value is between two numbers.

const products = await model
  .fromTable('products')
  .select('*')
  .whereField('price')
  .isBetween(100, 500)
// SQL: WHERE price BETWEEN 100 AND 500

isNotBetween(start, end)

Checks if field value is NOT between two numbers.

const users = await model
  .fromTable('users')
  .select('*')
  .whereField('age')
  .isNotBetween(13, 17)
// SQL: WHERE age NOT BETWEEN 13 AND 17

in(list) ✨ NEW

Checks if field value is in the provided list. More readable alternative to whereIn() in complex chains.

const posts = await model
  .fromTable('posts')
  .select('*')
  .whereField('author')
  .in(['John Doe', 'Jane Smith', 'Bob Wilson'])
// SQL: WHERE author IN ('John Doe', 'Jane Smith', 'Bob Wilson')

// In complex chains
const results = await model
  .fromTable('users')
  .select('*')
  .whereField('role').in(['admin', 'moderator'])
  .or()
  .whereField('department').in(['IT', 'HR'])

Rules:

• Must follow whereField()
• List must be a non-empty array

notIn(list) ✨ NEW

Checks if field value is NOT in the provided list.

const users = await model
  .fromTable('users')
  .select('*')
  .whereField('status')
  .notIn(['banned', 'deleted', 'suspended'])
// SQL: WHERE status NOT IN ('banned', 'deleted', 'suspended')

// Safe DELETE operations
const result = await model
  .fromTable('sessions')
  .delete()
  .whereField('user_id')
  .notIn([1, 2, 3]) // Protect admin user sessions

Rules:

• Must follow whereField()
• List must be a non-empty array

Logical Operators

and()

Adds AND logical operator.

const users = await model
  .fromTable('users')
  .select('*')
  .where('status', '=', 'active')
  .and()
  .where('role', '=', 'admin')

or()

Adds OR logical operator.

const users = await model
  .fromTable('users')
  .select('*')
  .where('status', '=', 'active')
  .or()
  .where('status', '=', 'pending')

andGroup(callback)

Creates an AND grouped condition using a callback.

const users = await model
  .fromTable('users')
  .select('id', 'name', 'role', 'department')
  .where('status', '=', 'active')
  .andGroup((builder) => {
    return builder
      .whereField('role').in(['admin', 'moderator'])
      .orWhere('department', '=', 'IT')
  })
// SQL: WHERE status = 'active' AND (role IN ('admin', 'moderator') OR department = 'IT')

orGroup(callback)

Creates an OR grouped condition using a callback.

const users = await model
  .fromTable('users')
  .select('id', 'name')
  .where('status', '=', 'banned')
  .orGroup((builder) => {
    return builder
      .where('role', '=', 'admin')
      .andWhere('override', '=', true)
  })
// SQL: WHERE status = 'banned' OR (role = 'admin' AND override = true)

Properties (Read-only)

state

Returns the current query state (query parts and values).

const query = model
  .fromTable('users')
  .select('id', 'name')
  .where('status', '=', 'active')

console.log(query.state)
// {
//   query: ['SELECT id, name FROM users', 'WHERE status = ?'],
//   values: ['active']
// }

table

Returns the current table name.

const query = model.fromTable('users')
console.log(query.table) // 'users'

operatorSignal

Returns the current operator signal flag (internal state).

const query = model.fromTable('users').where('id', '=', 1)
console.log(query.operatorSignal) // true

Express Integration

Complete REST API Example

import express from 'express'
import miniORM from './miniORM.js'

const app = express()
const model = new miniORM()

app.use(express.json())

// GET: Fetch unique categories
app.get('/categories', async (req, res) => {
  try {
    const categories = await model
      .fromTable('products')
      .select()
      .distinct('category')
    
    res.json(categories)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// GET: Fetch posts by featured authors
app.get('/posts/featured', async (req, res) => {
  try {
    const posts = await model
      .fromTable('posts')
      .select('id', 'title', 'author', 'created_at')
      .whereField('author')
      .in(['John Doe', 'Jane Smith', 'Bob Wilson'])
      .andWhere('published', '=', true)
    
    res.json(posts)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// GET: Fetch active users (excluding banned)
app.get('/users', async (req, res) => {
  try {
    const users = await model
      .fromTable('users')
      .select('id', 'name', 'email', 'role')
      .whereField('status')
      .notIn(['banned', 'deleted', 'suspended'])
      .andWhere('email_verified', '=', true)
    
    res.json(users)
  } catch (error) {
    res.status(500).json({ error: error.message })
  }
})

// POST: Create new user
app.post('/users', async (req, res) => {
  try {
    const result = await model
      .fromTable('users')
      .insert({
        name: req.body.name,
        email: req.body.email,
        role: 'user',
        created_at: new Date()
      })
    
    res.status(201).json({ id: result.insertId })
  } catch (error) {
    res.status(400).json({ error: error.message })
  }
})

// PUT: Update user
app.put('/users/:id', async (req, res) => {
  try {
    const result = await model
      .fromTable('users')
      .update({
        name: req.body.name,
        email: req.body.email,
        updated_at: new Date()
      })
      .where('id', '=', { value: req.params.id, type: 'number' })
    
    res.json({ success: true, affected: result.affectedRows })
  } catch (error) {
    res.status(400).json({ error: error.message })
  }
})

// DELETE: Remove user (protecting admins)
app.delete('/users/:id', async (req, res) => {
  try {
    const result = await model
      .fromTable('users')
      .delete()
      .where('id', '=', { value: req.params.id, type: 'number' })
      .andWhere((builder) => {
        return builder
          .whereField('role')
          .notIn(['admin', 'superadmin'])
      })
    
    res.json({ success: true, affected: result.affectedRows })
  } catch (error) {
    res.status(400).json({ error: error.message })
  }
})

app.listen(3000, () => {
  console.log('Server running on port 3000')
})

Error Handling

Query Validation Errors

try {
  // Invalid: empty column name
  await model.fromTable('users').select('')
} catch (error) {
  console.error(error.message)
  // "List of columns can't include [empty, null or undefined] column(s) name(s)!"
}

try {
  // Invalid: distinct() without columns
  await model.fromTable('users').select().distinct()
} catch (error) {
  console.error(error.message)
  // "Column or columns, required!"
}

try {
  // Invalid: distinct() without select()
  await model.fromTable('users').distinct('email')
} catch (error) {
  console.error(error.message)
  // Error about method positioning
}

WHERE Condition Errors

try {
  // Invalid: chaining where() after where()
  await model
    .fromTable('users')
    .select('*')
    .where('status', '=', 'active')
    .where('role', '=', 'admin') // Wrong!
} catch (error) {
  console.error(error.message)
  // Use andWhere() or orWhere() instead
}

try {
  // Invalid: in() with empty array
  await model
    .fromTable('users')
    .select('*')
    .whereField('role')
    .in([]) // Wrong!
} catch (error) {
  console.error(error.message)
  // "List should be an array type and not empty!"
}

try {
  // Invalid: in() without whereField()
  await model
    .fromTable('users')
    .select('*')
    .in(['admin', 'moderator']) // Wrong!
} catch (error) {
  console.error(error.message)
  // Must use whereField() first
}

Debug Mode

Enable debug logging using the DEBUG environment variable:

# Debug all miniORM operations
DEBUG=miniORM:* node app.js

# Debug only queries
DEBUG=miniORM:query node app.js

# Debug only database connections
DEBUG=miniORM:db node app.js

# Debug multiple namespaces
DEBUG=miniORM:*,examples:* node app.js

Available Debug Namespaces:

• miniORM:query - SQL queries and values
• miniORM:db - Connection pool events
• miniORM:options - Configuration options

Connection Management

Automatic Features

• Singleton Pattern: All miniORM instances share the same connection pool
• Auto-cleanup: Pool automatically closes on process exit (SIGINT, SIGTERM)
• No Manual Cleanup: No need to call .close() or .end() methods

Configuration Priority

1. Constructor options (highest priority)
2. Environment variables (from .env file)
3. Default values (fallback)

Example:

// This configuration takes precedence over .env variables
const model = new miniORM({
  host: 'production-db.example.com',
  user: 'prod_user',
  password: 'prod_password',
  database: 'prod_database',
  connectionLimit: 20
})

Best Practices

1. Always Use WHERE with UPDATE/DELETE

// ✅ Good - specific deletion
await model.fromTable('users').delete().where('id', '=', 1)

// ⚠️ Dangerous - deletes all records!
await model.fromTable('users').delete()

2. Use Type Casting for User Input

// ✅ Good - ensures type safety
await model
  .fromTable('users')
  .where('id', '=', { value: req.params.id, type: 'number' })

// ⚠️ Risky - might cause type mismatch
await model
  .fromTable('users')
  .where('id', '=', req.params.id)

3. Use distinct() for Unique Values

// ✅ Good - efficient database query
await model.fromTable('users').select().distinct('email')

// ⚠️ Less efficient - requires post-processing
const users = await model.fromTable('users').select('email')
const unique = [...new Set(users.map(u => u.email))]

4. Choose Between whereIn() and whereField().in()

// ✅ Both are valid - use what reads better

// Traditional approach
.whereIn('status', ['active', 'pending'])

// New field-based approach (more readable in chains)
.whereField('status').in(['active', 'pending'])

5. Use Groups for Complex Logic

// ✅ Good - clear intent with grouping
await model
  .fromTable('users')
  .select('*')
  .where('status', '=', 'active')
  .andGroup((builder) => {
    return builder
      .whereField('role').in(['admin', 'moderator'])
      .orWhere('department', '=', 'IT')
  })

6. Protect Sensitive Operations

// ✅ Good - protects admin accounts
await model
  .fromTable('users')
  .delete()
  .where('last_login', '<', '2024-01-01')
  .andWhere((builder) => {
    return builder
      .whereField('role')
      .notIn(['admin', 'superadmin'])
  })

Method Chaining Rules

Valid Patterns ✅

// Pattern 1: fromTable → query method → conditions
model.fromTable('users').select('*').where('id', '=', 1)

// Pattern 2: setTable → query method → conditions
model.setTable('users')
model.select('*').where('id', '=', 1)

// Pattern 3: Insert/Update/Delete first
model.fromTable('users').insert({...})
model.fromTable('users').update({...}).where('id', '=', 1)
model.fromTable('users').delete().where('id', '=', 1)

// Pattern 4: select() with distinct() ✨ NEW
model.fromTable('users').select().distinct('email')
model.fromTable('orders').select().distinct('customer_id', 'product_id')

// Pattern 5: whereField() with in()/notIn() ✨ NEW
model.fromTable('posts').select('*').whereField('author').in(['John', 'Jane'])
model.fromTable('users').select('*').whereField('status').notIn(['banned'])

Invalid Patterns ❌

// ❌ Cannot chain where() after where()
model.fromTable('users').select('*').where('id', '=', 1).where('status', '=', 'active')
// Use andWhere() or orWhere() instead

// ❌ Cannot use offset() without limit()
model.select('*').offset(20)

// ❌ fromTable() must be first
model.select('*').fromTable('users')

// ❌ Cannot end query with and()/or()
model.fromTable('users').select('*').where('id', '=', 1).and().done()

// ❌ distinct() requires columns
model.fromTable('users').select().distinct()

// ❌ distinct() requires select()
model.fromTable('users').distinct('email')

// ❌ in()/notIn() require whereField()
model.fromTable('users').select('*').in(['admin'])

Complete Method Reference

| Method | Category | Description | New | |--------|----------|-------------|-----| | new miniORM() | Core | Constructor | | | fromTable() | Core | Set table (must be first) | | | setTable() | Core | Set table (internal) | | | done() | Core | Execute query | | | select() | Query | SELECT columns | ✨ Enhanced | | distinct() | Query | SELECT DISTINCT | ✨ NEW | | selectAll() | Query | SELECT * | | | countRecords() | Query | COUNT(*) | | | insert() | Query | INSERT record | | | update() | Query | UPDATE records | | | delete() | Query | DELETE records | | | where() | Condition | WHERE clause | | | andWhere() | Condition | AND WHERE | | | orWhere() | Condition | OR WHERE | | | whereIn() | Condition | WHERE IN | | | whereNotIn() | Condition | WHERE NOT IN | | | whereField() | Condition | Field-based WHERE | | | isNull() | Field Operator | IS NULL | | | isNotNull() | Field Operator | IS NOT NULL | | | isBetween() | Field Operator | BETWEEN | | | isNotBetween() | Field Operator | NOT BETWEEN | | | in() | Field Operator | IN (after whereField) | ✨ NEW | | notIn() | Field Operator | NOT IN (after whereField) | ✨ NEW | | and() | Logical | AND operator | | | or() | Logical | OR operator | | | andGroup() | Logical | AND (grouped) | | | orGroup() | Logical | OR (grouped) | | | limit() | Pagination | LIMIT results | | | offset() | Pagination | OFFSET results | | | state | Property | Query state | | | table | Property | Table name | | | operatorSignal | Property | Operator flag | |