0
# Prepared Statements
1

2
Prepared statement functionality for optimized repeated query execution with proper parameter binding and both async and sync interfaces.
3

4
## Capabilities
5

6
### Asynchronous Statement Preparation
7

8
Prepare a named statement for later execution.
9

10
```javascript { .api }
11
/**
12
 * Prepare a named statement asynchronously
13
 * @param statementName - Unique name for the prepared statement
14
 * @param text - SQL query text with $1, $2, etc. placeholders
15
 * @param nParams - Number of parameters in the query
16
 * @param callback - Callback function called when preparation completes
17
 */
18
client.prepare(
19
  statementName: string,
20
  text: string,
21
  nParams: number,
22
  callback: (err: Error | null) => void
23
): void;
24
```
25

26
**Usage Examples:**
27

28
```javascript
29
const client = new Client();
30
client.connectSync();
31

32
// Prepare a simple select statement
33
client.prepare(
34
  'get_user_by_id',
35
  'SELECT name, email FROM users WHERE id = $1',
36
  1,
37
  function(err) {
38
    if (err) throw err;
39
    console.log('Statement prepared successfully');
40
  }
41
);
42

43
// Prepare an insert statement
44
client.prepare(
45
  'insert_user',
46
  'INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING id',
47
  3,
48
  function(err) {
49
    if (err) throw err;
50
    console.log('Insert statement prepared');
51
  }
52
);
53

54
// Prepare a complex query
55
client.prepare(
56
  'search_users',
57
  'SELECT * FROM users WHERE age BETWEEN $1 AND $2 AND city = $3 ORDER BY name',
58
  3,
59
  function(err) {
60
    if (err) throw err;
61
    console.log('Search statement prepared');
62
  }
63
);
64
```
65

66
### Synchronous Statement Preparation
67

68
Prepare a named statement synchronously.
69

70
```javascript { .api }
71
/**
72
 * Prepare a named statement synchronously
73
 * @param statementName - Unique name for the prepared statement
74
 * @param text - SQL query text with $1, $2, etc. placeholders
75
 * @param nParams - Number of parameters in the query
76
 * @throws Error if preparation fails
77
 */
78
client.prepareSync(
79
  statementName: string,
80
  text: string,
81
  nParams: number
82
): void;
83
```
84

85
**Usage Examples:**
86

87
```javascript
88
const client = new Client();
89
client.connectSync();
90

91
try {
92
  // Prepare statements synchronously
93
  client.prepareSync(
94
    'get_user_by_id',
95
    'SELECT name, email FROM users WHERE id = $1',
96
    1
97
  );
98

99
  client.prepareSync(
100
    'update_user_age',
101
    'UPDATE users SET age = $1 WHERE id = $2',
102
    2
103
  );
104

105
  console.log('All statements prepared successfully');
106
} catch (err) {
107
  console.error('Statement preparation failed:', err.message);
108
}
109
```
110

111
### Asynchronous Statement Execution
112

113
Execute a previously prepared statement with parameters.
114

115
```javascript { .api }
116
/**
117
 * Execute a prepared statement asynchronously
118
 * @param statementName - Name of the prepared statement
119
 * @param parameters - Array of parameter values
120
 * @param callback - Callback function receiving results
121
 */
122
client.execute(
123
  statementName: string,
124
  parameters: any[],
125
  callback: (err: Error | null, rows: any[], results?: any) => void
126
): void;
127
```
128

129
**Usage Examples:**
130

131
```javascript
132
const client = new Client();
133
client.connectSync();
134

135
// Prepare a statement first
136
client.prepareSync(
137
  'get_user_by_id',
138
  'SELECT name, email FROM users WHERE id = $1',
139
  1
140
);
141

142
// Execute the prepared statement multiple times
143
client.execute('get_user_by_id', [1], function(err, rows) {
144
  if (err) throw err;
145
  console.log('User 1:', rows[0]);
146
});
147

148
client.execute('get_user_by_id', [2], function(err, rows) {
149
  if (err) throw err;
150
  console.log('User 2:', rows[0]);
151
});
152

153
// Execute with different parameter types
154
client.prepareSync(
155
  'search_users',
156
  'SELECT * FROM users WHERE age > $1 AND active = $2',
157
  2
158
);
159

160
client.execute('search_users', [25, true], function(err, rows) {
161
  if (err) throw err;
162
  console.log('Active users over 25:', rows);
163
});
164
```
165

