Views, triggers & other schema objects

import { UnsupportedSchemaObjectError } from 'ts-prorm-orm';

try {
  await qi.createSequence('order_seq', { start: 1000 });
} catch (e) {
  if (e instanceof UnsupportedSchemaObjectError) {
    // "CREATE SEQUENCE is not supported on mysql. MySQL has no sequences -
    //  use an AUTO_INCREMENT column instead."
  }
}

const qi = prorm.getQueryInterface();

await qi.createView('customer_info', {
  from: Customer,
  attributes: ['firstName', 'lastName', 'email'],
  where: { country: 'USA' },
});

await qi.showViews();
await qi.viewExists('customer_info');
await qi.dropView('customer_info');

await Customer.createView('customer_info', {
  attributes: ['firstName', 'lastName', 'email'],
  where: { country: 'USA' },
});

await Customer.createView('all_customers');                       // SELECT *
await Customer.createMaterializedView('customer_totals', { attributes: ['id', 'total'] }); // same shape
await Customer.dropView('customer_info');

await Customer.viewExists('customer_info');                       // boolean
await Customer.refreshMaterializedView('customer_totals', { concurrently: true });
await Customer.materializedViewExists('customer_totals');
await Customer.dropMaterializedView('customer_totals');

await qi.createView('irish_customers', { from: Customer, where: { name: "O'Brien" } });
// SELECT * FROM "customers" WHERE "name" = 'O''Brien'

import { fn, col } from 'ts-prorm-orm';

await qi.createView('customers_per_country', {
  from: Customer,
  attributes: ['country', [fn('COUNT', col('id')), 'customerCount']],
  group: 'country',
});
// SELECT "country", COUNT("id") AS "customerCount" FROM "customers" GROUP BY "country"

import { View } from 'ts-prorm-orm';

@View({
  name: 'active_users',
  definition: { from: 'users', attributes: ['id', 'name'], where: { status: 'active' } },
  replace: true,        // CREATE OR REPLACE VIEW
  checkOption: 'LOCAL', // PostgreSQL
})
class ActiveUsers {}

prorm.addModel(ActiveUsers);
await prorm.sync();     // the view is created here

// Deprecated — still works, but prefer the object form above.
await qi.createView('active_users', "SELECT * FROM users WHERE status = 'active'");

await prorm.createMaterializedView({
  name: 'sales_by_month',
  definition: {
    from: Sale,
    attributes: ['month', [fn('SUM', col('total')), 'total']],
    group: 'month',
  },
});

await prorm.refreshMaterializedView('sales_by_month', { concurrently: true });
await prorm.hasMaterializedView('sales_by_month');
await prorm.showMaterializedViews();
await prorm.dropMaterializedView('sales_by_month', { cascade: true });

import { ConstraintType, ReferentialAction } from 'ts-prorm-orm';

await Order.addConstraint('order_user_fk', {
  type: ConstraintType.ForeignKey,
  fields: ['userId'],
  references: { table: 'users', fields: ['id'] },
  onDelete: ReferentialAction.Cascade,
});

await qi.addColumn('Articles', 'slug', { type: DataTypes.STRING });  // by table
await Article.addColumn('slug', { type: DataTypes.STRING });         // by model

await Article.createTrigger({ name: 'audit', timing: 'AFTER', event: 'INSERT', body: '…' });
await Article.createPolicy({ name: 'own_rows', using: { authorId: 42 } });
await Article.addIndex(['title'], { name: 'article_title_idx' });

await qi.addConstraint('users', 'users_age_check', {
  type: 'CHECK',
  check: { age: { gte: 18 } },
});
// ALTER TABLE "users" ADD CONSTRAINT "users_age_check" CHECK ("age" >= 18)

await qi.enableRowLevelSecurity('documents');

await qi.createPolicy({
  name: 'own_documents',
  table: 'documents',
  for: 'SELECT',
  using: { ownerId: literalExpr('current_setting(\'app.user_id\')::int') },
});

import { fn, col } from 'ts-prorm-orm';

