0
# SQL Dialects
1

2
Individual dialect formatters for 19 SQL dialects, each with dialect-specific syntax rules, keywords, and formatting options.
3

4
## Capabilities
5

6
### Dialect Objects
7

8
Each SQL dialect is exported as a constant that can be used directly with `formatDialect()`.
9

10
```typescript { .api }
11
// Database dialects
12
const bigquery: DialectOptions;     // Google Cloud BigQuery
13
const db2: DialectOptions;          // IBM DB2
14
const db2i: DialectOptions;         // IBM DB2i
15
const duckdb: DialectOptions;       // DuckDB
16
const hive: DialectOptions;         // Apache Hive
17
const mariadb: DialectOptions;      // MariaDB
18
const mysql: DialectOptions;        // MySQL
19
const postgresql: DialectOptions;   // PostgreSQL
20
const redshift: DialectOptions;     // Amazon Redshift
21
const singlestoredb: DialectOptions; // SingleStoreDB
22
const snowflake: DialectOptions;    // Snowflake
23
const spark: DialectOptions;        // Apache Spark SQL
24
const sqlite: DialectOptions;       // SQLite
25
const tidb: DialectOptions;         // TiDB
26
const trino: DialectOptions;        // Trino
27
const transactsql: DialectOptions;  // SQL Server Transact-SQL
28

29
// Query languages
30
const n1ql: DialectOptions;         // Couchbase N1QL
31
const plsql: DialectOptions;        // Oracle PL/SQL
32
const sql: DialectOptions;          // Generic SQL (default)
33
```
34

35
**Usage Examples:**
36

37
```typescript
38
import { formatDialect, mysql, postgresql } from "sql-formatter";
39

40
// Use MySQL dialect directly
41
const mysqlFormatted = formatDialect(
42
  "SELECT `user_id`, COUNT(*) FROM `users` GROUP BY `user_id`",
43
  { dialect: mysql }
44
);
45

46
// Use PostgreSQL dialect with configuration
47
const pgFormatted = formatDialect(
48
  'SELECT "user_id", COUNT(*) FROM "users" GROUP BY "user_id"',
49
  { 
50
    dialect: postgresql,
51
    keywordCase: "upper",
52
    indentStyle: "tabularLeft"
53
  }
54
);
55
```
56

57
### Dialect Options Interface
58

59
Configuration structure for each SQL dialect.
60

61
```typescript { .api }
62
interface DialectOptions {
63
  /** Name of the dialect */
64
  name: string;
65
  /** Tokenizer configuration for lexical analysis */
66
  tokenizerOptions: TokenizerOptions;
67
  /** Formatting options specific to this dialect */
68
  formatOptions: DialectFormatOptions;
69
}
70

71
interface TokenizerOptions {
72
  /** SELECT clause variations */
73
  reservedSelect: string[];
74
  /** Main clauses that start new blocks */
75
  reservedClauses: string[];
76
  /** Set operations like UNION */
77
  reservedSetOperations: string[];
78
  /** Join variations */
79
  reservedJoins: string[];
80
  /** Multi-word keyword phrases */
81
  reservedKeywordPhrases?: string[];
82
  /** Multi-word data type phrases */
83
  reservedDataTypePhrases?: string[];
84
  /** Built-in function names */
85
  reservedFunctionNames: string[];
86
  /** Data type names */
87
  reservedDataTypes: string[];
88
  /** Other reserved keywords */
89
  reservedKeywords: string[];
90
  /** String literal quote types */
91
  stringTypes: QuoteType[];
92
  /** Identifier quote types */
93
  identTypes: QuoteType[];
94
  /** Variable quote types */
95
  variableTypes?: VariableType[];
96
  /** Additional parenthesis types */
97
  extraParens?: ('[]' | '{}')[];
98
  /** Parameter placeholder types */
99
  paramTypes?: ParamTypes;
100
  /** Line comment types */
101
  lineCommentTypes?: string[];
102
  /** Enable nested block comments */
103
  nestedBlockComments?: boolean;
104
  /** Additional identifier characters */
105
  identChars?: IdentChars;
106
  /** Additional parameter characters */
107
  paramChars?: IdentChars;
108
  /** Additional operators */
109
  operators?: string[];
110
  /** Property access operators */
111
  propertyAccessOperators?: string[];
112
  /** Enable PostgreSQL OPERATOR(...) syntax */
113
  operatorKeyword?: boolean;
114
  /** Support underscores in numbers */
115
  underscoresInNumbers?: boolean;
116
  /** Post-processing function */
117
  postProcess?: (tokens: Token[]) => Token[];
118
}
119
```
120

121
### Major SQL Dialects
122

123
#### MySQL
124

125
MySQL dialect with MySQL-specific syntax, functions, and operators.
126

127
```typescript { .api }
128
const mysql: DialectOptions;
129
```
130

131
**Features:**
132
- Backtick identifiers `` `table_name` ``
133
- MySQL-specific functions (GROUP_CONCAT, etc.)
134
- Variable syntax with `@` prefix
135
- MySQL operators (`<=>`, `<<`, `>>`, etc.)
136
- STRAIGHT_JOIN support
137

138
**Usage Examples:**
139

140
```typescript
141
import { format, mysql, formatDialect } from "sql-formatter";
142

143
// Using language string
144
const result1 = format("SELECT * FROM `users`", { language: "mysql" });
145

146
// Using dialect object
147
const result2 = formatDialect("SELECT * FROM `users`", { dialect: mysql });
148
```
149

150
#### PostgreSQL 
151

152
PostgreSQL dialect with PostgreSQL-specific syntax and features.
153

154
```typescript { .api }
155
const postgresql: DialectOptions;
156
```
157

158
**Features:**
159
- Double-quoted identifiers `"table_name"`
160
- PostgreSQL-specific functions and operators
161
- Dollar-quoted strings `$tag$content$tag$`
162
- Array and JSON operators (`->`, `->>`, etc.)
163
- OPERATOR() syntax support
164

165
#### SQL Server (Transact-SQL)
166

167
SQL Server Transact-SQL dialect.
168

169
```typescript { .api }
170
const transactsql: DialectOptions;
171
```
172

173
**Features:**
174
- Square bracket identifiers `[table_name]`
175
- SQL Server specific functions
176
- Variable syntax with `@` prefix
177
- TOP clause support
178
- OUTPUT clause support
179

180
#### BigQuery
181

182
Google Cloud BigQuery dialect.
183

184
```typescript { .api }
185
const bigquery: DialectOptions;
186
```
187

188
**Features:**
189
- Backtick identifiers for dataset.table references
190
- BigQuery-specific functions (ARRAY_AGG, STRUCT, etc.)
191
- Standard SQL and legacy SQL support
192
- Parameterized queries with `@` prefix
193

194
#### Oracle PL/SQL
195

196
Oracle PL/SQL dialect.
197

198
```typescript { .api }
199
const plsql: DialectOptions;
200
```
201

202
**Features:**
203
- PL/SQL block structure support
204
- Oracle-specific functions and operators
205
- CONNECT BY hierarchical queries
206
- Dual table references
207

208
### Dialect Creation
209

210
Factory function for creating dialect objects with caching.
211

212
```typescript { .api }
213
/**
214
 * Factory function for building Dialect objects.
215
 * When called repeatedly with same options object returns the cached Dialect,
216
 * to avoid the cost of creating it again.
217
 */
218
function createDialect(options: DialectOptions): Dialect;
219

220
interface Dialect {
221
  tokenizer: Tokenizer;
222
  formatOptions: ProcessedDialectFormatOptions;
223
}
224
```
225

226
**Usage Examples:**
227

228
```typescript
229
import { createDialect, mysql } from "sql-formatter";
230

231
// Create a dialect instance (cached)
232
const dialect = createDialect(mysql);
233

234
// Use with custom formatter logic
235
const formatted = new Formatter(dialect, options).format(query);
236
```