BigAl vs Raw SQL

When to use BigAl

BigAl is a good fit for standard CRUD operations and queries that map naturally to its fluent API:

• Simple to moderately complex WHERE clauses
• Joins on defined relationships
• Pagination, sorting, and counting
• Subqueries with aggregates
• DISTINCT ON queries
• Upserts with ON CONFLICT
• Managed multi-repository transactions
• Explicit FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE row locks

When to use raw SQL

Drop to raw SQL (via your pool directly) when:

• You need CTEs (WITH clauses)
• Window functions beyond what DISTINCT ON provides
• Complex recursive queries
• Table-level (LOCK TABLE), advisory, or joined-table row locks
• Database-specific features BigAl does not wrap

Translation reference

Basic queries

SQL	BigAl
SELECT * FROM products WHERE id = 1	productRepo.findOne().where({ id: 1 })
SELECT name FROM products WHERE id = 1	productRepo.findOne({ select: ['name'] }).where({ id: 1 })
SELECT * FROM products WHERE name ILIKE '%widget%'	productRepo.find().where({ name: { contains: 'widget' } })
SELECT * FROM products WHERE price >= 100	productRepo.find().where({ price: { '>=': 100 } })
SELECT * FROM products WHERE status IN ('a','b')	productRepo.find().where({ status: ['a', 'b'] })
SELECT * FROM products WHERE status <> 'x'	productRepo.find().where({ status: { '!': 'x' } })
SELECT * FROM products WHERE deleted_at IS NOT NULL	productRepo.find().where({ deletedAt: { '!': null } })
SELECT * FROM products ORDER BY name LIMIT 10	productRepo.find().where({}).sort('name asc').limit(10)
SELECT COUNT(*) FROM products WHERE active = true	productRepo.count().where({ active: true })

CRUD

SQL	BigAl
INSERT INTO products (name) VALUES ('Widget') RETURNING *	productRepo.create({ name: 'Widget' })
UPDATE products SET name = 'X' WHERE id = 1 RETURNING *	productRepo.update({ id: 1 }, { name: 'X' })
DELETE FROM products WHERE id = 1 RETURNING *	productRepo.destroy({ id: 1 })

Subqueries, joins, and advanced

SQL	BigAl
WHERE store_id IN (SELECT id FROM stores WHERE active)	.where({ store: { in: subquery(storeRepo).select(['id']).where({ active: true }) } })
INNER JOIN stores s ON p.store_id = s.id WHERE s.name = 'Acme'	.join('store').where({ store: { name: 'Acme' } })
SELECT DISTINCT ON (store_id) * ... ORDER BY store_id, created_at DESC	.distinctOn(['store']).sort('store').sort('createdAt desc')
ON CONFLICT (sku) DO NOTHING	{ onConflict: { action: 'ignore', targets: ['sku'] } }
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name	{ onConflict: { action: 'merge', targets: ['sku'], merge: ['name'] } }

Mixing BigAl and raw SQL

BigAl does not lock you in. Use the same pool for raw queries:

const { rows } = await pool.query('SELECT * FROM products WHERE tsv @@ plainto_tsquery($1)', ['search term']);

Inside a managed transaction, use the scope so raw SQL runs on the same checked-out connection:

await transaction({ pool, repositories }, async (transactionScope) => {
  await transactionScope.query('SELECT pg_advisory_xact_lock($1::bigint)', [resourceKey]);
  return transactionScope.repositories.Product.update({ id: productId }, { name: 'Renamed' });
});

Use BigAl for the 90% of queries that are straightforward, and raw SQL for the rest.