BigAl

A fast, lightweight ORM for PostgreSQL and Node.js, written in TypeScript.

This ORM does not:

• Create or update db schemas for you
• Do much else than basic queries, inserts, updates, and deletes

Compatibility

• PostgreSQL 14 or above. Lower versions should work.

Install

npm install bigal

You'll also need a PostgreSQL driver. Choose one of the following:

# Option 1: postgres-pool (recommended)
npm install postgres-pool

# Option 2: node-postgres
npm install pg

# Option 3: Neon serverless
npm install @neondatabase/serverless

Using Alternative PostgreSQL Drivers

BigAl is compatible with any PostgreSQL driver that implements the standard pool.query() method, including:

• postgres-pool (default)
• pg (node-postgres)
• @neondatabase/serverless

Using with Neon Serverless

import { Pool } from '@neondatabase/serverless';
import { initialize, Repository } from 'bigal';
import { Product, Store } from './models';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const repositoriesByName = initialize({
  models: [Product, Store],
  pool,
});

Using with node-postgres (pg)

import { Pool } from 'pg';
import { initialize, Repository } from 'bigal';
import { Product, Store } from './models';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const repositoriesByName = initialize({
  models: [Product, Store],
  pool,
});

Type Requirements

Any pool implementation must satisfy the PoolLike interface:

import type { PoolLike, PoolQueryResult } from 'bigal';

interface PoolQueryResult<TRow extends Record<string, unknown>> {
  rows: TRow[];
  rowCount: number | null;
}

interface PoolLike {
  query<TRow extends Record<string, unknown> = Record<string, unknown>>(text: string, values?: readonly unknown[]): Promise<PoolQueryResult<TRow>>;
}

All standard PostgreSQL drivers (pg, postgres-pool, @neondatabase/serverless) satisfy this interface.

Configuring

Defining database models

Model definitions need to extend Entity.

import { column, primaryColumn, table, Entity } from 'bigal';
import { Store } from './Store';
import { Category } from './Category';
import { ProductCategory } from './ProductCategory';

@table({
  name: 'products',
})
export class Product extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({
    type: 'string',
    required: true,
  })
  public name!: string;

  @column({
    type: 'string',
  })
  public sku?: string;

  @column({
    type: 'string[]',
    defaultsTo: [],
    name: 'alias_names',
  })
  public aliases?: string[];

  @column({
    model: () => Store.name,
    name: 'store_id',
  })
  public store!: number | Store;

  @column({
    collection: () => Category.name,
    through: () => ProductCategory.name,
    via: 'product',
  })
  public categories?: Category[];
}

For detailed information about defining relationships and understanding the QueryResult type system, see the Relationships Guide.

Initialize repositories

import {
  initialize,
  Repository,
} from 'bigal';
import { Pool } from 'postgres-pool';
import {
  Category,
  Product,
  ProductCategory,
  Store,
} from './models';

let pool: Pool;
let readonlyPool: Pool;

export function startup({
  connectionString,
  readonlyConnectionString,
}: {
  connectionString: string,
  readonlyConnectionString: string,
}) {
  pool = new Pool(connectionString);
  readonlyPool = new Pool(readonlyConnectionString);

  const repositoriesByName = initialize({
    models: [
      Category,
      Product,
      ProductCategory,
      Store,
    ],
    pool,
    readonlyPool,
  });

  let categoryRepository: Repository<Category>;
  let productRepository: Repository<Product>;
  let storeRepository: Repository<Store>;
  for (const [modelName, repository] = Object.entries(repositoriesByName)) {
    switch (modelName) {
      case 'Category':
        categoryRepository = repository;
        break;
      case 'Product':
        productRepository = repository;
        break;
      case 'Store':
        storeRepository = repository;
        break;
    }
  }

  return {
    categoryRepository,
    productRepository,
    storeRepository,
  }
}

export function shutdown() {
  const shutdownEvents = [];
  if (pool) {
    shutdownEvents.push(pool.end());
  }

  if (readonlyPool) {
    shutdownEvents.push(readonlyPool.end());
  }

  return Promise.all(shutdownEvents);
}

