pg-sequelize package

Sequelize and sequelize-typescript, refined for @imqueue services.

The problem it was built for: a caller describes the query it wants as DATA — a filter, a page, an order, and the fields it actually needs — and the service has to turn that into one efficient statement, without trusting any of it and without knowing in advance which shape will arrive. Anything can hand you a query described that way; a GraphQL API is the case this was written against, where a resolver passes the arguments and the selected field set through almost untouched and gets back a query that selects the columns asked for, joins only the relations the selection reaches into, and filters on values that arrived as JSON. An RPC method taking a filter object, or a REST endpoint with query parameters, is the same problem with different packaging.

Which version, and which package. This targets Sequelize v6 — mature, proven in production, and what this package is actively developed against. Sequelize v7 is still in alpha upstream, so v6 is the line to build on for now, and if v7 lands this follows it. If you would rather build on Prisma, @imqueue/pg-prisma covers the same ground for that stack. Both are supported: pick the ORM you want to live with.

The query namespace and the serializable input types are what do it. Sequelize writes a filter with ES symbols as its operators, and a symbol cannot survive a JSON payload, so FilterInput, FieldsInput, PaginationInput and OrderByInput give the wire an equivalent that can. query.autoQuery turns them back into Sequelize options, and builds the include tree the requested fields imply — which is where the efficiency comes from: a field nobody asked for is not selected, and a relation nobody reached into is not joined.

database() builds and caches the connection and discovers your compiled model files. It is a process-wide singleton: the first call configures it, every later one hands back the same instance, and SQL logging that can prettify and colourise statements is installed along the way.

It is also the single import surface for the ORM stack, which is what most files in a service touch it for. Everything sequelize and sequelize-typescript export is re-exported here — Table, Column, DataType, AllowNull, ForeignKey, BelongsTo, HasMany, QueryInterface and the rest — so a model imports from one place rather than three, and several option types are widened on the way through (see ReturningOptions). A migration usually needs nothing but QueryInterface.

The decorators are the smallest part and the most opinionated: CreatedBy(), UpdatedBy() and DeletedBy() stamp the acting user from the RPC request context, AssociatedWith() declares a relation the query helpers can walk, and View and DynamicView let a model be a database view — a parameterised one, whose placeholders are filled in per query.

Example

// the shape every paginated read method in a service ends up with
const where = query.toWhereOptions(query.withRangeFilters(filter));
const rows = await LeadModel.findAll(query.autoQuery<FindOptions>(
    LeadModel,
    fields,
    where,
    query.toLimitOptions(pageOptions),
    query.toOrderOptions(orderBy),
));

Classes

Class	Description
DateRange	A range between two dates, as an `@imqueue/rpc` type.
FieldsInput	Which fields to return, nested to match the shape of the relations.
FilterInput	A where clause as plain, serializable JSON.
Graph	A directed, unweighted graph with depth-first traversal and cycle detection.
JsonObject	A free-form object, describable to `@imqueue/rpc`.
NumericRange	A range between two numbers, as an `@imqueue/rpc` type.
OrderByInput	Which columns to order by, and in which direction.
PaginationInput	Where a page starts and how big it is.
Sequelize	Sequelize's own connection class, taught about views, column indices and the widened `returning` option.

Abstract Classes

Abstract Class	Description
BaseModel	The class every model in an `@imqueue` service extends.

Enumerations

Enumeration	Description
IndexMethod	The index methods Postgres offers, for ColumnIndexOptions.method.
OrderDirection	The two directions a column can be ordered in.
SortOrder	Sort direction of an index key, for ColumnIndexOptions.order.

Functions

