ts-sql-query

ts-sql-query is a type-safe query builder that provides a way to build dynamic SQL queries in a type-safe way, that means, the TypeScript compiler verifies the queries.

Type-safe SQL means the mistakes writting a query will be detected during the compilation time. With ts-sql-query you don't need to be affraid of change the database, the problems caused by the change will be detected during compilation time.

ts-sql-query supports MariaDB, MySql, Oracle, PostgreSql, Sqlite and SqlServer. Note: this is not an ORM, and the most probably is you don't need one.

ts-sql-query supports Node.js and Bun.

Summary

• Install
• Documentation
• Examples
• Why?
• Basic queries structure
  • Select one row
  • Other options
• Dynamic queries
• See more
• Related projects
• License

Install

Install with npm in Node.js:

$ npm install --save ts-sql-query

Install in Bun:

$ bun install ts-sql-query

ts-sql-query is an ESM-only package and requires Node.js 22 or newer. The public API is the enumerated list of subpaths declared in the package's exports map. Any path not in that list — including abstract base classes, internal utilities, and helpers used to build custom runners — fails with ERR_PACKAGE_PATH_NOT_EXPORTED.

Root entry (aggregated, cross-database use): a single import 'ts-sql-query' re-exports the cross-database surface: the entry points listed below (except where noted), the extras/types and extras/utils symbols, and the dynamic-condition helpers. Database-specific symbols (per-database Connections, every QueryRunner implementation, and IDEncrypter) are intentionally not included in the root entry — you import them directly from their subpath, which keeps the import line database-aware.

// Cross-database, from the root entry
import { Table, Values, CustomBooleanTypeAdapter, dynamicPick,
         extractColumnsFrom, type Connection, type SelectedRow } from 'ts-sql-query'

// Database-specific, from the subpath
import { PostgreSqlConnection } from 'ts-sql-query/connections/PostgreSqlConnection'
import { PgPoolQueryRunner }    from 'ts-sql-query/queryRunners/PgPoolQueryRunner'

Subpath entry points (also available individually):

• ts-sql-query/Connection
• ts-sql-query/Table
• ts-sql-query/View
• ts-sql-query/TypeAdapter
• ts-sql-query/Values
• ts-sql-query/TsSqlError
• ts-sql-query/dynamicCondition

Public connections (one per supported database, plus SqliteConfiguration, which exports the SqliteDateTimeFormat and SqliteDateTimeFormatType type literals used to declare how SQLite stores dates/times — see docs/configuration/supported-databases/sqlite.md):

• ts-sql-query/connections/MariaDBConnection
• ts-sql-query/connections/MySqlConnection
• ts-sql-query/connections/OracleConnection
• ts-sql-query/connections/PostgreSqlConnection
• ts-sql-query/connections/SqlServerConnection
• ts-sql-query/connections/SqliteConfiguration
• ts-sql-query/connections/SqliteConnection

Public query runners (one subpath per file in src/queryRunners/ that has its own documentation page — covers every supported driver plus the general-purpose runners MockQueryRunner, NoopQueryRunner, ConsoleLogQueryRunner, ConsoleLogNoopQueryRunner, InterceptorQueryRunner, LoggingQueryRunner, and the QueryRunner interface module). The full list is in the exports field of package.json.

Public extras:

• ts-sql-query/extras/IDEncrypter
• ts-sql-query/extras/types
• ts-sql-query/extras/utils

TypeScript consumers must use moduleResolution: "node16", "nodenext" or "bundler" for these subpaths to resolve.

Escape hatch for advanced use cases

If you need to reach into anything that isn't on the public list — abstract base classes (AbstractSqlBuilder, AbstractQueryRunner, ManagedTransactionQueryRunner, …), the per-database error mappers under queryRunners/databaseErrorMappers/, the expression/builder internals, etc. — every file in the package remains importable under the ts-sql-query/unsupported/<original/path> prefix:

// Public, stable
import { Table } from 'ts-sql-query' // or 'ts-sql-query/Table'

// Unsupported, no stability guarantees
import { AbstractSqlBuilder } from 'ts-sql-query/unsupported/sqlBuilders/AbstractSqlBuilder'
import { ManagedTransactionQueryRunner } from 'ts-sql-query/unsupported/queryRunners/ManagedTransactionQueryRunner'
import { PostgresErrorMapper } from 'ts-sql-query/unsupported/queryRunners/databaseErrorMappers/PostgresErrorMapper'

Anything imported through unsupported/ may change, break or disappear in any release, including patch releases. The unsupported/ prefix is mandatory — the natural paths (e.g. ts-sql-query/sqlBuilders/...) remain blocked.

Documentation

See the documentation at: https://ts-sql-query.readthedocs.io/

Examples

You can find a complete example using ts-sql-query with PostgreSQL in the file PgExample.ts. You can browse the examples folder to see an example for each supported database using different ways to connect to it.

Why?

There are many libraries available in JavaScript/TypeScript that allows querying a SQL database, but they are typically:

• ORM doesn't allow to take advantage of the full potential of the database.
• String concatenation utilities in the way of query builders.
• Utilities designed without have type-safe criteria.
• Utilities not designed to write dynamic queries in an easy way.

ts-sql-query addresses these inconveniences, providing you with a library that allows you to query the database in a type-safe way, with SQL in mind, and with many helpers to create dynamic queries.

Basic queries structure

Select one row

const customerId = 10;

