Indexes & constraints

const User = prorm.define(
  'User',
  {
    id:     { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
    orgId:  { type: DataTypes.INTEGER(), references: { model: 'orgs', key: 'id', onDelete: 'CASCADE', onUpdate: 'RESTRICT' } },
    email:  { type: DataTypes.STRING(100) },
    name:   { type: DataTypes.STRING(50) },
    status: { type: DataTypes.STRING(20) },
    age:    { type: DataTypes.INTEGER() },
  },
  {
    tableName: 'users',
    indexes: [
      { fields: ['name'] },
      { name: 'idx_status_age', fields: ['status', 'age'] },
      { name: 'uq_name', fields: ['name'], unique: true },
    ],
    constraints: [
      { type: ConstraintType.Check,  name: 'ck_age', check: { age: { gte: 0 } } },
      { type: ConstraintType.Unique, name: 'uq_org_email', fields: ['orgId', 'email'] },
    ],
  },
);

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

constraints: [
  { type: ConstraintType.Check, name: 'ck_dates',  check: { ends_at: { gt: col('starts_at') } } },
  { type: ConstraintType.Check, name: 'ck_status', check: { status: { in: ['draft', 'live'] } } },
  { type: ConstraintType.Check, name: 'ck_seen',   check: { seen_at: { lte: fn('NOW') } } },
]

indexes: [{ name: 'idx_active', fields: ['status'], where: { status: 'active' } }]

UnsupportedSchemaObjectError: Partial (filtered) index is not supported on mysql. mysql has
no WHERE clause on CREATE INDEX - index the whole table, or index a generated/expression column
that encodes the predicate.

Partial (filtered) index is not supported on mariadb. mariadb has no WHERE
clause on CREATE INDEX - index the whole table, or index a generated/expression
column that encodes the predicate.

// 1. attribute-level grouping — columns sharing a uniqueKey form one constraint
const byAttribute = {
  orgId: { type: DataTypes.INTEGER(), uniqueKey: 'uq_org_email' },
  email: { type: DataTypes.STRING(100), uniqueKey: 'uq_org_email' },
};

// 2. model-level
const byModel = { uniqueKeys: [{ name: 'uq_org_email', fields: ['orgId', 'email'] }] };

// 3. the constraints option
const byConstraint = {
  constraints: [{ type: 'UNIQUE', name: 'uq_org_email', fields: ['orgId', 'email'] }],
};

const attributes = { email: { type: DataTypes.STRING(255), allowNull: false, unique: true } };

const attributes = {
  orgId: {
    type: DataTypes.INTEGER(),
    references: { model: 'orgs', key: 'id', onDelete: 'CASCADE', onUpdate: 'RESTRICT' },
  },
};

await prorm.disableForeignKeyChecks();
// … load data in any order …
await prorm.enableForeignKeyChecks();

import { readFileSync } from 'node:fs';

const orgs = JSON.parse(readFileSync('./export/orgs.json', 'utf8'));
const users = JSON.parse(readFileSync('./export/users.json', 'utf8'));

await prorm.disableForeignKeyChecks();
try {
  // Children before parents is fine while the checks are off.
  await prorm.getQueryInterface().bulkInsert('users', users);
  await prorm.getQueryInterface().bulkInsert('orgs', orgs);
} finally {
  await prorm.enableForeignKeyChecks();
}

// SQLite: list every row whose parent is missing. Empty means the load is sound.
const orphans = await prorm.query('PRAGMA foreign_key_check');
if (orphans.length > 0) throw new Error(`${orphans.length} rows reference missing parents`);

Session-level foreign key checks are not supported on mssql.
Use ALTER TABLE <table> NOCHECK CONSTRAINT ALL …

await prorm.addIndex('users', 'idx_age', ['age']);
await prorm.removeIndex('users', 'idx_age');
await prorm.addColumn('users', 'nickname', { type: DataTypes.STRING(50) });
await prorm.changeColumn('users', 'nickname', { type: DataTypes.STRING(100) });
await prorm.removeColumn('users', 'nickname');
await prorm.renameTable('users', 'people');
await prorm.describeTable('users');

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

await qi.addIndex('docs', [], { name: 'idx_lower_body', expression: fn('LOWER', col('body')) });
// CREATE INDEX IF NOT EXISTS "idx_lower_body" ON "docs" ((LOWER("body")))

const dialect = prorm.getDialectInstance();
await dialect.createExpressionIndex('docs', 'idx_lower_body', 'LOWER(body)');   // the raw form

addConstraint type "CHECK" cannot be added to an existing SQLite table;
define it when the table is created (SQLite only supports UNIQUE via a unique
index post-hoc).

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

const q = (i: string) => `"${i}"`;
buildCreateFullTextIndexSQL(
  { name: 'ft_docs', table: 'docs', columns: ['title', 'body'] },
  'postgres', q, q,
);

prorm.define('Membership', {
  userId: { type: DataTypes.INTEGER() },
  teamId: { type: DataTypes.INTEGER() },
  role:   { type: DataTypes.STRING(20) },
}, { primaryKey: ['userId', 'teamId'] });