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.
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).
Database drivers
The database drivers are optional peer dependencies, loaded with require() only when you connect. Install the one for your database:
| Database | driver value | Package 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.
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 shutdownPostgreSQL
url takes priority. Without it, the individual fields are used.
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
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
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
| Option | Type | Default | Description |
|---|---|---|---|
driver | 'postgres' | 'mysql' | 'sqlite' | required | Database engine |
url | string | none | Connection URL. Takes priority over the individual fields (PostgreSQL, MySQL) |
host | string | 'localhost' | Server host |
port | number | 5432 (PostgreSQL), 3306 (MySQL) | Server port |
database | string | none | Database name. For SQLite, used as the file path when filename is not set |
username | string | none | Database user |
password | string | none | Database password |
filename | string | ':memory:' | SQLite file path |
pool.min | number | 2 | Minimum pooled connections (PostgreSQL only) |
pool.max | number | 10 | Maximum pooled connections (PostgreSQL, MySQL) |
pool.idleTimeoutMs | number | 30000 | Release idle connections after this many ms (PostgreSQL only) |
ssl | boolean | { ca, cert, key, rejectUnauthorized } | none | TLS settings (PostgreSQL, MySQL) |
logging | boolean | unset | true logs every query; false also silences the "connected" message |
ConnectionManager
| Method | Description |
|---|---|
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:
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:
| Field | Description |
|---|---|
rows | Returned rows |
affectedRows | Rows changed by INSERT, UPDATE or DELETE |
insertId | Last 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.
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) | |
|---|---|---|
| Setup | Entity class extends BaseModel; call BaseModel.setDriver() once | Subclass BaseRepository<Entity> and pass the ConnectionManager |
| Calls | User.find(1), User.create({...}), user.save() | users.findById(1), users.create({...}) |
| Results | Instances of your class, with dirty tracking and toJSON() | Plain row objects |
| Property names | Mapped to column names (@Column({ name })) | Used as column names as-is |
| Best for | Small to medium apps, scripts, quick CRUD | Services with dependency injection and custom query methods |
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:
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
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.
Related
Was this page helpful?