CRUD Operations

BigAl repositories provide create(), update(), and destroy() methods. create() and update() return affected records by default (using RETURNING *); destroy() does not. All three accept returnRecords and returnSelect to shape what comes back. Return only the columns you need, or skip the returned rows entirely, to reduce bytes over the wire and hydration cost.

Create

Single record

const product = await productRepository.create({
  name: 'Widget',
  priceCents: 999,
});
// product = { id: 42, name: 'Widget', priceCents: 999, createdAt: ... }

Multiple records

const products = await productRepository.create([
  { name: 'Widget', priceCents: 999 },
  { name: 'Gadget', priceCents: 1499 },
]);
// products = [{ id: 42, ... }, { id: 43, ... }]

Passing an array builds a single multi-row INSERT statement, one round trip for the whole batch. Prefer this over calling create() in a loop, which issues a separate INSERT (and round trip) per record:

// Correct - one INSERT statement for all rows
const products = await productRepository.create(items);

// Slower - a separate INSERT statement and round trip per item
const products = [];
for (const item of items) {
  products.push(await productRepository.create(item));
}

Skip returning records

await productRepository.create({ name: 'Widget', priceCents: 999 }, { returnRecords: false });

Query projection (returnSelect)

Return only specific columns. The primary key is always included.

const product = await productRepository.create({ name: 'Widget', priceCents: 999 }, { returnSelect: ['name'] });
// product = { id: 42, name: 'Widget' }

Pass an empty array to return only the primary key:

const product = await productRepository.create({ name: 'Widget', priceCents: 999 }, { returnSelect: [] });
// product = { id: 42 }

onConflict (Upsert)

Handle constraint violations with PostgreSQL's ON CONFLICT clause.

Ignore (DO NOTHING)

const product = await productRepository.create(
  { name: 'Widget', sku: 'WDG-001' },
  {
    onConflict: {
      action: 'ignore',
      targets: ['sku'],
    },
  },
);

Merge (DO UPDATE) - all columns

const product = await productRepository.create(
  { name: 'Widget', sku: 'WDG-001', priceCents: 999 },
  {
    onConflict: {
      action: 'merge',
      targets: ['sku'],
    },
  },
);

Merge - specific columns

const product = await productRepository.create(
  { name: 'Widget', sku: 'WDG-001', priceCents: 999 },
  {
    onConflict: {
      action: 'merge',
      targets: ['sku'],
      merge: ['name', 'priceCents'],
    },
  },
);

Update

update() takes a where clause object and a values object. Returns an array of affected records.

// Update a single record
const products = await productRepository.update({ id: 42 }, { name: 'Super Widget' });
// products = [{ id: 42, name: 'Super Widget', ... }]

// Update multiple records
const products = await productRepository.update({ id: [42, 43] }, { priceCents: 1299 });
// products = [{ id: 42, ... }, { id: 43, ... }]

update() always returns an array, regardless of how many records were affected.

Without returning records:

await productRepository.update({ id: 42 }, { name: 'Super Widget' }, { returnRecords: false });

With query projection:

const products = await productRepository.update({ id: [42, 43] }, { priceCents: 1299 }, { returnSelect: ['id'] });
// products = [{ id: 42 }, { id: 43 }]

Destroy

destroy() takes a where clause object. Unlike create() and update(), it does not return records by default. It emits a plain DELETE with no RETURNING clause and resolves to void, which is the cheapest option.

// Delete a single record (resolves to void)
await productRepository.destroy({ id: 42 });

// Delete multiple records
await productRepository.destroy({ id: [42, 43] });

To get the deleted rows back, opt in with returnRecords: true:

const products = await productRepository.destroy({ id: [42, 43] }, { returnRecords: true });
// products = [{ id: 42, ... }, { id: 43, ... }]

With query projection (implies returnRecords):

const products = await productRepository.destroy({ id: [42, 43] }, { returnSelect: ['name'] });
// products = [{ id: 42, name: 'Widget' }, { id: 43, name: 'Gadget' }]

The primary key is always included. Pass an empty array to return only the primary key.

Pool overrides

Every write method accepts pool. This is useful when a helper receives an externally managed transaction connection or a BigAl TransactionScope:

await productRepository.create({ name: 'Widget', store: storeId }, { pool: connection });

const products = await productRepository.update({ id: productIds }, { location: 'A-12' }, { pool: connection });

await productRepository.destroy({ id: obsoleteProductIds }, { pool: connection });

Pool-only options preserve each method's default return behavior: single and bulk creates return records, updates return an array, and destroys return void. Combine pool with returnSelect, returnRecords, and onConflict as usual.

The override only routes the statement; it does not begin, commit, roll back, or release a transaction. See Transactions.