0
# Database Connection
1

2
Core database connection management, configuration, and instance-level operations for managing database connections across multiple supported dialects.
3

4
## Capabilities
5

6
### Sequelize Constructor
7

8
Creates a new Sequelize instance with database connection configuration.
9

10
```typescript { .api }
11
/**
12
 * Create a new Sequelize instance with database connection
13
 * @param database - Database name
14
 * @param username - Database username
15
 * @param password - Database password
16
 * @param options - Connection options
17
 */
18
constructor(database: string, username?: string, password?: string, options?: Options);
19

20
/**
21
 * Create a new Sequelize instance with connection URI
22
 * @param uri - Database connection URI
23
 * @param options - Connection options
24
 */
25
constructor(uri: string, options?: Options);
26

27
interface Options {
28
  /** Database dialect */
29
  dialect: 'mysql' | 'postgres' | 'sqlite' | 'mariadb' | 'mssql' | 'db2' | 'snowflake' | 'oracle';
30
  /** Database host */
31
  host?: string;
32
  /** Database port */
33
  port?: number;
34
  /** Connection pool configuration */
35
  pool?: PoolOptions;
36
  /** SQL query logging */
37
  logging?: boolean | ((sql: string, timing?: number) => void);
38
  /** Database timezone */
39
  timezone?: string;
40
  /** Dialect-specific options */
41
  dialectOptions?: any;
42
  /** Database storage path (SQLite only) */
43
  storage?: string;
44
  /** Connection retry options */
45
  retry?: RetryOptions;
46
  /** Query timeout in milliseconds */
47
  query?: {
48
    timeout?: number;
49
    raw?: boolean;
50
  };
51
  /** Enable/disable automatic camelCase conversion */
52
  define?: {
53
    underscored?: boolean;
54
    freezeTableName?: boolean;
55
    timestamps?: boolean;
56
    paranoid?: boolean;
57
  };
58
}
59

60
interface PoolOptions {
61
  /** Maximum connections in pool */
62
  max?: number;
63
  /** Minimum connections in pool */
64
  min?: number;
65
  /** Maximum idle time in milliseconds */
66
  idle?: number;
67
  /** Maximum time to get connection in milliseconds */
68
  acquire?: number;
69
  /** Time interval for evicting stale connections */
70
  evict?: number;
71
  /** Function to validate connections */
72
  validate?: (connection: any) => boolean;
73
}
74
```
75

76
**Usage Examples:**
77

78
```typescript
79
import { Sequelize } from "sequelize";
80

81
// PostgreSQL connection
82
const sequelize = new Sequelize('database', 'username', 'password', {
83
  host: 'localhost',
84
  dialect: 'postgres',
85
  port: 5432,
86
  pool: {
87
    max: 5,
88
    min: 0,
89
    acquire: 30000,
90
    idle: 10000
91
  }
92
});
93

94
// MySQL connection with URI
95
const sequelize = new Sequelize('mysql://user:pass@localhost:3306/database');
96

97
// SQLite file database
98
const sequelize = new Sequelize({
99
  dialect: 'sqlite',
100
  storage: 'path/to/database.sqlite'
101
});
102

103
// Connection with advanced options
104
const sequelize = new Sequelize('database', 'user', 'pass', {
105
  dialect: 'postgres',
106
  host: 'localhost',
107
  port: 5432,
108
  logging: console.log,
109
  timezone: '+09:00',
110
  pool: {
111
    max: 10,
112
    min: 2,
113
    acquire: 30000,
114
    idle: 10000
115
  },
116
  define: {
117
    underscored: true,
118
    freezeTableName: true,
119
    timestamps: true
120
  }
121
});
122
```
123

124
### Connection Testing
125

126
Tests the database connection to ensure it's working properly.
127

128
```typescript { .api }
129
/**
130
 * Test the database connection
131
 * @returns Promise that resolves if connection is successful
132
 * @throws ConnectionError if connection fails
133
 */
134
authenticate(): Promise<void>;
135
```
136

137
**Usage Example:**
138

139
```typescript
140
try {
141
  await sequelize.authenticate();
142
  console.log('Connection has been established successfully.');
143
} catch (error) {
144
  console.error('Unable to connect to the database:', error);
145
}
146
```
147

