Database Connection

/**
 * Create a new Sequelize instance with database connection
 * @param database - Database name
 * @param username - Database username
 * @param password - Database password
 * @param options - Connection options
 */
constructor(database: string, username?: string, password?: string, options?: Options);

/**
 * Create a new Sequelize instance with connection URI
 * @param uri - Database connection URI
 * @param options - Connection options
 */
constructor(uri: string, options?: Options);

interface Options {
  /** Database dialect */
  dialect: 'mysql' | 'postgres' | 'sqlite' | 'mariadb' | 'mssql' | 'db2' | 'snowflake' | 'oracle';
  /** Database host */
  host?: string;
  /** Database port */
  port?: number;
  /** Connection pool configuration */
  pool?: PoolOptions;
  /** SQL query logging */
  logging?: boolean | ((sql: string, timing?: number) => void);
  /** Database timezone */
  timezone?: string;
  /** Dialect-specific options */
  dialectOptions?: any;
  /** Database storage path (SQLite only) */
  storage?: string;
  /** Connection retry options */
  retry?: RetryOptions;
  /** Query timeout in milliseconds */
  query?: {
    timeout?: number;
    raw?: boolean;
  };
  /** Enable/disable automatic camelCase conversion */
  define?: {
    underscored?: boolean;
    freezeTableName?: boolean;
    timestamps?: boolean;
    paranoid?: boolean;
  };
}

interface PoolOptions {
  /** Maximum connections in pool */
  max?: number;
  /** Minimum connections in pool */
  min?: number;
  /** Maximum idle time in milliseconds */
  idle?: number;
  /** Maximum time to get connection in milliseconds */
  acquire?: number;
  /** Time interval for evicting stale connections */
  evict?: number;
  /** Function to validate connections */
  validate?: (connection: any) => boolean;
}

import { Sequelize } from "sequelize";

// PostgreSQL connection
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'postgres',
  port: 5432,
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
});

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

// SQLite file database
const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'path/to/database.sqlite'
});

// Connection with advanced options
const sequelize = new Sequelize('database', 'user', 'pass', {
  dialect: 'postgres',
  host: 'localhost',
  port: 5432,
  logging: console.log,
  timezone: '+09:00',
  pool: {
    max: 10,
    min: 2,
    acquire: 30000,
    idle: 10000
  },
  define: {
    underscored: true,
    freezeTableName: true,
    timestamps: true
  }
});

/**
 * Test the database connection
 * @returns Promise that resolves if connection is successful
 * @throws ConnectionError if connection fails
 */
authenticate(): Promise<void>;

try {
  await sequelize.authenticate();
  console.log('Connection has been established successfully.');
} catch (error) {
  console.error('Unable to connect to the database:', error);
}

/**
 * Sync all models to the database
 * @param options - Sync configuration options
 * @returns Promise resolving to the Sequelize instance
 */
sync(options?: SyncOptions): Promise<Sequelize>;

interface SyncOptions {
  /** Drop tables before recreating */
  force?: boolean;
  /** Alter existing tables to match models */
  alter?: boolean | SyncAlterOptions;
  /** Regex to match database name for safety */
  match?: RegExp;
  /** Schema name to create tables in */
  schema?: string;  
  /** Schema search path (PostgreSQL) */
  searchPath?: string;
  /** Enable/disable hooks during sync */
  hooks?: boolean;
  /** SQL query logging */
  logging?: boolean | ((sql: string, timing?: number) => void);
}

interface SyncAlterOptions {
  /** Allow dropping columns */
  drop?: boolean;
}

// Basic sync - create tables if they don't exist
await sequelize.sync();

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

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

// Sync with safety check
await sequelize.sync({ 
  force: true,
  match: /_test$/  // Only allow on databases ending with _test
});

/**
 * Close the database connection
 * @returns Promise that resolves when connection is closed
 */
close(): Promise<void>;

/**
 * Get connection information
 */
getDialect(): string;
getDatabaseName(): string;
getQueryInterface(): QueryInterface;

// Close connection when application shuts down
process.on('SIGINT', async () => {
  await sequelize.close();
  console.log('Database connection closed.');
  process.exit(0);
});

/**
 * Show all schemas in the database
 * @param options - Query options
 * @returns Promise resolving to schema names
 */
showAllSchemas(options?: QueryOptions): Promise<string[]>;

/**
 * Create a new schema
 * @param schema - Schema name
 * @param options - Query options
 * @returns Promise resolving when schema is created
 */
createSchema(schema: string, options?: QueryOptions): Promise<void>;

/**
 * Drop an existing schema
 * @param schema - Schema name
 * @param options - Query options
 * @returns Promise resolving when schema is dropped
 */
dropSchema(schema: string, options?: QueryOptions): Promise<void>;

/**
 * Drop all schemas
 * @param options - Query options
 * @returns Promise resolving when all schemas are dropped
 */
dropAllSchemas(options?: QueryOptions): Promise<void>;

/**
 * Validate the connection configuration
 * @returns Promise that resolves if configuration is valid
 */
validate(): Promise<void>;

const sequelize = new Sequelize('postgres://user:pass@localhost:5432/database', {
  dialectOptions: {
    ssl: process.env.NODE_ENV === 'production'
  }
});

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql', // or 'mariadb'
  dialectOptions: {
    charset: 'utf8mb4',
    timezone: 'local'
  }
});