Function	Description
AssociatedWith(cb)	Marks a field of a filter input as standing for an association rather than for a column.
ColumnIndex(options)	Declares an index on a model column, with Postgres's own index options.
ColumnIndex(target, propertyName, propertyDescriptor)	Declares a plain btree index on a model column.
CreatedBy(target, propertyName)	Stamps the decorated column with the acting user id on INSERT, taken from the in-flight IMQ request metadata (`currentMetadata()?.userId`) — so the id never travels through method arguments and cannot be spoofed by a caller. A property decorator, reusable on any model field. The hook is a no-op when there is no acting user (system / unattributed writes) and never overwrites a value the application set explicitly. Two hooks are registered, because rows are inserted in two ways: - instance / single `create()` → `beforeCreate` (receives the instance) - static `Model.bulkCreate(records, …)` → `beforeBulkCreate` (receives the built instances; a plain `bulkCreate` does not fire `beforeCreate`, so the per-instance hook alone would be bypassed). Sequelize filters the written columns down to `options.fields` when the caller supplies it, so an injected field is dropped unless also added there. Mechanism: a property decorator receives the prototype, but Sequelize hook decorators must target a static method on the constructor, so generated static methods are attached to `target.constructor` and registered through the public `@BeforeCreate` / `@BeforeBulkCreate`. Hooks are installed by sequelize-typescript's `installHooks` during `Sequelize#addModels`.
database(options)	Connects to the database, loads the models, and returns the Sequelize instance.
DeletedBy(target, propertyName)	Stamps the decorated column with the acting user id on soft-delete (and clears it on restore), taken from the in-flight IMQ request metadata (`currentMetadata()?.userId`). Mirrors `@DeletedAt`: set on delete, cleared on restore, and a delete touches neither `updatedAt` nor an `@UpdatedBy` column. A property decorator for paranoid models, reusable on any field. Soft-delete runs through `Model.destroy`, which Sequelize turns into a `deletedAt`-only UPDATE whose value hash is built internally and is unreachable from any hook. So instead of mutating that statement, the `beforeBulkDestroy` hook stamps the column with a sibling UPDATE over the same still-live rows (same `where`, same transaction) just before the soft-delete sets `deletedAt`; `beforeBulkRestore` does the inverse, clearing it as `deletedAt` is cleared. The sibling UPDATE runs on `this` — the concrete model the hook fires for, bound by Sequelize at call time (`runHooks` → `hook.apply(model, ...)`) — rather than the class captured at decoration time. That lets the decorator be declared on an abstract base model (e.g. a shared `BaseParanoid`) and still resolve to the real subclass; the captured constructor would be the (unregistered) base and break with "model not initialized". `hooks: false` avoids hook re-entrancy and keeps the delete from bumping an `@UpdatedBy` column; `silent: true` keeps it from bumping `updatedAt`, so the delete writes exactly `deletedBy` + `deletedAt` (like the native paranoid delete writes only `deletedAt`). No-op when there is no acting user.
DynamicView(options)	Declares a model to be a view whose definition is parameterised per query.
Emittable(_target)	Placeholder for change notifications, which are not implemented.
formatSql(sql)	Reformats a SQL string for logging, when SQL_PRETTIFY is on.
NullableIndex(options)	Declares a pair of partial indices on a nullable column, one for the rows where it is null and one for the rows where it is not.
NullableIndex(target, propertyName, propertyDescriptor)	Declares the pair of partial indices with no options of their own.
UpdatedBy(target, propertyName)	Stamps the decorated column with the acting user id on INSERT and UPDATE, taken from the in-flight IMQ request metadata (`currentMetadata()?.userId`). A property decorator, reusable on any model field. It mirrors `@UpdatedAt`: - on INSERT it is set to the creating actor (just as `updatedAt` is set equal to `createdAt` on insert), without overwriting an explicit value; - on UPDATE it always overwrites with the current actor — "last modified by" must reflect who actually ran the update and not be caller-spoofable. Four hooks are registered, because models are written in four ways: - single INSERT → `beforeCreate` (set-if-empty) - `Model.bulkCreate(records, …)` → `beforeBulkCreate` (set-if-empty on the built instances; a plain `bulkCreate` does not fire `beforeCreate`, so the per-instance hook alone would be bypassed) - instance `save()` / `update()` → `beforeUpdate` (receives the instance) - static `Model.update(values, …)` → `beforeBulkUpdate` (receives options; the values to write live on `options.attributes`, and Sequelize filters the written columns down to `options.fields`, computed before this hook from the caller's values — so an injected field is dropped unless also added there). No-op when there is no acting user (system / unattributed writes).
View(options)	Declares a model to be a database view rather than a table.

Interfaces

Interface	Description
ColumnIndexOptions	The clauses of the `CREATE INDEX` statement that a ColumnIndex declaration turns into.
DataPage	One page of results together with the size of the whole set.
FindOptions	Sequelize's own find options, plus the parameters of a dynamic view.
IAssociated	Describes the association a filter field stands for.
IDynamicViewDefineOptions	Options for a view whose definition is parameterised.
IMQORMOptions	Everything database() needs on its first call.
InitOptions	What a model's `options` actually holds on a model of this package.
IRange	The shape every range filter shares: a start and an end.
IViewDefineOptions	Sequelize's model options, plus the SQL that defines the view.
QueryInterface	Sequelize's query interface, extended with view creation and removal.
ReturningOptions	Widens `returning` so it can name the columns to fetch back, not just ask whether to fetch any.
SyncOptions	Sequelize's own sync options, extended for views and column indices.
ViewParams	Values for a dynamic view's placeholders, keyed by placeholder name.
WithIncludeMap	The shape sequelize's resolved include tree actually has, as this module reads it.

Namespaces

Namespace	Description
query	Turns a caller's serialized query into Sequelize options, and back out as SQL.

Variables

Variable	Description
DB_CONN_STR	`DB_CONN_STR` from the environment, read once when this module loads.
ENUM_ORDER_DIRECTION	The `OrderDirection` values as an `@imqueue/rpc` type description.
FILTER_OPS	Maps each `$`-prefixed filter operator to the Sequelize `Op` symbol it means.
MATCHER	The placeholder pattern as a source string: `@{name}`, where the name is letters, digits and underscores.
RX_MATCHER	MATCHER compiled to find every placeholder in a definition.
RX_NAME_MATCHER	MATCHER compiled to pull the name out of a single placeholder.
SQL_COLORIZE	Whether logged SQL carries ANSI colour. Off by default.
SQL_PRETTIFY	Whether logged SQL is reformatted across multiple lines. Off by default.

Type Aliases

Type Alias	Description
BuildOptions	Options for `build()`, with `returning` widened to accept a column list.
BulkCreateOptions	Options for `bulkCreate()`, with `returning` widened to accept a column list.
CreateOptions	Options for `create()`, with `returning` widened to accept a column list.
FunctionType	The property decorator a decorator factory hands back.
GraphForeachCallback	Called once per vertex during a traversal.
GraphMap	A graph as adjacency lists: each vertex mapped to the vertices it points at.
IModelClass	Names a model class by the instance type it produces.
Modify	Replaces every member of `T` that `R` also declares with `R`'s version of it.
NullableColumnIndexOptions	What NullableIndex accepts: every ColumnIndexOptions field except `expression`, which it sets itself.
QueryOptions	Options for a raw `query()`, with `returning` widened to accept a column list.
SaveOptions	Options for an instance's `save()`, with `returning` widened to accept a column list.
SqlLoggingFunction	A sequelize `logging` callback: the SQL, and the timing when benchmarking is on.
UpdateOptions	Options for the static `update()`, with `returning` widened to accept a column list.
UpsertOptions	Options for `upsert()`, with `returning` widened to accept a column list.