TimescaleDB Dialect

TimescaleDB Dialect Reference for the timescaledb SQL dialect. Source: timescaledb Tests: timescaledb, timescaledb-crud 1. Overview TimescaleDB is not a separate database engine. It is a PostgreSQL extension (CREATE EXTENSION timescaledb) that layers time-series capabilities on top of a stock PostgreSQL server. Because of that, TimescaleDBDialect extends PostgresDialect and reuses the exact same driver, wire protocol, and default port: Base class: PostgresDialect (src/dialects/postgres/index.ts) Driver / library: 'pg' — inherited unchanged from the Postgres base Default port: 5432 — set in the TimescaleDBDialect constructor name: reports 'timescaledb' at runtime The subclass overrides only the dialect name and adds a handful of thin, pure synchronous SQL builders for TimescaleDB-specific features. Every standard SQL operation — CRUD, DDL, upserts, joins, pagination, type mapping — is plain PostgreSQL, inherited without modification. See Inherited PostgreSQL behavior. Identity, verified by tests: Implementation note on name: the base types name as the string literal 'postgres', so the override is written readonly name = 'timescaledb' as unknown as 'postgres'. The runtime value is 'timescaledb'; the cast only satisfies the property-override type check without editing the base class. 2. Connection Because TimescaleDB speaks the PostgreSQL wire protocol through the same pg driver, connection options are identical to PostgreSQL (TimescaleDBDialectOptions is an alias of PostgresDialectOptions). The only difference is the constructor defaults port to 5432 (still overridable). TimescaleDB features require the extension to be enabled once per database. The dialect exposes a dedicated synchronous SQL builder for this: The inherited async createExtension() from the Postgres base also works and emits the same statement: 3. Time-series helpers These methods are pure, synchronous SQL builders — they return the SQL string so callers can run it through the inherited query(). They do not touch the database themselves. All string arguments are escaped through the inherited escape() / escapeId() helpers (single quotes are doubled, identifiers are double-quoted). createHypertable(table, timeColumn, opts?) Builds a SELECT createhypertable(...) call that converts a regular table into a hypertable partitioned on a time column. Options (CreateHypertableOptions): chunkTimeInterval?: string | number — default '7 days'. A string is emitted as a PostgreSQL interval literal (chunktimeinterval => INTERVAL '...'); a number is emitted as a bare integer with no INTERVAL keyword (for hypertables partitioned on an integer/bigint time column). ifNotExists?: boolean — appends ifnotexists => TRUE. migrateData?: boolean — appends migratedata => TRUE (moves existing rows into chunks). Custom interval: With flags: Integer-time hypertable (bare integer, no INTERVAL): Single quotes in arguments are escaped defensively — "o'brien" produces createhypertable('o''brien', 'time', ...). enableCompression(table, opts?) Builds an ALTER TABLE ... SET (timescaledb.compress, ...) statement enabling native compression on a hypertable. Options (EnableCompressionOptions): segmentBy?: string | string[] → timescaledb.compresssegmentby. An array is joined into a single comma-separated literal. orderBy?: string | string[] → timescaledb.compressorderby (e.g. 'time DESC'). The table name is quoted via quoteTable(). Array forms are joined into one literal: addCompressionPolicy(table, olderThan) Builds a SELECT addcompressionpolicy(...) that schedules automatic compression of chunks older than olderThan. The table must have compression enabled first (see enableCompression). A string is a PostgreSQL interval literal; a number is a bare integer threshold (for integer-time hypertables). addRetentionPolicy(table, olderThan) Builds a SELECT addretentionpolicy(...) that schedules automatic dropping of chunks older than olderThan. Same string/number interval rules as above. createContinuousAggregate(viewName, query) Builds a CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous) AS ... statement for a continuous aggregate — a materialized view that TimescaleDB refreshes incrementally as new data arrives. The view name is double-quoted; the query is inserted verbatim (typically built with timebucket(...)). 4. Inherited PostgreSQL behavior TimescaleDB adds nothing to standard SQL generation — all CRUD and DDL is inherited from PostgresDialect unchanged. This means double-quoted identifiers, $N positional parameters, and PostgreSQL ON CONFLICT upserts. The following are all verified against TimescaleDBDialect in timescaledb-crud.test.ts. INSERT { returning: true } appends RETURNING . Multi-row bulkInsert() is also inherited (it routes its SQL through query()), producing INSERT INTO "readings" ("deviceid", "temperature") VALUES with one parenthesised value tuple per row. SELECT WHERE with $or / operators ($gt) → WHERE ... OR ..., "temperature" > $N order / limit / offset → ORDER BY "temperature" DESC, LIMIT 10, OFFSET 20 include with required: true → INNER JOIN onto "devices" "device" attributes accept raw expressions such as "timebucket('1 hour', time) AS bucket", with group / having producing GROUP BY / HAVING — useful for time-series aggregation. UPDATE / DELETE UPSERT (ON CONFLICT) conflictFields is required — omitting it throws conflictFields is required (inherited PostgreSQL safeguard). DDL All routed through the inherited async methods (verified via a query() stub): Pagination is inherited too: dialect.buildLimitOffset(5, 15) → ' LIMIT 5 OFFSET 15'. Inherited PostgreSQL quirks These are behaviors of the Postgres base builder, surfaced here only for accuracy — they are not TimescaleDB-specific and are documented as-is in the tests: bulkInsert placeholder numbering is non-sequential across rows (the base advances values.length while emitting a row). buildUpdateQuery does not re-offset the WHERE placeholder past the SET params (it can emit $1 again); the ordered values array is what the driver actually binds. include joins omit a space between the join keyword and the table identifier (e.g. INNER JOIN"devices"). 5. Type mapping Type mapping is identical to PostgreSQL — getDataTypeSql() is inherited unchanged. For example (verified on TimescaleDBDialect): Column types like TIMESTAMPTZ and DOUBLE PRECISION pass straight through, as they do in the Postgres dialect. Refer to the PostgreSQL dialect documentation for the full type table — there are no TimescaleDB overrides. 6. Not-yet-verified Be honest about the boundary of what has been tested: SQL-generation verified (no database required). Every method and inherited behavior above is exercised by connection-free unit tests that assert the exact emitted SQL string. The time-series builders are pure functions, and the CRUD/DDL tests either call the pure { sql, values } builders directly or capture SQL via a stubbed query(). No real database is touched anywhere in the test suite. Requires a live TimescaleDB (not yet verified). The following are not covered by the current tests and would need a running TimescaleDB instance: Actually executing CREATE EXTENSION timescaledb and confirming the extension loads. Executing createhypertable(...) and confirming a real hypertable is created and partitioned into chunks. Runtime behavior of compression (enableCompression + addcompressionpolicy), retention policies (addretentionpolicy), and continuous aggregates (CREATE MATERIALIZED VIEW ... WITH (timescaledb.continuous)) — including whether the generated SQL is accepted by the server for a given schema and interval type, and whether background jobs run as scheduled. End-to-end correctness of the inherited PostgreSQL quirks noted above against a real pg connection. In short: the dialect is confirmed to generate the correct TimescaleDB SQL; it has not been confirmed to execute against a live TimescaleDB server. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL