0
# Parameter Replacement
1

2
Parameter placeholder replacement for prepared statements with support for positional, named, and custom parameter types.
3

4
## Capabilities
5

6
### Parameter Items
7

8
Key-value mapping for named parameter replacement.
9

10
```typescript { .api }
11
type ParamItems = { [k: string]: string };
12
```
13

14
**Usage Examples:**
15

16
```typescript
17
import { format } from "sql-formatter";
18

19
// Named parameters with object mapping
20
const result = format(
21
  "SELECT * FROM users WHERE id = :userId AND role = :userRole",
22
  {
23
    params: {
24
      userId: "123",
25
      userRole: "admin"
26
    }
27
  }
28
);
29
```
30

31
### Parameter Types Configuration
32

33
Configuration for different types of parameter placeholders.
34

35
```typescript { .api }
36
interface ParamTypes {
37
  /** Enable positional "?" parameter placeholders */
38
  positional?: boolean;
39
  /** Prefixes for numbered parameter placeholders to support, e.g. :1, :2, :3 */
40
  numbered?: ("?" | ":" | "$")[];
41
  /** Prefixes for named parameter placeholders to support, e.g. :name */
42
  named?: (":" | "@" | "$")[];
43
  /** Prefixes for quoted parameter placeholders to support, e.g. :"name" */
44
  quoted?: (":" | "@" | "$")[];
45
  /** Custom parameter type definitions */
46
  custom?: CustomParameter[];
47
}
48

49
interface CustomParameter {
50
  /** Regex pattern for matching the parameter */
51
  regex: string;
52
  /** Takes the matched parameter string and returns the name of the parameter */
53
  key?: (text: string) => string;
54
}
55
```
56

57
### Positional Parameters
58

59
Simple positional parameter replacement using `?` placeholders.
60

61
**Usage Examples:**
62

63
```typescript
64
import { format } from "sql-formatter";
65

66
// Array of values for positional parameters
67
const result = format(
68
  "SELECT * FROM users WHERE id = ? AND status = ? AND created_at > ?",
69
  {
70
    params: ["123", "active", "2023-01-01"]
71
  }
72
);
73

74
// Result:
75
// SELECT
76
//   *
77
// FROM
78
//   users
79
// WHERE
80
//   id = '123'
81
//   AND status = 'active'
82
//   AND created_at > '2023-01-01'
83
```
84

85
### Named Parameters
86

87
Named parameter replacement with various prefix styles.
88

89
**Usage Examples:**
90

91
```typescript
92
import { format } from "sql-formatter";
93

94
// Colon-prefixed named parameters (:name)
95
const result = format(
96
  "SELECT * FROM users WHERE department = :dept AND salary > :minSalary",
97
  {
98
    params: {
99
      dept: "Engineering", 
100
      minSalary: "50000"
101
    }
102
  }
103
);
104

105
// At-sign prefixed parameters (@name) - SQL Server style
106
const result = format(
107
  "SELECT * FROM users WHERE id = @userId",
108
  {
109
    params: { userId: "456" }
110
  }
111
);
112

113
// Dollar-prefixed parameters ($name) - PostgreSQL style  
114
const result = format(
115
  "SELECT * FROM users WHERE id = $1 AND role = $2",
116
  {
117
    language: "postgresql",
118
    params: ["789", "admin"]
119
  }
120
);
121
```
122

123
### Numbered Parameters
124

125
Numbered parameter placeholders with prefixes.
126

127
**Usage Examples:**
128

129
```typescript
130
import { format } from "sql-formatter";
131

132
// Colon-prefixed numbered parameters (:1, :2, :3)
133
const result = format(
134
  "SELECT * FROM users WHERE id = :1 AND role = :2",
135
  {
136
    paramTypes: { numbered: [":"] },
137
    params: ["123", "admin"]
138
  }
139
);
140
```
141

142
### Quoted Parameters
143

144
Quoted parameter placeholders that use identifier quote types.
145

146
**Usage Examples:**
147

148
```typescript
149
import { format } from "sql-formatter";
150

151
// Quoted named parameters with colons (:"parameter_name")
152
const result = format(
153
  'SELECT * FROM users WHERE id = :"user_id"',
154
  {
155
    paramTypes: { quoted: [":"] },
156
    params: { user_id: "123" }
157
  }
158
);
159
```
160

161
### Custom Parameters
162

163
Define custom parameter patterns with regex matching.
164

165
```typescript { .api }
166
interface CustomParameter {
167
  /** Regex pattern for matching the parameter */
168
  regex: string;
169
  /** Function to extract parameter name from matched text */
170
  key?: (text: string) => string;
171
}
172
```
173

174
**Usage Examples:**
175

176
```typescript
177
import { format } from "sql-formatter";
178

179
// Custom curly brace parameters {paramName}
180
const result = format(
181
  "SELECT * FROM users WHERE id = {userId} AND role = {userRole}",
182
  {
183
    paramTypes: {
184
      custom: [{
185
        regex: "\\{\\w+\\}",
186
        key: (text) => text.slice(1, -1) // Remove { and }
187
      }]
188
    },
189
    params: {
190
      userId: "123",
191
      userRole: "admin"
192
    }
193
  }
194
);
195

196
// Custom hash parameters #{param}
197
const result = format(
198
  "SELECT * FROM users WHERE status IN (#{activeStatuses})",
199
  {
200
    paramTypes: {
201
      custom: [{
202
        regex: "#\\{[^}]+\\}",
203
        key: (text) => text.slice(2, -1) // Remove #{ and }
204
      }]
205
    },
206
    params: {
207
      activeStatuses: "'active', 'verified'"
208
    }
209
  }
210
);
211
```
212

213
### Parameter Handling Class
214

215
Internal parameter replacement implementation.
216

217
```typescript { .api }
218
class Params {
219
  constructor(params: ParamItems | string[] | undefined);
220
  
221
  /** Returns param value that matches given placeholder with param key */
222
  get(options: { key?: string; text: string }): string;
223
  
224
  /** Returns index of current positional parameter */
225
  getPositionalParameterIndex(): number;
226
  
227
  /** Sets index of current positional parameter */
228
  setPositionalParameterIndex(i: number): void;
229
}
230
```
231

232
This class is used internally by the formatter but is exported for advanced use cases.
233

234
## Parameter Validation
235

236
The parameter system validates parameter configurations and values.
237

238
**Common Validation Rules:**
239

240
- All parameter values should be strings
241
- Custom parameter regexes cannot be empty
242
- Parameter types must specify valid prefixes
243

244
**Usage Examples:**
245

246
```typescript
247
import { format } from "sql-formatter";
248

249
// Invalid parameter configuration will show warning
250
const result = format("SELECT * FROM users WHERE id = ?", {
251
  params: [123] // Warning: should be string
252
});
253

254
// Invalid custom parameter regex will throw ConfigError
255
const result = format("SELECT * FROM users WHERE id = {userId}", {
256
  paramTypes: {
257
    custom: [{
258
      regex: "" // Error: empty regex
259
    }]
260
  }
261
});
262
```
263

264
## Disable Formatting for Parameters
265

266
You can disable formatting for sections containing problematic parameters:
267

268
```sql
269
/* sql-formatter-disable */
270
SELECT * FROM users WHERE complex_param = {some_complex_param};
271
/* sql-formatter-enable */
272
SELECT * FROM products WHERE id = ?;
273
```