0
# Table Operations
1
2
This document covers table definition, column management, and basic CRUD operations for database schema modeling and data manipulation.
3
4
## Table Definition
5
6
Create table definitions with columns, constraints, and relationships.
7
8
```javascript { .api }
9
function define(config: TableDefinition): Table;
10
11
interface TableDefinition {
12
name: string;
13
schema?: string;
14
columns: (string | ColumnDefinition)[];
15
foreignKeys?: ForeignKeyDefinition[];
16
isTemporary?: boolean;
17
snakeToCamel?: boolean;
18
columnWhiteList?: boolean;
19
}
20
```
21
22
### Basic Table Definition
23
24
```javascript
25
const user = sql.define({
26
name: 'user',
27
columns: ['id', 'name', 'email', 'created_at']
28
});
29
```
30
31
### Advanced Table Definition
32
33
```javascript
34
const user = sql.define({
35
name: 'user',
36
schema: 'public',
37
columns: [
38
{ name: 'id', dataType: 'integer', primaryKey: true },
39
{ name: 'name', dataType: 'varchar(255)', notNull: true },
40
{ name: 'email', dataType: 'varchar(255)', unique: true },
41
{
42
name: 'state_or_province',
43
property: 'state', // Access as user.state instead of user.state_or_province
44
dataType: 'varchar(100)'
45
}
46
],
47
foreignKeys: [{
48
table: 'department',
49
columns: ['dept_id'],
50
refColumns: ['id'],
51
onDelete: 'cascade'
52
}],
53
snakeToCamel: true // Convert snake_case columns to camelCase properties
54
});
55
```
56
57
## Table Class
58
59
The Table class provides methods for schema operations and query building.
60
61
```javascript { .api }
62
class Table {
63
// Properties
64
getName(): string;
65
getSchema(): string;
66
setSchema(schema: string): void;
67
columns: Column[];
68
foreignKeys: ForeignKey[];
69
70
// Column management
71
addColumn(column: string | ColumnDefinition, options?: AddColumnOptions): Table;
72
hasColumn(column: string | Column): boolean;
73
getColumn(name: string): Column;
74
get(name: string): Column; // Alias for getColumn
75
createColumn(columnDef: string | ColumnDefinition): Column;
76
77
// Query operations
78
select(...columns: any[]): Query;
79
insert(data: object | object[]): Query;
80
replace(data: object | object[]): Query;
81
update(data: object): Query;
82
delete(conditions?: any): Query;
83
84
// DDL operations
85
create(): CreateQuery;
86
drop(): DropQuery;
87
alter(): AlterQuery;
88
truncate(): Query;
89
90
// Utilities
91
star(options?: StarOptions): Column;
92
literal(statement: string): LiteralNode;
93
count(alias?: string): Column;
94
as(alias: string): Table;
95
clone(config?: Partial<TableDefinition>): Table;
96
subQuery(alias?: string): SubQuery;
97
toNode(): TableNode;
98
99
// Joins
100
join(other: Table): JoinNode;
101
leftJoin(other: Table): JoinNode;
102
joinTo(other: Table): JoinNode; // Auto-join based on foreign keys
103
104
// Index management
105
indexes(): IndexQuery;
106
}
107
108
interface AddColumnOptions {
109
noisy?: boolean; // Default true, throws error if column exists
110
}
111
112
interface StarOptions {
113
prefix?: string; // Prefix column names
114
}
115
```
116
117
## Column Definition
118
119
Define columns with data types, constraints, and references.
120
121
```javascript { .api }
122
interface ColumnDefinition {
123
name: string;
124
property?: string; // Property name for access (defaults to name)
125
dataType?: string; // SQL data type
126
primaryKey?: boolean; // Primary key constraint
127
notNull?: boolean; // NOT NULL constraint
128
unique?: boolean; // UNIQUE constraint
129
defaultValue?: any; // Default value
130
autoGenerated?: boolean; // Skip in INSERT/UPDATE operations
131
references?: { // Foreign key reference
132
table: string;
133
column: string;
134
onDelete?: ReferentialAction;
135
onUpdate?: ReferentialAction;
136
};
137
subfields?: string[]; // For complex types (arrays/objects)
138
}
139
140
type ReferentialAction = 'restrict' | 'cascade' | 'no action' | 'set null' | 'set default';
141
```
142
143
## CRUD Operations
144
145
### INSERT Operations
146
147
```javascript { .api }
148
// Insert single record
149
table.insert(data: object): Query;
150
151
// Insert multiple records
152
table.insert(data: object[]): Query;
153
154
// Insert with specific columns
155
table.insert(column1.value(val1), column2.value(val2)): Query;
156
```
157
158
Usage examples:
159
160
```javascript
161
// Insert single user
162
const insertQuery = user.insert({
163
name: 'John Doe',
164
email: 'john@example.com'
165
}).toQuery();
166
167
// Insert multiple users
168
const multiInsertQuery = user.insert([
169
{ name: 'Alice', email: 'alice@example.com' },
170
{ name: 'Bob', email: 'bob@example.com' }
171
]).toQuery();
172
173
// Insert with explicit columns
174
const explicitInsertQuery = user.insert(
175
user.name.value('Charlie'),
176
user.email.value('charlie@example.com')
177
).toQuery();
178
```
179
180
### UPDATE Operations
181
182
```javascript { .api }
183
table.update(data: object): Query;
184
```
185
186
Usage examples:
187
188
```javascript
189
// Update with WHERE clause
190
const updateQuery = user
191
.update({ name: 'John Smith' })
192
.where(user.id.equals(1))
193
.toQuery();
194
195
// Update multiple columns
196
const multiUpdateQuery = user
197
.update({
198
name: 'Jane Doe',
199
email: 'jane.doe@example.com'
200
})
201
.where(user.id.equals(2))
202
.toQuery();
203
```
204
205
### DELETE Operations
206
207
```javascript { .api }
208
// Delete with conditions
209
table.delete(conditions?: any): Query;
210
211
// Delete specific tables (for joins)
212
table.delete(tables: Table | Table[]): Query;
213
```
214
215
Usage examples:
216
217
```javascript
218
// Delete with WHERE clause
219
const deleteQuery = user
220
.delete()
221
.where(user.id.equals(1))
222
.toQuery();
223
224
// Delete with conditions shorthand
225
const shorthandDeleteQuery = user
226
.delete(user.inactive.equals(true))
227
.toQuery();
228
229
// Multi-table delete
230
const multiDeleteQuery = user
231
.delete([user, profile])
232
.from(user.join(profile).on(user.id.equals(profile.userId)))
233
.where(user.active.equals(false))
234
.toQuery();
235
```
236
237
### SELECT Operations
238
239
```javascript { .api }
240
// Select all columns
241
table.select(): Query;
242
243
// Select specific columns
244
table.select(...columns: any[]): Query;
245
246
// Select with star
247
table.select(table.star()): Query;
248
```
249
250
Usage examples:
251
252
```javascript
253
// Select all
254
const allQuery = user.select().toQuery();
255
256
// Select specific columns
257
const specificQuery = user.select(user.id, user.name).toQuery();
258
259
// Select with alias
260
const aliasQuery = user.select(
261
user.id,
262
user.name.as('full_name')
263
).toQuery();
264
```
265
266
## DDL Operations
267
268
### CREATE TABLE
269
270
```javascript { .api }
271
interface CreateQuery {
272
ifNotExists(): CreateQuery;
273
toQuery(): QueryResult;
274
}
275
```
276
277
Usage:
278
279
```javascript
280
// Create table
281
const createQuery = user.create().toQuery();
282
283
// Create table if not exists
284
const createIfNotExistsQuery = user.create().ifNotExists().toQuery();
285
```
286
287
### DROP TABLE
288
289
```javascript { .api }
290
interface DropQuery {
291
ifExists(): DropQuery;
292
cascade(): DropQuery;
293
restrict(): DropQuery;
294
toQuery(): QueryResult;
295
}
296
```
297
298
Usage:
299
300
```javascript
301
// Drop table
302
const dropQuery = user.drop().toQuery();
303
304
// Drop table if exists
305
const dropIfExistsQuery = user.drop().ifExists().toQuery();
306
307
// Drop with cascade
308
const dropCascadeQuery = user.drop().cascade().toQuery();
309
```
310
311
### ALTER TABLE
312
313
```javascript { .api }
314
interface AlterQuery {
315
addColumn(column: Column | string, options?: string): AlterQuery;
316
dropColumn(column: Column | string): AlterQuery;
317
renameColumn(oldColumn: Column | string, newColumn: Column | string): AlterQuery;
318
rename(newName: string): AlterQuery;
319
toQuery(): QueryResult;
320
}
321
```
322
323
Usage:
324
325
```javascript
326
// Add column
327
const addColumnQuery = user.alter()
328
.addColumn('middle_name', 'VARCHAR(100)')
329
.toQuery();
330
331
// Drop column
332
const dropColumnQuery = user.alter()
333
.dropColumn('middle_name')
334
.toQuery();
335
336
// Rename column
337
const renameColumnQuery = user.alter()
338
.renameColumn('name', 'full_name')
339
.toQuery();
340
341
// Rename table
342
const renameTableQuery = user.alter()
343
.rename('users')
344
.toQuery();
345
```
346
347
## Index Management
348
349
```javascript { .api }
350
interface IndexQuery {
351
create(indexName?: string): IndexCreationQuery;
352
drop(indexName: string): Query;
353
drop(...columns: Column[]): Query;
354
}
355
356
interface IndexCreationQuery {
357
unique(): IndexCreationQuery;
358
using(indexType: string): IndexCreationQuery;
359
on(...columns: (Column | OrderByValueNode)[]): IndexCreationQuery;
360
withParser(parserName: string): IndexCreationQuery;
361
fulltext(): IndexCreationQuery;
362
spatial(): IndexCreationQuery;
363
toQuery(): QueryResult;
364
}
365
```
366
367
Usage:
368
369
```javascript
370
// Create index
371
const createIndexQuery = user.indexes()
372
.create('idx_user_email')
373
.on(user.email)
374
.toQuery();
375
376
// Create unique index
377
const uniqueIndexQuery = user.indexes()
378
.create('idx_user_email_unique')
379
.unique()
380
.on(user.email)
381
.toQuery();
382
383
// Create composite index
384
const compositeIndexQuery = user.indexes()
385
.create('idx_user_name_email')
386
.on(user.name, user.email)
387
.toQuery();
388
389
// Drop index
390
const dropIndexQuery = user.indexes()
391
.drop('idx_user_email')
392
.toQuery();
393
```
394
395
## Foreign Keys
396
397
```javascript { .api }
398
interface ForeignKeyDefinition {
399
table: string; // Referenced table name
400
columns: string[]; // Local columns
401
refColumns: string[]; // Referenced columns
402
onDelete?: ReferentialAction;
403
onUpdate?: ReferentialAction;
404
}
405
```
406
407
Usage:
408
409
```javascript
410
const post = sql.define({
411
name: 'post',
412
columns: ['id', 'title', 'user_id'],
413
foreignKeys: [{
414
table: 'user',
415
columns: ['user_id'],
416
refColumns: ['id'],
417
onDelete: 'cascade',
418
onUpdate: 'restrict'
419
}]
420
});
421
```
422
423
## Table Relationships and Joins
424
425
### Auto-Join Based on Foreign Keys
426
427
```javascript
428
// Automatic join using foreign key relationships
429
const autoJoinQuery = user.joinTo(post).toQuery();
430
```
431
432
### Manual Joins
433
434
```javascript
435
// Inner join
436
const innerJoinQuery = user
437
.select(user.name, post.title)
438
.from(user.join(post).on(user.id.equals(post.user_id)))
439
.toQuery();
440
441
// Left join
442
const leftJoinQuery = user
443
.select(user.name, post.title)
444
.from(user.leftJoin(post).on(user.id.equals(post.user_id)))
445
.toQuery();
446
```