0
# Dialect Support
1

2
This document covers multi-database compatibility with dialect-specific features, optimizations, and configuration options for PostgreSQL, MySQL, Microsoft SQL Server, Oracle, and SQLite.
3

4
## Supported Dialects
5

6
The SQL builder supports five major database dialects with automatic query generation optimization for each.
7

8
```javascript { .api }
9
type SQLDialect = 'postgres' | 'mysql' | 'mssql' | 'oracle' | 'sqlite';
10

11
// Default dialect (PostgreSQL)
12
const DEFAULT_DIALECT = 'postgres';
13

14
// Set global dialect
15
sql.setDialect(dialect: SQLDialect, config?: object): void;
16

17
// Create dialect-specific instances
18
sql.create(dialect?: SQLDialect, config?: object): Sql;
19

20
// Get dialect implementation
21
function getDialect(dialectName: string): DialectImplementation;
22
```
23

24
## Dialect Configuration
25

26
### Global Dialect Setting
27

28
```javascript
29
// Set dialect globally (affects default sql instance)
30
sql.setDialect('mysql');
31

32
// All subsequent queries use MySQL syntax
33
const query = user.select().toQuery();
34
console.log(query.text); // Uses MySQL quotation marks: `user`.*
35
```
36

37
### Instance-Specific Dialects
38

39
```javascript
40
// Create separate instances for different databases
41
const pgSql = sql.create('postgres');
42
const mysqlSql = sql.create('mysql');
43
const mssqlSql = sql.create('mssql');
44

45
// Each instance generates appropriate SQL
46
const pgQuery = pgSql.define({ name: 'user', columns: ['id'] }).select().toQuery();
47
const mysqlQuery = mysqlSql.define({ name: 'user', columns: ['id'] }).select().toQuery();
48

49
console.log(pgQuery.text);    // SELECT "user".* FROM "user"
50
console.log(mysqlQuery.text); // SELECT `user`.* FROM `user`
51
```
52

53
## PostgreSQL (postgres)
54

55
PostgreSQL is the default dialect with the richest feature set support.
56

57
### PostgreSQL-Specific Features
58

59
```javascript { .api }
60
// Advanced PostgreSQL features
61
query.distinctOn(...columns: any[]): Query;        // DISTINCT ON
62
query.returning(...columns: any[]): Query;         // RETURNING clause
63
query.onConflict(action: ConflictAction): Query;    // ON CONFLICT (UPSERT)
64

65
// JSON operators
66
column.key(key: string): BinaryExpression;          // -> operator
67
column.keyText(key: string): BinaryExpression;      // ->> operator
68
column.path(path: string[]): BinaryExpression;      // #> operator
69
column.pathText(path: string[]): BinaryExpression;  // #>> operator
70

71
// Array operators
72
column.contains(value: any): BinaryExpression;      // @> operator
73
column.containedBy(value: any): BinaryExpression;   // <@ operator
74
column.overlap(value: any): BinaryExpression;       // && operator
75

76
// Text search
77
column.match(query: string): BinaryExpression;      // @@ operator
78

79
// Case-insensitive operations
80
column.ilike(pattern: string): BinaryExpression;    // ILIKE
81
column.iregex(pattern: string): BinaryExpression;   // ~* operator
82
```
83

84
Usage examples:
85

86
```javascript
87
sql.setDialect('postgres');
88

89
// DISTINCT ON (PostgreSQL specific)
90
const distinctOnQuery = user
91
  .select(user.id, user.name, user.email)
92
  .distinctOn(user.email)
93
  .order(user.email, user.id)
94
  .toQuery();
95

96
// RETURNING clause
97
const insertReturningQuery = user
98
  .insert({ name: 'John', email: 'john@example.com' })
99
  .returning(user.id, user.created_at)
100
  .toQuery();
101

102
// ON CONFLICT (UPSERT)
103
const upsertQuery = user
104
  .insert({ email: 'john@example.com', name: 'John Doe' })
105
  .onConflict({
106
    target: 'email',
107
    action: { name: 'John Updated' }
108
  })
109
  .toQuery();
110

111
// JSON operations
112
const jsonQuery = user
113
  .select(
114
    user.id,
115
    user.metadata.key('preferences').as('user_prefs'),
116
    user.settings.path(['ui', 'theme']).as('theme')
117
  )
118
  .where(user.metadata.containsKey('preferences'))
119
  .toQuery();
120

121
// Array operations  
122
const arrayQuery = post
123
  .select()
124
  .where(post.tags.contains(['javascript', 'nodejs']))
125
  .toQuery();
126

127
// Full-text search
128
const textSearchQuery = document
129
  .select()
130
  .where(document.search_vector.match('search & terms'))
131
  .toQuery();
132
```
133

