Raw Queries & Query Building

Raw Queries & Query Building Drop down to hand-written SQL with prorm.query(), bind values safely with replacements, and inject trusted SQL fragments with prorm.literal() — without giving up prorm's escaping and result shaping. Most of the time you'll reach for the model API (findAll, create, update) or the typed query builder. But when you need a database-specific statement, a hairy analytical query, or a PRAGMA/EXPLAIN, prorm gives you a first-class escape hatch. prorm.query() runs arbitrary SQL, applies your replacements, infers the statement type, and returns results already shaped for that type. Running a raw query prorm.query(sql, options?) executes a SQL string against the connected dialect and returns the results. The shape of the return value depends on the query type (see Result shaping). You never build SQL by concatenating user input. Instead you leave placeholders in the string and pass values through the replacements option — prorm escapes them for the active dialect before the query runs. Bind parameters & replacements The replacements option accepts either an object (for named :param placeholders) or an array (for positional ? placeholders). Values are escaped by the dialect, so strings are quoted, null/undefined become NULL, booleans become 1/0, Date objects are formatted, and Buffers are hex-encoded. Named replacements (:param) A named parameter that appears in the SQL but is missing from the replacements object throws Replacement parameter :name is not defined, so typos surface immediately rather than producing broken SQL. Positional replacements (?) Positional ? markers are filled left-to-right from the array. ? characters inside single- or double-quoted string literals are left untouched, so WHERE note = 'is it? maybe' is safe. Arrays expand for IN clauses When a replacement value is itself an array, it is expanded into a parenthesized list — ideal for IN (...): Objects (non-Date, non-Buffer) are serialized to JSON and escaped as strings, which is handy for JSON columns. Choosing the query type prorm inspects the leading keyword of your SQL to decide how to process the result. A statement starting with SELECT is treated as a SELECT, INSERT as INSERT (or UPSERT when it contains ON CONFLICT, ON DUPLICATE KEY, or INSERT OR REPLACE), UPDATE as UPDATE, DELETE as DELETE, and PRAGMA/EXPLAIN/anything else as RAW. When the inference is wrong — for example a CTE that begins with WITH but ultimately selects — set type explicitly using the QueryTypes enum (or its string equivalent): QueryTypes includes SELECT, INSERT, UPDATE, DELETE, BULKDELETE, BULKINSERT, UPSERT, VERSION, SHOWTABLES, DESCRIBE, and RAW. Result shaping The return type of prorm.query() follows the (inferred or explicit) query type: Query type | Return value | --- | --- | SELECT, SHOWTABLES, DESCRIBE, VERSION | unknown[] — an array of row objects | INSERT | [rows, created] — inserted rows plus a boolean | UPDATE, DELETE, BULKDELETE, UPSERT | number — affected row count | BULKINSERT | number — inserted row count | RAW (default) | unknown[] — raw rows | Mapping rows to model instances For SELECT-style queries you can hydrate the plain rows into full model instances by passing a model and mapToModel: true. The returned objects are real ModelInstances (with instance methods, not isNewRecord), rather than plain objects. Use raw: true when you explicitly want untouched driver output with no model instantiation. Other query options prorm.query() accepts several execution controls alongside type and replacements: logging — true logs to the console, false silences it, or pass a (sql, timing) => void callback. benchmark — force timing output even when logging is otherwise off. retry — retry up to max times when the error message matches one of the match patterns. transaction — bind the query to a Transaction you already opened. Every executed query also emits a query event (and a slowQuery event when it exceeds the configured slowQueryThreshold), so you can hook centralized instrumentation. prorm.literal() — trusted raw SQL fragments prorm.literal(sql) wraps a string so prorm splices it into generated SQL verbatim, instead of quoting it as a value. It returns a Literal object usable inside model options — default values, attributes, where, and update payloads. Because the SQL is injected as-is, only pass literals you construct yourself, never user input. Expression helpers: fn, col, cast, where For structured expressions that stay dialect-aware, prefer the typed helpers over hand-written literals. They exist both as methods on the prorm instance and as standalone functions importable from prorm. The same helpers are available as free functions, which compose more naturally in shared code: Lower-level query-builder helpers Under the model layer sits a standalone SQL compiler you can drive directly when you want a SQL string without executing it — for building statements to feed into prorm.query(), tests, or migrations. createQuickQuery() returns thin wrappers around the compiler for the common clauses. These live in the internal query-builders module (imported by relative path, not from the package root). Each of select/insert/update/delete returns a { sql, values } pair produced by the underlying SqlCompiler; where, order, and limitOffset return the corresponding clause fragment. For full control, createQueryBuilder({ parameterized, paramChar }) hands back the raw sqlCompiler instance whose compileSelect, compileInsert, compileUpdate, compileDelete, compileWhere, compileOrder, and compileLimitOffset methods accept richer option objects (joins, CTEs, grouping, having, pivots). Guidelines Never interpolate user input into SQL. Use replacements for values and reserve prorm.literal() for SQL you author yourself. Let prorm infer the type, override when it's wrong. Set type for CTEs or statements whose leading keyword doesn't match their behavior. Prefer typed helpers (fn, col, cast, and the query builder) over raw string SQL where a dialect-aware equivalent exists — you keep portability and escaping. Reach for prorm.query() for genuinely database-specific features (PRAGMA, vendor extensions, complex analytics) that the model API doesn't cover. Related reading Core concepts — the reading path these belong to Going further — the specialised material