Knex.js Date object serialization across dialect drivers

Direct Answer

Knex.js does not provide a built-in, dialect-agnostic setting to force UTC serialization of Date objects. It passes Date instances directly to the underlying driver, so behavior depends on each driver’s configuration and the database column type. To achieve cross-dialect consistency you must configure each driver explicitly and store values in timezone-aware columns.

Driver-Specific UTC Configuration

PostgreSQL (pg driver)

Set the connection option timezone: 'Z' (or 'UTC') in the Knex client configuration. This tells the driver to send timestamps in UTC regardless of the server’s timezone.

const knex = require('knex')({
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'user',
    password: 'pass',
    database: 'db',
    timezone: 'Z'   // forces UTC serialization
  }
});

MySQL (mysql2 driver)

Use the timezone option with '+00:00' or 'Z' to convert all Date objects to UTC before sending.

const knex = require('knex')({
  client: 'mysql2',
  connection: {
    host: 'localhost',
    user: 'user',
    password: 'pass',
    database: 'db',
    timezone: '+00:00'
  }
});

Microsoft SQL Server (tedious driver)

Enable options.useUTC: true in the connection configuration.

const knex = require('knex')({
  client: 'mssql',
  connection: {
    server: 'localhost',
    user: 'user',
    password: 'pass',
    database: 'db',
    options: { useUTC: true }
  }
});

Recommended Practice for Cross-Dialect Consistency

1. Convert to ISO UTC strings before passing to Knex: date.toISOString() yields a string like "2026-10-11T20:15:41.889Z" that all drivers will interpret as UTC.
2. Use timezone-aware column types:
   • PostgreSQL: TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ)
   • MySQL: DATETIME(6) with the connection timezone set to UTC, or TIMESTAMP (which converts to UTC on storage)
   • SQL Server: DATETIMEOFFSET or DATETIME2 with UTC offsets
3. Verify by round-tripping: Insert a known UTC date, then query the raw value and compare it to the original ISO string.

Verification Steps

• Confirm exact driver versions (e.g., pg@^8.0, mysql2@^3.0, tedious@^15.0).
• Apply the driver-specific timezone options shown above.
• Insert a test row with new Date('2026-01-01T00:00:00.000Z') and read it back with a raw SQL query (e.g., SELECT column FROM table).
• Check that the returned value matches the input ISO string exactly.
• Inspect the column definition to ensure it is timezone-aware.

Assumptions and Uncertainty

The driver options listed are stable in the major versions noted, but they can change between major releases. Always consult the current driver documentation for your exact versions. Even with driver-level UTC settings, the database server’s timezone can affect stored values if the column type is not timezone-aware.

One Missing Diagnostic Detail

Please provide the exact driver versions you are using and the column types (e.g., TIMESTAMPTZ, DATETIME(6), DATETIMEOFFSET) in each target database. This will confirm whether the recommended settings are sufficient or if additional server-side configuration is required.