StrictDB Documentation

Complete API reference for strictdb and strictdb-mcp. One unified API for MongoDB, PostgreSQL, MySQL, MSSQL, SQLite, and Elasticsearch.

Installation

Install the core package and the peer dependency for your database:

# Core package
npm install strictdb

# Add your database driver (pick one or more)
npm install mongodb              # MongoDB
npm install pg                   # PostgreSQL
npm install mysql2               # MySQL
npm install mssql                # MSSQL
npm install better-sqlite3       # SQLite
npm install @elastic/elasticsearch  # Elasticsearch

Quick Start

StrictDB auto-detects your backend from the connection URI. No configuration files, no schema definitions required.

import { StrictDB } from 'strictdb';

// Auto-detects backend from URI
const db = await StrictDB.create({
  uri: 'postgresql://user:pass@localhost:5432/mydb'
});

// Read
const user = await db.queryOne('users', { email: 'alice@example.com' });

// Write — returns a receipt, never void
const receipt = await db.insertOne('users', {
  email: 'bob@example.com',
  name: 'Bob',
  role: 'user'
});
console.log(receipt.insertedCount); // 1

// Update with MongoDB-style operators
await db.updateOne('users',
  { email: 'bob@example.com' },
  { $set: { role: 'admin' }, $inc: { loginCount: 1 } }
);

// Same code works on Mongo, Postgres, MySQL, MSSQL, SQLite, Elasticsearch
await db.close();

URI Auto-Detection

Configuration

const db = await StrictDB.create({
  uri: 'postgresql://localhost:5432/mydb',  // required
  pool: 'standard',        // 'high' | 'standard' | 'low'
  dbName: 'mydb',           // override database name
  label: 'primary',          // logging label
  schema: false,             // enable Zod validation on writes
  sanitize: true,            // input sanitization (default: true)
  guardrails: {              // or just true/false
    limitRequired: true,    // block unbounded queries (default: true)
    emptyFilter: true,      // block empty-filter deletes/updates (default: true)
    nullComparison: true,   // block = NULL in SQL mode (default: true)
  },
  logging: true,             // true | false | 'verbose'
  slowQueryMs: 1000,         // slow query threshold in ms
  timestamps: true,          // auto createdAt/updatedAt injection
  sanitizeRules: [],          // custom SanitizeRule[] array
  reconnect: {                // or just true/false
    enabled: true,
    maxAttempts: 10,
    initialDelayMs: 1000,
    maxDelayMs: 30000,
    backoffMultiplier: 2
  },
  elastic: {                  // Elasticsearch-specific options
    apiKey: '...',
    caFingerprint: '...',
    sniffOnStart: false
  }
});

Read Operations

queryOne<T>(collection, filter, options?)

Find a single document matching the filter. Returns null if not found.

const user = await db.queryOne<User>('users', { email: 'alice@example.com' });
// → User | null

// With projection — only return specific fields
const user = await db.queryOne('users',
  { email: 'alice@example.com' },
  { projection: { name: 1, email: 1 } }
);

queryMany<T>(collection, filter, options?)

Find multiple documents. Limit is required — guardrails block unbounded queries.

const admins = await db.queryMany<User>('users', {
  role: 'admin',
  status: { $in: ['active', 'pending'] },
  age: { $gte: 18 }
}, {
  sort: { createdAt: -1 },
  limit: 50,
  skip: 0
});
// → User[]

queryWithLookup<T>(collection, options)

Join across collections. Uses $lookup on MongoDB, two-query approach on SQL/ES.

const order = await db.queryWithLookup<OrderWithUser>('orders', {
  match: { orderId: 'ORD-001' },
  lookup: {
    from: 'users',          // related collection
    localField: 'userId',    // field in orders
    foreignField: '_id',     // field in users
    as: 'user',              // nest result here
    type: 'left'             // 'left' | 'inner'
  },
  sort: { createdAt: -1 },
  limit: 1
});
// → { orderId: 'ORD-001', userId: '...', user: { name: 'Alice', ... } }

count<T>(collection, filter?)

const total = await db.count('users', { role: 'admin' });
// → number

QueryOptions

Write Operations

All write operations return an OperationReceipt — never void.

insertOne<T>(collection, doc)

const receipt = await db.insertOne('users', {
  email: 'alice@example.com',
  name: 'Alice',
  role: 'user',
  age: 28
});
// receipt.insertedCount === 1

insertMany<T>(collection, docs)

const receipt = await db.insertMany('users', [
  { email: 'alice@example.com', name: 'Alice', role: 'user' },
  { email: 'bob@example.com', name: 'Bob', role: 'admin' },
]);
// receipt.insertedCount === 2

updateOne<T>(collection, filter, update, upsert?)

Updates a single document. Enforces single-row semantics on all SQL dialects.

await db.updateOne('users',
  { email: 'alice@example.com' },
  { $set: { role: 'admin' }, $inc: { loginCount: 1 } }
);

// Upsert — insert if not found
await db.updateOne('users',
  { email: 'new@example.com' },
  { $set: { name: 'New User', role: 'user' } },
  true  // upsert
);

updateMany<T>(collection, filter, update, options?)

Update all matching documents. Empty filter is blocked by guardrails.

// Update all inactive users
await db.updateMany('users',
  { status: 'inactive', lastLogin: { $lt: cutoffDate } },
  { $set: { archived: true } }
);

// To update ALL documents, you must confirm:
await db.updateMany('users',
  { _id: { $exists: true } },
  { $set: { migrated: true } },
  { confirm: 'UPDATE_ALL' }
);

deleteOne<T>(collection, filter)

await db.deleteOne('users', { email: 'old@example.com' });

deleteMany<T>(collection, filter, options?)

// Delete matching documents
await db.deleteMany('logs', { createdAt: { $lt: thirtyDaysAgo } });

// Delete ALL requires explicit confirmation
await db.deleteMany('temp_data',
  { _id: { $exists: true } },
  { confirm: 'DELETE_ALL' }
);

SQL Mode — Overview & API

Execute SQL on any backend — including MongoDB. On MongoDB, StrictDB parses the SQL, generates aggregate pipelines, and executes them natively. On SQL databases, it routes the query to the native driver. On Elasticsearch, SELECT queries translate to Query DSL.

// db.sql() works on ALL backends
const results = await db.sql(`
  SELECT u.name, COUNT(o.id) AS orders
  FROM users u
  JOIN orders o ON u.id = o.user_id
  WHERE u.status = 'active'
  GROUP BY u.name
  HAVING COUNT(o.id) > 5
  ORDER BY orders DESC
  LIMIT 10
`);
// On MongoDB: translates to $lookup, $unwind, $match, $group, $sort, $limit
// On PostgreSQL/MySQL/MSSQL/SQLite: executes SQL natively

Signature

db.sql(sql: string, options?: SqlOptions): Promise<SqlMode2Result | OperationReceipt>

Options

Return Types

SELECT queries return SqlMode2Result:

{ data: Record<string, unknown>[], plan?: ExplainPlan }

Write queries (INSERT, UPDATE, DELETE) return OperationReceipt:

{ operation, success, matchedCount, modifiedCount, insertedCount, deletedCount, duration, backend }

Supported Features

SQL Mode supports a comprehensive set of SQL features across all backends. On SQL databases, queries execute natively. On MongoDB, every feature is translated to aggregation pipeline stages.

JOINs

All five standard JOIN types are supported. On MongoDB, JOINs translate to $lookup and $unwind pipeline stages.

// INNER JOIN
const results = await db.sql(`
  SELECT u.name, o.total
  FROM users u
  INNER JOIN orders o ON u.id = o.user_id
  WHERE o.total > 100
  LIMIT 20
`);

// FULL OUTER JOIN — runs two pipelines in parallel
const all = await db.sql(`
  SELECT u.name, o.total
  FROM users u
  FULL OUTER JOIN orders o ON u.id = o.user_id
  LIMIT 50
`);

// Multi-condition JOIN
const matched = await db.sql(`
  SELECT a.*, b.score
  FROM students a
  JOIN grades b ON a.id = b.student_id AND a.semester = b.semester
  LIMIT 100
`);

Subqueries

Subqueries are resolved as dependencies before the main query executes. On MongoDB, results are injected into the pipeline via $match with $in, $nin, or $expr.

