SQLite

This page describes how ts-sql-query integrates with SQLite, including dialect-specific behaviors, configuration options, and available features. It covers the proper setup of a SQLite connection, guidelines for connection management, and advanced behaviors such as UUID handling.

Info

To configure the database dialect, extend the appropriate database connection class when defining your connection. You must choose the correct database type to ensure that the generated SQL queries follow the dialect expected by that database.

Do not share connections between requests

A ts-sql-query connection object — along with the query runner instances passed to its constructor — represents a dedicated connection to the database.

Therefore, you must not share the same connection object between concurrent HTTP requests. Instead, create a new connection object for each request, along with its own query runners.

Even if the query runner internally uses a connection pool, the ts-sql-query connection still represents a single active connection, acquired from the pool. It must be treated as such and never reused across requests.

Usage Example

import { SqliteConnection } from "ts-sql-query/connections/SqliteConnection";

class DBConnection extends SqliteConnection<'DBConnection'> { }

Tip

If you use better-sqlite3 or sqlite-wasm OO1 to connect to the database you can run your queries synchronously. See BetterSqlite3QueryRunner, Sqlite3WasmOO1QueryRunner and Synchronous query runners for more information.

Working with Date and Time

ts-sql-query provides multiple strategies to handle date and time that are compatible with SQLite’s date and time functions. To define the strategy to be used, you must overwrite the getDateTimeFormat function; this function receives as an argument the type of date to handle (date, time, dateTime) and returns the strategy to use for that specific case. In addition, there are three properties (treatUnexpectedIntegerDateTimeAsJulian, treatUnexpectedStringDateTimeAsUTC, and unexpectedUnixDateTimeAreMilliseconds) that allow controlling how unexpected formats are interpreted. Example:

import { SqliteConnection } from "ts-sql-query/connections/SqliteConnection";
import { SqliteDateTimeFormat, SqliteDateTimeFormatType } from "ts-sql-query/connections/SqliteConfiguration";

class DBConnection extends SqliteConnection<'DBConnection'> {
    protected getDateTimeFormat(type: SqliteDateTimeFormatType): SqliteDateTimeFormat {
        switch(type) {
            case 'date':
                return 'localdate as text'
            case 'time':
                return 'UTC as text'
            case 'dateTime':
                return 'Unix time seconds as integer'
        }
    }
    protected treatUnexpectedIntegerDateTimeAsJulian = false
    protected treatUnexpectedStringDateTimeAsUTC = true
}

Date and type strategies

• localdate as text: (default strategy)
  • Dates and time are interpreted to be in the same timezone of the running application.
  • Column type in sqlite: TEXT
  • Date format: YYYY-MM-DD. Example: 2021-09-25
  • Time format: HH:MM:SS.SSS. Example: 18:00:00. In JavaScript, always use 1970-01-01 as the base date when representing a time value.
  • Date time format: YYYY-MM-DD HH:MM:SS.SSS. Example: 2021-09-25 18:00:00
  • Limitation: If you get the unix time in the database you will get the wrong value; the returned value will be like that date is in UTC timezone.
• localdate as text using T separator
  • Dates and time are interpreted to be in the same timezone of the running application.
  • Column type in sqlite: TEXT
  • Date format: YYYY-MM-DD. Example: 2021-09-25
  • Time format: HH:MM:SS.SSS. Example: 18:00:00. In JavaScript, always use 1970-01-01 as the base date when representing a time value.
  • Date time format: YYYY-MM-DDTHH:MM:SS.SSS. Example: 2021-09-25T18:00:00
  • Limitation: If you get the unix time in the database you will get the wrong value; the returned value will be like that date is in UTC timezone.
• UTC as text
  • Dates and time are interpreted to be in the UTC timezone.
  • Column type in sqlite: TEXT
  • Date format: YYYY-MM-DD. Example: 2021-09-25
  • Time format: HH:MM:SS.SSS. Example: 18:00:00. In JavaScript, always use 1970-01-01 as the base date when representing a time value.
  • Date time format: YYYY-MM-DD HH:MM:SS.SSS. Example: 2021-09-25 18:00:00
• UTC as text using T separator:
  • Dates and time are interpreted to be in the UTC timezone.
  • Column type in sqlite: TEXT
  • Date format: YYYY-MM-DD. Example: 2021-09-25
  • Time format: HH:MM:SS.SSS. Example: 18:00:00. In JavaScript, always use 1970-01-01 as the base date when representing a time value.
  • Date time format: YYYY-MM-DDTHH:MM:SS.SSS. Example: 2021-09-25T18:00:00
• UTC as text using Z timezone:
  • Dates and time are interpreted to be in the UTC timezone.
  • Column type in sqlite: TEXT
  • Date format: YYYY-MM-DD. Example: 2021-09-25. No Z is used here because it is not supported by sqlite.
  • Time format: HH:MM:SS.SSSZ. Example: 18:00:00Z. In JavaScript, always use 1970-01-01 as the base date when representing a time value.
  • Date time format: YYYY-MM-DD HH:MM:SS.SSSZ. Example: 2021-09-25 18:00:00Z