134
## MySQL (mysql)
135

136
MySQL dialect with MySQL-specific syntax and limitations.
137

138
### MySQL-Specific Features
139

140
```javascript { .api }
141
// MySQL features
142
query.onDuplicate(action: object): Query;           // ON DUPLICATE KEY UPDATE
143
column.regexp(pattern: string): BinaryExpression;   // REGEXP operator
144
column.rlike(pattern: string): BinaryExpression;    // RLIKE operator
145

146
// MySQL quoting uses backticks
147
// Identifiers quoted with `identifier`
148
// Parameter placeholders use ? instead of $1, $2, etc.
149
```
150

151
Usage examples:
152

153
```javascript
154
sql.setDialect('mysql');
155

156
// MySQL uses backticks for identifiers
157
const mysqlQuery = user.select().toQuery();
158
console.log(mysqlQuery.text); // SELECT `user`.* FROM `user`
159

160
// ON DUPLICATE KEY UPDATE
161
const duplicateKeyQuery = user
162
  .insert({ id: 1, name: 'John', email: 'john@example.com' })
163
  .onDuplicate({ 
164
    name: 'John Updated',
165
    updated_at: new Date()
166
  })
167
  .toQuery();
168

169
// REGEXP operator
170
const regexpQuery = user
171
  .select()
172
  .where(user.email.regexp('[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}'))
173
  .toQuery();
174

175
// MySQL parameter style (uses ? placeholders)
176
const paramQuery = user
177
  .select()
178
  .where(user.name.equals('John'))
179
  .toQuery();
180
console.log(paramQuery.text);   // SELECT `user`.* FROM `user` WHERE (`user`.`name` = ?)
181
console.log(paramQuery.values); // ['John']
182
```
183

184
### MySQL Functions
185

186
```javascript
187
// MySQL-specific functions
188
const mysqlFunctions = {
189
  CONCAT: sql.function('CONCAT'),
190
  DATE_FORMAT: sql.function('DATE_FORMAT'),
191
  SUBSTRING_INDEX: sql.function('SUBSTRING_INDEX'),
192
  GROUP_CONCAT: sql.function('GROUP_CONCAT'),
193
  IFNULL: sql.function('IFNULL')
194
};
195

196
const mysqlSpecificQuery = user
197
  .select(
198
    user.id,
199
    mysqlFunctions.CONCAT(user.first_name, ' ', user.last_name).as('full_name'),
200
    mysqlFunctions.DATE_FORMAT(user.created_at, '%Y-%m-%d').as('formatted_date'),
201
    mysqlFunctions.IFNULL(user.nick_name, user.first_name).as('display_name')
202
  )
203
  .toQuery();
204
```
205

206
## Microsoft SQL Server (mssql)
207

208
SQL Server dialect with T-SQL specific features.
209

210
### MSSQL-Specific Features
211

212
```javascript { .api }
213
// SQL Server features
214
// Uses square brackets for identifier quoting: [identifier]
215
// Parameter placeholders use @p1, @p2, etc.
216
// Supports TOP clause instead of LIMIT
217
// Temporary tables prefixed with #
218
```
219

220
Usage examples:
221

222
```javascript
223
sql.setDialect('mssql');
224

225
// SQL Server uses square brackets
226
const mssqlQuery = user.select().toQuery();
227
console.log(mssqlQuery.text); // SELECT [user].* FROM [user]
228

229
// SQL Server parameter style
230
const mssqlParamQuery = user
231
  .select()
232
  .where(user.name.equals('John'))
233
  .toQuery();
234
console.log(mssqlParamQuery.text); // Uses @p1, @p2, etc.
235

236
// Temporary tables
237
const tempTable = sql.define({
238
  name: 'temp_users',
239
  isTemporary: true,
240
  columns: ['id', 'name']
241
});
242

243
const tempTableQuery = tempTable.create().toQuery();
244
// Creates table with # prefix: #temp_users
245
```
246

247
### SQL Server Functions
248

249
```javascript
250
// SQL Server specific functions
251
const mssqlFunctions = {
252
  ISNULL: sql.function('ISNULL'),
253
  DATEPART: sql.function('DATEPART'),
254
  CHARINDEX: sql.function('CHARINDEX'),
255
  LEN: sql.function('LEN'),
256
  GETDATE: sql.function('GETDATE')
257
};
258

259
const mssqlSpecificQuery = user
260
  .select(
261
    user.id,
262
    mssqlFunctions.ISNULL(user.middle_name, '').as('middle_name'),
263
    mssqlFunctions.DATEPART('year', user.created_at).as('year_created'),
264
    mssqlFunctions.LEN(user.name).as('name_length')
265
  )
266
  .toQuery();
267
```
268

269
## Oracle (oracle)
270

271
Oracle Database dialect with Oracle SQL specific syntax.
272

273
### Oracle-Specific Features
274

275
```javascript { .api }
276
// Oracle features
277
// Uses double quotes for identifier quoting: "identifier"
278
// Parameter placeholders use :1, :2, etc.
279
// Supports Oracle-specific functions and syntax
280
```
281

282
Usage examples:
283

284
```javascript
285
sql.setDialect('oracle');
286

287
// Oracle parameter style
288
const oracleQuery = user
289
  .select()
290
  .where(user.name.equals('John'))
291
  .toQuery();
292
console.log(oracleQuery.text); // Uses :1, :2, etc. parameters
293

294
// Oracle ROWNUM for limiting (older versions)
295
const limitQuery = user
296
  .select()
297
  .where('ROWNUM <= 10')
298
  .toQuery();
299
```
300

301
### Oracle Functions
302

303
```javascript
304
// Oracle-specific functions
305
const oracleFunctions = {
306
  NVL: sql.function('NVL'),
307
  NVL2: sql.function('NVL2'),
308
  DECODE: sql.function('DECODE'),
309
  EXTRACT: sql.function('EXTRACT'),
310
  TO_CHAR: sql.function('TO_CHAR'),
311
  TO_DATE: sql.function('TO_DATE'),
312
  SYSDATE: sql.function('SYSDATE')
313
};
314

315
const oracleSpecificQuery = user
316
  .select(
317
    user.id,
318
    oracleFunctions.NVL(user.nick_name, user.first_name).as('display_name'),
319
    oracleFunctions.TO_CHAR(user.created_at, 'YYYY-MM-DD').as('formatted_date'),
320
    oracleFunctions.EXTRACT('YEAR FROM', user.created_at).as('year_created')
321
  )
322
  .toQuery();
323
```
324

325
## SQLite (sqlite)
326

327
SQLite dialect optimized for the lightweight database engine.
328

329
### SQLite-Specific Features
330

331
```javascript { .api }
332
// SQLite features
333
// Uses double quotes for identifier quoting: "identifier"
334
// Parameter placeholders use ?, ?2, etc.
335
// Limited function support compared to other databases
336
// No complex joins in some operations
337
```
338

339
Usage examples:
340

341
```javascript
342
sql.setDialect('sqlite');
343

344
// SQLite parameter style
345
const sqliteQuery = user
346
  .select()
347
  .where(user.name.equals('John'))
348
  .toQuery();
349
console.log(sqliteQuery.text); // Uses ? parameters
350

351
// SQLite date functions
352
const dateQuery = user
353
  .select(
354
    user.id,
355
    user.name,
356
    sql.function('DATE')(user.created_at).as('created_date'),
357
    sql.function('STRFTIME')('%Y', user.created_at).as('created_year')
358
  )
359
  .toQuery();
360
```
361

362
### SQLite Functions
363

364
```javascript
365
// SQLite-specific functions
366
const sqliteFunctions = {
367
  DATE: sql.function('DATE'),
368
  TIME: sql.function('TIME'),
369
  DATETIME: sql.function('DATETIME'),
370
  STRFTIME: sql.function('STRFTIME'),
371
  JULIANDAY: sql.function('JULIANDAY'),
372
  IFNULL: sql.function('IFNULL')
373
};
374

375
const sqliteSpecificQuery = user
376
  .select(
377
    user.id,
378
    sqliteFunctions.DATE(user.created_at).as('created_date'),
379
    sqliteFunctions.STRFTIME('%Y-%m', user.created_at).as('year_month'),
380
    sqliteFunctions.IFNULL(user.nick_name, user.first_name).as('display_name')
381
  )
382
  .toQuery();
383
```
384

385
## Cross-Dialect Compatibility
386

387
### Writing Portable Queries
388