148
### Database Synchronization
149

150
Synchronizes all models with the database schema.
151

152
```typescript { .api }
153
/**
154
 * Sync all models to the database
155
 * @param options - Sync configuration options
156
 * @returns Promise resolving to the Sequelize instance
157
 */
158
sync(options?: SyncOptions): Promise<Sequelize>;
159

160
interface SyncOptions {
161
  /** Drop tables before recreating */
162
  force?: boolean;
163
  /** Alter existing tables to match models */
164
  alter?: boolean | SyncAlterOptions;
165
  /** Regex to match database name for safety */
166
  match?: RegExp;
167
  /** Schema name to create tables in */
168
  schema?: string;  
169
  /** Schema search path (PostgreSQL) */
170
  searchPath?: string;
171
  /** Enable/disable hooks during sync */
172
  hooks?: boolean;
173
  /** SQL query logging */
174
  logging?: boolean | ((sql: string, timing?: number) => void);
175
}
176

177
interface SyncAlterOptions {
178
  /** Allow dropping columns */
179
  drop?: boolean;
180
}
181
```
182

183
**Usage Examples:**
184

185
```typescript
186
// Basic sync - create tables if they don't exist
187
await sequelize.sync();
188

189
// Force sync - drop and recreate all tables
190
await sequelize.sync({ force: true });
191

192
// Alter sync - modify existing tables to match models
193
await sequelize.sync({ alter: true });
194

195
// Sync with safety check
196
await sequelize.sync({ 
197
  force: true,
198
  match: /_test$/  // Only allow on databases ending with _test
199
});
200
```
201

202
### Connection Management
203

204
Manages database connection lifecycle.
205

206
```typescript { .api }
207
/**
208
 * Close the database connection
209
 * @returns Promise that resolves when connection is closed
210
 */
211
close(): Promise<void>;
212

213
/**
214
 * Get connection information
215
 */
216
getDialect(): string;
217
getDatabaseName(): string;
218
getQueryInterface(): QueryInterface;
219
```
220

221
**Usage Example:**
222

223
```typescript
224
// Close connection when application shuts down
225
process.on('SIGINT', async () => {
226
  await sequelize.close();
227
  console.log('Database connection closed.');
228
  process.exit(0);
229
});
230
```
231

232
### Schema Operations
233

234
Database schema management operations.
235

236
```typescript { .api }
237
/**
238
 * Show all schemas in the database
239
 * @param options - Query options
240
 * @returns Promise resolving to schema names
241
 */
242
showAllSchemas(options?: QueryOptions): Promise<string[]>;
243

244
/**
245
 * Create a new schema
246
 * @param schema - Schema name
247
 * @param options - Query options
248
 * @returns Promise resolving when schema is created
249
 */
250
createSchema(schema: string, options?: QueryOptions): Promise<void>;
251

252
/**
253
 * Drop an existing schema
254
 * @param schema - Schema name
255
 * @param options - Query options
256
 * @returns Promise resolving when schema is dropped
257
 */
258
dropSchema(schema: string, options?: QueryOptions): Promise<void>;
259

260
/**
261
 * Drop all schemas
262
 * @param options - Query options
263
 * @returns Promise resolving when all schemas are dropped
264
 */
265
dropAllSchemas(options?: QueryOptions): Promise<void>;
266
```
267

268
### Configuration Validation
269

270
Validates connection configuration before attempting connection.
271

272
```typescript { .api }
273
/**
274
 * Validate the connection configuration
275
 * @returns Promise that resolves if configuration is valid
276
 */
277
validate(): Promise<void>;
278
```
279

280
## Connection Examples by Dialect
281

282
### PostgreSQL
283
```typescript
284
const sequelize = new Sequelize('postgres://user:pass@localhost:5432/database', {
285
  dialectOptions: {
286
    ssl: process.env.NODE_ENV === 'production'
287
  }
288
});
289
```
290

291
### MySQL/MariaDB
292
```typescript  
293
const sequelize = new Sequelize('database', 'username', 'password', {
294
  host: 'localhost',
295
  dialect: 'mysql', // or 'mariadb'
296
  dialectOptions: {
297
    charset: 'utf8mb4',
298
    timezone: 'local'
299
  }
300
});
301
```
302

