Raw Queries & Query Building

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

const prorm = new Prorm({ dialect: 'sqlite', storage: ':memory:' });
await prorm.connect();

// SELECT returns an array of row objects
const rows = await prorm.query('SELECT id, email FROM users');
console.log(rows); // [{ id: 1, email: 'a@example.com' }, ...]

const active = await prorm.query(
  'SELECT * FROM users WHERE status = :status AND age >= :minAge',
  {
    replacements: { status: 'active', minAge: 18 },
  },
);

const top = await prorm.query('SELECT TOP (10) * FROM orders WHERE customer_id = @customerId', {
  replacements: { customerId: 42 },
});
// SELECT TOP (10) * FROM orders WHERE customer_id = 42

const found = await prorm.query('SELECT * FROM users WHERE last_name = $1 AND is_active = $2', {
  replacements: ["O'Brien", true],
});

const found = await prorm.query('SELECT * FROM users WHERE last_name = ? AND is_active = ?', {
  replacements: ["O'Brien", true],
});

const found = await prorm.query('SELECT * FROM users WHERE last_name = ? AND is_active = ?', {
  replacements: ["O'Brien", true],
});

const found = await prorm.query('SELECT * FROM users WHERE last_name = @p1 AND is_active = @p2', {
  replacements: ["O'Brien", true],
});

const orders = await prorm.query(
  'SELECT id, total FROM orders WHERE customer_id = :customerId AND status IN (:statuses)',
  { replacements: { customerId: 42, statuses: ['paid', 'shipped'] } },
);

const statuses = ['paid', 'shipped'];
const names = statuses.map((_, i) => `:s${i}`).join(', '); // ':s0, :s1'
const orders = await prorm.query(`SELECT id, total FROM orders WHERE status IN (${names})`, {
  replacements: Object.fromEntries(statuses.map((s, i) => [`s${i}`, s])),
});
// SELECT id, total FROM orders WHERE status IN ('paid', 'shipped')

const statements: string[] = [];
const rows = await prorm.query(
  'SELECT id, status, total FROM orders WHERE customer_id = :customerId AND status IN (:statuses) ORDER BY id',
  {
    replacements: { customerId: 42, statuses: ['paid', 'shipped'] },
    logging: (sql: string) => statements.push(sql),
  },
);
// rows:       [{ id: 1, status: 'paid', total: 120 }, { id: 2, status: 'shipped', total: 80 }]
// statements: ["SELECT id, status, total FROM orders WHERE customer_id = 42 AND status IN ('paid', 'shipped') ORDER BY id"]

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

const report = await prorm.query(
  `WITH recent AS (SELECT * FROM orders WHERE created_at > :since)
   SELECT customer_id, COUNT(*) AS n, SUM(total) AS revenue
   FROM recent GROUP BY customer_id ORDER BY customer_id`,
  {
    type: QueryTypes.SELECT,
    replacements: { since: '2026-01-01' },
  },
);
// [{ customer_id: 7, n: 1, revenue: 15 }, { customer_id: 42, n: 2, revenue: 200 }]

// UPDATE returns the affected count
const affected = await prorm.query(
  'UPDATE users SET status = :status WHERE last_seen < :cutoff',
  { type: QueryTypes.UPDATE, replacements: { status: 'dormant', cutoff: '2025-01-01' } },
);
console.log(`${affected} rows updated`);

// INSERT returns [rows, created]
const [inserted, created] = await prorm.query(
  'INSERT INTO tags (name) VALUES (:name)',
  { type: QueryTypes.INSERT, replacements: { name: 'urgent' } },
);

const User = prorm.define('User', { /* ... */ });

const users = await prorm.query('SELECT * FROM users WHERE status = :status', {
  replacements: { status: 'active' },
  model: User,
  mapToModel: true,
});
// users are User instances, not plain row objects

await prorm.query('SELECT * FROM big_table', {
  logging: (sql, timing) => console.log(`[${timing}ms] ${sql}`), // or `true` / `false`
  benchmark: true, // always log execution time
  retry: { max: 3, match: ['ECONNREFUSED', 'Timeout'] }, // retry on matching errors
  transaction: tx, // run inside an existing transaction
});

// As a column default: evaluate NOW() in the database
const Event = prorm.define('Event', {
  createdAt: { type: DATE, defaultValue: prorm.literal('NOW()') },
});

// In-place update expressions (increment without a read-modify-write)
await View.update(
  { count: prorm.literal('count + 1') },
  { where: { postId: 42 } },
);

// Computed attributes in a SELECT
const rows = await User.findAll({
  attributes: [[prorm.literal("first_name || ' ' || last_name"), 'fullName']],
});

// Raw condition in a where clause
const power = await User.findAll({
  where: prorm.literal('LENGTH(bio) > 500'),
});

await Product.update({ stock: literal('stock - 1') }, { where: { id: 1 } });

// COUNT(id) grouped by role, as a named attribute
const byRole = await User.findAll({
  attributes: ['role', [prorm.fn('COUNT', prorm.col('id')), 'count']],
  group: ['role'],
});

// col() produces quoted identifiers: col('User', 'name') -> "User"."name"
const upper = prorm.fn('UPPER', prorm.col('User.name'));

// cast() emits a type conversion
const asText = prorm.cast(prorm.col('price'), 'VARCHAR');

// where() pairs an expression with a value or operator object
const in2026 = await User.findAll({
  where: prorm.where(prorm.fn('YEAR', prorm.col('createdAt')), 2026),
});

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

Order.findAll({
  attributes: [
    'status',
    [fn('SUM', col('total')), 'revenue'],
    [cast(col('created_at'), 'DATE'), 'day'],
  ],
});

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

const revenue = await Order.findAll({
  attributes: ['status', [fn('SUM', col('total')), 'revenue']],
  where: { status: 'paid' },
  group: ['status'],
});

import { createQuickQuery } from '../query-builders';

const q = createQuickQuery();

q.select('users', { status: 'active' });          // { sql, values } for a SELECT
q.insert('users', { email: 'a@example.com' });     // compiled INSERT
q.update('users', { status: 'dormant' }, { id: 7 }); // compiled UPDATE
q.delete('users', { id: 7 });                       // compiled DELETE
q.where({ age: { $gte: 18 } });                     // just the WHERE clause string
q.order([['createdAt', 'DESC']]);                   // just the ORDER BY clause
q.limitOffset(20, 40);                              // LIMIT / OFFSET clause