Database

Database: getting started

Your app talks to SQLite, PostgreSQL or MySQL through one API: raw SQL with the DB facade, the query builder, and your models.

Introduction#

Oriel reaches your database through Kysely, with three drivers:

SQLite, with better-sqlite3 on Node and Bun's own bun:sqlite on Bun. A new app uses it, so you need no database server to start.
PostgreSQL, with pg.
MySQL, with mysql2.

pg and mysql2 aren't installed with the framework: install the one you need, npm install pg or npm install mysql2. A driver is loaded the first time your app queries the database.

Configuration#

Your connections are in config/database.ts. default names the one your app uses, sqlite unless DB_CONNECTION names another:

const database = {  default: env('DB_CONNECTION', 'sqlite'),  connections: {    sqlite: { driver: 'sqlite', database: env('DB_DATABASE', join(basePath, 'database/database.sqlite')) },    pgsql: { driver: 'pgsql', host: env('DB_HOST', '127.0.0.1'), port: Number(env('DB_PORT', 5432)), database: env('DB_DATABASE', 'oriel') },  },}

Each connection takes host, port, database, username and password, read from the DB_* variables of your .env (see Databases and migrations). pool.max is the size of the connection pool, 10 unless you set it. A pgsql connection takes a search_path, and a mysql one a charset, utf8mb4 unless you set it.

SQLite configuration#

A SQLite connection's database is the path of the database file. A new app's is database/database.sqlite, as an absolute path, since a relative one is opened from the folder your app runs in; migrate creates the file. :memory: gives a database in memory, which your tests use.

SQLite checks foreign keys, as foreign_key_constraints: true asks; set it to false to turn the checks off. Changing a table on SQLite rebuilds it: SQLite and schema changes explains.

Configuration using URLs#

A pgsql or mysql connection can take a single url, such as the one a hosting provider gives you, in DB_URL:

DB_URL=postgresql://user:secret@db.example.com:5432/blog

A URL's parts win over the separate keys. A SQLite connection ignores url: give it a database path.

Running SQL queries#

The DB facade runs raw SQL on the default connection. Every method is async.

Running a select query#

DB.select() runs a select, with the values for its ? placeholders in an array, and returns the rows as objects:

app/Http/Controllers/UserController.ts

import { DB } from '@orieljs/framework'import { Controller } from './Controller'export class UserController extends Controller {  /** Show a list of all of the application's users. */  async index() {    const users = await DB.select<{ id: number; name: string }>('select * from users where active = ?', [1])    return { users }  }}

The values are bound, never written into the SQL, so they are safe from SQL injection. The type you give the rows is a cast, not a check.

Using multiple database connections#

DB.connection() returns another connection of config/database.ts, with select(), statement(), affectingStatement(), table() and transaction():

const users = await DB.connection('sqlite').select('select * from users')

DB.usingConnection('sqlite', () => ...) runs a function with another connection as the default, and puts the default back after it.

Using Kysely#

Oriel's queries run on Kysely. DB.kysely() returns the connection's Kysely instance, for a query the query builder doesn't write; inside a transaction it is the transaction's, so its queries join it:

const rows = await DB.kysely().selectFrom('users').select(['id', 'name']).where('votes', '>', 100).execute()

Running an insert statement#

await DB.insert('insert into users (id, name) values (?, ?)', [1, 'Marc'])

Running an update statement#

DB.update() returns the number of rows it changed:

const affected = await DB.update('update users set votes = 100 where name = ?', ['Anita'])

Running a delete statement#

DB.delete() returns the number of rows it deleted:

const deleted = await DB.delete('delete from users')

Running a general statement#

DB.statement() runs SQL that returns nothing, such as a schema change, and DB.affectingStatement() returns the number of rows a statement changed:

await DB.statement('drop table users')

Raw expressions#

DB.raw() makes an expression the query builder writes as it is, with its own bindings:

const users = await DB.table('users').select(DB.raw('count(*) as user_count, status')).groupBy('status').get()

A raw expression is SQL you write, so never put a user's input in it: bind it with ? instead.

Values in bindings#

Oriel turns some values into what the database stores: true and false become 1 and 0 (except on PostgreSQL, which has booleans), a Date becomes its UTC time as '2026-01-02 03:04:05', an object or an array becomes JSON, and undefined becomes null.

Dates come back as strings, on every driver: your models' date casts turn them into Date objects.

Database transactions#

DB.transaction() runs a function in a transaction. When the function throws, the transaction is rolled back and the error is thrown again; otherwise it is committed:

await DB.transaction(async () => {  await DB.update('update users set votes = 1')  await DB.delete('delete from posts')})

The queries your function runs, through the facade, the query builder or your models, are in the transaction, and those of other requests running at the same time aren't. A transaction inside another uses a savepoint, so an inner one that throws rolls back only its own work. The function's result is transaction()'s:

const user = await DB.transaction(async () => {  const id = await DB.table('users').insertGetId({ name: 'Taylor', email: 'taylor@example.com', password: '' })  return DB.table('users').where('id', id).first()})

Manually using transactions#

DB.beginTransaction() starts a transaction that lasts, for every query on the connection, until DB.commit() or DB.rollBack():

await DB.beginTransaction()try {  await DB.update('update users set votes = 1')  await DB.commit()} catch (error) {  await DB.rollBack()  throw error}

Prefer transaction(): a manual transaction takes in the queries of every request until it ends, which suits a script or a test, not a request.

Logging queries#

DB.enableQueryLog() records each query the default connection runs, with its bindings and how many milliseconds it took, and DB.getQueryLog() returns them:

DB.enableQueryLog()await DB.table('users').get()const queries = DB.getQueryLog() // [{ query: 'select * from "users"', bindings: [], time: 0.4 }]

A query that fails throws a QueryError, whose message ends with the connection's name and the SQL, and which has sql and bindings fields.

Closing connections#

Your app's server, its oriel commands and its queue workers close their database connections when they shut down. A script of your own can end with await app.shutdown(), or close them with await DB.disconnectAll(); DB.disconnect('mysql') closes one.