0
# Core Querying
1

2
Essential SQL query execution using tagged template literals with automatic parameter binding and type conversion.
3

4
## Capabilities
5

6
### Connection Factory
7

8
Creates a configured SQL instance for executing queries against a PostgreSQL database.
9

10
```javascript { .api }
11
/**
12
 * Create a postgres connection instance
13
 * @param options - Connection configuration options
14
 * @returns Configured SQL instance
15
 */
16
function postgres(options?: ConnectionOptions): Sql;
17

18
/**
19
 * Create a postgres connection instance from URL
20
 * @param url - PostgreSQL connection URL
21
 * @param options - Additional connection options
22
 * @returns Configured SQL instance
23
 */
24
function postgres(url: string, options?: ConnectionOptions): Sql;
25
```
26

27
**Usage Examples:**
28

29
```javascript
30
import postgres from "postgres";
31

32
// Using connection options
33
const sql = postgres({
34
  host: "localhost",
35
  port: 5432,
36
  database: "myapp",
37
  username: "user",
38
  password: "password"
39
});
40

41
// Using connection URL
42
const sql = postgres("postgres://user:password@localhost:5432/myapp");
43

44
// With additional options
45
const sql = postgres("postgres://localhost/myapp", {
46
  max: 20,
47
  idle_timeout: 30,
48
  ssl: "require"
49
});
50
```
51

52
### Tagged Template Queries
53

54
Execute SQL queries using tagged template literals with automatic parameter binding and SQL injection protection.
55

56
```javascript { .api }
57
/**
58
 * Execute SQL query using tagged template literal
59
 * @param template - Template strings array from template literal
60
 * @param parameters - Interpolated values from template literal
61
 * @returns Promise resolving to query results
62
 */
63
<T extends readonly any[] = Row[]>(
64
  template: TemplateStringsArray, 
65
  ...parameters: any[]
66
): PendingQuery<T>;
67
```
68

69
**Usage Examples:**
70

71
```javascript
72
// Basic query
73
const users = await sql`SELECT * FROM users`;
74

75
// Query with parameters (safe from SQL injection)
76
const userId = 123;
77
const user = await sql`
78
  SELECT * FROM users 
79
  WHERE id = ${userId}
80
`;
81

82
// Multiple parameters
83
const activeUsers = await sql`
84
  SELECT * FROM users 
85
  WHERE active = ${true} 
86
  AND created_at > ${new Date('2023-01-01')}
87
`;
88

89
// Type-specific queries
90
const products = await sql`
91
  SELECT id, name, price 
92
  FROM products 
93
  WHERE category = ${category}
94
`;
95
```
96

97
### Unsafe Queries
98

99
Execute raw SQL strings with optional parameter binding when template literals are not suitable.
100

101
```javascript { .api }
102
/**
103
 * Execute raw SQL string (use with caution)
104
 * @param query - Raw SQL query string
105
 * @param parameters - Optional array of parameters
106
 * @param options - Query execution options
107
 * @returns Promise resolving to query results
108
 */
109
unsafe<T extends any[] = Row[]>(
110
  query: string, 
111
  parameters?: any[], 
112
  options?: UnsafeQueryOptions
113
): PendingQuery<T>;
114

115
interface UnsafeQueryOptions {
116
  /** Execute as prepared statement */
117
  prepare?: boolean; // default: false
118
}
119
```
120

121
**Usage Examples:**
122

123
```javascript
124
// Dynamic query construction
125
const tableName = "users";
126
const results = await sql.unsafe(`SELECT * FROM ${tableName}`);
127

128
// With parameters
129
const results = await sql.unsafe(
130
  "SELECT * FROM users WHERE age > $1 AND city = $2",
131
  [25, "New York"]
132
);
133

134
// With options
135
const results = await sql.unsafe(
136
  "SELECT * FROM products WHERE category = $1",
137
  ["electronics"],
138
  { prepare: true }
139
);
140

141
// Dynamic SQL with simple protocol (no parameters)
142
const results = await sql.unsafe("SHOW server_version");
143
```
144

145
### File Queries
146

147
Execute SQL queries from files, useful for complex queries or migrations.
148

149
```javascript { .api }
150
/**
151
 * Execute SQL from file
152
 * @param path - Path to SQL file
153
 * @param args - Optional parameters for the query
154
 * @param options - Query execution options
155
 * @returns Promise resolving to query results
156
 */
157
file<T extends readonly any[] = Row[]>(
158
  path: string | Buffer | URL | number,
159
  args?: any[],
160
  options?: FileQueryOptions
161
): PendingQuery<T>;
162

163
interface FileQueryOptions {
164
  /** Use simple query protocol */
165
  simple?: boolean;
166
  /** Cache file contents */
167
  cache?: boolean;
168
}
169
```
170

171
**Usage Examples:**
172

173
```javascript
174
// Execute SQL from file
175
const results = await sql.file("./queries/get-users.sql");
176

177
// With parameters
178
const results = await sql.file("./queries/get-user-by-id.sql", [userId]);
179

180
// With options
181
const results = await sql.file("./migrations/001-create-tables.sql", [], {
182
  simple: true
183
});
184
```
185

