0
# Type System & Parameters
1

2
Type-safe parameter binding, custom PostgreSQL types, and automatic type conversion.
3

4
## Capabilities
5

6
### Typed Parameters
7

8
Create explicitly typed parameters for precise PostgreSQL type control.
9

10
```javascript { .api }
11
/**
12
 * Create typed parameter with specific PostgreSQL type
13
 * @param value - JavaScript value to type
14
 * @param oid - PostgreSQL type OID
15
 * @returns Typed parameter object
16
 */
17
typed<T>(value: T, oid: number): Parameter<T>;
18

19
// Alias for typed function
20
types: typeof typed;
21

22
interface Parameter<T> {
23
  readonly type: number;      // PostgreSQL OID
24
  readonly value: string | null;  // Serialized value
25
  readonly raw: T | null;     // Original JavaScript value
26
}
27
```
28

29
**Usage Examples:**
30

31
```javascript
32
// Explicit type casting
33
const userAge = sql.typed(25, 23); // 23 = INTEGER OID
34
const result = await sql`
35
  INSERT INTO users (name, age) 
36
  VALUES (${userName}, ${userAge})
37
`;
38

39
// Using types alias
40
const timestamp = sql.types(new Date(), 1114); // 1114 = TIMESTAMP OID
41
await sql`UPDATE posts SET updated_at = ${timestamp} WHERE id = ${postId}`;
42

43
// Custom type handling
44
const point = sql.typed({ x: 10, y: 20 }, 600); // 600 = POINT OID
45
await sql`INSERT INTO locations (coordinates) VALUES (${point})`;
46
```
47

48
### Array Parameters
49

50
Handle PostgreSQL arrays with automatic type inference and explicit typing.
51

52
```javascript { .api }
53
/**
54
 * Create array parameter with optional type specification
55
 * @param value - JavaScript array to convert
56
 * @param type - Optional PostgreSQL element type OID
57
 * @returns Array parameter object
58
 */
59
array<T>(value: T[], type?: number): ArrayParameter<T>;
60

61
interface ArrayParameter<T> extends Parameter<T[]> {
62
  readonly array: true;
63
}
64
```
65

66
**Usage Examples:**
67

68
```javascript
69
// Automatic type inference
70
const tags = sql.array(["javascript", "postgresql", "node"]);
71
await sql`
72
  INSERT INTO posts (title, tags) 
73
  VALUES (${title}, ${tags})
74
`;
75

76
// Explicit array element type
77
const scores = sql.array([95, 87, 92], 23); // 23 = INTEGER
78
await sql`
79
  INSERT INTO test_results (student_id, scores) 
80
  VALUES (${studentId}, ${scores})
81
`;
82

83
// Multi-dimensional arrays
84
const matrix = sql.array([[1, 2], [3, 4]], 23);
85
await sql`INSERT INTO matrices (data) VALUES (${matrix})`;
86

87
// Array queries
88
const userIds = [1, 2, 3, 4, 5];
89
const users = await sql`
90
  SELECT * FROM users 
91
  WHERE id = ANY(${sql.array(userIds)})
92
`;
93
```
94

95
### JSON Parameters
96

97
Handle JSON and JSONB data types with automatic serialization.
98

99
```javascript { .api }
100
/**
101
 * Create JSON parameter
102
 * @param value - JavaScript object/array to serialize as JSON
103
 * @returns JSON parameter object
104
 */
105
json(value: any): Parameter;
106
```
107

108
**Usage Examples:**
109

110
```javascript
111
// JSON object storage
112
const metadata = { version: "1.0", features: ["auth", "api"] };
113
await sql`
114
  INSERT INTO applications (name, metadata) 
115
  VALUES (${appName}, ${sql.json(metadata)})
116
`;
117

118
// Complex nested objects
119
const userProfile = {
120
  preferences: {
121
    theme: "dark",
122
    notifications: {
123
      email: true,
124
      push: false
125
    }
126
  },
127
  settings: {
128
    timezone: "UTC",
129
    language: "en"
130
  }
131
};
132

133
await sql`
134
  UPDATE users 
135
  SET profile = ${sql.json(userProfile)} 
136
  WHERE id = ${userId}
137
`;
138

139
// JSON arrays
140
const permissions = ["read", "write", "admin"];
141
await sql`
142
  INSERT INTO roles (name, permissions) 