// WHERE IN (subquery)
const active = await db.sql(`
  SELECT * FROM users
  WHERE id IN (SELECT user_id FROM orders WHERE total > 100)
  LIMIT 50
`);

// WHERE NOT IN
const inactive = await db.sql(`
  SELECT * FROM users
  WHERE id NOT IN (SELECT user_id FROM logins WHERE last_seen > '2025-01-01')
  LIMIT 50
`);

// WHERE EXISTS
const withOrders = await db.sql(`
  SELECT * FROM users u
  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
  LIMIT 50
`);

// Scalar subquery
const above = await db.sql(`
  SELECT * FROM employees
  WHERE salary > (SELECT AVG(salary) FROM employees)
  LIMIT 50
`);

CTEs (Common Table Expressions)

WITH clauses are resolved as dependencies before the main query. Multiple CTEs can reference each other. WITH RECURSIVE translates to $graphLookup on MongoDB.

// Basic CTE
const result = await db.sql(`
  WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
  )
  SELECT name, email FROM active_users
  ORDER BY name
  LIMIT 100
`);

// Multiple CTEs with dependencies
const report = await db.sql(`
  WITH
    dept_totals AS (
      SELECT department, SUM(salary) as total
      FROM employees
      GROUP BY department
    ),
    avg_total AS (
      SELECT AVG(total) as avg_dept FROM dept_totals
    )
  SELECT department, total FROM dept_totals
  WHERE total > (SELECT avg_dept FROM avg_total)
  LIMIT 50
`);

// Recursive CTE (translates to $graphLookup on MongoDB)
const tree = await db.sql(`
  WITH RECURSIVE org AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e JOIN org o ON e.manager_id = o.id
  )
  SELECT * FROM org LIMIT 500
`);

Window Functions

Window functions translate to $setWindowFields on MongoDB. Supports PARTITION BY and ORDER BY within the window specification.

// RANK with PARTITION BY
const ranked = await db.sql(`
  SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
  FROM employees
  LIMIT 100
`);

// LAG — compare to previous row
const deltas = await db.sql(`
  SELECT date, revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change
  FROM daily_stats
  LIMIT 90
`);

Aggregation & GROUP BY

Full aggregation support with GROUP BY, HAVING, and aggregate functions. On MongoDB, these translate to $group and $match stages.

// GROUP BY with HAVING
const stats = await db.sql(`
  SELECT department, COUNT(*) as headcount, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department
  HAVING COUNT(*) > 5
  ORDER BY avg_salary DESC
  LIMIT 20
`);

// UNION ALL
const combined = await db.sql(`
  SELECT name, 'employee' as type FROM employees
  UNION ALL
  SELECT name, 'contractor' as type FROM contractors
  LIMIT 100
`);

SQL Functions

30+ SQL functions are supported and automatically translated to MongoDB expressions. On SQL backends, functions execute natively.

String Functions

Numeric Functions

Date Functions

Conditional Functions

Type Conversion

Parameterized Queries

Prevent SQL injection with parameterized placeholders. Both MySQL-style (?) and PostgreSQL-style ($1) are supported on all backends.

// MySQL-style placeholders
const users = await db.sql(
  'SELECT * FROM users WHERE email = ? AND status = ? LIMIT 10',
  { params: ['alice@example.com', 'active'] }
);

// PostgreSQL-style placeholders
const orders = await db.sql(
  'SELECT * FROM orders WHERE user_id = $1 AND total > $2 LIMIT 20',
  { params: [42, 100] }
);

Transactions in SQL Mode

Execute multi-statement transactions via SQL or the programmatic API. Supported on all backends except Elasticsearch.

// SQL transaction block
await db.sql(`
  BEGIN;
  INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
  COMMIT;
`);

// Programmatic transaction (auto-commits on success, rolls back on error)
await db.withTransaction(async (tx) => {
  await tx.insertOne('users', { name: 'Alice' });
  await tx.updateOne('accounts', { userId: 1 }, { $inc: { balance: -100 } });
});

Explain Mode

Pass { explain: true } to see the full execution plan alongside results. Useful for debugging and understanding how SQL translates to MongoDB pipelines.

const result = await db.sql(`
  SELECT department, AVG(salary) as avg_sal
  FROM employees
  GROUP BY department
  HAVING AVG(salary) > 50000
  LIMIT 10
`, { explain: true });