await qi.addIndex('users', ['email'], {
  name: 'users_email_lower_idx',
  expression: fn('LOWER', col('email')),   // LOWER("email")
});

import { TriggerTiming, TriggerEvent, TriggerForEach, NEW, OLD, fn } from 'ts-prorm-orm';

await Article.createTrigger({
  name: 'article_touch',
  timing: TriggerTiming.Before,
  event: TriggerEvent.Update,
  forEach: TriggerForEach.Row,
  body: { set: { updatedAt: fn('CURRENT_TIMESTAMP') } },
});

await Article.createTrigger({
  name: 'article_audit',
  timing: TriggerTiming.After,
  event: TriggerEvent.Update,
  body: [
    { insertInto: AuditLog, values: { articleId: NEW('id'), was: OLD('title') } },
  ],
});

when: { status: { ne: OLD('status') } }     // WHEN NEW."status" <> OLD."status"

await Article.createTrigger({
  name: 'status_changed',
  timing: TriggerTiming.After,
  event: TriggerEvent.Update,
  updateOf: ['status', 'title'],              // UPDATE OF "status", "title"
  body: '…',
});

@Trigger({
  name: 'article_touch',
  timing: TriggerTiming.Before,
  event: TriggerEvent.Update,
  body: { set: { updatedAt: fn('CURRENT_TIMESTAMP') } },
})
class Article extends Model {}

class TextFunctions {
  @SqlFunction({
    name: 'slugify',
    returns: 'text',
    language: RoutineLanguage.PlPgSql,
    volatility: Volatility.Immutable,     // IMMUTABLE; Volatility.Deterministic on MySQL
    params: [{ name: 'value', type: 'text', mode: ParamMode.In }],
    body: '…',
  })
  slugify() {}
}

await qi.createExtension('uuid-ossp');
await qi.createExtension('postgis', { cascade: true });

await qi.createEnum('order_status', ['pending', 'shipped', 'delivered']);
await qi.addEnumValue('order_status', 'refunded', { after: 'delivered' });
await qi.renameEnumValue('order_status', 'shipped', 'dispatched');

await qi.createDomain('email', {
  as: 'text',
  notNull: true,
  check: "VALUE ~ '@'",          // VALUE stands for the value being checked
  constraintName: 'email_shape',
});

await Article.comment('Published posts, one row per revision');
await Article.commentOnColumn('slug', 'URL segment; unique per locale');
await Article.comment(null);      // remove it

await qi.setSequenceValue('users_id_seq', 10_000);
await qi.restartSequence('users_id_seq', 1);
const next = await qi.nextSequenceValue('users_id_seq');

await Article.analyze();            // refresh the planner's statistics
await Article.vacuum({ full: true });
await Article.reindex();
await Article.checkTable();

await Article.vacuum({ full: true, analyze: true });   // VACUUM (FULL, ANALYZE)
await qi.vacuum(undefined, { into: '/backups/snap.db' }); // SQLite: a consistent copy

await qi.createExternalFunction({
  name: 'levenshtein',
  language: 'c',
  returns: 'INTEGER',
  library: 'my_udf.so',
});
// CREATE FUNCTION `levenshtein` RETURNS INTEGER SONAME 'my_udf.so'

await qi.createExternalFunction({
  name: 'levenshtein',
  language: 'c',
  returns: 'integer',
  params: [{ name: 'a', type: 'text' }, { name: 'b', type: 'text' }],
  library: '$libdir/my_udf',
  symbol: 'pg_levenshtein',
  strict: true,            // RETURNS NULL ON NULL INPUT
  volatility: 'IMMUTABLE', // required to index a call to it
});

await qi.createExternalFunction({
  name: 'slugify',
  language: 'plpython3u',
  returns: 'text',
  params: [{ name: 'value', type: 'text' }],
  source: `
import re
return re.sub(r"[^a-z0-9]+", "-", value.lower()).strip("-")
  `,
  replace: true,
});