166
### Synchronous Statement Execution
167

168
Execute a previously prepared statement synchronously.
169

170
```javascript { .api }
171
/**
172
 * Execute a prepared statement synchronously
173
 * @param statementName - Name of the prepared statement
174
 * @param parameters - Array of parameter values
175
 * @returns Array of result rows
176
 * @throws Error if execution fails
177
 */
178
client.executeSync(statementName: string, parameters: any[]): any[];
179
```
180

181
**Usage Examples:**
182

183
```javascript
184
const client = new Client();
185
client.connectSync();
186

187
// Prepare statements
188
client.prepareSync(
189
  'get_user_by_id',
190
  'SELECT name, email FROM users WHERE id = $1',
191
  1
192
);
193

194
client.prepareSync(
195
  'insert_user',
196
  'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id',
197
  2
198
);
199

200
try {
201
  // Execute select statement
202
  const user = client.executeSync('get_user_by_id', [1]);
203
  console.log('Retrieved user:', user[0]);
204

205
  // Execute insert statement
206
  const insertResult = client.executeSync('insert_user', ['Alice', 'alice@example.com']);
207
  console.log('New user ID:', insertResult[0].id);
208

209
  // Execute multiple times with different parameters
210
  const users = [
211
    ['Bob', 'bob@example.com'],
212
    ['Charlie', 'charlie@example.com']
213
  ];
214

215
  users.forEach(userData => {
216
    const result = client.executeSync('insert_user', userData);
217
    console.log('Inserted user ID:', result[0].id);
218
  });
219

220
} catch (err) {
221
  console.error('Statement execution failed:', err.message);
222
}
223
```
224

225
### Complete Prepared Statement Workflow
226

227
A complete example showing the full prepare-execute-reuse pattern:
228

229
```javascript
230
const Client = require('pg-native');
231

232
const client = new Client();
233
client.connectSync();
234

235
// Prepare multiple statements for a user management system
236
client.prepareSync(
237
  'create_user',
238
  'INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING id, created_at',
239
  3
240
);
241

242
client.prepareSync(
243
  'get_user',
244
  'SELECT id, name, email, age, created_at FROM users WHERE id = $1',
245
  1
246
);
247

248
client.prepareSync(
249
  'update_user',
250
  'UPDATE users SET name = $1, email = $2, age = $3 WHERE id = $4 RETURNING *',
251
  4
252
);
253

254
client.prepareSync(
255
  'delete_user',
256
  'DELETE FROM users WHERE id = $1',
257
  1
258
);
259

260
try {
261
  // Create a new user
262
  const newUser = client.executeSync('create_user', ['John Doe', 'john@example.com', 30]);
263
  const userId = newUser[0].id;
264
  console.log('Created user:', newUser[0]);
265

266
  // Retrieve the user
267
  const retrievedUser = client.executeSync('get_user', [userId]);
268
  console.log('Retrieved user:', retrievedUser[0]);
269

270
  // Update the user
271
  const updatedUser = client.executeSync('update_user', [
272
    'John Smith',
273
    'johnsmith@example.com',
274
    31,
275
    userId
276
  ]);
277
  console.log('Updated user:', updatedUser[0]);
278

279
  // Delete the user
280
  client.executeSync('delete_user', [userId]);
281
  console.log('User deleted');
282

283
} catch (err) {
284
  console.error('Operation failed:', err.message);
285
} finally {
286
  client.end();
287
}
288
```
289

290
## Performance Benefits
291

292
Prepared statements provide several advantages:
293

294
1. **Query Plan Caching**: PostgreSQL parses and plans the query once
295
2. **Parameter Binding**: Secure parameter substitution without string concatenation
296
3. **Reduced Network Traffic**: Statement name sent instead of full SQL text
297
4. **Type Safety**: PostgreSQL validates parameter types during preparation
298

299
## Best Practices
300

301
1. **Prepare once, execute many**: Ideal for queries executed multiple times
302
2. **Use meaningful names**: Choose descriptive statement names
303
3. **Match parameter counts**: Ensure `nParams` matches the actual placeholders
304
4. **Handle preparation errors**: Always check for preparation failures
305
5. **Clean resource usage**: Prepared statements persist for the connection lifetime