// result.data → [{ department: 'Engineering', avg_sal: 95000 }, ...]
// result.plan → {
//   phases: 1,
//   dependencies: [],
//   pipelines: [{ collection: 'employees', stages: [
//     { $group: { _id: '$department', avg_sal: { $avg: '$salary' } } },
//     { $match: { avg_sal: { $gt: 50000 } } },
//     { $limit: 10 }
//   ]}],
//   parallel: false,
//   durationMs: 23
// }

Explain Output Fields

Guardrails in SQL Mode

The same safety guardrails that protect MongoDB-style queries also apply to SQL Mode.

// This is blocked (no LIMIT):
await db.sql('SELECT * FROM users');
// Error: LIMIT_REQUIRED — Add a LIMIT clause

// This is allowed:
await db.sql('SELECT * FROM users LIMIT 100');

Cross-Dialect Normalization

StrictDB automatically normalizes dialect-specific SQL so queries work across all backends. Write in the dialect you know — StrictDB handles the rest.

MSSQL Normalizations

MySQL Normalizations

db.aggregate(collection, pipeline, options?)

Run a raw aggregation pipeline directly against MongoDB. Bypasses filter translation — you write the pipeline yourself. On SQL and Elasticsearch backends, this method throws UNSUPPORTED_OPERATION.

// Raw MongoDB aggregate pipeline
const stats = await db.aggregate('orders', [
  { $match: { status: 'completed' } },
  {
    $group: {
      _id: '$userId',
      totalSpent: { $sum: '$amount' },
      orderCount: { $sum: 1 }
    }
  },
  { $sort: { totalSpent: -1 } },
  { $limit: 10 }
]);
// → [{ _id: 'user-1', totalSpent: 450, orderCount: 9 }, ...]

// With explain
const plan = await db.aggregate('orders', pipeline, { explain: true });

db.bulkWrite(collection, operations, options?)

Execute multiple write operations in a single round-trip. More efficient than calling individual methods in a loop. Returns a combined OperationReceipt.

const receipt = await db.bulkWrite('products', [
  {
    insertOne: {
      doc: { sku: 'WIDGET-1', name: 'Widget', price: 9.99, stock: 100 }
    }
  },
  {
    updateOne: {
      filter: { sku: 'GADGET-5' },
      update: { $inc: { stock: -10 } }
    }
  },
  {
    updateMany: {
      filter: { price: { $lt: 1 } },
      update: { $set: { discontinued: true } }
    }
  },
  {
    deleteOne: {
      filter: { sku: 'OLD-SKU' }
    }
  }
]);

// receipt.insertedCount  → 1
// receipt.modifiedCount  → number of rows/docs updated
// receipt.deletedCount   → 1
// receipt.duration       → total ms for the batch

bulkWrite() Supported Operations

Operation Receipts

Every write operation returns a structured receipt:

{
  operation: 'insertOne',     // operation name
  collection: 'users',        // target collection/table
  success: true,              // did it succeed?
  matchedCount: 0,            // documents matched by filter
  modifiedCount: 0,           // documents actually modified
  insertedCount: 1,           // documents inserted
  insertedId: 'abc123',       // ID of inserted doc (insertOne only)
  insertedIds: ['a', 'b'],    // IDs inserted (insertMany only)
  upsertedId: 'xyz789',       // ID of upserted doc (upsert only)
  deletedCount: 0,            // documents deleted
  duration: 12,               // execution time in ms
  backend: 'sql'              // 'mongo' | 'sql' | 'elastic'
}

Transactions

Full transaction support across all backends:

await db.withTransaction(async (tx) => {
  // All operations inside use the same transaction
  await tx.insertOne('orders', {
    userId: 'user-1',
    item: 'widget',
    qty: 5
  });

  await tx.updateOne('inventory',
    { sku: 'W1' },
    { $inc: { stock: -5 } }
  );

  // If anything throws, entire transaction rolls back
});

Batch Operations

const receipt = await db.batch([
  { operation: 'insertOne', collection: 'orders',
    doc: { item: 'widget', qty: 5, userId: 'u1' } },
  { operation: 'updateOne', collection: 'inventory',
    filter: { sku: 'W1' },
    update: { $inc: { stock: -5 } } },
  { operation: 'deleteOne', collection: 'cart_items',
    filter: { userId: 'u1', sku: 'W1' } }
]);

