Repositories

A Repository<T> is the CRUD interface for an entity — it builds and executes the SELECT/INSERT/UPDATE/DELETE statements for T using @sqb/builder under the hood, and converts rows back into instances of T (including nested embedded objects and eager associations).

Get one from a client or connection — don't construct Repository yourself:

const repo = client.getRepository(Customer);
// or, inside a transaction/acquired connection:
const repo = connection.getRepository(Customer);

See SqbClient and SqbConnection.

Events

Repository extends strict-typed-events' AsyncEventEmitter and emits:

Event	Signature	When
execute	(request: QueryRequest) => void	A query is about to run.
error	(error: Error) => void	An error occurred.
acquire	(connection: SqbConnection) => Promise<void>	A connection was acquired from the pool to run an operation (only fires when the repository was created from a SqbClient, not from an already-acquired SqbConnection).

Methods

Every read/write method accepts an options object as its last argument and internally acquires a connection (or reuses one you pass via options.connection, or the connection the repository was already bound to). Methods that can return either a full entity instance or a plain partial object are overloaded: passing a projection in the options narrows the return type to PartialDTO<T> (a plain object with only the requested fields); omitting it returns a full T instance.

create()

create(input: PartialDTO<T>, options?: Repository.CreateOptions): Promise<T>;
create(input: PartialDTO<T>, options: RequiredSome<Repository.CreateOptions, 'projection'>): Promise<PartialDTO<T>>;

Inserts a row, then re-fetches it (by its returned key) and resolves with the created record. Throws if input is falsy, or if no column ends up with a value to insert (No field given to create new entity instance), or if the insert's RETURNING didn't yield a key (Unable to insert new row).

const customer = await repo.create({ givenName: 'Jane', countryCode: 'US' });

createOnly()

createOnly(
  input: PartialDTO<T>,
  options?: StrictOmit<Repository.CreateOptions, 'projection'>,
): Promise<any>;

Inserts a row but skips the re-fetch — resolves with the new row's primary-key value (a scalar for a single-column key, or a Record<string, any> for a composite key), or with whatever the database returned via RETURNING if the entity has no primary index. Cheaper than create() when you don't need the row back.

const id = await repo.createOnly({ givenName: 'Jane', countryCode: 'US' });

count()

count(options?: Repository.CountOptions): Promise<number>;

const total = await repo.count({ filter: { active: true } });

exists() / existsOne()

exists(keyValue: any | Record<string, any>, options?: Repository.ExistsOptions): Promise<boolean>;
existsOne(options?: Repository.ExistsOptions): Promise<boolean>;

exists() checks for a row by primary key (optionally narrowed further with options.filter); existsOne() checks for any row matching options.filter with no key involved.

await repo.exists(1);
await repo.existsOne({ filter: { givenName: 'Jane' } });

findById()

findById(keyValue: any | Record<string, any>, options?: Repository.FindOptions): Promise<T | undefined>;
findById(keyValue: any | Record<string, any>, options: RequiredSome<Repository.FindOptions, 'projection'>): Promise<PartialDTO<T> | undefined>;

const customer = await repo.findById(1);
const summary = await repo.findById(1, { projection: ['id', 'givenName'] });

findOne()

findOne(options?: Repository.FindOneOptions): Promise<T | undefined>;

Like findMany with an implicit limit: 1, but resolves to the single record (or undefined) instead of an array.

const customer = await repo.findOne({ filter: { givenName: 'Jane' }, sort: ['-id'] });

findMany()

findMany(options?: Repository.FindManyOptions): Promise<T[]>;
findMany(options: RequiredSome<Repository.FindManyOptions, 'projection'>): Promise<PartialDTO<T>[]>;

const customers = await repo.findMany({
  filter: { active: true },
  projection: ['id', 'givenName', 'country'],
  sort: ['givenName'],
  limit: 20,
  offset: 40,
});

update()

update(keyValue: any | Record<string, any>, input: PatchDTO<T>, options?: Repository.UpdateOptions): Promise<T | undefined>;
update(keyValue: any | Record<string, any>, input: PatchDTO<T>, options: RequiredSome<Repository.UpdateOptions, 'projection'>): Promise<PartialDTO<T> | undefined>;

