Database

ORM Overview

Connect @axiljs/orm to PostgreSQL, MySQL or SQLite, configure pools and SSL, run raw SQL, and choose between BaseModel and BaseRepository in TypeScript.

7 min readUpdated DocumentationEdit this page
On this page13

@axiljs/orm is the database layer of AxilJS. This page covers installing it with the right driver, connecting to PostgreSQL, MySQL or SQLite, running raw SQL, and choosing between the two data-access styles: Active Record models (BaseModel) and repositories (BaseRepository).

❯npm install @axiljs/orm
❯pnpm add @axiljs/orm
❯yarn add @axiljs/orm
❯bun add @axiljs/orm

Database drivers

The database drivers are optional peer dependencies, loaded with require() only when you connect. Install the one for your database:

Databasedriver valuePackage to install
PostgreSQL'postgres'npm install pg
MySQL 5.7+, MySQL 8, MariaDB'mysql'npm install mysql2
SQLite'sqlite'npm install better-sqlite3

If the package is missing, createConnection() throws with the exact install command, for example pg is required for PostgreSQL support. Run: npm install pg.

Note

'mongodb' appears in the DatabaseDriver type, but no MongoDB driver ships yet. Passing it to createConnection() throws Unsupported database driver.

Connect

createConnection(config, name?) builds the driver, opens the connection and registers it under name (default 'default'). It returns a ConnectionManager.

TypeScript
TS
1import { createConnection } from '@axiljs/orm'
2
3const db = await createConnection({
4  driver: 'postgres',
5  url: process.env.DATABASE_URL
6})
7
8const { rows } = await db.getDriver().query('SELECT now() AS time')
9
10await db.disconnect()   // on shutdown

PostgreSQL

url takes priority. Without it, the individual fields are used.

TypeScript
TS
1const db = await createConnection({
2  driver: 'postgres',
3  host: 'localhost',
4  port: 5432,
5  database: 'shop',
6  username: 'app',
7  password: process.env.DB_PASSWORD,
8  ssl: { rejectUnauthorized: false },
9  pool: { min: 2, max: 20, idleTimeoutMs: 30000 }
10})

PostgreSQL uses all three pool settings and passes ssl straight to pg.

MySQL

TypeScript
TS
1const db = await createConnection({
2  driver: 'mysql',
3  url: 'mysql://app:secret@localhost:3306/shop',
4  pool: { max: 20 }
5})