• UTC as text using T separator and Z timezone:
  • Dates and time are interpreted to be in the UTC timezone.
  • Column type in sqlite: TEXT
  • Date format: YYYY-MM-DD. Example: 2021-09-25. No Z is used here because it is not supported by sqlite.
  • Time format: HH:MM:SS.SSSZ. Example: 18:00:00Z. In JavaScript, always use 1970-01-01 as the base date when representing a time value.
  • Date time format: YYYY-MM-DDTHH:MM:SS.SSSZ. Example: 2021-09-25T18:00:00Z
• Julian day as real number:
  • Dates and time are expressed as Julian day.
  • Column type in sqlite: REAL
  • Date format: NNNNNNNNNN.5. Example: 2459482.5 (2021-09-25).
  • Time format: 0.NNNNNNNNNN or -0.NNNNNNNNNN. Example: 0.75 (18:00:00). This value is a number between -0.5 to 0.5 that express the time on the first day of the calendar (November 24, 4714 BC). In JavaScript, always use 1970-01-01 as the base date when representing a time value.
  • Date time format: NNNNNNNNNN.NNNNNNNNNN. Example: 2459483.25 (2021-09-25 18:00:00)
• Unix time seconds as integer
  • Dates and time are expressed as the number of seconds from the beginning of the UNIX time (1970-01-01).
  • Column type in sqlite: INTEGER
  • Date format: NNNNNNNNNN. Example: 1632528000 (2021-09-25).
  • Time format: NNNNNNNNNN. Example: 64800 (18:00:00). This value is a number that expresses the number of seconds for the provided time on the first day of the calendar (1970-01-01). In JavaScript, always use 1970-01-01 as the base date when representing a time value.
  • Date time format: NNNNNNNNNN. Example: 1632592800 (2021-09-25 18:00:00)
• Unix time milliseconds as integer
  • Dates and time are expressed as the number of milliseconds from the beginning of the UNIX time (1970-01-01).
  • Column type in sqlite: INTEGER
  • Date format: NNNNNNNNNNNNN. Example: 1632528000000 (2021-09-25).
  • Time format: NNNNNNNNNNNNN. Example: 64800000 (18:00:00). This value is a number that expresses the number of milliseconds for the provided time on the first day of the calendar (1970-01-01). In JavaScript, always use 1970-01-01 as the base date when representing a time value.
  • Date time format: NNNNNNNNNNNNN. Example: 1632592800000 (2021-09-25 18:00:00)

Dealing with different date and time formats coming from the database

When a value is returned from the database that is different from the defined strategy, ts-sql-query tries to parse the value to respect the format returned from the database and respect the semantic of the expected datatype. You can configure the way how to interpret the value when the default behaviour doesn't match with the situation in the database using the following properties:

• treatUnexpectedIntegerDateTimeAsJulian: (default false)
  • When a string representation is expected, but a numeric value is received, if the value is an integer, it is treated as UNIX time; if it has decimals, it is treated as Julian day.
  • The problem with this approach is that any Julian date set at noon is an integer value in JavaScript; then, the value is misunderstood.
  • However, you can force to ignore the UNIX time and always consider the value as Julian day if you set this property to true.
• treatUnexpectedStringDateTimeAsUTC: (default false)
  • When a numeric representation is expected (UNIX time or Julian day), but a string representation is received.
  • If that string representation doesn't have a defined timezone, the value is treated as local date-time (the time zone is the same as the running application).
  • If you set this property to true, you force to treat this case as UTC time.
• unexpectedUnixDateTimeAreMilliseconds: (default false)
  • When a string representation is expected, but a numeric value is received, if the value is an integer, it is treated as UNIX time; if it has decimals, it is treated as Julian day.
  • By default, this unexpected UNIX time is understood as the number of seconds from the beginning of the UNIX time (1970-01-01).
  • If you set this property to true, you force to treat this UNIX time as the number of milliseconds from the beginning of the UNIX time (1970-01-01).

UUID strategies

ts-sql-query offers you different strategies to handle UUIDs in Sqlite:

• uuid-extension: (default strategy) In this case, the UUID is represented and stored as blob data type of length 16. This requires the UUID extension or another compatible implementation (if you use better-sqlite3 you can provide your own one).
• string: In this case, the UUID is represented as string and stored in a column with text data type and length 36 characters.

You can configure the strategy by overriding the uuidStrategy field in your connection class:

import { SqliteConnection } from "ts-sql-query/connections/SqliteConnection";

class DBConnection extends SqliteConnection<'DBConnection'> {
    protected uuidStrategy = 'string' as const
}

Compatibility mode

The compatibility mode prevents the use of syntax introduced in newer versions of SQLite.

The newer syntax are:

• Sqlite 3.30.0 (2019-10-04): Add support for the NULLS FIRST and NULLS LAST syntax in ORDER BY clauses. In compatibility mode, they are emulated.
• Sqlite 3.35.0 (2021-03-12): Add support for the RETURNING clause on DELETE, INSERT, and UPDATE statements. When compatibility mode is disabled, the RETURNING clause is enabled for insert statements.

By default the compatibility mode is enabled. To disable the compatibility mode you must set the compatibilityMode property of the connection to false.

import { SqliteConnection } from "ts-sql-query/connections/SqliteConnection";

class DBConnection extends SqliteConnection<'DBConnection'> {
    protected compatibilityMode = false
}