Filter Reference — Comparison Operators

StrictDB uses MongoDB-style filters across all backends. Filters are automatically translated to SQL WHERE clauses or Elasticsearch Query DSL.

$eq — Equality

Implicit when you pass a value directly. These are identical:

{ role: 'admin' }                   // shorthand
{ role: { $eq: 'admin' } }          // explicit

"role" = $1        -- params: ['admin']

{ term: { role: 'admin' } }

$ne — Not Equal

{ status: { $ne: 'banned' } }

"status" != $1

{ bool: { must_not: { term: { status: 'banned' } } } }

$gt, $gte, $lt, $lte — Range

{ age: { $gte: 18, $lt: 65 } }

"age" >= $1 AND "age" < $2
-- params: [18, 65]

{ range: { age: { gte: 18, lt: 65 } } }

null — Null Equality

{ email: null }

"email" IS NULL

{ bool: { must_not: { exists: { field: 'email' } } } }

Filter Reference — Array & Pattern Operators

$in — Match Any Value in Array

{ role: { $in: ['admin', 'mod'] } }

"role" IN ($1, $2)
-- params: ['admin', 'mod']

{ terms: { role: ['admin', 'mod'] } }

$nin — Not In Array

{ role: { $nin: ['banned', 'suspended'] } }

"role" NOT IN ($1, $2)

{ bool: { must_not: { terms: { role: ['banned', 'suspended'] } } } }

$exists — Field Existence

{ email: { $exists: true } }   // field IS NOT NULL
{ email: { $exists: false } }  // field IS NULL

"email" IS NOT NULL

{ exists: { field: 'email' } }

$regex — Pattern Matching

{ name: { $regex: '^Tim' } }

$size — Array Length

{ tags: { $size: 3 } }

Filter Reference — Logical Operators

$and — All Conditions Must Match

{ $and: [{ age: { $gte: 18 } }, { role: 'admin' }] }

(("age" >= $1) AND ("role" = $2))

{ bool: { must: [
  { range: { age: { gte: 18 } } },
  { term: { role: 'admin' } }
] } }

Multiple top-level fields are implicitly $and:

{ role: 'admin', age: { $gte: 18 } }
// SQL: "role" = $1 AND "age" >= $2

$or — Any Condition Matches

{ $or: [{ role: 'admin' }, { role: 'mod' }] }

(("role" = $1) OR ("role" = $2))

{ bool: { should: [
  { term: { role: 'admin' } },
  { term: { role: 'mod' } }
], minimum_should_match: 1 } }

$nor — No Conditions Match

{ $nor: [{ role: 'banned' }, { status: 'inactive' }] }

NOT (("role" = $1) OR ("status" = $2))

{ bool: { must_not: [
  { term: { role: 'banned' } },
  { term: { status: 'inactive' } }
] } }

$not — Negate a Condition

{ age: { $not: { $gt: 18 } } }

NOT ("age" > $1)

{ bool: { must_not: [
  { range: { age: { gt: 18 } } }
] } }

Full SQL Translation Examples

Complete before/after examples showing how MongoDB-style filters become native SQL across all four dialects.

Real-World Query: Active Admin Users

// StrictDB filter (works on any backend)
const filter = {
  status: 'active',
  age: { $gte: 18 },
  role: { $in: ['admin', 'mod'] }
};

Complex Nested Query

{
  $or: [
    { role: 'admin' },
    { $and: [
      { age: { $gte: 18 } },
      { status: { $ne: 'banned' } },
      { email: { $exists: true } }
    ]}
  ]
}

-- PostgreSQL output:
(("role" = $1) OR (("age" >= $2) AND ("status" != $3) AND ("email" IS NOT NULL)))

Full SELECT Statements

Elasticsearch Translation Examples

Complex Query to ES Query DSL

// StrictDB filter
{
  status: 'active',
  age: { $gte: 18, $lt: 65 },
  role: { $in: ['admin', 'mod'] },
  email: { $exists: true }
}

