SQL Select TS Query Builder

Common errors

• TS2339: Property 'nonExistentColumn' does not exist on type 'Table<User>'

  cause Attempting to reference a column name that is not defined in the TypeScript interface associated with the table.
  fix Ensure the column name exactly matches a property in the interface provided to `table<T>`, or add the missing property to the interface.

• SyntaxError: require is not defined in ES module scope

  cause Attempting to use CommonJS `require()` syntax to import modules from `sql-select-ts` in an ES Module environment (or when Node.js is configured for ESM).
  fix Use ES Module `import` syntax: `import { select, column } from 'sql-select-ts';`

• TypeError: select is not a function

  cause Incorrectly attempting a default import of `select` or a CommonJS `require` of a named export.
  fix Ensure `select` is imported as a named export: `import { select } from 'sql-select-ts';` (not `import select from 'sql-select-ts'`).

Warnings

• gotcha Using the `raw()` function directly allows for arbitrary SQL injection if user-supplied input is not properly sanitized, bypassing the type safety and protective mechanisms of the query builder.
  Fix: Always ensure that any data passed into `raw()` functions is either hardcoded or strictly sanitized/parameterized if derived from user input. Prefer using the type-safe builder methods where possible.
• gotcha While `sql-select-ts` is database-agnostic, it generates standard SQL. Specific database dialects (e.g., PostgreSQL, MySQL, SQL Server) may have unique functions or syntax not directly supported by the builder's fluent API. These will require the use of `raw()` expressions.
  Fix: Consult your specific database's documentation for non-standard features. Employ `raw()` for such constructs, carefully testing the generated SQL against your target database.
• gotcha When using `.as()` for column aliases, especially in conjunction with `raw()` expressions, the inferred TypeScript type of the resulting query's output may become less specific or require explicit type assertions downstream.
  Fix: For complex `raw()` expressions with aliases, consider defining explicit interfaces or types for the result set, or use type assertions (`as MyResultType`) where the type inference might fall short to maintain strict type safety.

Install

• npm install sql-select-ts npm
• yarn add sql-select-ts yarn
• pnpm add sql-select-ts pnpm

Imports

• select
  wrong:

  import select from 'sql-select-ts'

  correct:

  import { select } from 'sql-select-ts'

  The primary function to start building a SELECT query; it is a named export.
• column
  wrong:

  const column = require('sql-select-ts').column

  correct:

  import { column } from 'sql-select-ts'

  Used to reference database columns with type safety. This library primarily uses ES Module exports.
• table

  import { table } from 'sql-select-ts'

  Used to define and type-associate database tables within the query builder.
• raw

  import { raw } from 'sql-select-ts'

  For injecting raw SQL strings, to be used cautiously for security reasons.

Quickstart

Demonstrates building a multi-table SELECT query with aliased columns, aggregates, joins, complex WHERE clauses, grouping, ordering, and pagination, then converts it to a raw SQL string.

import { select, column, table, raw } from 'sql-select-ts';

interface User {
  id: number;
  name: string;
  email: string;
  createdAt: Date;
}

interface Post {
  id: number;
  userId: number;
  title: string;
}

const users = table<User>('users');
const posts = table<Post>('posts');

// Build a complex SELECT query with joins, conditions, grouping, and ordering
const query = select(
  column(users.id),
  column(users.name).as('userName'),
  column(users.email),
  raw('COUNT(posts.id)').as('postCount')
)
  .from(users)
  .leftJoin(posts)
  .on(column(users.id).eq(column(posts.userId)))
  .where(column(users.createdAt).gt(raw("NOW() - INTERVAL '1 year'")))
  .groupBy(column(users.id), column(users.name), column(users.email))
  .orderBy(column(users.name).asc())
  .limit(10)
  .offset(0);

const queryString = query.toSQL();
console.log(queryString);
/*
Expected output:
SELECT users.id, users.name AS userName, users.email, COUNT(posts.id) AS postCount
FROM users
LEFT JOIN posts ON users.id = posts.userId
WHERE users.createdAt > NOW() - INTERVAL '1 year'
GROUP BY users.id, users.name, users.email
ORDER BY users.name ASC
LIMIT 10 OFFSET 0
*/