await Article.findAll({ attributes: ['title', [fn('slugify', col('title')), 'slug']] });

await qi.createSequence('order_seq', {
  start: 1000, increment: 1, minValue: 1, maxValue: 99999,
  cache: 20, cycle: true, ifNotExists: true,
});
await qi.dropSequence('order_seq', { ifExists: true });

await qi.createExternalFunction({
  name: 'slugify',
  language: 'plpython3u',
  returns: 'text',
  params: [{ name: 'value', type: 'text' }],
  file: './db/functions/slugify.py',
});

class Reports {
  @Procedure({ name: 'rebuild_totals' })
  @FromFile('./db/procedures/rebuild_totals.sql')
  rebuildTotals!: () => Promise<void>;
}

await qi.createTrigger({
  name: 'stamp_updated',
  table: 'users',
  timing: 'BEFORE',
  event: 'UPDATE',
  forEach: 'ROW',
  when: 'OLD.email <> NEW.email',
  body: 'NEW.updated_at = NOW();',
});

await qi.dropTrigger('stamp_updated', 'users');

UnsupportedSchemaObjectError: a BEFORE trigger is not supported on mssql.
SQL Server has only AFTER and INSTEAD OF triggers. Use AFTER, or INSTEAD OF
with a body that performs the write itself.

@Trigger({
  name: 'stamp_updated',
  timing: 'BEFORE',
  event: 'UPDATE',
  forEach: 'ROW',
  body: 'NEW.updated_at = NOW();',
})
@Table({ tableName: 'users' })
class User extends Model {}

import { Procedure, SqlFunction } from 'ts-prorm-orm';

class Reports {
  @Procedure({
    name: 'archive_orders',
    params: [{ name: 'cutoff', type: 'DATE', mode: 'IN' }],
    body: 'DELETE FROM orders WHERE created_at < cutoff;',
    dialect: 'postgres',
    replace: true,
  })
  archiveOrders() {}

  @SqlFunction({ name: 'order_total', returns: 'DECIMAL(10,2)', body: '…' })
  orderTotal() {}
}

prorm.registerProcedures(Reports);
await prorm.sync();   // procedures and functions are created here

const dialect = prorm.getDialectInstance();
await dialect.executeStoredProcedure({ name: 'archive_orders', params: [cutoff] });
await dialect.hasStoredProcedure('archive_orders');
await dialect.dropStoredProcedure('archive_orders', { ifExists: true });

await qi.enableRowLevelSecurity('docs');
await qi.createPolicy({
  table: 'docs',
  name: 'tenant_isolation',
  for: 'ALL',
  to: ['app_user'],
  using: { tenant_id: literalExpr("current_setting('app.tenant')::int") },
  withCheck: { tenant_id: literalExpr("current_setting('app.tenant')::int") },
});
await qi.dropPolicy('tenant_isolation', 'docs');
await qi.disableRowLevelSecurity('docs');

await prorm.createPartitionedTable('events', columns, {
  partitionBy: { type: 'range', column: 'created_at' },
  partitions: [
    { name: 'events_2026_q1', bound: { from: '2026-01-01', to: '2026-04-01' } },
  ],
});

await prorm.createPartition({
  parentTable: 'events',
  name: 'events_2026_q2',                                  // note: `name` here…
  bound: { from: '2026-04-01', to: '2026-07-01' },
});

await prorm.attachPartition({ parentTable: 'events', partitionName: 'events_2026_q2' });
await prorm.detachPartition({ partitionName: 'events_2026_q2', validate: true });
await prorm.dropPartition('events_2026_q2');

await prorm.createSchema('reporting');
await prorm.showSchemas();
await prorm.dropSchema('reporting', { cascade: true });

await prorm.createDatabase({ name: 'analytics', encoding: 'UTF8', template: 'template0' });
await prorm.dropDatabase('analytics');

await prorm.createExtension('postgis', { ifNotExists: true, schema: 'public' });
await prorm.hasExtension('postgis');
await prorm.getExtensions();
await prorm.dropExtension('postgis', { cascade: true });

