Database Drivers — PostgreSQL, MySQL, SQLite

Connect SysMARA ORM to PostgreSQL, MySQL, or SQLite through a unified driver interface.

Overview

Starting with v0.7.0, SysMARA ORM supports real database drivers for PostgreSQL, MySQL, and SQLite. Each driver is an optional peer dependency — you install only the one you need. When no driver is installed, the ORM falls back to an in-memory store suitable for testing and local development.

All drivers implement a unified DatabaseDriver interface, so switching between databases is a configuration change, not a code change. The query builder produces PostgreSQL-style $1, $2 placeholders. MySQL and SQLite drivers convert placeholders and handle quoting differences automatically.

Supported Drivers

Driver	npm Package	Provider	Notes
pg	`pg`	`postgresql`	Connection pooling via `pg.Pool`. Native `$1, $2` placeholders.
mysql2	`mysql2`	`mysql`	Promise-based pool. Converts `$N` to `?` and double-quotes to backticks.
better-sqlite3	`better-sqlite3`	`sqlite`	Synchronous driver wrapped in async interface. WAL mode enabled by default. Handles `RETURNING` emulation.
In-Memory	None (built-in)	Any	JavaScript `Map`-based store. No installation required. Used as fallback.

Installation

Install the driver for your target database as a peer dependency:

# PostgreSQL
npm install pg
npm install -D @types/pg

# MySQL
npm install mysql2

# SQLite
npm install better-sqlite3
npm install -D @types/better-sqlite3

Only install the driver you need. SysMARA dynamically imports the package at runtime, so uninstalled drivers do not cause errors until you attempt to connect with that provider.

Configuration

Set the provider and connectionString in your sysmara.config.yaml:

# sysmara.config.yaml
database:
  adapter: sysmara-orm
  provider: postgresql      # postgresql | mysql | sqlite
  connectionString: env(DATABASE_URL)

Example connection strings for each provider:

# PostgreSQL
DATABASE_URL=postgresql://user:pass@localhost:5432/mydb

# MySQL
DATABASE_URL=mysql://user:pass@localhost:3306/mydb

# SQLite (file path or in-memory)
DATABASE_URL=sqlite://./data/app.db
DATABASE_URL=:memory:

How It Works

The DatabaseDriver Interface

Every driver implements a unified four-method interface:

interface DatabaseDriver {
  readonly provider: DatabaseProvider;
  connect(): Promise<void>;
  disconnect(): Promise<void>;
  query(sql: string, params?: unknown[]): Promise<QueryResult>;
  exec(sql: string): Promise<void>;
}

Method	Description
`connect()`	Opens a connection (or pool) to the database. Tests connectivity before resolving.
`disconnect()`	Closes the connection and releases resources.
`query(sql, params)`	Executes a parameterized SQL query. Returns `{ rows, rowCount }`.
`exec(sql)`	Executes raw SQL statements (e.g. `CREATE TABLE`). Used for schema application.

 Placeholder Conversion
 
The query builder always produces PostgreSQL-style $1, $2, $3 placeholders. This is the canonical format across SysMARA ORM. When you use the MySQL or SQLite driver, the driver transparently converts:
  $1, $2 placeholders become ? placeholders.
 Parameters are reordered to match the $N indices (PostgreSQL allows out-of-order references like $3, $1, $2).
 MySQL converts double-quoted identifiers to backtick-quoted identifiers ("user" becomes `user`).
 SQLite strips PostgreSQL-specific statements like CREATE EXTENSION and emulates RETURNING * with a follow-up SELECT.
 
 Driver Factory
 
Drivers are created through two factory functions:
 import { createDriver, createInMemoryDriver } from 'sysmara/database/adapters/sysmara-orm/driver';

// Create a real driver based on provider
const driver = createDriver('postgresql', process.env.DATABASE_URL);

// Create an in-memory driver for testing
const memDriver = createInMemoryDriver();
 In-Memory Fallback
 
When no database driver package is installed, SysmaraORM automatically uses the built-in InMemoryDriver. This driver stores data in plain JavaScript Map objects and supports the same query interface as the real drivers.
 The in-memory driver is useful for:
  Unit testing — no database setup required.
 Local development — prototype quickly without running a database server.
 CI pipelines — run integration tests without external dependencies.
 
 
The in-memory driver parses CREATE TABLE statements from exec() calls to initialize table storage, and handles SELECT, INSERT, UPDATE, and DELETE operations with basic WHERE clause parsing. It auto-generates IDs and timestamps for inserted rows.
 Repository Integration
 
In most cases, you do not interact with the driver directly. The driver is used internally by SysmaraRepository, which provides capability-scoped, typed CRUD operations. The ORM selects and manages the driver based on your configuration.
 Full Example
 import { SysmaraORM } from 'sysmara/database/adapters/sysmara-orm/orm';

// 1. Create the ORM with your config and parsed specs
const orm = new SysmaraORM(
  { adapter: 'sysmara-orm', provider: 'postgresql', connectionString: process.env.DATABASE_URL },
  specs
);

// 2. Connect — creates a PostgresDriver internally
await orm.connect();

// 3. Apply schema — runs CREATE TABLE statements through the driver
await orm.applySchema();

// 4. Use a typed repository for CRUD
const userRepo = orm.repository<User>('user', 'create_user');

// Insert
const newUser = await userRepo.create({ email: 'alice@example.com', role: 'admin' });

// Query
const found = await userRepo.findOne({ email: 'alice@example.com' });

// Update
const updated = await userRepo.update(newUser.id, { role: 'member' });

// Delete
await userRepo.delete(newUser.id);

// 5. Disconnect when done
await orm.disconnect();
 
The same code works across all providers. Change provider to 'mysql' or 'sqlite' and install the corresponding package — no application code changes required.
 Related Concepts
  SysMARA ORM — the AI-first ORM that uses these drivers
 Database Adapters — the pluggable adapter system for Prisma, Drizzle, TypeORM, and SysMARA ORM
 Entities — YAML entity specs that define the database schema
 CLI Reference — sysmara db commands