or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

column-operations.mddialect-support.mdfunctions.mdindex.mdquery-building.mdtable-operations.md
tile.json

tessl/npm-sql

SQL query builder for Node.js supporting multiple dialects with fluent, chainable API

Workspace
tessl
Visibility
Public
Created
Last updated
Describes
npmpkg:npm/sql@0.78.x

To install, run

npx @tessl/cli install tessl/npm-sql@0.78.0

index.mddocs/

SQL

SQL is a powerful Node.js SQL query builder that enables developers to construct SQL statements programmatically using JavaScript syntax. It supports multiple SQL dialects (PostgreSQL, MySQL, Microsoft SQL Server, Oracle, and SQLite) with automatic parameterization for security and provides a fluent, chainable API for building complex queries while maintaining type safety through TypeScript definitions.

Package Information

  • Package Name: sql
  • Package Type: npm
  • Language: JavaScript with TypeScript definitions
  • Installation: npm install sql
  • Version: 0.78.0

Core Imports

const sql = require('sql');

For TypeScript projects:

import * as sql from 'sql';
// or destructured imports
import { create } from 'sql';

Basic Usage

const sql = require('sql');

// Set the SQL dialect (optional, defaults to 'postgres')
sql.setDialect('postgres'); // 'postgres', 'mysql', 'mssql', 'oracle', 'sqlite'

// Define tables
const user = sql.define({
  name: 'user',
  columns: ['id', 'name', 'email', 'lastLogin']
});

const post = sql.define({
  name: 'post', 
  columns: ['id', 'userId', 'date', 'title', 'body']
});

// Build a simple query
const query = user.select(user.star()).from(user).toQuery();
console.log(query.text); // SELECT "user".* FROM "user"
console.log(query.values); // []

// Build a complex query with conditions
const complexQuery = user
  .select(user.id, user.name)
  .from(user)
  .where(
    user.name.equals('John').and(user.id.gt(5))
  )
  .order(user.name.asc)
  .limit(10)
  .toQuery();

// Parameterized query output
console.log(complexQuery.text); 
// SELECT "user"."id", "user"."name" FROM "user" 
// WHERE (("user"."name" = $1) AND ("user"."id" > $2)) 
// ORDER BY "user"."name" ASC LIMIT $3

console.log(complexQuery.values); // ['John', 5, 10]

Architecture

SQL is built around several core components:

  • Sql Class: Main interface for creating instances and setting dialects
  • Table Definitions: Structured table schemas with column definitions and relationships
  • Query Builder: Fluent API for constructing SQL statements through method chaining
  • Column Operations: Rich set of comparison, mathematical, and logical operations
  • Dialect System: Multi-database support with dialect-specific optimizations
  • Node System: Internal AST representation for composable query fragments
  • Type System: Full TypeScript definitions for type-safe query building

Capabilities

Core SQL Instance

Main SQL instance creation and configuration for multi-dialect support.

// Default export - pre-configured SQL instance (postgres dialect)
const sql = require('sql');

// Create new SQL instances
function create(dialect?: string, config?: object): Sql;

// Static exports
class Sql;
class Table;

class Sql {
  constructor(dialect?: string, config?: object);
  
  // Core configuration
  setDialect(dialect: SQLDialect, config?: object): Sql;
  dialectName: string;
  dialect: DialectImplementation;
  config: object;
  
  // Table operations
  define(tableDefinition: TableDefinition): Table;
  
  // Query building
  select(...columns: any[]): Query;
  array(...values: any[]): ArrayCall;
  interval(timeComponents: object): Interval;
  constant(value: any): Column;
  
  // Function creation
  functionCallCreator(name: string): (...args: any[]) => FunctionCall;
  functions: StandardFunctions;
  function: (functionName: string) => (...args: any[]) => FunctionCall;
}

// Available dialects
type SQLDialect = 'postgres' | 'mysql' | 'mssql' | 'oracle' | 'sqlite';

Table Operations

Table definition, column management, and basic CRUD operations for database schema modeling.

function define(config: TableDefinition): Table;

interface TableDefinition {
  name: string;
  schema?: string;
  columns: (string | ColumnDefinition)[];
  foreignKeys?: ForeignKeyDefinition[];
  isTemporary?: boolean;
}

class Table {
  select(...columns: any[]): Query;
  insert(data: object | object[]): Query;
  update(data: object): Query;
  delete(conditions?: any): Query;
  create(): Query;
  drop(): Query;
}

Table Operations

Query Building