const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: 'database.sqlite',
  logging: false
});

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mssql',
  dialectOptions: {
    options: {
      encrypt: true,
      trustServerCertificate: true
    }
  }
});

/**
 * Define a new model
 * @param modelName - Name of the model
 * @param attributes - Model attributes
 * @param options - Model options
 * @returns Defined model class
 */
define(modelName: string, attributes: ModelAttributes, options?: DefineOptions): typeof Model;

/**
 * Get existing model by name
 * @param modelName - Name of the model
 * @returns Model class if exists
 */
model(modelName: string): typeof Model;

/**
 * Check if model is defined
 * @param modelName - Name of the model
 * @returns True if model exists
 */
isDefined(modelName: string): boolean;

// Define models using sequelize.define()
const User = sequelize.define('User', {
  firstName: DataTypes.STRING,
  lastName: DataTypes.STRING,
  email: DataTypes.STRING
});

const Post = sequelize.define('Post', {
  title: DataTypes.STRING,
  content: DataTypes.TEXT,
  userId: DataTypes.INTEGER
});

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

// Check if model exists
if (sequelize.isDefined('User')) {
  console.log('User model is defined');
}

/**
 * Escape SQL values for safe insertion
 * @param value - Value to escape
 * @returns Escaped SQL string
 */
escape(value: any): string;

/**
 * Set session variables (MySQL/MariaDB)
 * @param variables - Variables to set
 * @param options - Query options
 * @returns Promise resolving when variables are set
 */
set(variables: object, options?: QueryOptions): Promise<void>;

/**
 * Truncate all tables
 * @param options - Truncate options
 * @returns Promise resolving when all tables are truncated
 */
truncate(options?: TruncateOptions): Promise<void>;

/**
 * Drop all tables
 * @param options - Drop options
 * @returns Promise resolving when all tables are dropped
 */
drop(options?: DropOptions): Promise<void>;

/**
 * Get database version
 * @param options - Query options
 * @returns Promise resolving to version string
 */
databaseVersion(options?: QueryOptions): Promise<string>;

interface TruncateOptions {
  /** Cascade truncate */
  cascade?: boolean;
  /** Restart identity columns */
  restartIdentity?: boolean;
  /** Transaction */
  transaction?: Transaction;
}

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

// Set session variables (MySQL)
await sequelize.set({
  sql_mode: 'STRICT_TRANS_TABLES',
  time_zone: '+00:00'
});

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

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

// Drop all tables (very careful!)
await sequelize.drop({ cascade: true });

/**
 * Create database function calls
 * @param fn - Function name
 * @param args - Function arguments
 * @returns Function expression
 */
static fn(fn: string, ...args: any[]): Fn;

/**
 * Column reference
 * @param col - Column name
 * @returns Column expression
 */
static col(col: string): Col;

/**
 * Raw SQL literal
 * @param val - SQL string
 * @returns Literal expression
 */
static literal(val: string): Literal;

/**
 * Cast expression
 * @param val - Value to cast
 * @param type - Target type
 * @returns Cast expression
 */
static cast(val: any, type: string): Cast;

/**
 * WHERE condition
 * @param attr - Attribute or expression
 * @param comparator - Comparison operator
 * @param logic - Value to compare
 * @returns WHERE condition
 */
static where(attr: any, comparator: any, logic?: any): Where;

/**
 * AND condition
 * @param conditions - Conditions to combine
 * @returns AND expression
 */
static and(...conditions: any[]): And;

/**
 * OR condition
 * @param conditions - Conditions to combine
 * @returns OR expression
 */
static or(...conditions: any[]): Or;

/**
 * JSON query operations
 * @param conditionsOrPath - JSON path or conditions
 * @param value - Value for comparison
 * @returns JSON condition
 */
static json(conditionsOrPath: string | object, value?: any): object;

/**
 * Database-agnostic random function
 * @returns Random expression
 */
static random(): Fn;

import { fn, col, literal, cast, where, and, or, json } from 'sequelize';

// Database functions
const users = await User.findAll({
  attributes: [
    'firstName',
    [fn('COUNT', col('posts.id')), 'postCount'],
    [fn('MAX', col('createdAt')), 'lastPost']
  ],
  include: [Post],
  group: ['User.id']
});

// Complex WHERE conditions
const conditions = where(
  fn('LOWER', col('email')),
  'LIKE',
  '%@company.com%'
);

// Combine conditions
const complexWhere = and(
  { isActive: true },
  or(
    { role: 'admin' },
    { verified: true }
  )
);

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

// Random ordering
const randomUsers = await User.findAll({
  order: fn('RANDOM') // or sequelize.random()
});

/**
 * Use Continuation Local Storage for automatic transaction context
 * @param namespace - CLS namespace
 */
static useCLS(namespace: any): void;

/**
 * Get connection configuration
 */
getDialect(): string;
getDatabaseName(): string;
getQueryInterface(): QueryInterface;

import cls from 'cls-hooked';

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

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

// Get instance information
console.log('Dialect:', sequelize.getDialect());
console.log('Database:', sequelize.getDatabaseName());
const queryInterface = sequelize.getQueryInterface();