SQL features

import {
  buildIdentitySQL,
  buildGeneratedAlwaysSQL,
  buildExclusionSQL,
  generateReplicaIdentitySQL,
} from 'ts-prorm-orm';

export async function up(prorm: { query(sql: string): Promise<unknown> }) {
  await prorm.query('CREATE EXTENSION IF NOT EXISTS btree_gist'); // `room WITH =` in a GiST index
  await prorm.query(
    [
      'CREATE TABLE bookings (',
      `  id bigint ${buildIdentitySQL({ type: 'ALWAYS' })} PRIMARY KEY,`,
      '  room int NOT NULL,',
      '  during tstzrange NOT NULL,',
      '  nightly_rate numeric(10,2) NOT NULL,',
      '  nights int NOT NULL,',
      `  total numeric(12,2) ${buildGeneratedAlwaysSQL({ as: 'nightly_rate * nights', stored: true })},`,
      `  ${buildExclusionSQL('bookings', {
        name: 'no_overlap',
        using: 'gist',
        elements: ['room WITH =', 'during WITH &&'],
      })}`,
      ')',
    ].join('\n'),
  );
  await prorm.query(`ALTER TABLE bookings ${generateReplicaIdentitySQL({ type: 'FULL' })}`);
}

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

buildGeneratedAlwaysSQL({ as: 'price * qty', stored: true });
// GENERATED ALWAYS AS (price * qty) STORED

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

const dialect = prorm.getDialectInstance()!;
// `{ ...fn() }`: see the note below on the `as` type
buildGeneratedAlwaysSQL({ as: { ...fn('LOWER', col('email')) }, stored: true }, dialect);

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

buildIdentitySQL({ type: 'ALWAYS', start: 1, increment: 1 });
// GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 )

buildIdentitySQL({ type: 'BY DEFAULT', start: 1000 });
// GENERATED BY DEFAULT AS IDENTITY ( START WITH 1000 )

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

generateColumnCollateSQL('name', 'en_US.utf8');
// MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE en_US.utf8

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

buildExclusionSQL('bookings', {
  name: 'no_overlap',
  using: 'gist',
  elements: ['room WITH =', 'during WITH &&'],
});
// CONSTRAINT "no_overlap" EXCLUDE USING gist (room WITH =, during WITH &&)

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

buildDeferrableSQL({ deferrable: 'INITIALLY DEFERRED' });
// DEFERRABLE INITIALLY DEFERRED

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

generateIndexHintSQL({ useIndex: ['idx_created'], forceIndex: ['idx_status'] }, 'mysql');
// USE INDEX (idx_created) FORCE INDEX (idx_status)

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

generateSpatialIndexSQL('places', 'location', 'places_geo', prorm.getDialect());

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

generateClusteredSQL({ type: 'clustered', fillFactor: 90 });
// CLUSTERED (FILLFACTOR = 90)

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

generateStatisticsSQL({ for: 'columns', columns: ['status', 'country'], target: 500 }, 'orders');
// ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500, ALTER COLUMN country SET STATISTICS 500

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

generateDistinctOnSQL(['user_id']);
// DISTINCT ON (user_id)

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

const latest = await prorm.query(
  `SELECT ${generateDistinctOnSQL(['user_id'])} * FROM orders ORDER BY user_id, created_at DESC`,
);
// SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC

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

generateTablesampleSQL({ method: 'BERNOULLI', percentage: 10 });
// TABLESAMPLE BERNOULLI(10)

generateTablesampleSQL({ method: 'SYSTEM', percentage: 1, repeatable: 42 });
// TABLESAMPLE SYSTEM(1) REPEATABLE(42)

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

generateCursorSQL({ scroll: true, holdable: 'HOLD' }, 'c1');
// DECLARE c1 CURSOR SCROLL WITH HOLD FOR

import { generateJSONExtractSQL, generateJSONSetSQL, generateJSONRemoveSQL } from 'ts-prorm-orm';

generateJSONExtractSQL('data', '$.address.city');
// JSON_EXTRACT(`data`, '$.address.city')

generateJSONExtractSQL('data', '$.address.city', true);
// JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.address.city'))

generateJSONSetSQL('data', '$.address.city', "'Leeds'");
// JSON_SET(`data`, '$.address.city', 'Leeds')

generateJSONRemoveSQL('data', '$.address.zip');
// JSON_REMOVE(`data`, '$.address.zip')

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

generateInheritsSQL({ parentTable: 'events' });
// INHERITS (events)

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

generateTablespaceSQL({ name: 'fast_ssd' }, prorm.getDialect());

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

generateCompressionSQL({ algorithm: 'lz4' });
// COMPRESSION lz4 (COMPRESS_LEVEL 6)

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

generateReplicaIdentitySQL({ type: 'FULL' });
// REPLICA IDENTITY FULL

generateReplicaIdentitySQL({ type: 'INDEX', index: 'orders_pkey' });
// REPLICA IDENTITY INDEX orders_pkey

import { generateStatementTimeoutSQL, generateIdleInTransactionTimeoutSQL } from 'ts-prorm-orm';

generateStatementTimeoutSQL(5000);
// SET session statement_timeout = '5000ms'

generateIdleInTransactionTimeoutSQL(60000);
// SET session idle_in_transaction_session_timeout = '60000ms'