Views, triggers & other schema objects

Views, triggers & other schema objects Beyond tables, indexes and constraints, a database holds views, materialized views, sequences, triggers, stored procedures, functions, row-level security policies and partitions. Prorm can create all of them, from three places: 1. prorm. — imperative methods on the connection. 2. prorm.getQueryInterface() — the same operations, dialect-normalised, and what migrations use. 3. Decorators — @View, @MaterializedView, @Trigger, @Procedure, @SqlFunction declared on a class and applied by sync(). Where an engine genuinely lacks a feature, the builders throw UnsupportedSchemaObjectError with an explanation rather than emitting SQL the driver will reject. That is the design: fail with a reason at the call site, not with a syntax error from the server. Views A view is a saved query. Describe it with the same options you would pass to findAll() and the dialect compiles the SELECT for you — there is no SQL to write: From the model itself When the view selects from one model, put the call on that model — from is implied and drops out: That is the same view as the qi.createView() call above; the model supplies from. Omit the definition entirely for a view of the whole table, and pass from anyway to select from somewhere else while keeping the call on the model: Available on every model, however it was defined — prorm.define(), prorm.addModel() or the decorators. A model that was never registered says so rather than failing with "not a function". Selecting from anywhere from takes a model class or a plain table name. Everything else — where, include, group, having, order, limit, distinct, cte, union — means exactly what it means in a query, because it is the query: the body is built by the same buildSelectQuery() that runs your findAll() calls. So the view is quoted for the database you are connected to, and a definition that works on PostgreSQL works on MySQL. Values are escaped, not concatenated A CREATE VIEW cannot carry bind parameters — the definition is stored in the catalog, so its values have to be literals. Prorm folds them in through the dialect's own escaping rather than string concatenation, which is why a value like O'Brien is safe: Aggregates and expressions Aggregate views need no SQL either — fn(), col() and cast() cover the expression forms, and [expression, alias] names the output column: Declaratively, with a decorator Compiling a definition needs a live dialect, so ViewRegistry.getSQL() wants one when you call it directly — ViewRegistry.createView(prorm, ActiveUsers) takes it from the instance. The view name defaults to the class name in snakecase. The raw SQL form (deprecated) Deprecated: passing a SELECT string to createView(), and the decorator's query and queryFn options. Use definition instead. The string form still works and is not scheduled for removal in 2.x, so existing code keeps running — but it is no longer the documented way to define a view. A hand-written string is not portable across dialects, does not quote identifiers for you, and puts you back in the business of escaping values by hand — the three things definition exists to take care of. Materialized views Materialized views take the same structured definition: prorm.createMaterializedView() is PostgreSQL-only and throws Materialized views are only supported in PostgreSQL elsewhere. The QueryInterface versions delegate straight to the dialect, so Oracle's materialized views — with FAST / COMPLETE / FORCE refresh methods and ON COMMIT / ON DEMAND schedules from RefreshOptions — are reachable through prorm.getQueryInterface() or the dialect instance. @MaterializedView declares one on a class, with refreshStrategy defaulting to 'ON DEMAND'. It accepts definition exactly as @View does. Deprecated: query and queryFn on @MaterializedView, and the query option on prorm.createMaterializedView() / qi.createMaterializedView(). Pass definition instead. A model mapped onto a materialized view gets Model.refresh() and Model.isMaterializedView(). Addressing a model instead of a table Every operation below exists on prorm.getQueryInterface(), where the first argument is a table name. When you have the model, that argument is redundant — and it is a second place for the table name to live, one a tableName option or a schema prefix can quietly make wrong. So each is also a method on the model: The model supplies its own resolved table name, so the two can never disagree. On the model | Delegates to | --- | --- | Article.addColumn(name, def) | addColumn(table, …) | Article.removeColumn(name) | removeColumn(table, …) | Article.changeColumn(name, def) | changeColumn(table, …) | Article.renameColumn(old, new) | renameColumn(table, …) | Article.addIndex(fields, opts) | addIndex(table, …) | Article.removeIndex(name) | removeIndex(table, …) | Article.showIndexes() | showIndexes(table) | Article.indexExists(name) | indexExists(table, …) | Article.createFullTextIndex(opts) | createFullTextIndex({ table, … }) | Article.addConstraint(name, def) | addConstraint(table, …) | Article.removeConstraint(name) | removeConstraint(table, …) | Article.getConstraints() | getConstraints(table) | Article.getForeignKeys() | getForeignKeysForTable(table) | Article.tableExists() | tableExists(table) | Article.renameTable(newName) | renameTable(table, …) | Article.describeTable() | describeTable(table) | Article.enableRowLevelSecurity() | enableRowLevelSecurity(table) | Article.disableRowLevelSecurity() | disableRowLevelSecurity(table) | Article.createPolicy(opts) | createPolicy({ table, … }) | Article.dropPolicy(name) | dropPolicy(name, table) | Article.createTrigger(opts) | createTrigger({ table, … }) | Article.dropTrigger(name) | dropTrigger(name, table) | Article.createPartition(opts) | createPartition({ parentTable, … }) | Article.attachPartition(opts) | attachPartition({ parentTable, … }) | Article.detachPartition(opts) | detachPartition(…) | Article.dropPartition(name) | dropPartition(name, …) | Article.createView(name, def) | createView(name, { from: Article, … }) | Article.createMaterializedView(name, def) | createMaterializedView({ definition, … }) | Article.dropView(name) | dropView(name) | Options objects that carry a table (or parentTable) get it filled in, and passing one explicitly still wins — so a call can sit on the model while acting somewhere else: These are pure addressing — each delegates straight to the QueryInterface, so behaviour, dialect support and error messages are identical. Use whichever reads better: the model form in application code, the table form in migrations, where the model may not exist yet. Note: Article.describeTable() reports the table as the database has it, which is what qi.describeTable() returns. Article.describe() is a different, older method that returns the model's own attribute definitions. Constraints, policies and indexes without SQL The remaining places that stored a SQL fragment now take the same where syntax a query does. Values are escaped and identifiers quoted for the connected database, exactly as in a view. Check constraints Note: a CHECK with no predicate used to emit CHECK (1=1) — a constraint that silently enforced nothing. It now throws instead. Row-level security using decides which rows are visible; withCheck decides which rows may be written. Both take a where object or, still, a raw string. Expression indexes Index a function of a column with the expression helpers rather than a string: Functions written in another language Sometimes the logic does not belong in SQL — you have C you want the database to call, or you would rather write the transformation in Python or JavaScript. createExternalFunction() registers either kind, and afterwards it is just a function you can call from a query. Compiled (C) You compile to a shared library; the database loads it. This is the MySQL case: MySQL resolves library against the server's plugindir, so pass a bare file name. Loadable functions may return only STRING, INTEGER, REAL or DECIMAL — anything else is rejected here rather than by the server — and the statement carries no parameter list, because a loadable function checks its own arity in its init routine. Add aggregate: true for a GROUP BY-capable function. PostgreSQL wants the object file and the exported symbol, and does take parameters: Interpreted (Python, Perl, JavaScript) Here the source lives in the database and a procedural-language handler runs it: Then use it like anything else — including from the ORM, with no SQL: source is code in that language: it is stored verbatim and never parsed as SQL. It is dollar-quoted, and the tag widens automatically if your source happens to contain it, so a body cannot terminate its own literal. Language | language | Where the code lives | --- | --- | --- | C / C++ / Rust (C ABI) | 'c' | compiled shared library | Python | 'plpython3u' | source | Perl | 'plperl', 'plperlu' | source | JavaScript | 'plv8' | source | R | 'plr' | source | The language handler has to be installed in the database first (CREATE EXTENSION plpython3u), and MySQL supports only the compiled form — asking it for plpython3u throws a message saying so rather than emitting SQL the server will reject. Drop one with dropExternalFunction(name, { params }); PostgreSQL identifies overloads by argument types, so pass the same params when several exist. Sequences Dialect | SQL | --- | --- | PostgreSQL | CREATE SEQUENCE IF NOT EXISTS "orderseq" START WITH 1000 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99999 CACHE 20 CYCLE | MariaDB | identical (MariaDB 10.3+) | Oracle | same without IF NOT EXISTS — Oracle has no such clause | SQL Server | same without CACHE | MySQL | throws — "MySQL has no sequences - use an AUTOINCREMENT column instead." | SQLite | throws — "SQLite has no sequences - use an INTEGER PRIMARY KEY AUTOINCREMENT column instead." | DROP SEQUENCE omits IF EXISTS on Oracle for the same reason. Triggers One declaration, five different shapes: PostgreSQL — a trigger calls a function, so two statements are emitted (the function is named <trigger>fn): MySQL / MariaDB — always row-level, no WHEN: SQLite — always row-level, WHEN without parentheses, idempotent: SQL Server — has no BEFORE, so BEFORE is translated to INSTEAD OF: Oracle — CREATE OR REPLACE, FOR EACH ROW, parenthesised WHEN. Dropping differs too: PostgreSQL needs the table (DROP TRIGGER … ON "users"), MySQL/SQLite do not, and SQL Server/Oracle have no IF EXISTS here. The decorator form is applied by sync(): prorm.createTrigger() / prorm.dropTrigger() are the connection-level equivalents. Stored procedures and functions Procedures are declared on plain classes — they are not models — so the connection has to be told about them: Calling and dropping them at runtime goes through the dialect: prorm.createAlgorithm() / dropAlgorithm() cover the same ground with a single call for procedures, functions and events. Row-level security PostgreSQL-family only: On MySQL it throws; on SQL Server it throws with the pointer that SQL Server uses CREATE SECURITY POLICY with a predicate function instead. Oracle's VPD and SQL Server's security policies are reachable through their own dialects. For an application-level equivalent that works everywhere, see the RowLevelSecurity and SessionIsolation modules in Compliance. Partitions (createPartition takes name; attachPartition / detachPartition take partitionName — the asymmetry is real, not a typo here.) bound covers all three schemes: { from, to } for RANGE, { values } for LIST, { modulus, remainder } for HASH. RANGE, LIST and HASH are supported, with the syntax each engine uses — declarative partitioning on PostgreSQL, PARTITION BY clauses on MySQL/MariaDB, and a base table plus child tables joined by a union view on SQLite. The same four calls exist on the QueryInterface. Schemas and databases Set schema on a model (or searchPath on a query) to target one. Extensions For a searchable catalogue of what extensions exist per engine, see Extensions. Related reading QueryInterface — the full imperative schema API Migrations — running these changes in order, versioned Indexes & constraints Decorators — @View, @Trigger, @Procedure Dialects — per-engine support notes