// Elasticsearch Query DSL output:
{
  bool: {
    must: [
      { term: { status: 'active' } },
      { range: { age: { gte: 18, lt: 65 } } },
      { terms: { role: ['admin', 'mod'] } },
      { exists: { field: 'email' } }
    ]
  }
}

Update Operators — $set, $inc, $unset

$set — Set Field Values

{ $set: { name: 'Bob', role: 'admin' } }

SET "name" = $1, "role" = $2

ctx._source.name = params.set_name;
ctx._source.role = params.set_role

$inc — Increment Numeric Fields

{ $inc: { loginCount: 1, score: -5 } }

SET "loginCount" = "loginCount" + $1,
    "score" = "score" + $2

ctx._source.loginCount += params.inc_loginCount;
ctx._source.score += params.inc_score

$unset — Remove Fields

{ $unset: { tempField: true } }

SET "tempField" = NULL

ctx._source.remove('tempField')

Combined Update

{ $set: { name: 'Bob' }, $inc: { count: 1 } }
// SQL: SET "name" = $1, "count" = "count" + $2
// ES:  ctx._source.name = params.set_name; ctx._source.count += params.inc_count

Update Operators — $push, $pull

$push — Append to Array

{ $push: { tags: 'new-tag' } }

$pull — Remove from Array

{ $pull: { tags: 'old-tag' } }

SQL Dialects — Parameter Styles

SQL Dialects — Pagination

Each SQL dialect handles LIMIT/OFFSET differently. StrictDB generates the correct syntax automatically.

SQL Dialects — Single-Row Semantics

updateOne and deleteOne enforce exactly one row across all dialects:

SQL Dialects — Upsert

Pass upsert: true to updateOne. StrictDB uses an UPDATE-then-INSERT pattern that works identically across all SQL dialects:

1. Try UPDATE (limited to 1 row)
2. If zero rows matched, INSERT with filter equality fields merged with $set fields

await db.updateOne('users',
  { email: 'test@test.com' },                     // filter
  { $set: { name: 'Test', role: 'user' } },       // update
  true                                              // upsert
);
// If no match → INSERT INTO "users" ("email", "name", "role") VALUES ($1, $2, $3)

SQL Dialects — Projection

Inclusion (select specific columns):

{ projection: { name: 1, email: 1 } }
// → SELECT "name", "email" FROM "users" ...