143
  VALUES (${roleName}, ${sql.json(permissions)})
144
`;
145
```
146

147
### Custom Type Registration
148

149
Register custom PostgreSQL types for automatic conversion.
150

151
```javascript { .api }
152
interface PostgresType<T = any> {
153
  to: number;                    // PostgreSQL type OID to serialize to
154
  from: number[];               // PostgreSQL type OIDs to parse from
155
  serialize: (value: T) => unknown;  // Convert JS value to PostgreSQL format
156
  parse: (raw: any) => T;       // Convert PostgreSQL value to JS format
157
}
158

159
// Built-in BigInt type example
160
const BigInt: PostgresType<bigint>;
161
```
162

163
**Usage Examples:**
164

165
```javascript
166
// Using built-in BigInt type
167
const largeMoney = BigInt("999999999999999999");
168
await sql`
169
  INSERT INTO transactions (amount) 
170
  VALUES (${largeMoney})
171
`;
172

173
// Custom type definition (conceptual - would be configured in options)
174
const customPointType = {
175
  to: 600, // POINT OID
176
  from: [600],
177
  serialize: (point) => `(${point.x},${point.y})`,
178
  parse: (raw) => {
179
    const [x, y] = raw.slice(1, -1).split(',');
180
    return { x: parseFloat(x), y: parseFloat(y) };
181
  }
182
};
183

184
// Using custom types through options
185
const sql = postgres({
186
  types: {
187
    point: customPointType
188
  }
189
});
190

191
// Now point type is available
192
const location = sql.types.point({ x: 10.5, y: 20.3 });
193
await sql`INSERT INTO locations (coordinates) VALUES (${location})`;
194
```
195

196
### Automatic Type Conversion
197

198
Understanding how postgres.js automatically converts between JavaScript and PostgreSQL types.
199

200
```javascript { .api }
201
// Built-in type mappings (automatic conversion)
202
interface TypeMappings {
203
  // JavaScript → PostgreSQL
204
  string:   25;      // TEXT
205
  number:   [21, 23, 26, 700, 701]; // SMALLINT, INTEGER, OID, REAL, DOUBLE
206
  boolean:  16;      // BOOLEAN
207
  Date:     [1082, 1114, 1184];     // DATE, TIMESTAMP, TIMESTAMPTZ
208
  Buffer:   17;      // BYTEA
209
  Array:    'inferred'; // Array type based on elements
210
  Object:   [114, 3802]; // JSON, JSONB (via sql.json())
211
}
212
```
213

214
**Usage Examples:**
215

216
```javascript
217
// Automatic conversions (no explicit typing needed)
218
await sql`
219
  INSERT INTO users (
220
    name,           -- string → TEXT
221
    age,            -- number → INTEGER  
222
    active,         -- boolean → BOOLEAN
223
    birth_date,     -- Date → TIMESTAMP
224
    avatar          -- Buffer → BYTEA
225
  ) VALUES (
226
    ${userName},
227
    ${userAge}, 
228
    ${isActive},
229
    ${birthDate},
230
    ${avatarBuffer}
231
  )
232
`;
233

234
// Arrays are auto-typed based on content
235
const tags = ["javascript", "database"]; // → TEXT[]
236
const scores = [95, 87, 92];            // → INTEGER[]
237
const flags = [true, false, true];      // → BOOLEAN[]
238

239
await sql`
240
  INSERT INTO posts (tags, scores, flags) 
241
  VALUES (${sql.array(tags)}, ${sql.array(scores)}, ${sql.array(flags)})
242
`;
243

244
// Dates are automatically formatted
245
const now = new Date();
246
const yesterday = new Date(Date.now() - 86400000);
247

248
await sql`
249
  SELECT * FROM events 
250
  WHERE created_at BETWEEN ${yesterday} AND ${now}
251
`;
252
```
253

254
### Type Inference and Validation
255

256
How postgres.js infers and validates types during query execution.
257

258
**Usage Examples:**
259

260
```javascript
261
// Type inference for arrays
262
const mixedArray = [1, "2", 3]; // Will be treated as TEXT[] (most general type)
263
const numberArray = [1, 2, 3];  // Will be treated as INTEGER[]
264
const stringArray = ["a", "b"]; // Will be treated as TEXT[]
265

266
// Null handling
267
const nullableValue = maybeValue ?? null;
268
await sql`
269
  INSERT INTO users (optional_field) 