303
### SQLite
304
```typescript
305
const sequelize = new Sequelize({
306
  dialect: 'sqlite',
307
  storage: 'database.sqlite',
308
  logging: false
309
});
310
```
311

312
### Microsoft SQL Server
313
```typescript
314
const sequelize = new Sequelize('database', 'username', 'password', {
315
  host: 'localhost',
316
  dialect: 'mssql',
317
  dialectOptions: {
318
    options: {
319
      encrypt: true,
320
      trustServerCertificate: true
321
    }
322
  }
323
});
324
```
325

326
## Additional Sequelize Instance Methods
327

328
### Model Management
329

330
Methods for defining and managing models on the Sequelize instance.
331

332
```typescript { .api }
333
/**
334
 * Define a new model
335
 * @param modelName - Name of the model
336
 * @param attributes - Model attributes
337
 * @param options - Model options
338
 * @returns Defined model class
339
 */
340
define(modelName: string, attributes: ModelAttributes, options?: DefineOptions): typeof Model;
341

342
/**
343
 * Get existing model by name
344
 * @param modelName - Name of the model
345
 * @returns Model class if exists
346
 */
347
model(modelName: string): typeof Model;
348

349
/**
350
 * Check if model is defined
351
 * @param modelName - Name of the model
352
 * @returns True if model exists
353
 */
354
isDefined(modelName: string): boolean;
355
```
356

357
**Usage Examples:**
358

359
```typescript
360
// Define models using sequelize.define()
361
const User = sequelize.define('User', {
362
  firstName: DataTypes.STRING,
363
  lastName: DataTypes.STRING,
364
  email: DataTypes.STRING
365
});
366

367
const Post = sequelize.define('Post', {
368
  title: DataTypes.STRING,
369
  content: DataTypes.TEXT,
370
  userId: DataTypes.INTEGER
371
});
372

373
// Get existing models
374
const UserModel = sequelize.model('User');
375
const PostModel = sequelize.model('Post');
376

377
// Check if model exists
378
if (sequelize.isDefined('User')) {
379
  console.log('User model is defined');
380
}
381
```
382

383
### Database Utilities
384

385
Utility methods for database operations and SQL manipulation.
386

387
```typescript { .api }
388
/**
389
 * Escape SQL values for safe insertion
390
 * @param value - Value to escape
391
 * @returns Escaped SQL string
392
 */
393
escape(value: any): string;
394

395
/**
396
 * Set session variables (MySQL/MariaDB)
397
 * @param variables - Variables to set
398
 * @param options - Query options
399
 * @returns Promise resolving when variables are set
400
 */
401
set(variables: object, options?: QueryOptions): Promise<void>;
402

403
/**
404
 * Truncate all tables
405
 * @param options - Truncate options
406
 * @returns Promise resolving when all tables are truncated
407
 */
408
truncate(options?: TruncateOptions): Promise<void>;
409

410
/**
411
 * Drop all tables
412
 * @param options - Drop options
413
 * @returns Promise resolving when all tables are dropped
414
 */
415
drop(options?: DropOptions): Promise<void>;
416

417
/**
418
 * Get database version
419
 * @param options - Query options
420
 * @returns Promise resolving to version string
421
 */
422
databaseVersion(options?: QueryOptions): Promise<string>;
423

424
interface TruncateOptions {
425
  /** Cascade truncate */
426
  cascade?: boolean;
427
  /** Restart identity columns */
428
  restartIdentity?: boolean;
429
  /** Transaction */
430
  transaction?: Transaction;
431
}
432
```
433

434
**Usage Examples:**
435

436
```typescript
437
// Escape values for raw SQL
438
const safeValue = sequelize.escape("O'Reilly");
439
const sql = `SELECT * FROM users WHERE name = ${safeValue}`;
440

441
// Set session variables (MySQL)
442
await sequelize.set({
443
  sql_mode: 'STRICT_TRANS_TABLES',
444
  time_zone: '+00:00'
445
});
446

447
// Get database version
448
const version = await sequelize.databaseVersion();
449
console.log('Database version:', version);
450

451
// Truncate all tables (careful!)
452
await sequelize.truncate({ cascade: true });
453

454
// Drop all tables (very careful!)
455
await sequelize.drop({ cascade: true });
456
```
457