import { Model, Table, Column, PrimaryKey, AutoIncrement, DataTypes } from 'ts-prorm-orm';

@Table({ tableName: 'users', timestamps: false })
export class User extends Model {
  @PrimaryKey @AutoIncrement @Column(DataTypes.INTEGER()) declare id: number;
  @Column(DataTypes.STRING()) declare email: string;
  @Column(DataTypes.STRING()) declare status: string;
  @Column(DataTypes.INTEGER()) declare tenantId: number;
}

@Table({ tableName: 'orders', timestamps: false })
export class Order extends Model {
  @PrimaryKey @AutoIncrement @Column(DataTypes.INTEGER()) declare id: number;
  @Column(DataTypes.INTEGER()) declare userId: number;
  @Column(DataTypes.STRING()) declare status: string;
  @Column(DataTypes.DECIMAL(12, 2)) declare total: number;
  @Column(DataTypes.DATE()) declare updatedAt: Date;
}

@Table({ tableName: 'order_lines', timestamps: false })
export class OrderLine extends Model {
  @PrimaryKey @AutoIncrement @Column(DataTypes.INTEGER()) declare id: number;
  @Column(DataTypes.INTEGER()) declare orderId: number;
  @Column(DataTypes.INTEGER()) declare quantity: number;
  @Column(DataTypes.DECIMAL(12, 2)) declare unitPrice: number;
}

@Table({ tableName: 'order_events', timestamps: false })
export class OrderEvent extends Model {
  @PrimaryKey @AutoIncrement @Column(DataTypes.INTEGER()) declare id: number;
  @Column(DataTypes.INTEGER()) declare orderId: number;
  @Column(DataTypes.STRING()) declare op: string;
  @Column(DataTypes.JSON()) declare payload: unknown;
}

import { Sql } from 'ts-prorm-orm';
const { from } = Sql;

const bigOrders = from(Order, 'o')
  .join(User, 'u', ({ o, u }) => o.userId.eq(u.id))
  .where(({ o }) => o.total.gte(100))
  .orderBy(({ o }) => o.total.desc())
  .limit(10)
  .select(({ o, u }) => ({ orderId: o.id, email: u.email, total: o.total }));

const rows = await prorm.select(bigOrders); // or: await bigOrders.all(prorm)
console.log(bigOrders.toSQL('postgres'));

import { ReadonlyModel, View, Sql } from 'ts-prorm-orm';
const { fn } = Sql;

@View(
  (q) =>
    q
      .from(User, 'u')
      .leftJoin(Order, 'o', ({ u, o }) => o.userId.eq(u.id))
      .where(({ u }) => u.status.eq('active'))
      .groupBy(({ u }) => [u.id, u.email])
      .select(({ u, o }) => ({
        userId: u.id,
        email: u.email,
        orderCount: fn.count(o.id),
        lifetimeValue: fn.coalesce(fn.sum(o.total), 0),
      })),
  { name: 'customer_summary', description: 'One row per active customer.' },
)
export class CustomerSummary extends ReadonlyModel<CustomerSummary> {
  declare userId: number;
  declare email: string;
  declare orderCount: number;
  declare lifetimeValue: number;
}

const ActiveUser = User.asView('active_users', (q) => q.where(({ u }) => u.status.eq('active')));

import { ReadonlyModel, MaterializedView, Sql } from 'ts-prorm-orm';
const { fn, Refresh } = Sql;

@MaterializedView(
  (q) =>
    q
      .from(Order, 'o')
      .where(({ o }) => o.status.eq('paid'))
      .groupBy(({ o }) => [o.userId])
      .select(({ o }) => ({ userId: o.userId, revenue: fn.sum(o.total) })),
  {
    name: 'user_revenue',
    refresh: Refresh.every('15m'),
    indexes: [{ unique: true, fields: ['userId'] }],
  },
)
export class UserRevenue extends ReadonlyModel<UserRevenue> {
  declare userId: number;
  declare revenue: number;
}