For MySQL only pool.max is used (as mysql2's connectionLimit). pool.min and pool.idleTimeoutMs are ignored. ssl is passed through when set.

SQLite

TypeScript
TS
1const db = await createConnection({ driver: 'sqlite', filename: './dev.db' })
2
3// In-memory, handy for tests
4const testDb = await createConnection({ driver: 'sqlite', filename: ':memory:', logging: false })

The file path comes from filename, then database, and falls back to ':memory:'. On connect, the driver turns on WAL journal mode and foreign-key enforcement. pool and ssl do not apply.

Connection options

OptionTypeDefaultDescription
driver'postgres' | 'mysql' | 'sqlite'requiredDatabase engine
urlstringnoneConnection URL. Takes priority over the individual fields (PostgreSQL, MySQL)
hoststring'localhost'Server host
portnumber5432 (PostgreSQL), 3306 (MySQL)Server port
databasestringnoneDatabase name. For SQLite, used as the file path when filename is not set
usernamestringnoneDatabase user
passwordstringnoneDatabase password
filenamestring':memory:'SQLite file path
pool.minnumber2Minimum pooled connections (PostgreSQL only)
pool.maxnumber10Maximum pooled connections (PostgreSQL, MySQL)
pool.idleTimeoutMsnumber30000Release idle connections after this many ms (PostgreSQL only)
sslboolean | { ca, cert, key, rejectUnauthorized }noneTLS settings (PostgreSQL, MySQL)
loggingbooleanunsettrue logs every query; false also silences the "connected" message

ConnectionManager

MethodDescription
connect()Open the connection. createConnection() already calls it
disconnect()Close the pool or SQLite file
getDriver()The underlying driver: query(), transaction(), ping()
isConnected()true after a successful connect
ConnectionManager.register(name, manager)Store a connection under a name
ConnectionManager.get(name?)Look up a registered connection (default 'default')

Because createConnection() registers the connection, any module can reach it without importing db:

TypeScript
TS
1import { ConnectionManager, createConnection } from '@axiljs/orm'
2
3await createConnection({ driver: 'postgres', url: process.env.DATABASE_URL })
4await createConnection({ driver: 'postgres', url: process.env.REPORTING_URL }, 'reporting')
5
6const main = ConnectionManager.get()
7const reporting = ConnectionManager.get('reporting')

Raw SQL

getDriver().query(sql, params) runs a parameterized statement and returns a QueryResult:

FieldDescription
rowsReturned rows
affectedRowsRows changed by INSERT, UPDATE or DELETE
insertIdLast inserted ID. On PostgreSQL this is rows[0].id, so add RETURNING id

Placeholders follow the database: $1, $2 for PostgreSQL, ? for MySQL and SQLite.

TypeScript
TS
1const driver = db.getDriver()
2
3await driver.query('SELECT * FROM users WHERE email = $1', ['tariq@axiljs.dev'])   // PostgreSQL
4await driver.query('SELECT * FROM users WHERE email = ?', ['tariq@axiljs.dev'])    // MySQL, SQLite
5
6const alive = await driver.ping()

For anything beyond one-off SQL, use the query builder, which picks the right placeholders for you.

Models or repositories

The ORM gives you two ways to work with tables. Both use the same entity decorators.

BaseModel (Active Record)BaseRepository (Repository pattern)
SetupEntity class extends BaseModel; call BaseModel.setDriver() onceSubclass BaseRepository<Entity> and pass the ConnectionManager
CallsUser.find(1), User.create({...}), user.save()users.findById(1), users.create({...})
ResultsInstances of your class, with dirty tracking and toJSON()Plain row objects
Property namesMapped to column names (@Column({ name }))Used as column names as-is
Best forSmall to medium apps, scripts, quick CRUDServices with dependency injection and custom query methods
TypeScript
TS
1import { BaseModel, createConnection } from '@axiljs/orm'
2import { User } from './entities/user.entity'
3import { UserRepository } from './repositories/user.repository'
4
5const db = await createConnection({ driver: 'sqlite', filename: './dev.db' })
6
7// Active Record: register the driver once at startup
8BaseModel.setDriver(db.getDriver(), 'sqlite')
9const ann = await User.find(1)
10
11// Repository: pass the connection in
12const users = new UserRepository(db)
13const bob = await users.findById(2)

See Entities for BaseModel and Repositories for BaseRepository. Relations (@HasMany, @BelongsTo and the rest) are covered in Relations.

Transactions

Every driver has transaction(work). It commits when work resolves and rolls back when it throws. Run every statement through the trx it passes in:

TypeScript
TS
1await db.getDriver().transaction(async (trx) => {
2  const { rows } = await trx.query<{ id: number }>(
3    'INSERT INTO users (name) VALUES ($1) RETURNING id',
4    ['Tariq']
5  )
6  await trx.query('INSERT INTO profiles (user_id) VALUES ($1)', [rows[0].id])
7})

The fluent Transaction.run() API, savepoints and per-driver behaviour are covered in Transactions.

Logging

TypeScript
TS
1// Default: prints a one-line "connected" message, no query logs
2const db = await createConnection({ driver: 'sqlite', filename: './dev.db' })
3
4// Log every query (and the connected message)
5const verbose = await createConnection({ driver: 'postgres', url: process.env.DATABASE_URL, logging: true })
6
7// Completely silent, e.g. in tests or CLI tools
8const quiet = await createConnection({ driver: 'sqlite', filename: ':memory:', logging: false })

The connected message hides the password from a connection URL.

Note

Changed in 0.3.3: logging: false also silences the [AxilJS ORM] ... connected message. Leaving logging unset keeps the connected message but does not log queries.

Schema is managed by migrations

Entity decorators describe tables for the ORM; they never create or alter them. Create tables, columns and indexes with migrations, run by axil migrate or MigrationRunner. Database views and stored procedures have their own helpers, covered in Views and procedures.

Was this page helpful?

Help improve the documentation

AxilJS is open source and documentation improvements are welcome.

AxilJS DocumentationMIT License · Built by SyntaxilitY