const customerWithId = connection.selectFrom(tCustomer)
    .where(tCustomer.id.equals(customerId))
    .select({
        id: tCustomer.id,
        firstName: tCustomer.firstName,
        lastName: tCustomer.lastName,
        birthday: tCustomer.birthday
    })
    .executeSelectOne();

The executed query is:

select id as id, first_name as firstName, last_name as lastName, birthday as birthday 
from customer 
where id = $1

The parameters are: [ 10 ]

The result type is:

const customerWithId: Promise<{
    id: number;
    firstName: string;
    lastName: string;
    birthday?: Date;
}>

The executeSelectOne returns one result, but if it is not found in the database an exception will be thrown. If you want to return the result when it is found or null when it is not found you must use the executeSelectNoneOrOne method.

Other options

You can execute the query using:

• executeSelectNoneOrOne(): Promise<RESULT | null>: Execute the select query that returns one o no result from the database
• executeSelectOne(): Promise<RESULT>: Execute the select query that returns one result from the database. If no result is returned by the database an exception will be thrown.
• executeSelectMany(): Promise<RESULT[]>: Execute the select query that returns zero or many results from the database
• executeSelectPage(): Promise<{ data: RESULT[], count: number }>: Execute the select query that returns zero or many results from the database. Select page execute the query twice, the first one to get the data from the database and the second one to get the count of all data without the limit and the offset.
• executeSelectPage<EXTRAS extends {}>(extras: EXTRAS): Promise<{ data: RESULT[], count: number } & EXTRAS>: Execute the select query as a select page, but allows to include extra properties to will be resulting object. If the object provided by argument includes the property count, the query that count the data will be omitted and this value will be used. If the object provided by argument includes the property data, the query that extract the data will be omitted and this value will be used.

Dynamic queries

ts-sql-query offers many commodity methods with name ended with IfValue to build dynamic queries; these methods allow to be ignored when the values specified by argument are null or undefined or an empty string (only when the allowEmptyString flag in the connection is not set to true, that is the default behaviour). When these methods are used in operations that return booleans value, ts-sql-query is smart enough to omit the operation when it is required, even when the operation is part of complex composition with ands and ors.

When you realize an insert or update, you can:

• set a column value conditionally using the method setIfValue
• replace a previously set value during the construction of the query using the method setIfSet or the method setIfSetIfValue
• set a value if it was not previously set during the construction of the query using the method setIfNotSet or the method setIfNotSetIfValue
• ignore a previously set value using the method ignoreIfSet
• don't worry if you end with an update or delete with no where, you will get an error instead of update or delete all rows. You can allow explicitly having an update or delete with no where if you create it using the method updateAllowingNoWhere or deleteAllowingNoWhereFrom respectively

When you realize a select, you can:

• specify in your order by clause that the order must be case insensitive when the column type is string (ignored otherwise). To do it, add insensitive at the end of the ordering criteria/mode
• add a dynamic order by provided by the user without risk of SQL injection and without exposing the internal structure of the database. To build a dynamic order by use the method orderByFromString with the usual order by syntax (and with the possibility to use the insensitive extension), but using as column's name the name of the property in the resulting object
• You can apply order by, limit and offset optionally calling orderByFromStringIfValue, limitIfValue and offsetIfValue

Additionally, you can:

• create a boolean expression that only applies if a certain condition is met, calling the onlyWhen method in the boolean expression. The ignoreWhen method does the opposite.
• create an expression that only applies if a certain condition is met; otherwise, the value will be null, calling the onlyWhenOrNull method in the expression. The ignoreWhenAsNull method does the opposite.
• create a dynamic boolean expression that you can use in a where (by example), calling the dynamicBooleanExpresionUsing method in the connection object.
• create a custom boolean condition from criteria object that you can use in a where (by example), calling the dynamicConditionFor method in the connection object. This functionality is useful when creating a complex search & filtering functionality in the user interface, where the user can apply a different combination of constraints.
• create a query where it is possible to pick the columns to be returned by the query.
• define an optional join in a select query. That join only must be included in the final query if the table involved in the join is used in the final query. For example, a column of the joined table was picked or used in a dynamic where.

const firstNameContains = 'ohn';
const lastNameContains = null;
const birthdayIs = null;
const searchOrderBy = 'name insensitive, birthday asc nulls last';

const searchedCustomers = connection.selectFrom(tCustomer)
    .where(
                tCustomer.firstName.containsIfValue(firstNameContains)
            .or(tCustomer.lastName.containsIfValue(lastNameContains))
        ).and(
            tCustomer.birthday.equalsIfValue(birthdayIs)
        )
    .select({
        id: tCustomer.id,
        name: tCustomer.firstName.concat(' ').concat(tCustomer.lastName),
        birthday: tCustomer.birthday
    })
    .orderByFromString(searchOrderBy)
    .executeSelectMany();

The executed query is:

select id as id, first_name || $1 || last_name as name, birthday as birthday 
from customer 
where first_name like ('%' || $2 || '%') 
order by lower(name), birthday asc nulls last

The parameters are: [ ' ', 'ohn' ]

The result type is:

const customerWithId: Promise<{
    id: number;
    name: string;
    birthday?: Date;
}[]>

See more

See more information at: https://ts-sql-query.readthedocs.io/

Related projects

• ts-sql-codegen: Utility that generates table mapper classes for ts-sql-query by inspecting a database through tbls.

License

MIT