import { Sql } from 'ts-prorm-orm';
const { sqlFunction, sqlProcedure, out, t } = Sql;

export class Pricing {
  /** An expression function: the body is one expression. */
  static lineTotal = sqlFunction(
    {
      name: 'line_total',
      params: { quantity: t.int(), unitPrice: t.decimal(12, 2) },
      returns: t.decimal(12, 2),
      deterministic: true,
    },
    ({ quantity, unitPrice }) => unitPrice.mul(quantity),
  );

  /** A function with statements: a variable and two branches. */
  static shippingFee = sqlFunction(
    {
      name: 'shipping_fee',
      params: { subtotal: t.decimal(12, 2), express: t.boolean() },
      returns: t.decimal(12, 2),
      deterministic: true,
    },
    ({ subtotal, express }, $) => {
      const fee = $.declare('fee', t.decimal(12, 2), 7.5);
      return [
        $.if(subtotal.gte(100)).then(fee.set(0)),
        $.if(express).then(fee.set(fee.add(15))),
        $.return(fee),
      ];
    },
  );

  /** A procedure with an OUT parameter. */
  static cancelStaleOrders = sqlProcedure(
    { name: 'cancel_stale_orders', params: { before: t.timestamp(), cancelled: out(t.int()) } },
    ({ before, cancelled }, $) => [
      $.update(Order, 'o')
        .set(() => ({ status: 'cancelled' }))
        .where(({ o }) => o.status.eq('pending').and(o.updatedAt.lt(before))),
      cancelled.set($.rowCount()),
    ],
  );
}

prorm.use(Pricing);
await prorm.sync(); // creates line_total, shipping_fee and cancel_stale_orders

// Typed calls: argument names and types come from `params`.
const fee = await Pricing.shippingFee({ subtotal: 120, express: true }); // '15.00'
const { cancelled } = await Pricing.cancelStaleOrders({ before: new Date('2026-09-01') });

// Or bound to a specific instance:
const pricing = prorm.routines(Pricing);
await pricing.lineTotal({ quantity: 3, unitPrice: 19.99 });

error OBJECT_UNSUPPORTED: sqlite has no stored functions beyond single-expression functions
hint: Run it in the app (fallback: "app" on the routine, or
      new Prorm({ sqlObjects: { fallback: { sqlite: 'app' } } })), or skip it on this engine.

import { Sql } from 'ts-prorm-orm';
const { defineTrigger, fn } = Sql;

/** Stamp updatedAt whenever the status changes. */
export const touchOrder = defineTrigger(
  Order,
  {
    name: 'orders_touch',
    timing: 'before',
    events: ['update'],
    when: ({ new: n, old: o }) => n.status.ne(o.status),
  },
  ({ new: n }) => [n.set({ updatedAt: fn.now() })],
);

/** Append every insert and update to order_events as JSON. */
export const auditOrder = defineTrigger(
  Order,
  { name: 'orders_audit', timing: 'after', events: ['insert', 'update'] },
  ({ new: n }, $) => [
    $.insert(OrderEvent).values({ op: $.op(), orderId: n.id, payload: $.rowToJson('new') }),
  ],
);

/** Reject negative totals with a coded error. */
export const guardOrder = defineTrigger(
  Order,
  { name: 'orders_guard', timing: 'before', events: ['insert', 'update'] },
  ({ new: n }, $) => [
    $.assert(n.total.gte(0), 'total must not be negative', { code: 'TOTAL_NEGATIVE' }),
  ],
);

for (const trigger of [touchOrder, auditOrder, guardOrder]) {
  prorm.sqlObjects.addObject(trigger.def);
}

import { Sql } from 'ts-prorm-orm';
const { defineSequence, defineEnum, defineDomain, definePolicy, sessionSetting, objectsPlugin, t } = Sql;

