0
# SQL
1

2
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.
3

4
## Package Information
5

6
- **Package Name**: sql
7
- **Package Type**: npm
8
- **Language**: JavaScript with TypeScript definitions
9
- **Installation**: `npm install sql`
10
- **Version**: 0.78.0
11

12
## Core Imports
13

14
```javascript
15
const sql = require('sql');
16
```
17

18
For TypeScript projects:
19

20
```typescript
21
import * as sql from 'sql';
22
// or destructured imports
23
import { create } from 'sql';
24
```
25

26
## Basic Usage
27

28
```javascript
29
const sql = require('sql');
30

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

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

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

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

50
// Build a complex query with conditions
51
const complexQuery = user
52
  .select(user.id, user.name)
53
  .from(user)
54
  .where(
55
    user.name.equals('John').and(user.id.gt(5))
56
  )
57
  .order(user.name.asc)
58
  .limit(10)
59
  .toQuery();
60

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

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

70
## Architecture
71

72
SQL is built around several core components:
73

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

82
## Capabilities
83

84
### Core SQL Instance
85

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

88
```javascript { .api }
89
// Default export - pre-configured SQL instance (postgres dialect)
90
const sql = require('sql');
91

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

95
// Static exports
96
class Sql;
97
class Table;
98

99
class Sql {
100
  constructor(dialect?: string, config?: object);
101
  
102
  // Core configuration
103
  setDialect(dialect: SQLDialect, config?: object): Sql;
104
  dialectName: string;
105
  dialect: DialectImplementation;
106
  config: object;
107
  
108
  // Table operations
109
  define(tableDefinition: TableDefinition): Table;
110
  
111
  // Query building
112
  select(...columns: any[]): Query;
113
  array(...values: any[]): ArrayCall;
114
  interval(timeComponents: object): Interval;
115
  constant(value: any): Column;
116
  
117
  // Function creation
118
  functionCallCreator(name: string): (...args: any[]) => FunctionCall;
119
  functions: StandardFunctions;
120
  function: (functionName: string) => (...args: any[]) => FunctionCall;
121
}
122

123
// Available dialects
124
type SQLDialect = 'postgres' | 'mysql' | 'mssql' | 'oracle' | 'sqlite';
125
```
126

127
### Table Operations
128

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

131
```javascript { .api }
132
function define(config: TableDefinition): Table;
133

134
interface TableDefinition {
135
  name: string;
136
  schema?: string;
137
  columns: (string | ColumnDefinition)[];
138
  foreignKeys?: ForeignKeyDefinition[];
139
  isTemporary?: boolean;
140
}
141