186
### SQL Identifier Helper
187

188
Create safely escaped SQL identifiers for dynamic table/column names.
189

190
```javascript { .api }
191
/**
192
 * Create SQL identifier (safely escaped)
193
 * @param identifier - Column or table name to escape
194
 * @returns Escaped identifier object
195
 */
196
(identifier: string): Identifier;
197

198
interface Identifier {
199
  readonly value: string;
200
}
201
```
202

203
**Usage Examples:**
204

205
```javascript
206
// Dynamic table name
207
const tableName = "user_profiles";
208
const results = await sql`SELECT * FROM ${sql(tableName)}`;
209

210
// Dynamic column name
211
const columnName = "created_at";
212
const results = await sql`
213
  SELECT ${sql(columnName)} 
214
  FROM users 
215
  ORDER BY ${sql(columnName)} DESC
216
`;
217

218
// Multiple identifiers
219
const results = await sql`
220
  SELECT ${sql("u.name")}, ${sql("p.title")}
221
  FROM ${sql("users")} u
222
  JOIN ${sql("posts")} p ON u.id = p.user_id
223
`;
224
```
225

226
### Query Chaining and Dynamic Building
227

228
Build complex queries dynamically using helper objects and conditional logic.
229

230
```javascript { .api }
231
/**
232
 * SQL helper for dynamic query building
233
 * @param first - First argument defining helper behavior
234
 * @param rest - Additional arguments based on helper type
235
 * @returns Helper object for use in template literals
236
 */
237
<T, K extends Rest<T>>(
238
  first: T, 
239
  ...rest: K
240
): Helper<T, K>;
241

242
interface Helper<T, U> {
243
  readonly first: T;
244
  readonly rest: U;
245
}
246
```
247

248
**Usage Examples:**
249

250
```javascript
251
// Conditional WHERE clauses
252
const filters = [];
253
if (minAge) filters.push(sql`age >= ${minAge}`);
254
if (city) filters.push(sql`city = ${city}`);
255

256
const users = await sql`
257
  SELECT * FROM users
258
  ${filters.length ? sql`WHERE ${sql.join(filters, sql` AND `)}` : sql``}
259
`;
260

261
// Dynamic column selection
262
const columns = ["id", "name"];
263
if (includeEmail) columns.push("email");
264

265
const users = await sql`
266
  SELECT ${sql(columns)} 
267
  FROM users
268
`;
269

270
// Insert helper with object
271
const userData = { name: "Alice", email: "alice@example.com", age: 30 };
272
const result = await sql`
273
  INSERT INTO users ${sql(userData)}
274
  RETURNING id
275
`;
276

277
// Insert multiple records
278
const users = [
279
  { name: "Bob", email: "bob@example.com" },
280
  { name: "Carol", email: "carol@example.com" }
281
];
282
await sql`INSERT INTO users ${sql(users)}`;
283

284
// Update with object (requires WHERE clause)
285
const updateData = { email: "newemail@example.com", updated_at: new Date() };
286
await sql`
287
  UPDATE users SET ${sql(updateData)} 
288
  WHERE id = ${userId}
289
`;
290

291
// Insert with specific columns
292
const columns = ["name", "email"];
293
const values = [["Alice", "alice@example.com"], ["Bob", "bob@example.com"]];
294
await sql`INSERT INTO users ${sql(values, columns)}`;
295

296
// Complex dynamic queries
297
const query = sql`
298
  SELECT u.name, COUNT(p.id) as post_count
299
  FROM users u
300
  LEFT JOIN posts p ON u.id = p.user_id
301
  ${groupBy ? sql`GROUP BY u.id, u.name` : sql``}
302
  ${having ? sql`HAVING COUNT(p.id) > ${having}` : sql``}
303
`;
304
```
305

306
### Result Types
307

308
Understanding the different result types returned by queries.
309

310
```javascript { .api }
311
interface RowList<T extends readonly any[]> extends Array<T[number]> {
312
  // Result metadata
313
  readonly count: number;
314
  readonly command: string;
315
  readonly columns: ColumnList<keyof T[number]>;
316
  readonly state: ConnectionState;
317
  readonly statement: Statement;
318
}
319

320
interface Statement {
321
  readonly name: string;
322
  readonly string: string;
323
  readonly types: number[];
324
  readonly columns: ColumnList<string>;
325
}
326

327
interface ExecutionResult<T> extends Array<never> {
328
  readonly count: number;
329
  readonly command: string;
330
  readonly statement: Statement;
331
  readonly state: ConnectionState;
332
}
333
```
334

335
**Usage Examples:**
336

337
```javascript
338
// Access result metadata
339
const result = await sql`SELECT * FROM users`;
340
console.log(`Found ${result.count} users`);
341
console.log(`Command: ${result.command}`);
342
console.log(`Columns:`, result.columns);
343

344
// Iterate over results
345
for (const user of result) {
346
  console.log(user.name);
347
}
348

349
// Result is also an array
350
const firstUser = result[0];
351
const userCount = result.length;
352
```