export const OrderNo = defineSequence({ name: 'order_no', start: 1000, cache: 20 });
export const OrderStatus = defineEnum('order_status', ['pending', 'paid', 'shipped', 'cancelled'] as const);
export const Email = defineDomain('email', t.varchar(255), (v) => v.like('%@%'), { notNull: true });
export const TenantOnly = definePolicy(User, {
  name: 'tenant_isolation',
  using: (u) => u.tenantId.eq(sessionSetting('app.tenant_id', t.int())),
});

prorm.use(
  objectsPlugin(
    OrderNo,
    OrderStatus,
    OrderStatus.applyTo(Order, 'status'),
    Email,
    Email.applyTo(User, 'email'),
    TenantOnly,
  ),
);

// A sequence is also a typed expression and a runtime call:
const next = await OrderNo.next(prorm); // SELECT nextval('order_no') on PostgreSQL
OrderStatus.is('paid'); // true — a type guard for the values

import { Prorm, Sql } from 'ts-prorm-orm';
const { sqlFunction, t } = Sql;

class Pricing {
  static shippingFee = sqlFunction(
    {
      name: 'shipping_fee',
      params: { subtotal: t.decimal(12, 2), express: t.boolean() },
      returns: t.decimal(12, 2),
      deterministic: true,
      fallback: 'app', // this routine only
    },
    ({ subtotal, express }, $) => {
      const fee = $.declare('fee', t.decimal(12, 2), 7.5);
      return [
        $.if(subtotal.gte(100)).then(fee.set(0)),
        $.if(express).then(fee.set(fee.add(15))),
        $.return(fee),
      ];
    },
  );
}

// …or every routine on an engine:
const prorm = new Prorm({
  dialect: 'sqlite',
  storage: ':memory:',
  sqlObjects: { fallback: { sqlite: 'app' } },
});
await prorm.connect();
prorm.use(Pricing);
await prorm.sync();

await Pricing.shippingFee({ subtotal: 40, express: false }); // '7.50'
await Pricing.shippingFee({ subtotal: 120, express: true }); // '15.00'

import {
  Model, Table, Column, PrimaryKey, AutoIncrement, DataTypes,
  Description, Unit, Between, Immutable, Sensitive, Deprecated,
  describeModel, toJsonSchema, redactSensitive, Sql,
} from 'ts-prorm-orm';

@Description('A customer invoice.')
@Table({ tableName: 'invoices', timestamps: false })
export class Invoice extends Model {
  @PrimaryKey @AutoIncrement @Column(DataTypes.INTEGER()) declare id: number;

  @Description('Invoice total in cents.')
  @Unit('cents')
  @Between(0, 10_000_000)
  @Column(DataTypes.INTEGER())
  declare totalCents: number;

  @Immutable @Column(DataTypes.STRING()) declare reference: string;

  @Sensitive({ category: 'PII', mask: 'email' })
  @Column(DataTypes.STRING())
  declare customerEmail: string;

  @Deprecated({ since: '4.2', use: 'totalCents' })
  @Column(DataTypes.DECIMAL(12, 2))
  declare total: number;
}

prorm.use(Sql.describePlugin()); // comments, CHECKs and guard triggers in sync()

describeModel(Invoice).attributes[1].comment; // 'Invoice total in cents. [unit: cents]'
toJsonSchema(Invoice); // totalCents: { minimum: 0, maximum: 10000000, 'x-unit': 'cents' }
redactSensitive(Invoice, { id: 1, customerEmail: 'ada@example.com' });
// → { id: 1, customerEmail: 'a***a@example.com' }

const plan = prorm.sqlObjects.plan('postgres');
for (const sql of plan.statements()) console.log(`${sql};`);
for (const d of plan.diagnostics) console.log(d.severity, d.code, d.object, d.message);

const problems = prorm.sqlObjects
  .check(['postgres', 'mysql', 'mssql'])
  .filter((d) => d.severity === 'error');
if (problems.length) throw new Error(problems.map((d) => `${d.code} ${d.object}`).join('\n'));

await prorm.sync({ objects: { dryRun: true } }); // report only
await prorm.sync({ objects: { prune: true } }); // apply; drop objects no longer defined