142
class Table {
143
  select(...columns: any[]): Query;
144
  insert(data: object | object[]): Query;
145
  update(data: object): Query;
146
  delete(conditions?: any): Query;
147
  create(): Query;
148
  drop(): Query;
149
}
150
```
151

152
[Table Operations](./table-operations.md)
153

154
### Query Building
155

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

158
```javascript { .api }
159
class Query {
160
  select(...columns: any[]): Query;
161
  from(...tables: any[]): Query;
162
  where(...conditions: any[]): Query;
163
  join(table: Table): JoinQuery;
164
  leftJoin(table: Table): JoinQuery;
165
  order(...criteria: any[]): Query;
166
  group(...columns: any[]): Query;
167
  having(...conditions: any[]): Query;
168
  limit(count: number): Query;
169
  offset(count: number): Query;
170
  toQuery(): { text: string; values: any[] };
171
}
172
```
173

174
[Query Building](./query-building.md)
175

176
### Column Operations
177

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

180
```javascript { .api }
181
class Column {
182
  // Comparison operations
183
  equals(value: any): BinaryExpression;
184
  notEquals(value: any): BinaryExpression;
185
  gt(value: any): BinaryExpression;
186
  gte(value: any): BinaryExpression;
187
  lt(value: any): BinaryExpression;
188
  lte(value: any): BinaryExpression;
189
  
190
  // String operations
191
  like(pattern: string): BinaryExpression;
192
  ilike(pattern: string): BinaryExpression;
193
  
194
  // Set operations
195
  in(values: any[]): BinaryExpression;
196
  notIn(values: any[]): BinaryExpression;
197
  
198
  // Null checks
199
  isNull(): UnaryExpression;
200
  isNotNull(): UnaryExpression;
201
}
202
```
203

204
[Column Operations](./column-operations.md)
205

206
### SQL Functions
207

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

210
```javascript { .api }
211
interface StandardFunctions {
212
  // Aggregate functions
213
  AVG(column: Column): FunctionCall;
214
  COUNT(column?: Column): FunctionCall;
215
  MAX(column: Column): FunctionCall;
216
  MIN(column: Column): FunctionCall;
217
  SUM(column: Column): FunctionCall;
218
  
219
  // String functions
220
  LOWER(column: Column): FunctionCall;
221
  UPPER(column: Column): FunctionCall;
222
  LENGTH(column: Column): FunctionCall;
223
  SUBSTR(column: Column, start: number, length?: number): FunctionCall;
224
  
225
  // Date functions
226
  CURRENT_TIMESTAMP(): FunctionCall;
227
  YEAR(column: Column): FunctionCall;
228
  MONTH(column: Column): FunctionCall;
229
  DAY(column: Column): FunctionCall;
230
}
231
```
232

233
[SQL Functions](./functions.md)
234

235
### Dialect Support
236

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

239
```javascript { .api }
240
// Supported SQL dialects
241
type Dialect = 'postgres' | 'mysql' | 'mssql' | 'oracle' | 'sqlite';
242

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

246
// Create dialect-specific instance
247
const mysqlSql = sql.create('mysql');
248
const postgresSql = sql.create('postgres');
249
```
250

251
[Dialect Support](./dialect-support.md)
252

253
### Advanced Query Features
254

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

257
```javascript { .api }
258
// Time intervals for date/time operations
259
class Interval {
260
  constructor(timeComponents: object);
261
  years: number;
262
  months: number;
263
  days: number;
264
  hours: number;
265
  minutes: number;
266
  seconds: number;
267
}
268

269
// Function calls for SQL functions
270
class FunctionCall {
271
  constructor(name: string, args: any[]);
272
  name: string;
273
  as(alias: string): FunctionCall;
274
  // Inherits all Value Expression methods
275
}
276

277
// Array literals for SQL arrays
278
class ArrayCall {
279
  constructor(values: any[]);
280
  as(alias: string): ArrayCall;
281
  // Inherits all Value Expression methods
282
}
283

284
// Query result structures
285
interface QueryResult {
286
  text: string;        // SQL string with parameter placeholders
287
  values: any[];       // Array of parameter values
288
}
289

290
interface NamedQueryResult extends QueryResult {
291
  name: string;        // Query name for identification
292
}
293
```
294

295
## Types
296

297
```typescript { .api }
298
interface TableDefinition {
299
  name: string;
300
  schema?: string;
301
  columns: (string | ColumnDefinition)[];
302
  foreignKeys?: ForeignKeyDefinition[];
303
  isTemporary?: boolean;
304
  snakeToCamel?: boolean;
305
  columnWhiteList?: boolean;
306
}
307

308
interface ColumnDefinition {
309
  name: string;
310
  property?: string;
311
  dataType?: string;
312
  primaryKey?: boolean;
313
  notNull?: boolean;
314
  unique?: boolean;
315
  defaultValue?: any;
316
  references?: {
317
    table: string;
318
    column: string;
319
    onDelete?: ReferentialAction;
320
    onUpdate?: ReferentialAction;
321
  };
322
}
323

324
interface ForeignKeyDefinition {
325
  table: string;
326
  columns: string[];
327
  refColumns: string[];
328
  onDelete?: ReferentialAction;
329
  onUpdate?: ReferentialAction;
330
}
331

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

334
interface QueryResult {
335
  text: string;
336
  values: any[];
337
}
338

339
interface NamedQueryResult extends QueryResult {
340
  name: string;
341
}
342
```