458
### Query Utilities
459

460
Static utility methods for building SQL expressions.
461

462
```typescript { .api }
463
/**
464
 * Create database function calls
465
 * @param fn - Function name
466
 * @param args - Function arguments
467
 * @returns Function expression
468
 */
469
static fn(fn: string, ...args: any[]): Fn;
470

471
/**
472
 * Column reference
473
 * @param col - Column name
474
 * @returns Column expression
475
 */
476
static col(col: string): Col;
477

478
/**
479
 * Raw SQL literal
480
 * @param val - SQL string
481
 * @returns Literal expression
482
 */
483
static literal(val: string): Literal;
484

485
/**
486
 * Cast expression
487
 * @param val - Value to cast
488
 * @param type - Target type
489
 * @returns Cast expression
490
 */
491
static cast(val: any, type: string): Cast;
492

493
/**
494
 * WHERE condition
495
 * @param attr - Attribute or expression
496
 * @param comparator - Comparison operator
497
 * @param logic - Value to compare
498
 * @returns WHERE condition
499
 */
500
static where(attr: any, comparator: any, logic?: any): Where;
501

502
/**
503
 * AND condition
504
 * @param conditions - Conditions to combine
505
 * @returns AND expression
506
 */
507
static and(...conditions: any[]): And;
508

509
/**
510
 * OR condition
511
 * @param conditions - Conditions to combine
512
 * @returns OR expression
513
 */
514
static or(...conditions: any[]): Or;
515

516
/**
517
 * JSON query operations
518
 * @param conditionsOrPath - JSON path or conditions
519
 * @param value - Value for comparison
520
 * @returns JSON condition
521
 */
522
static json(conditionsOrPath: string | object, value?: any): object;
523

524
/**
525
 * Database-agnostic random function
526
 * @returns Random expression
527
 */
528
static random(): Fn;
529
```
530

531
**Usage Examples:**
532

533
```typescript
534
import { fn, col, literal, cast, where, and, or, json } from 'sequelize';
535

536
// Database functions
537
const users = await User.findAll({
538
  attributes: [
539
    'firstName',
540
    [fn('COUNT', col('posts.id')), 'postCount'],
541
    [fn('MAX', col('createdAt')), 'lastPost']
542
  ],
543
  include: [Post],
544
  group: ['User.id']
545
});
546

547
// Complex WHERE conditions
548
const conditions = where(
549
  fn('LOWER', col('email')),
550
  'LIKE',
551
  '%@company.com%'
552
);
553

554
// Combine conditions
555
const complexWhere = and(
556
  { isActive: true },
557
  or(
558
    { role: 'admin' },
559
    { verified: true }
560
  )
561
);
562

563
// JSON queries
564
const users = await User.findAll({
565
  where: json('preferences.theme', 'dark')
566
});
567

568
// Random ordering
569
const randomUsers = await User.findAll({
570
  order: fn('RANDOM') // or sequelize.random()
571
});
572
```
573

574
### Advanced Configuration
575

576
Additional configuration and utility methods.
577

578
```typescript { .api }
579
/**
580
 * Use Continuation Local Storage for automatic transaction context
581
 * @param namespace - CLS namespace
582
 */
583
static useCLS(namespace: any): void;
584

585
/**
586
 * Get connection configuration
587
 */
588
getDialect(): string;
589
getDatabaseName(): string;
590
getQueryInterface(): QueryInterface;
591
```
592

593
**Usage Examples:**
594

595
```typescript
596
import cls from 'cls-hooked';
597

598
// Setup CLS for automatic transaction handling
599
const namespace = cls.createNamespace('sequelize-transaction');
600
Sequelize.useCLS(namespace);
601

602
// Now transactions are automatically passed to queries
603
await sequelize.transaction(async () => {
604
  // All queries within this block automatically use the transaction
605
  await User.create({ name: 'John' }); // Uses transaction automatically
606
  await Post.create({ title: 'Hello' }); // Uses transaction automatically
607
});
608

609
// Get instance information
610
console.log('Dialect:', sequelize.getDialect());
611
console.log('Database:', sequelize.getDatabaseName());
612
const queryInterface = sequelize.getQueryInterface();
613
```