Exclusion (hide columns — SQL doesn't natively support this, so StrictDB uses post-processing):

{ projection: { password: 0, secret: 0 } }
// → SELECT * FROM "users" ...  (then strips password, secret from results)

Schema & Validation — Zod Registration

import { z } from 'zod';

const userSchema = z.object({
  email: z.string().max(255),
  name: z.string(),
  age: z.number().int().optional(),
  role: z.enum(['admin', 'user', 'mod']),
  active: z.boolean(),
  createdAt: z.date(),
  tags: z.array(z.string()).optional(),
  metadata: z.object({}).optional(),
});

db.registerCollection({ name: 'users', schema: userSchema });

// Auto-create tables/indexes
await db.ensureCollections();
await db.ensureIndexes();

Schema — SQL DDL Generation

StrictDB introspects Zod schemas and generates CREATE TABLE statements for each dialect:

PostgreSQL

CREATE TABLE IF NOT EXISTS "users" (
  "email" VARCHAR(255) NOT NULL,
  "name" TEXT NOT NULL,
  "age" INTEGER,
  "role" TEXT NOT NULL CHECK(role IN ('admin', 'user', 'mod')),
  "active" BOOLEAN NOT NULL,
  "createdAt" TIMESTAMPTZ NOT NULL,
  "tags" JSONB,
  "metadata" JSONB
)

Type Mapping

Schema — Elasticsearch Mapping Generation

Schema — Runtime Validation

With schema: true in config, all writes are validated before execution:

const db = await StrictDB.create({ uri, schema: true });

try {
  await db.insertOne('users', { name: '', email: 'bad' });
} catch (err) {
  err.code  // 'VALIDATION_ERROR'
  err.fix   // "Field 'name' must be at least 1 character. Field 'email' must be a valid email."
}

Guardrails

Enabled by default. These are hard blocks, not warnings.

Error code: GUARDRAIL_BLOCKED. Emits guardrail-blocked event.

Granular Configuration

Pass true/false to enable/disable all guardrails, or an object for granular control:

const db = await StrictDB.create({
  uri,
  guardrails: {
    limitRequired: false,    // allow unbounded queries
    emptyFilter: true,       // still block empty-filter deletes/updates
    nullComparison: true,    // still block = NULL in SQL mode
  }
});

Sanitization

Enabled by default. Prevents injection and validates field names.

• SQL: Column names validated against registered schema whitelist
• Elasticsearch: Internal fields (_id, _source, _score, _index, _type, _routing, _meta, _field_names, _ignored, _seq_no, _primary_term) blocked from user queries
• ES index names: Wildcards, commas, spaces, system indices (. or - prefix) blocked
• Regex: Patterns validated for catastrophic backtracking (ReDoS). Nested quantifiers and patterns >1000 chars rejected

Custom Sanitize Rules

const db = await StrictDB.create({
  uri,
  sanitizeRules: [
    // Trim all string fields
    { transform: (v) => typeof v === 'string' ? v.trim() : v },

    // Lowercase emails
    { field: 'email', transform: (v) => v.toLowerCase() },

    // Strip HTML from specific fields
    { field: ['name', 'bio'],
      transform: (v) => v.replace(/<[^>]*>/g, '') },

    // Apply to ALL string fields
    { field: '*',
      transform: (v) => typeof v === 'string' ? v.trim() : v },
  ]
});

SanitizeRule Interface

AI Integration — Why AI-First?

Traditional database drivers give AI agents raw access with no guardrails. StrictDB provides four tools that make AI database interactions safe and self-correcting:

describe(collection) — Schema Discovery

const schema = await db.describe('users');
// {
//   name: 'users',
//   backend: 'sql',
//   fields: [
//     { name: 'id', type: 'integer', required: true },
//     { name: 'email', type: 'string', required: true },
//     { name: 'role', type: 'string', required: true, enum: ['admin', 'user', 'mod'] },
//     { name: 'age', type: 'integer', required: false },
//   ],
//   indexes: [{ fields: { email: 1 }, unique: true }],
//   documentCount: 12847,
//   exampleFilter: { role: 'admin' }
// }

validate(collection, operation) — Dry-Run

const check = await db.validate('users', {
  filter: { rol: 'admin' },              // typo!
  doc: { email: 'test@test.com' }
});
// { valid: false, errors: [
//   { field: 'rol', message: 'Unknown field', expected: 'role' }
// ]}

explain(collection, operation) — Query Translation

const plan = await db.explain('users', {
  filter: { role: 'admin', age: { $gte: 18 } },
  sort: { createdAt: -1 },
  limit: 50
});
// {
//   backend: 'sql',
//   native: 'SELECT * FROM "users" WHERE "role" = $1 AND "age" >= $2 ORDER BY "createdAt" DESC LIMIT 50'
// }

Self-Correcting Errors

Every StrictDBError includes a .fix field — a plain-English corrective action:

try {
  await db.insertOne('users', { email: 'alice@example.com' });
} catch (err) {
  err.code       // 'DUPLICATE_KEY'
  err.fix        // 'Use updateOne() instead or check with queryOne() first'
  err.retryable  // false
  err.backend    // 'sql'
  err.collection // 'users'
  err.operation  // 'insertOne'
}

Fuzzy Collection Matching

await db.queryOne('usres', { id: 1 });  // typo!
// Error fix: 'Did you mean "users"? Registered collections: users, orders, products'

Method Suggestions

// If an AI tries to use MongoDB-native method names:
db.find(...)       // → fix: 'Use queryMany() instead'
db.findOne(...)    // → fix: 'Use queryOne() instead'
db.bulkWrite(...)  // → fix: 'Use batch() instead'

MCP Server — Setup

# Install
npm install -g strictdb-mcp

# Run (reads STRICTDB_URI from environment)
STRICTDB_URI="postgresql://user:pass@localhost:5432/mydb" npx strictdb-mcp

MCP Server — All 14 Tools

MCP Server — Claude Desktop Configuration

{
  "mcpServers": {
    "strictdb": {
      "command": "npx",
      "args": ["strictdb-mcp"],
      "env": {
        "STRICTDB_URI": "postgresql://user:pass@localhost:5432/mydb"
      }
    }
  }
}

Claude Code Configuration

{
  "mcpServers": {
    "strictdb": {
      "type": "stdio",
      "command": "npx",
      "args": ["strictdb-mcp"],
      "env": {
        "STRICTDB_URI": "postgresql://user:pass@localhost:5432/mydb"
      }
    }
  }
}

Events

db.on('connected', ({ backend, dbName, label }) => { ... });
db.on('disconnected', ({ backend, reason, timestamp }) => { ... });
db.on('reconnecting', ({ backend, attempt, maxAttempts, delayMs }) => { ... });
db.on('reconnected', ({ backend, attempt, downtimeMs }) => { ... });
db.on('error', ({ code, message, fix, backend }) => { ... });
db.on('operation', ({ collection, operation, durationMs, receipt }) => { ... });
db.on('slow-query', ({ collection, operation, durationMs, threshold }) => { ... });
db.on('guardrail-blocked', ({ collection, operation, reason }) => { ... });
db.on('pool-status', ({ active, idle, waiting, max }) => { ... });
db.on('shutdown', ({ exitCode }) => { ... });

Reconnection

Exponential backoff with jitter to prevent thundering herd:

// Delay formula:
// baseDelay = initialDelayMs * (backoffMultiplier ^ (attempt - 1))
// cappedDelay = min(baseDelay, maxDelayMs)
// actualDelay = cappedDelay * (0.75 + random * 0.5)   ← jitter ±25%

// Example with defaults (1s initial, 2x multiplier, 30s max):
// Attempt 1: ~1s    (750ms – 1250ms)
// Attempt 2: ~2s    (1500ms – 2500ms)
// Attempt 3: ~4s    (3000ms – 5000ms)
// Attempt 4: ~8s    (6000ms – 10000ms)
// Attempt 5: ~16s   (12000ms – 20000ms)
// Attempt 6+: ~30s  (capped at maxDelayMs)

Auto Timestamps

// Enable with defaults (created_at, updated_at)
const db = await StrictDB.create({ uri, timestamps: true });

// Or customize field names
const db = await StrictDB.create({
  uri,
  timestamps: { createdAt: 'born', updatedAt: 'modified' }
});

• Insert: Both createdAt and updatedAt injected
• Update: Only updatedAt injected (never overwrites createdAt)
• User values preserved: If you explicitly set a timestamp field, StrictDB won't overwrite it
• Immutable: Original documents are never mutated — new objects returned

Escape Hatch — db.raw()

Access the underlying native driver when you need backend-specific features:

const native = db.raw();

// MongoDB → returns MongoClient
// PostgreSQL → returns pg.Pool
// MySQL → returns mysql2 connection
// MSSQL → returns mssql connection pool
// SQLite → returns better-sqlite3 Database
// Elasticsearch → returns @elastic/elasticsearch Client

Error Code Reference

TypeScript Types

import type {
  Backend,              // 'mongo' | 'sql' | 'elastic'
  Driver,               // 'mongodb' | 'pg' | 'mysql2' | 'mssql' | 'sqlite' | 'elasticsearch'
  SqlDialect,           // 'pg' | 'mysql2' | 'mssql' | 'sqlite'
  StrictFilter,         // MongoDB-style filter object
  UpdateOperators,      // { $set?, $inc?, $unset?, $push?, $pull? }
  QueryOptions,         // { sort?, limit?, skip?, projection? }
  LookupOptions,        // { match, lookup, unwind?, sort?, limit? }
  OperationReceipt,     // Write operation result
  StrictDBConfig,       // create() configuration
  StrictDBEvents,       // Typed event map
  SanitizeRule,         // Custom sanitization rule
  ReconnectConfig,      // Reconnection options
  IndexDefinition,      // Index schema
  CollectionSchema,     // Collection + Zod schema registration
  StrictDBError,        // Error class with .fix, .code, .retryable
} from 'strictdb';

Using Generics

interface User {
  email: string;
  name: string;
  role: 'admin' | 'user' | 'mod';
  age?: number;
}

// Fully typed — result is User | null
const user = await db.queryOne<User>('users', { email: 'alice@example.com' });

// Filter autocomplete works with the generic
const admins = await db.queryMany<User>('users',
  { role: 'admin' },
  { sort: { name: 1 }, limit: 50 }
);