1. What It Is and Why You Need It

\n ts-sql-query is a powerful library for TypeScript that allows you to write SQL queries with full type support (type-safe). It provides a convenient DSL (Domain-Specific Language) for building SQL queries, with each query checked by the TypeScript compiler against the database schema. This means errors in table names, field names, or data types will be caught at compile time, not at runtime.\n

\n Why is this needed? In traditional approaches (raw SQL strings or ORM), developers often face problems: typos in column names, type mismatches, difficulty with refactoring. ts-sql-query solves these problems by providing strict typing for all operations: SELECT, INSERT, UPDATE, DELETE, JOIN, subqueries, aggregate functions, and much more. The library supports many DBMS (PostgreSQL, MySQL, SQLite, MariaDB, MSSQL) and can work both directly with drivers and through connection pools.\n

\n Unlike ORMs (e.g., TypeORM or Prisma), ts-sql-query does not hide SQL behind abstractions but makes it part of the TypeScript code. You still write SQL, but with autocompletion, type checking, and no risk of SQL injection. It is an ideal choice for projects where performance and full control over queries are important, but you still want to leverage the benefits of static typing.\n

2. Installation

Installation is done via npm. You will need to install the library itself and the driver for your database.

# Install the main library\nnpm install ts-sql-query\n\n# Install the driver for PostgreSQL (example)\nnpm install pg\n\n# Install the driver for MySQL\nnpm install mysql2\n\n# Install the driver for SQLite\nnpm install better-sqlite3\n\n# Install the driver for SQLite (async)\nnpm install sql.js\n\n# Install types for Node.js (if using)\nnpm install --save-dev @types/node

After installing the driver, you need to create a connection to the database. ts-sql-query provides adapters for each driver.

3. Quick Start — Minimal Working Example

Let's look at an example for PostgreSQL. We'll create a simple users table and execute a SELECT query.

import { createPool } from 'pg';\nimport { PostgreSqlConnection } from 'ts-sql-query/connections/PostgreSqlConnection';\n\n// 1. Create a connection pool\nconst pool = createPool({\n    host: 'localhost',\n    port: 5432,\n    database: 'testdb',\n    user: 'user',\n    password: 'password',\n});\n\n// 2. Create a connection class\nclass DBConnection extends PostgreSqlConnection {\n    // Methods for working with tables can be defined here\n}\n\n// 3. Create a connection instance\nconst connection = new DBConnection(pool);\n\n// 4. Define a type for the users table\ninterface User {\n    id: number;\n    name: string;\n    email: string;\n    age: number;\n}\n\n// 5. Create an object for working with the table\nconst users = connection.table('users').columns({\n    id: 'id',\n    name: 'name',\n    email: 'email',\n    age: 'age',\n});\n\n// 6. Execute the query\nasync function getUsers() {\n    const query = connection\n        .selectFrom(users)\n        .select(users.id, users.name, users.email)\n        .where(users.age.greaterThan(18))\n        .orderBy(users.name, 'asc');\n\n    const result = await query.executeSelectMany();\n    return result;\n}\n\ngetUsers().then(users => {\n    console.log('Users:', users);\n}).catch(err => {\n    console.error('Error:', err);\n});

In this example, we created