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.
On this page9
- Views
- `ViewManager.create(name, selectSQL, db, replace = true, driverType?)`
- Query a view
- Check and drop
- Stored procedures and functions
- `ProcedureManager.call(name, params, driver, driverType)`
- `ProcedureManager.scalar(name, params, driver, driverType)`
- `ProcedureManager.execute(name, params, driver, driverType)`
- Related
@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').
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?)
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:
| Dialect | replace: true (default) | replace: false |
|---|---|---|
| PostgreSQL, MySQL | CREATE OR REPLACE VIEW | CREATE VIEW, skipped when the view already exists |
| SQLite | DROP VIEW IF EXISTS then CREATE VIEW | CREATE 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.
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
await ViewManager.exists('active_users', driver, 'postgres') // true | false
await ViewManager.drop('active_users', driver) // DROP VIEW IF EXISTSexists() 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.
1const rows = await ProcedureManager.call<{ sku: string; discount: number }>(
2 'calculate_discounts',
3 [userId, orderTotal],
4 driver,
5 'postgres'
6)| Dialect | SQL issued |
|---|---|
| PostgreSQL | SELECT * FROM name($1, $2, ...) |
| MySQL, SQLite | CALL 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.
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.
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.
Related
Was this page helpful?