Database

Views and Stored Procedures

Create, query and drop SQL views and call stored procedures and functions on PostgreSQL, MySQL and SQLite with ViewManager and ProcedureManager.

3 min readUpdated DocumentationEdit this page
On this page9

@axiljs/orm ships two small static helpers for database objects that live beside your tables: ViewManager for SQL views and ProcedureManager for stored procedures and functions. Both take the driver from createConnection() and the dialect name ('postgres', 'mysql' or 'sqlite').

TypeScript
TS
1import { createConnection, ViewManager, ProcedureManager } from '@axiljs/orm'
2
3const db     = await createConnection({ driver: 'postgres', url: process.env.DATABASE_URL })
4const driver = db.getDriver()

Views

ViewManager.create(name, selectSQL, db, replace = true, driverType?)

TypeScript
TS
1await ViewManager.create(
2  'active_users',
3  'SELECT id, name, email FROM users WHERE is_active = true',
4  driver,
5  true,
6  'postgres'
7)

The SQL issued depends on the dialect and replace:

Dialectreplace: true (default)replace: false
PostgreSQL, MySQLCREATE OR REPLACE VIEWCREATE VIEW, skipped when the view already exists
SQLiteDROP VIEW IF EXISTS then CREATE VIEWCREATE VIEW IF NOT EXISTS

driverType is optional when you pass a driver created by createConnection(): the dialect is read from its connection config.

Query a view

ViewManager.query() returns the regular query builder pointed at the view, so filtering, ordering and pagination work as on a table.

TypeScript
TS
1const recent = await ViewManager.query<{ id: number; name: string }>('active_users', driver, 'postgres')
2  .where('name', 'LIKE', 'A%')
3  .orderBy('name')
4  .limit(20)
5  .get()

Check and drop

TypeScript
TS
await ViewManager.exists('active_users', driver, 'postgres') // true | false
await ViewManager.drop('active_users', driver)               // DROP VIEW IF EXISTS

exists() looks in the current schema on PostgreSQL, the current database on MySQL, and sqlite_master on SQLite.

Tip

Create views in a migration's up() and drop them in down() so they are versioned with the tables they read from. See Migrations.

Note

The view name and SELECT are interpolated into SQL as-is. Pass trusted, code-defined values only, never user input.

Stored procedures and functions

ProcedureManager.call(name, params, driver, driverType)

Calls a procedure or set-returning function with bound parameters and resolves with the result rows.

TypeScript
TS
1const rows = await ProcedureManager.call<{ sku: string; discount: number }>(
2  'calculate_discounts',
3  [userId, orderTotal],
4  driver,
5  'postgres'
6)
DialectSQL issued
PostgreSQLSELECT * FROM name($1, $2, ...)
MySQL, SQLiteCALL name(?, ?, ...)

On MySQL, the first result set of the CALL is returned as the row array.

ProcedureManager.scalar(name, params, driver, driverType)

For functions that return a single value. Resolves with the first column of the first row, or null when there are no rows.

TypeScript
TS
const total = await ProcedureManager.scalar<number>('order_total', [orderId], driver, 'postgres')

ProcedureManager.execute(name, params, driver, driverType)

Runs a procedure for its side effects and discards the result.

TypeScript
TS
await ProcedureManager.execute('archive_old_orders', [90], driver, 'mysql')

Note

SQLite has no stored procedures. call() on SQLite issues CALL ..., which SQLite rejects; use views or application code there.

Was this page helpful?

Help improve the documentation

AxilJS is open source and documentation improvements are welcome.

AxilJS DocumentationMIT License · Built by SyntaxilitY