389
```javascript
390
// Use standard SQL features for portability
391
function createPortableQuery(sqlInstance) {
392
  return user
393
    .select(
394
      user.id,
395
      user.name,
396
      user.email
397
    )
398
    .where(
399
      user.active.equals(true)
400
        .and(user.created_at.gt(new Date('2023-01-01')))
401
    )
402
    .order(user.name.asc)
403
    .limit(10)
404
    .toQuery();
405
}
406

407
// Works across all dialects
408
const pgQuery = createPortableQuery(sql.create('postgres'));
409
const mysqlQuery = createPortableQuery(sql.create('mysql'));
410
const sqliteQuery = createPortableQuery(sql.create('sqlite'));
411
```
412

413
### Dialect-Specific Adaptations
414

415
```javascript
416
function createAdaptedQuery(sqlInstance) {
417
  const baseQuery = user.select(user.id, user.name, user.email);
418
  
419
  // Add dialect-specific features
420
  switch (sqlInstance.dialectName) {
421
    case 'postgres':
422
      return baseQuery
423
        .distinctOn(user.email)
424
        .order(user.email, user.id)
425
        .toQuery();
426
        
427
    case 'mysql':
428
      return baseQuery
429
        .where(user.email.regexp('[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}'))
430
        .toQuery();
431
        
432
    case 'mssql':
433
      return baseQuery
434
        .where(sql.function('LEN')(user.name).gt(2))
435
        .toQuery();
436
        
437
    default:
438
      return baseQuery.toQuery();
439
  }
440
}
441
```
442

443
## Dialect Configuration Options
444

445
### Custom Dialect Configuration
446

447
```javascript { .api }
448
interface DialectConfig {
449
  // Custom configuration options per dialect
450
  [key: string]: any;
451
}
452

453
// Configure dialect with options
454
sql.setDialect('postgres', {
455
  // Custom PostgreSQL configuration
456
  searchPath: ['public', 'custom_schema'],
457
  timeZone: 'UTC'
458
});
459

460
sql.setDialect('mysql', {
461
  // Custom MySQL configuration
462
  charset: 'utf8mb4',
463
  timezone: '+00:00'
464
});
465
```
466

467
## Feature Comparison Matrix
468

469
| Feature | PostgreSQL | MySQL | SQL Server | Oracle | SQLite |
470
|---------|------------|-------|------------|--------|--------|
471
| DISTINCT ON | ✓ | ✗ | ✗ | ✗ | ✗ |
472
| RETURNING | ✓ | ✗ | ✓ | ✓ | ✗ |
473
| ON CONFLICT | ✓ | ✗ | ✗ | ✗ | ✓ |
474
| ON DUPLICATE KEY | ✗ | ✓ | ✗ | ✗ | ✗ |
475
| JSON Operators | ✓ | ✓ | ✓ | ✓ | ✓ |
476
| Array Types | ✓ | ✗ | ✗ | ✓ | ✗ |
477
| Full-text Search | ✓ | ✓ | ✓ | ✓ | ✓ |
478
| Window Functions | ✓ | ✓ | ✓ | ✓ | ✓ |
479
| CTEs | ✓ | ✓ | ✓ | ✓ | ✓ |
480
| LIMIT/OFFSET | ✓ | ✓ | ✓ | ✓ | ✓ |
481
482
## Best Practices
483

484
### Dialect Selection
485

486
```javascript
487
// Choose dialect based on your database
488
const dbDialect = process.env.DB_TYPE || 'postgres';
489
sql.setDialect(dbDialect);
490

491
// Or create specific instances
492
const dbInstances = {
493
  postgres: sql.create('postgres'),
494
  mysql: sql.create('mysql'),
495
  mssql: sql.create('mssql'),
496
  oracle: sql.create('oracle'),
497
  sqlite: sql.create('sqlite')
498
};
499

500
const currentSql = dbInstances[process.env.DB_TYPE];
501
```
502

503
### Error Handling
504

505
```javascript
506
// Handle dialect-specific errors
507
try {
508
  sql.setDialect('unknown_dialect');
509
} catch (error) {
510
  console.error('Unsupported dialect:', error.message);
511
  // Fallback to default
512
  sql.setDialect('postgres');
513
}
514
```
515

516
### Testing Across Dialects
517

518
```javascript
519
// Test queries across multiple dialects
520
const dialects = ['postgres', 'mysql', 'sqlite'];
521
const testResults = {};
522

523
dialects.forEach(dialect => {
524
  const testSql = sql.create(dialect);
525
  const query = user.select().where(user.active.equals(true)).toQuery();
526
  testResults[dialect] = {
527
    text: query.text,
528
    values: query.values
529
  };
530
});
531

532
console.log('Cross-dialect test results:', testResults);
533
```