Repository class methods

.findOne() - Fetch a single object

Fetch a single object with where criteria specified as a chained method

const item = await ProductRepository.findOne().where({
  id: context.params.id,
});

Fetch a single object and restrict columns selected from db (query projection)

const item = await ProductRepository.findOne({
  select: ['name'],
}).where({
  id: context.params.id,
});

Fetch a single object using explicit DB pool

const poolOverride = new Pool(connectionString);

const item = await ProductRepository.findOne({
  pool: poolOverride,
}).where({
  id: context.params.id,
});

Populate relation - Relations can be one-to-many (ether direction) or many-to-many

const item = await ProductRepository.findOne()
  .where({
    id: context.params.id,
  })
  .populate('store', {
    select: ['name'],
  });

Populate relation using explicit DB pool

const poolOverride = new Pool(connectionString);

const item = await ProductRepository.findOne()
  .where({
    id: context.params.id,
  })
  .populate('store', {
    select: ['name'],
    pool: poolOverride,
  });

Fetch a single object and perform a db sort before returning result

const item = await ProductRepository.findOne()
  .where({
    foo: context.params.foo,
  })
  .sort('name asc');

.find() - Fetch a multiple objects

Fetch multiple objects with where criteria specified as a chained method

const items = await ProductRepository.find().where({
  foo: context.params.foo,
});

Fetch multiple objects and restrict columns selected from db (query projection)

const items = await ProductRepository.find({
  select: ['name'],
}).where({
  foo: context.params.foo,
});

Fetch a multiple objects using explicit DB pool

const poolOverride = new Pool(connectionString);

const item = await ProductRepository.find({
  pool: poolOverride,
}).where({
  foo: context.params.foo,
});

String matching operators

BigAl provides four string matching operators. All use case-insensitive matching (PostgreSQL ILIKE) and accept arrays for OR matching.

| Operator | Description | SQL Pattern | | ------------ | --------------------------------------------- | ----------- | | like | Raw ILIKE pattern with your own % wildcards | As provided | | contains | Substring match anywhere in the string | %value% | | startsWith | Matches strings starting with the value | value% | | endsWith | Matches strings ending with the value | %value |

const items = await ProductRepository.find().where({
  name: { contains: 'widget' },
});
// SQL: SELECT ... FROM product WHERE name ILIKE '%widget%'

const items = await ProductRepository.find().where({
  name: { startsWith: 'Pro' },
});
// SQL: SELECT ... FROM product WHERE name ILIKE 'Pro%'

const items = await ProductRepository.find().where({
  name: { endsWith: 'ter' },
});
// SQL: SELECT ... FROM product WHERE name ILIKE '%ter'

const items = await ProductRepository.find().where({
  name: { like: 'Pro%Widget%' },
});
// SQL: SELECT ... FROM product WHERE name ILIKE 'Pro%Widget%'

// Arrays create OR conditions
const items = await PersonRepository.find().where({
  firstName: { like: ['walter', 'Jess%'] },
});
// SQL: SELECT ... FROM person WHERE (first_name ILIKE 'walter' OR first_name ILIKE 'Jess%')

Comparison operators

For number and date fields, use comparison operators:

| Operator | Description | | -------- | ------------------------ | | < | Less than | | <= | Less than or equal to | | > | Greater than | | >= | Greater than or equal to |

const items = await ProductRepository.find().where({
  price: { '>=': 100 },
});
// SQL: SELECT ... FROM product WHERE price >= $1

// Multiple operators on same field create AND
const items = await ProductRepository.find().where({
  createdAt: { '>=': startDate, '<': endDate },
});
// SQL: SELECT ... FROM product WHERE created_at >= $1 AND created_at < $2

Array values (OR conditions)

When you pass an array of values, BigAl creates an OR condition:

const items = await PersonRepository.find().where({
  age: [22, 23, 24],
});
// SQL: SELECT ... FROM person WHERE age IN ($1, $2, $3)

const items = await ProductRepository.find().where({
  name: { startsWith: ['Pro', 'Pre'] },
});
// SQL: SELECT ... FROM product WHERE (name ILIKE 'Pro%' OR name ILIKE 'Pre%')

Negation operator (!)

Use ! to negate any condition:

const items = await ProductRepository.find().where({
  status: { '!': 'discontinued' },
});
// SQL: SELECT ... FROM product WHERE status <> $1

const items = await ProductRepository.find().where({
  status: { '!': ['discontinued', 'archived'] },
});
// SQL: SELECT ... FROM product WHERE status NOT IN ($1, $2)

const items = await ProductRepository.find().where({
  name: { '!': { startsWith: 'Test' } },
});
// SQL: SELECT ... FROM product WHERE name NOT ILIKE 'Test%'

const items = await ProductRepository.find().where({
  deletedAt: { '!': null },
});
// SQL: SELECT ... FROM product WHERE deleted_at IS NOT NULL

Example of an AND statement

const items = await PersonRepository.find().where({
  lastName: {
    '!': {
      lastName: [null, '', 'Whi%'],
    },
  },
});

Example of a date range (AND statement)

const items = await PersonRepository.find().where({
  createdAt: {
    '>=': startDate,
    '<': endDate,
  },
});

Equivalent to:

select id,first_name as firstName,last_name as lastName,created_at as createdAt from person where created_at >= $1 AND created_at < $2

Using or for OR conditions across different columns

const items = await PersonRepository.find().where({
  or: [{ firstName: 'Walter' }, { lastName: 'White' }],
});

Equivalent to:

select * from person where (first_name = $1) OR (last_name = $2)

Using and to combine multiple OR groups

const items = await PersonRepository.find().where({
  and: [{ or: [{ firstName: 'Walter' }, { lastName: 'White' }] }, { or: [{ firstName: 'Jesse' }, { lastName: 'Pinkman' }] }],
});

Equivalent to:

select * from person where ((first_name = $1) OR (last_name = $2)) AND ((first_name = $3) OR (last_name = $4))

Sorting results

Use .sort() to order results. Two syntax options are available:

String syntax - Use asc or desc (comma-separated for multiple columns):

const items = await PersonRepository.find().where({ lastName: 'Smith' }).sort('age asc');
// SQL: SELECT ... FROM person WHERE last_name = $1 ORDER BY age ASC

const items = await PersonRepository.find().where({ lastName: 'Smith' }).sort('age asc, createdAt desc');
// SQL: SELECT ... FROM person WHERE last_name = $1 ORDER BY age ASC, created_at DESC

Object syntax - Use 1 for ascending, -1 for descending:

const items = await PersonRepository.find().where({ lastName: 'Smith' }).sort({ age: 1 });
// SQL: SELECT ... FROM person WHERE last_name = $1 ORDER BY age ASC

const items = await PersonRepository.find().where({ lastName: 'Smith' }).sort({ age: 1, createdAt: -1 });
// SQL: SELECT ... FROM person WHERE last_name = $1 ORDER BY age ASC, created_at DESC

DISTINCT ON

Get one row per unique combination of specified columns. Useful for "greatest-per-group" queries.

// Get the most recently created product per store
const latestPerStore = await ProductRepository.find().distinctOn(['store']).sort('store').sort('createdAt desc');
// SQL: SELECT DISTINCT ON ("store_id") ... FROM "products" ORDER BY "store_id", "created_at" DESC

Requirements:

• ORDER BY is required and must start with the DISTINCT ON columns
• Cannot be combined with withCount()

See the DISTINCT ON documentation for more details.

Limit number results returned

const items = await PersonRepository.find()
  .where({
    age: [22, 23, 24],
  })
  .limit(42);

Skip x results

const items = await FooRepository.find()
  .where({
    or: [
      {
        foo: context.params.foo,
      },
      {
        bar: context.params.foo,
      },
    ],
  })
  .skip(42);