Updates the row identified by keyValue, then re-fetches and returns it (undefined if no row matched). Any key fields present in input are stripped before the UPDATE is built (you can't change the primary key value this way).

const updated = await repo.update(1, { givenName: 'Janet' });

updateOnly()

updateOnly(keyValue: any | Record<string, any>, input: PatchDTO<T>, options?: Repository.UpdateOnlyOptions): Promise<boolean>;

Same as update() but skips the re-fetch, resolving to whether a row was actually updated.

const wasUpdated = await repo.updateOnly(1, { givenName: 'Janet' });

updateMany()

updateMany(input: PartialDTO<T>, options?: Repository.UpdateManyOptions): Promise<number>;

Updates every row matching options.filter, resolving to the number of affected rows.

const count = await repo.updateMany({ active: false }, { filter: { countryCode: 'XX' } });

delete()

delete(keyValue: any | Record<string, any>, options?: Repository.DeleteOptions): Promise<boolean>;

Deletes the row identified by keyValue (optionally narrowed further with options.filter), resolving to whether a row was deleted.

await repo.delete(1);

deleteMany()

deleteMany(options?: Repository.DeleteManyOptions): Promise<number>;

Deletes every row matching options.filter, resolving to the number of deleted rows.

const deleted = await repo.deleteMany({ filter: { countryCode: 'XX' } });

Bulk operations and an empty filter — important

deleteMany() and updateMany() act on whatever options.filter matches, exactly like a plain SQL DELETE/UPDATE without a WHERE clause when no filter is given. @sqb/connect does not require a non-empty filter for either operation — reading DeleteCommand/UpdateCommand directly confirms both explicitly allow an empty (or all-conditions-stripped) filter through, with the code comment "An empty filter (or one that resolves to zero conditions) must be allowed". This means:

await repo.deleteMany(); // deletes every row in the table — no error, no confirmation
await repo.updateMany({ active: false }); // sets every row's `active` to false

both run without throwing. If your application wants to guard against an accidental full-table wipe from a bug (e.g. a filter that ends up undefined due to a typo upstream), you need to enforce that yourself at the call site — @sqb/connect will not do it for you.

Repository.CommandOptions

Shared by every method's options:

Option	Type	Description
connection	SqbConnection	Run this call on a specific connection instead of acquiring one from the pool (e.g. inside a transaction).
prettyPrint	boolean	Format the generated SQL for readability (useful when logging).
comment	string | string[] | { comment: string; dialect?: string[] } | { comment: string; dialect?: string[] }[]	SQL comment(s) appended to the generated statement — the object form restricts a comment to specific dialects.
optimizerHint	string | string[] | TableName.OptimizerHint | TableName.OptimizerHint[]	Dialect-specific optimizer hints applied to the query's TableName.

Repository.FindManyOptions

Extends CommandOptions plus projection/filter, adding:

Option	Type	Description
sort	string[]	Column or dotted-path names to sort by; prefix with - for descending (default ascending, + also accepted). Sorting through a to-many association is rejected (Can not sort by "...", since there's no single row to order against).
offset	number	Row offset (also available on findOne/findById/find-style calls via FindOneOptions).
limit	number	Max rows to return.
distinct	boolean	Adds DISTINCT to the generated SELECT.
maxEagerFetch	number	Caps how many related rows a to-many association's eager sub-query may return in total across the whole result set before throwing (Number of returning rows for "..." exceeds maxEagerFetch limit). Defaults to 100000.
maxSubQueries	number	Caps how many levels deep chained to-many associations are allowed to eagerly resolve before @sqb/connect stops adding more sub-queries. Defaults to 5.
onTransformRow	(fields: FieldInfoMap, row: object, obj: object) => void	Called once per raw row during conversion — lets you inspect/mutate the raw row alongside the object being built.

See Repository.FindManyOptions for the full reference.

Filters

A filter can be:

• A plain object — each key/value pair becomes an equality (or, for keys with a trailing operator like 'age >=', a comparison) condition, all AND-ed together:

  { city: 'Istanbul' }
  { 'age >=': 18 }

• An @sqb/builder operator (Eq, Ne, Gt, Gte, Lt, Lte, In, Like, And, Or, ...) for anything more complex than plain-object equality:

  import { And, Eq, Gte } from '@sqb/builder';
  filter: And(Eq('countryCode', 'US'), Gte('age', 18));

  See Operators and conditions for the full operator list — Repository filters accept the exact same operator objects a query-builder .where() call does.

• A dotted path reaching into an embedded object or through an association:

  { 'address.city': 'Dallas' }        // embedded object sub-field
  { 'country.hasMarket': true }       // through a to-one/to-many association (correlated EXISTS)

• An array of any of the above, combined with AND (or matching the semantics of whatever LogicalOperator you nest it under).

Named parameters work the same way they do in the query builder — pass an @sqb/builder Param('name') as a filter value and supply the actual value via the sibling params option:

await repo.findMany({
  filter: { givenName: Param('givenName') },
  params: { givenName: 'Belle' },
});

Projection

projection controls which fields end up on the returned object(s), and accepts a string or string[]:

• Bare field name — include that field: 'givenName'.
• -field — exclude a field that would otherwise be included by default.
• +field — include a field that's marked exclusive (exclusive fields, and every @Link association in practice, are omitted unless explicitly requested this way).
• Dotted path — reach into an embedded object's or an association's own fields: 'name.given', 'country.name'. A trailing - inside a nested segment excludes just that sub-field: 'country.-code'.
• hidden fields (see Data Columns) can never be included via projection, regardless of sign — they're skipped unconditionally.

// only these two fields
await repo.findMany({ projection: ['id', 'givenName'] });

// default fields, minus familyName, plus the exclusive `country` association
// (itself projected down to just its `name` sub-field)
await repo.findMany({ projection: ['+country', '-familyName', 'country.name'] });

// only the "given" sub-field of the embedded `name` object
await repo.findMany({ projection: ['name.given'] });

When projection is omitted (or contains only exclusion/+ entries and no positive plain inclusions), the default field set is used: every non-hidden, non-exclusive column and embedded field — associations and exclusive columns are left out unless explicitly requested.

Passing any projection at all narrows the return type from T to PartialDTO<T> at the type level (see the overloads on each find*/create/update method above) — a signal that the result may be missing fields the full entity type declares.