270
  VALUES (${nullableValue})
271
`; // null is properly handled
272

273
// Undefined handling (transforms to null by default)
274
const result = await sql`
275
  INSERT INTO users (name, email) 
276
  VALUES (${name}, ${email || undefined})
277
`; // undefined becomes null
278

279
// Buffer handling for binary data
280
const binaryData = Buffer.from("Hello, World!", "utf8");
281
await sql`
282
  INSERT INTO files (content) 
283
  VALUES (${binaryData})
284
`; // Automatically converts to BYTEA
285
```
286

287
### Type Transformation Hooks
288

289
Configure custom transformations for columns, values, and rows.
290

291
```javascript { .api }
292
interface TransformOptions {
293
  undefined?: any; // Value to use for undefined
294
  column?: ColumnTransform;
295
  value?: ValueTransform; 
296
  row?: RowTransform;
297
}
298

299
interface ColumnTransform {
300
  from?: (column: string) => string; // Transform result column names
301
  to?: (column: string) => string;   // Transform input column names
302
}
303

304
interface ValueTransform {
305
  from?: (value: any, column?: Column) => any; // Transform result values
306
  to?: (value: any) => any; // Transform input values (unused)
307
}
308

309
interface RowTransform {
310
  from?: (row: Row) => any; // Transform entire result rows
311
  to?: (row: Row) => any;   // Transform input rows (unused)
312
}
313
```
314

315
**Usage Examples:**
316

317
```javascript
318
// Snake case to camel case transformation
319
const sql = postgres({
320
  transform: {
321
    column: {
322
      from: postgres.toCamel, // Convert snake_case columns to camelCase
323
      to: postgres.fromCamel  // Convert camelCase to snake_case for queries
324
    },
325
    undefined: null // Convert undefined to null
326
  }
327
});
328

329
// Now columns are automatically transformed
330
const users = await sql`SELECT user_id, first_name, last_name FROM users`;
331
// Result objects will have: userId, firstName, lastName
332

333
// Use camelCase in dynamic queries
334
const columnName = "firstName"; // Will be converted to first_name
335
await sql`SELECT ${sql(columnName)} FROM users`;
336

337
// Custom value transformation
338
const sql = postgres({
339
  transform: {
340
    value: {
341
      from: (value, column) => {
342
        // Custom date parsing for specific columns
343
        if (column?.name.endsWith('_date') && typeof value === 'string') {
344
          return new Date(value);
345
        }
346
        return value;
347
      }
348
    }
349
  }
350
});
351
```
352

353
### Case Conversion Utilities
354

355
Built-in utilities for converting between naming conventions.
356

357
```javascript { .api }
358
// Standalone functions
359
function toPascal(str: string): string;
360
function fromPascal(str: string): string; 
361
function toCamel(str: string): string;
362
function fromCamel(str: string): string;
363
function toKebab(str: string): string;
364
function fromKebab(str: string): string;
365

366
// Namespace objects with transformers
367
const pascal: {
368
  column: { from: (str: string) => string; to: (str: string) => string };
369
  value: { from: (str: unknown, column: Column) => string };
370
};
371

372
const camel: {
373
  column: { from: (str: string) => string; to: (str: string) => string };
374
  value: { from: (str: unknown, column: Column) => string };
375
};
376

377
const kebab: {
378
  column: { from: (str: string) => string; to: (str: string) => string };
379
  value: { from: (str: unknown, column: Column) => string };
380
};
381
```
382

383
**Usage Examples:**
384

385
```javascript
386
// Manual case conversion
387
const snakeCase = "user_profile_data";
388
const camelCase = postgres.toCamel(snakeCase); // "userProfileData"
389
const pascalCase = postgres.toPascal(snakeCase); // "UserProfileData" 
390
const kebabCase = postgres.toKebab(snakeCase); // "user-profile-data"
391

392
// Using namespace transformers
393
const sql = postgres({
394
  transform: {
395
    column: postgres.camel.column, // Uses both from and to transformers
396
    value: postgres.camel.value
397
  }
398
});
399

400
// Or individual transformers
401
const sql = postgres({
402
  transform: {
403
    column: {
404
      from: postgres.toCamel,    // snake_case → camelCase for results
405
      to: postgres.fromCamel     // camelCase → snake_case for queries
406
    }
407
  }
408
});
409
```