Page results using skip() & limit()

const items = await FooRepository.find()
  .where({
    foo: context.params.foo,
  })
  .skip(84)
  .limit(42);

Page results using paginate

const page = 2;
const pageSize = 42;
const items = await FooRepository.find()
  .where({
    foo: context.params.foo,
  })
  .paginate(page, pageSize);

Page results with total count using withCount

Use .withCount() to get both paginated results and the total count of matching records in a single query. This uses PostgreSQL's COUNT(*) OVER() window function for efficient execution.

const { results, totalCount } = await ProductRepository.find()
  .where({
    store: storeId,
  })
  .sort('name')
  .limit(10)
  .skip(20)
  .withCount();

// results: Product[] (10 items from offset 20)
// totalCount: number (total matching products, ignoring LIMIT/OFFSET)

This is useful for building paginated UIs where you need to display total pages:

const page = 3;
const pageSize = 25;
const { results, totalCount } = await ProductRepository.find().where({ isActive: true }).paginate({ page, limit: pageSize }).withCount();

const totalPages = Math.ceil(totalCount / pageSize);

Join related tables

Use join() for INNER JOIN or leftJoin() for LEFT JOIN to filter or sort by related table columns in a single query.

// INNER JOIN - only returns products that have a store
const items = await ProductRepository.find()
  .join('store')
  .where({
    store: {
      name: 'Acme',
    },
  });

// LEFT JOIN - returns all products, even those without a store
const items = await ProductRepository.find()
  .leftJoin('store')
  .where({
    store: {
      name: 'Acme',
    },
  });

Join with alias

Use an alias when you need to join the same table multiple times or for clarity.

const items = await ProductRepository.find()
  .join('store', 'primaryStore')
  .where({
    primaryStore: {
      name: 'Acme',
    },
  });

Join with additional ON constraints

Add extra conditions to the JOIN's ON clause using leftJoin().

const items = await ProductRepository.find()
  .leftJoin('store', 'store', {
    isDeleted: false,
  })
  .where({
    name: {
      like: 'Widget%',
    },
  });

Sort by joined table columns

Use dot notation to sort by columns on joined tables.

const items = await ProductRepository.find().join('store').sort('store.name asc');

Combine multiple where conditions

Mix regular where conditions with joined table conditions.

const items = await ProductRepository.find()
  .join('store')
  .where({
    name: {
      like: 'Widget%',
    },
    store: {
      name: {
        like: ['Acme', 'foo'],
      },
    },
  });

Note: join() and populate() serve different purposes. Use join() when you need to filter or sort by related table columns in SQL. Use populate() when you want to fetch the full related object(s) as nested data in results.

Subqueries

Use the subquery() function to create subqueries for use in WHERE clauses.

WHERE IN with subquery

import { subquery } from 'bigal';

// Find products from active stores
const activeStoreIds = subquery(StoreRepository).select(['id']).where({ isActive: true });

const items = await ProductRepository.find().where({
  store: { in: activeStoreIds },
});

Equivalent SQL:

SELECT * FROM products
WHERE store_id IN (SELECT id FROM stores WHERE is_active = $1)

WHERE NOT IN with subquery

Use the existing ! negation operator:

const discontinuedProductIds = subquery(DiscontinuedProductRepository).select(['productId']);

const items = await ProductRepository.find().where({
  id: { '!': { in: discontinuedProductIds } },
});

Equivalent SQL:

SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM discontinued_products)

WHERE EXISTS

// Find stores that have at least one product
const items = await StoreRepository.find().where({
  exists: subquery(ProductRepository).where({ storeId: 42 }),
});

Equivalent SQL:

SELECT * FROM stores
WHERE EXISTS (SELECT 1 FROM products WHERE store_id = $1)

WHERE NOT EXISTS

// Find stores with no products
const items = await StoreRepository.find().where({
  '!': {
    exists: subquery(ProductRepository).where({ storeId: 42 }),
  },
});