Comprehensive query construction with method chaining, joins, subqueries, and advanced SQL features.

class Query {
  select(...columns: any[]): Query;
  from(...tables: any[]): Query;
  where(...conditions: any[]): Query;
  join(table: Table): JoinQuery;
  leftJoin(table: Table): JoinQuery;
  order(...criteria: any[]): Query;
  group(...columns: any[]): Query;
  having(...conditions: any[]): Query;
  limit(count: number): Query;
  offset(count: number): Query;
  toQuery(): { text: string; values: any[] };
}

Query Building

Column Operations

Rich column expressions including comparisons, mathematical operations, string functions, and type casting.

class Column {
  // Comparison operations
  equals(value: any): BinaryExpression;
  notEquals(value: any): BinaryExpression;
  gt(value: any): BinaryExpression;
  gte(value: any): BinaryExpression;
  lt(value: any): BinaryExpression;
  lte(value: any): BinaryExpression;
  
  // String operations
  like(pattern: string): BinaryExpression;
  ilike(pattern: string): BinaryExpression;
  
  // Set operations
  in(values: any[]): BinaryExpression;
  notIn(values: any[]): BinaryExpression;
  
  // Null checks
  isNull(): UnaryExpression;
  isNotNull(): UnaryExpression;
}

Column Operations

SQL Functions

Built-in SQL functions for aggregation, string manipulation, date operations, and mathematical calculations.

interface StandardFunctions {
  // Aggregate functions
  AVG(column: Column): FunctionCall;
  COUNT(column?: Column): FunctionCall;
  MAX(column: Column): FunctionCall;
  MIN(column: Column): FunctionCall;
  SUM(column: Column): FunctionCall;
  
  // String functions
  LOWER(column: Column): FunctionCall;
  UPPER(column: Column): FunctionCall;
  LENGTH(column: Column): FunctionCall;
  SUBSTR(column: Column, start: number, length?: number): FunctionCall;
  
  // Date functions
  CURRENT_TIMESTAMP(): FunctionCall;
  YEAR(column: Column): FunctionCall;
  MONTH(column: Column): FunctionCall;
  DAY(column: Column): FunctionCall;
}

SQL Functions

Dialect Support

Multi-database compatibility with dialect-specific features and optimizations.

// Supported SQL dialects
type Dialect = 'postgres' | 'mysql' | 'mssql' | 'oracle' | 'sqlite';

// Set dialect globally
sql.setDialect(dialect: Dialect, config?: object): void;

// Create dialect-specific instance
const mysqlSql = sql.create('mysql');
const postgresSql = sql.create('postgres');

Dialect Support

Advanced Query Features

Additional query capabilities for complex SQL operations and database-specific features.

// Time intervals for date/time operations
class Interval {
  constructor(timeComponents: object);
  years: number;
  months: number;
  days: number;
  hours: number;
  minutes: number;
  seconds: number;
}

// Function calls for SQL functions
class FunctionCall {
  constructor(name: string, args: any[]);
  name: string;
  as(alias: string): FunctionCall;
  // Inherits all Value Expression methods
}

// Array literals for SQL arrays
class ArrayCall {
  constructor(values: any[]);
  as(alias: string): ArrayCall;
  // Inherits all Value Expression methods
}

// Query result structures
interface QueryResult {
  text: string;        // SQL string with parameter placeholders
  values: any[];       // Array of parameter values
}

interface NamedQueryResult extends QueryResult {
  name: string;        // Query name for identification
}

Types

interface TableDefinition {
  name: string;
  schema?: string;
  columns: (string | ColumnDefinition)[];
  foreignKeys?: ForeignKeyDefinition[];
  isTemporary?: boolean;
  snakeToCamel?: boolean;
  columnWhiteList?: boolean;
}

interface ColumnDefinition {
  name: string;
  property?: string;
  dataType?: string;
  primaryKey?: boolean;
  notNull?: boolean;
  unique?: boolean;
  defaultValue?: any;
  references?: {
    table: string;
    column: string;
    onDelete?: ReferentialAction;
    onUpdate?: ReferentialAction;
  };
}

interface ForeignKeyDefinition {
  table: string;
  columns: string[];
  refColumns: string[];
  onDelete?: ReferentialAction;
  onUpdate?: ReferentialAction;
}

type ReferentialAction = 'restrict' | 'cascade' | 'no action' | 'set null' | 'set default';

interface QueryResult {
  text: string;
  values: any[];
}

interface NamedQueryResult extends QueryResult {
  name: string;
}