Equivalent SQL:

SELECT * FROM stores
WHERE NOT EXISTS (SELECT 1 FROM products WHERE store_id = $1)

Scalar subquery comparisons

Use aggregate methods (count(), avg(), sum(), max(), min()) to create scalar subqueries for comparisons:

// Find products priced above the average
const avgPrice = subquery(ProductRepository).where({ category: 'electronics' }).avg('price');

const items = await ProductRepository.find().where({
  price: { '>': avgPrice },
});

Equivalent SQL:

SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products WHERE category = $1)

Combining subqueries with other conditions

Subqueries can be combined with regular where conditions and other operators:

const premiumStoreIds = subquery(StoreRepository).select(['id']).where({ tier: 'premium' });

const items = await ProductRepository.find().where({
  store: { in: premiumStoreIds },
  price: { '>=': 100 },
  isActive: true,
});

Reusable subqueries

Subqueries are standalone objects that can be reused across multiple queries:

const activeStoreIds = subquery(StoreRepository).select(['id']).where({ isActive: true });

// Use in multiple queries
const products = await ProductRepository.find().where({ store: { in: activeStoreIds } });

const orders = await OrderRepository.find().where({ store: { in: activeStoreIds } });

Joining to Subqueries

You can join to subqueries (derived tables) using join() or leftJoin(). This is useful for aggregating data and joining it back to the main query.

Basic subquery join with COUNT aggregate

import { subquery } from 'bigal';

// Count products per store
const productCounts = subquery(ProductRepository)
  .select(['store', (s) => s.count().as('productCount')])
  .groupBy(['store']);

// Join to get stores with their product counts
const stores = await StoreRepository.find().join(productCounts, 'productStats', { on: { id: 'store' } });

Equivalent SQL:

SELECT * FROM stores
INNER JOIN (
  SELECT store_id AS store, COUNT(*) AS "productCount"
  FROM products
  GROUP BY store_id
) AS "productStats" ON stores.id = "productStats".store

LEFT JOIN to subquery

Use leftJoin() to include rows even when there's no matching subquery row:

const productCounts = subquery(ProductRepository)
  .select(['store', (s) => s.count().as('productCount')])
  .groupBy(['store']);

// LEFT JOIN returns all stores, even those with no products
const stores = await StoreRepository.find().leftJoin(productCounts, 'productStats', { on: { id: 'store' } });

Sorting by subquery columns

Use dot notation to sort by columns from the subquery:

const productCounts = subquery(ProductRepository)
  .select(['store', (s) => s.count().as('productCount')])
  .groupBy(['store']);

// Sort by product count descending (most products first)
const stores = await StoreRepository.find()
  .join(productCounts, 'productStats', { on: { id: 'store' } })
  .sort('productStats.productCount desc');

Subquery with WHERE clause and aggregate

Filter within the subquery before aggregating:

// Count only active products per store
const activeProductCounts = subquery(ProductRepository)
  .select(['store', (s) => s.count().as('activeCount')])
  .where({ isActive: true })
  .groupBy(['store']);

const stores = await StoreRepository.find().join(activeProductCounts, 'activeStats', { on: { id: 'store' } });

COUNT DISTINCT in subquery

Use .distinct() for counting unique values:

// Count unique product names per store
const uniqueNameCounts = subquery(ProductRepository)
  .select(['store', (s) => s.count('name').distinct().as('uniqueNames')])
  .groupBy(['store']);

const stores = await StoreRepository.find().join(uniqueNameCounts, 'stats', { on: { id: 'store' } });

Equivalent SQL:

SELECT * FROM stores
INNER JOIN (
  SELECT store_id AS store, COUNT(DISTINCT name) AS "uniqueNames"
  FROM products
  GROUP BY store_id
) AS stats ON stores.id = stats.store

Multiple aggregates in a single subquery

Compute multiple aggregates in one subquery:

const orderStats = subquery(OrderRepository)
  .select(['store', (s) => s.count().as('orderCount'), (s) => s.sum('total').as('totalRevenue'), (s) => s.avg('total').as('avgOrderValue')])
  .groupBy(['store']);

const stores = await StoreRepository.find()
  .join(orderStats, 'stats', { on: { id: 'store' } })
  .sort('stats.totalRevenue desc');

Default aggregate aliases

Aggregate functions have default aliases matching their function name:

// Without .as() - alias defaults to function name
const counts = subquery(ProductRepository)
  .select(['store', (s) => s.count()]) // alias: "count"
  .groupBy(['store']);

// With .as() - custom alias
const counts = subquery(ProductRepository)
  .select(['store', (s) => s.count().as('productCount')]) // alias: "productCount"
  .groupBy(['store']);

HAVING clause for filtering aggregated results

Use .having() to filter groups based on aggregate values:

// Only include stores with more than 10 products
const productCounts = subquery(ProductRepository)
  .select(['store', (s) => s.count().as('productCount')])
  .groupBy(['store'])
  .having({ productCount: { '>': 10 } });

const popularStores = await StoreRepository.find().join(productCounts, 'stats', { on: { id: 'store' } });

Supported comparison operators: >, >=, <, <=, !=, or exact equality (number).

// Multiple conditions on different aggregates
const orderStats = subquery(OrderRepository)
  .select(['store', (s) => s.count().as('orderCount'), (s) => s.avg('total').as('avgOrderValue')])
  .groupBy(['store'])
  .having({ orderCount: { '>=': 5 }, avgOrderValue: { '>': 100 } });

WHERE vs HAVING in subqueries

WHERE and HAVING serve different purposes in aggregated subqueries:

• WHERE filters individual rows before grouping
• HAVING filters groups after aggregation

// WHERE: Only count active products (filters rows before counting)
const activeProductCounts = subquery(ProductRepository)
  .select(['store', (s) => s.count().as('productCount')])
  .where({ isActive: true }) // Excludes inactive products from the count
  .groupBy(['store']);

// HAVING: Only include stores with high product counts (filters groups after counting)
const highVolumeStores = subquery(ProductRepository)
  .select(['store', (s) => s.count().as('productCount')])
  .groupBy(['store'])
  .having({ productCount: { '>': 100 } }); // Excludes stores with 100 or fewer products

// Combined: Count active products, then filter to stores with many active products
const highVolumeActiveStores = subquery(ProductRepository)
  .select(['store', (s) => s.count().as('activeCount')])
  .where({ isActive: true }) // Step 1: Only consider active products
  .groupBy(['store'])
  .having({ activeCount: { '>': 50 } }); // Step 2: Only keep stores with >50 active products

For more details on subqueries and joins, see the Subqueries and Joins Guide.

.count() - Get the number of records matching the where criteria

const count = await PersonRepository.count().where({
  name: {
    like: 'Karl',
  },
});
// count = 3

.create() - Insert one or multiple objects

Insert a single object

const item = await PersonRepository.create({
  name: 'Karl',
});
// item = { id: 42, name: 'Karl', createdAt: ... }

Insert a single object without returning results from the db

await PersonRepository.create(
  {
    name: 'Karl',
  },
  {
    returnRecords: false,
  },
);

Insert a single object but limit columns returned from db for inserted records (query projection)

const item = await PersonRepository.create(
  {
    name: 'Karl',
  },
  {
    returnSelect: ['name'],
  },
);
// item = { id: 42, name: 'Karl' }

Note: The primary key will always be included. To only return the primary key value, pass an empty array

Insert multiple objects

const items = await PersonRepository.create([
  {
    name: 'LX',
  },
  {
    name: 'Big Al',
  },
]);
// items = [{ id: 24, name: 'LX', createdAt: ... }, { id: 25, name: 'Big Al', createdAt: ... }]

Insert multiple objects without returning results from the db

await PersonRepository.create(
  [
    {
      name: 'LX',
    },
    {
      name: 'Big Al',
    },
  ],
  {
    returnRecords: false,
  },
);

Insert multiple objects with limited return properties

const items = await PersonRepository.create(
  [
    {
      name: 'LX',
    },
    {
      name: 'Big Al',
    },
  ],
  {
    returnSelect: ['id'],
  },
);
// items = [{ id: 24 }, { id: 25 }]

Note: The primary key will always be included. To only return the primary key value, pass an empty array

Insert a single object with onConflict ignore (ON CONFLICT DO NOTHING)

const item = await PersonRepository.create(
  {
    name: 'Karl',
  },
  {
    onConflict: {
      action: 'ignore',
      targets: ['name'],
    },
  },
);
// item = { id: 42, name: 'Karl', createdAt: ... }

Insert a single object with onConflict merge (ON CONFLICT DO UPDATE) - Update all data

const item = await PersonRepository.create(
  {
    name: 'Karl',
  },
  {
    onConflict: {
      action: 'merge',
      targets: ['ssn'],
    },
  },
);
// item = { id: 42, name: 'Karl', createdAt: ... }

Insert a single object with onConflict merge (ON CONFLICT DO UPDATE) - Update specific data

const item = await PersonRepository.create(
  {
    name: 'Karl',
  },
  {
    onConflict: {
      action: 'merge',
      targets: ['ssn'],
      merge: ['name', 'age'],
    },
  },
);
// item = { id: 42, name: 'Karl', createdAt: ... }

.update() - Update objects

Update single record

const items = await PersonRepository.update(
  {
    id: 42,
  },
  {
    name: 'Big Al',
  },
);
// items = [{ id: 42, name: 'Big Al', createdAt: ... }]

Note: This method will return an array, regardless of how many records were affected

Update record without returning results from the db

await PersonRepository.update(
  {
    id: 42,
  },
  {
    name: 'Big Al',
  },
  {
    returnRecords: false,
  },
);

Update records and limit columns returned from db for affected records (query projection)

const items = await PersonRepository.update(
  {
    id: [42, 43],
  },
  {
    occupation: 'Water Purification Engineer',
  },
  {
    returnSelect: ['id'],
  },
);
// items = [{ id: 42 }, { id: 43 }]

.destroy() - Delete objects from the db

Delete single record

const items = await PersonRepository.destroy({
  id: 42,
});
// items = [{ id: 42, name: 'Big Al', createdAt: ... }]

Note: This method will return an array, regardless of how many records were affected

Delete record without returning row data from the db

await PersonRepository.destroy(
  {
    id: 42,
  },
  {
    returnRecords: false,
  },
);

Delete records and limit columns returned from db for affected records (query projection)

const items = await PersonRepository.destroy(
  {
    id: [24, 25],
  },
  {
    returnSelect: ['name'],
  },
);
// items = [{ id: 24, name: 'LX' }, { id: 25, name: 'Big Al' }]

Note: The primary key will always be included. To only return the primary key value, pass an empty array

Known issues

Entity collections must be optional

BigAl expects that all entity collection properties must be optional. There will be some type issues with QueryResult if you make a collection non-optional.

For example:

export class Store extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({
    type: 'string',
    required: true,
  })
  public name!: string;

  // This property MUST be optional
  @column({
    collection: () => Product.name,
    via: 'store',
  })
  public products?: Product[];
}

Non-entity object arrays

If you have a JSON property, with an id field, on an entity model, TypeScript will probably think it is a BigAl entity due to how the type system works. In that case, you'll want to wrap the type with NotEntity<>. For example:

export interface IMyJsonType {
  id: string;
  foo: string;
}

export class Product extends Entity {
  @primaryColumn({ type: 'integer' })
  public id!: number;

  @column({
    type: 'string',
    required: true,
  })
  public name!: string;

  @column({
    type: 'json',
  })
  public myJson?: NotEntity<IMyJsonType>;
}

Debugging

Debugging can be enabled by passing the DEBUG_BIGAL environment flag with a value of true.

Debugging will print the generated SQL code in the console.

License

MIT