### Install PostgresKit Package

Source: https://github.com/gravity-ui/postgreskit/blob/main/README.md

Installs the @gravity-ui/postgreskit package using npm. This is the first step to integrate PostgresKit into your project.

```bash
npm install --save @gravity-ui/postgreskit
```

--------------------------------

### Setup and Teardown for PostgresKit Tests with Jest

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

This snippet shows the Jest setup and teardown functions for integration tests using PostgresKit. It includes functions to clear the database before tests (`beforeAll`), prepare it (`beforeAll`), and clear data between each test (`beforeEach`), ensuring a clean state for every test case. Dependencies include PostgresKit's helper functions.

```typescript
// File: tests/setup.ts - Test environment setup
import {helpers} from '../db';
import {TeachersModel} from '../../db/models/teachers';

beforeAll(async () => {
  await helpers.clearDatabase();
  await helpers.prepareDatabase();
});

afterAll(async () => {
  await helpers.clearDatabase();
});

beforeEach(async () => {
  // Clear data between tests
  await TeachersModel.query(TeachersModel.primary).delete();
});

```

--------------------------------

### Express Route Handler with PostgresKit (TypeScript)

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

This example demonstrates integrating PostgresKit models within Express.js route handlers. It shows how to use replica databases for read operations (e.g., fetching teachers) and the primary database for write operations (e.g., creating, updating, deleting teachers). The code includes examples for fetching single and multiple records, and handling POST, PUT, and DELETE requests with appropriate responses.

```typescript
import {ExpressKit} from '@gravity-ui/expresskit';
import {TeachersModel, TeachersModelColumn} from './db/models/teachers';
import {db} from './db';

const app = new ExpressKit(nodekit, {
  'GET /': {
    handler: async (_req, res) => {
      try {
        const teacher = await TeachersModel.query(TeachersModel.replica)
          .select()
          .first()
          .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

        res.send(teacher);
      } catch (error) {
        res.status(500).send({error: 'Database error'});
      }
    }
  },

  'GET /teachers': {
    handler: async (req, res) => {
      const {page = 1, limit = 10} = req.query;
      const offset = (page - 1) * limit;

      const teachers = await TeachersModel.query(TeachersModel.replica)
        .select()
        .limit(Number(limit))
        .offset(offset)
        .orderBy(TeachersModelColumn.Name, 'asc')
        .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

      res.json({teachers, page, limit});
    }
  },

  'GET /teachers/:id': {
    handler: async (req, res) => {
      const teacher = await TeachersModel.query(TeachersModel.replica)
        .findById(req.params.id)
        .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

      if (!teacher) {
        return res.status(404).send({error: 'Teacher not found'});
      }

      res.json(teacher);
    }
  },

  'POST /teachers': {
    handler: async (req, res) => {
      const {name, email, age} = req.body;

      const teacher = await TeachersModel.query(TeachersModel.primary)
        .insert({name, email, age})
        .returning('*')
        .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

      res.status(201).json(teacher);
    }
  },

  'PUT /teachers/:id': {
    handler: async (req, res) => {
      const {name, email, age} = req.body;

      const teacher = await TeachersModel.query(TeachersModel.primary)
        .patchAndFetchById(req.params.id, {name, email, age})
        .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

      if (!teacher) {
        return res.status(404).send({error: 'Teacher not found'});
      }

      res.json(teacher);
    }
  },

  'DELETE /teachers/:id': {
    handler: async (req, res) => {
      const deleted = await TeachersModel.query(TeachersModel.primary)
        .deleteById(req.params.id)
        .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

      if (!deleted) {
        return res.status(404).send({error: 'Teacher not found'});
      }

      res.status(204).send();
    }
  }
});

// Middleware to ensure database is ready
app.use(async (req, res, next) => {
  try {
    await db.ready();
    next();
  } catch (error) {
    res.status(503).send({error: 'Database not available'});
  }
});

if (require.main === module) {
  app.run();
}

export default app;

```

--------------------------------

### Create and manage database schema changes using Knex migrations in TypeScript

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Illustrates how to define and apply database schema migrations using Knex. It provides examples for both raw SQL and the Knex query builder to create and drop tables. Ensure Knex is properly configured in your project.

```typescript
// File: migrations/20230605084026_create_teachers.ts
import {Knex} from 'knex';

// Apply migration using raw SQL
export async function up(knex: Knex): Promise<void> {
  return knex.raw(`
    CREATE TABLE teachers(
      school_id SERIAL PRIMARY KEY,
      name VARCHAR(30) NOT NULL,
      email VARCHAR(30) UNIQUE,
      age INT CHECK(age >= 18),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX idx_teachers_email ON teachers(email);
    CREATE INDEX idx_teachers_age ON teachers(age);
  `);
}

// Rollback migration using raw SQL
export async function down(knex: Knex): Promise<void> {
  return knex.raw(`
    DROP TABLE IF EXISTS teachers CASCADE;
  `);
}

// Using Knex query builder instead of raw SQL
export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('teachers', (table) => {
    table.increments('school_id').primary();
    table.string('name', 30).notNullable();
    table.string('email', 30).unique();
    table.integer('age').checkPositive();
    table.timestamps(true, true);

    table.index('email', 'idx_teachers_email');
    table.index('age', 'idx_teachers_age');
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('teachers');
}
```

--------------------------------

### Initialize PostgresKit Database Connection

Source: https://github.com/gravity-ui/postgreskit/blob/main/README.md

Initializes the database connection using the initDB function from @gravity-ui/postgreskit. It configures connection strings, dispatcher options for health checks, Knex options for migrations and seeds, and a custom logger. This setup is crucial for establishing and managing PostgreSQL connections.

```typescript
import {initDB} from '@gravity-ui/postgreskit';
import {knexSnakeCaseMappers} from 'objection';
import * as path from 'path';

export const {db, CoreBaseModel, helpers} = initDB({
  connectionString: process.env.POSTGRES_DSN_LIST,
  dispatcherOptions: {
    healthcheckInterval: 5000,
    healthcheckTimeout: 700,
    suppressStatusLogs: process.env.SUPPRESS_DB_STATUS_LOGS === 'true',
  },
  knexOptions: {
    ...knexSnakeCaseMappers(),
    migrations: {
      directory: path.resolve(__dirname, 'migrations'),
      tableName: 'migrations',
      extension: 'js',
      loadExtensions: ['.js'],
    },
    seeds: {
      directory: path.resolve(__dirname, 'seeds'),
      loadExtensions: ['.js'],
    },
    debug: process.env.SQL_DEBUG,
  },
  logger: {
    info(message, extra) {
      console.log(message, extra);
    },
    error(message, error, extra) {
      console.error(message, error, extra);
    },
  },
});
```

--------------------------------

### Populate database with initial or test data using Knex seed files in TypeScript

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Shows how to seed a PostgreSQL database with initial data using Knex seed files. It includes examples for clearing existing data and inserting new records, both sequentially and in parallel using `Promise.all`. Ensure your Knex configuration is set up to manage seed files.

```typescript
// File: seeds/init.ts
import {Knex} from 'knex';

export async function seed(knex: Knex) {
  // Clear existing data
  await knex('teachers').del();

  // Insert seed data
  const teachers = [
    {
      name: 'Dana Wilson',
      email: 'dana.wilson@school.edu',
      age: 34
    },
    {
      name: 'Jack Parker',
      email: 'jack.parker@school.edu',
      age: 45
    },
    {
      name: 'Sarah Johnson',
      email: 'sarah.johnson@school.edu',
      age: 29
    },
    {
      name: 'Michael Chen',
      email: 'michael.chen@school.edu',
      age: 38
    }
  ];

  await knex('teachers').insert(teachers);

  console.log(`Seeded ${teachers.length} teachers`);
}

// Alternative: using Promise.all for parallel inserts
export async function seed(knex: Knex) {
  await knex('teachers').del();

  const teachers = [
    {name: 'Dana Wilson', email: 'dana@school.edu', age: 34},
    {name: 'Jack Parker', email: 'jack@school.edu', age: 45}
  ];

  await Promise.all(
    teachers.map(teacher => knex('teachers').insert(teacher))
  );
}
```

--------------------------------

### Perform SELECT queries using replica connections in TypeScript

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Demonstrates how to execute SELECT queries against a PostgreSQL replica for read operations. It includes examples for simple selects, queries with WHERE clauses, finding single records, paginated results, aggregations, and complex queries with joins. Ensure the TeachersModel and its associated configurations are correctly set up.

```typescript
import {TeachersModel, TeachersModelColumn} from './models/teachers';

// Simple SELECT
async function getTeacher(schoolId) {
  const teacher = await TeachersModel.query(TeachersModel.replica)
    .findById(schoolId)
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return teacher;
}

// SELECT with WHERE clause
async function findTeachersByAge(minAge) {
  const teachers = await TeachersModel.query(TeachersModel.replica)
    .where(TeachersModelColumn.Age, '>=', minAge)
    .orderBy(TeachersModelColumn.Name, 'asc')
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return teachers;
}

// SELECT first matching record
async function findTeacherByEmail(email) {
  const teacher = await TeachersModel.query(TeachersModel.replica)
    .findOne({[TeachersModelColumn.Email]: email})
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return teacher;
  // Returns: TeachersModel instance or undefined
}

// SELECT with pagination
async function getTeachersPaginated(page = 1, pageSize = 10) {
  const offset = (page - 1) * pageSize;

  const results = await TeachersModel.query(TeachersModel.replica)
    .select()
    .limit(pageSize)
    .offset(offset)
    .orderBy(TeachersModelColumn.SchoolId, 'desc')
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return results;
}

// SELECT with aggregation
async function getTeacherStats() {
  const stats = await TeachersModel.query(TeachersModel.replica)
    .select(
      TeachersModel.replica.raw('COUNT(*) as total'),
      TeachersModel.replica.raw('AVG(age) as avg_age'),
      TeachersModel.replica.raw('MIN(age) as min_age'),
      TeachersModel.replica.raw('MAX(age) as max_age')
    )
    .first()
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return stats;
  // Returns: {total: '50', avg_age: '38.5', min_age: 22, max_age: 65}
}

// Complex query with joins (if relations defined)
async function getTeachersWithCourses() {
  const teachers = await TeachersModel.query(TeachersModel.replica)
    .select('teachers.*')
    .withGraphFetched('courses')
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return teachers;
}
```

--------------------------------

### Rollback Database Migrations using PostgresKit

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Enables rolling back applied migrations to a previous schema version. Users can choose to roll back the last batch of migrations or just one step at a time. Requires database connection setup.

```typescript
import {initDB} from '@gravity-ui/postgreskit';

const { helpers } = initDB({
  connectionString: process.env.POSTGRES_DSN_LIST,
  knexOptions: {
    migrations: {
      directory: './migrations',
      tableName: 'migrations'
    }
  }
});

// Rollback last batch of migrations
async function rollbackLastBatch() {
  try {
    await helpers.rollbackDatabase();
    console.log('Rolled back last migration batch');
  } catch (error) {
    console.error('Rollback failed:', error);
    process.exit(1);
  }
}

// Rollback one migration step
async function rollbackOneStep() {
  try {
    await helpers.rollbackDatabase({onlyOne: true});
    console.log('Rolled back one migration');
  } catch (error) {
    console.error('Rollback failed:', error);
    process.exit(1);
  }
}

// CLI script example
// File: scripts/rollback.ts
import { helpers } from '../db';

const onlyOne = process.argv.includes('--one');

helpers
  .rollbackDatabase({onlyOne})
  .then(() => {
    console.log(`✓ Rollback completed${onlyOne ? ' (one step)' : ''}`);
    process.exit(0);
  })
  .catch((error) => {
    console.error('✗ Rollback failed:', error);
    process.exit(1);
  });

// Run with: npx ts-node scripts/rollback.ts
// Or: npx ts-node scripts/rollback.ts --one
```

--------------------------------

### Prepare Database with Migrations and Seeds (TypeScript)

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

This snippet demonstrates how to initialize the database connection using PostgresKit and prepare the database by running all pending migrations and executing seed files. It includes error handling for the preparation process. This function is essential for setting up the database environment for development, testing, or application startup.

```typescript
import {initDB} from '@gravity-ui/postgreskit';

const {helpers} = initDB({
  connectionString: process.env.POSTGRES_DSN_LIST,
  knexOptions: {
    migrations: {
      directory: './migrations'
    },
    seeds: {
      directory: './seeds'
    }
  }
});

// Prepare database (migrate + seed)
async function prepareDatabase() {
  try {
    await helpers.prepareDatabase();
    console.log('Database prepared: migrations and seeds applied');
  } catch (error) {
    console.error('Prepare failed:', error);
    process.exit(1);
  }
}

// Development setup script
// File: scripts/setup.ts
import { helpers } from '../db';

async function setupDevelopmentDatabase() {
  console.log('Setting up development database...');

  try {
    await helpers.prepareDatabase();
    console.log('✓ Database setup complete');
    console.log('  - All migrations applied');
    console.log('  - All seeds executed');
  } catch (error) {
    console.error('✗ Setup failed:', error);
    process.exit(1);
  }
}

setupDevelopmentDatabase();

// Test environment setup
async function setupTestEnvironment() {
  // Clear existing data
  await helpers.clearDatabase();

  // Apply fresh schema and test data
  await helpers.prepareDatabase();

  console.log('Test environment ready');
}

// Application startup
async function startApplication() {
  // Ensure database is up to date
  await helpers.prepareDatabase();

  // Start server
  app.listen(3000, () => {
    console.log('Server running on port 3000');
  });
}
```

--------------------------------

### Create a database migration using Knex

Source: https://github.com/gravity-ui/postgreskit/blob/main/README.md

This TypeScript code snippet shows a sample migration file for creating an 'entries' table. It includes the 'up' function to create the table and the 'down' function to drop it. Both functions accept a Knex instance and return a Promise.

```typescript
import type {Knex} from 'knex';

export function up(knex: Knex): Promise<unknown> {
  return knex.raw(`
        CREATE TABLE entries (content TEXT);
    `);
}

export function down(knex: Knex): Promise<unknown> {
  return knex.raw(`
        DROP TABLE entries;
    `);
}
```

--------------------------------

### Create a database seed for initial data population

Source: https://github.com/gravity-ui/postgreskit/blob/main/README.md

This TypeScript code snippet provides a sample seed file for populating the 'tenants' table with a default entry. The 'seed' function accepts a Knex instance and uses raw SQL to insert data, returning a Promise.

```typescript
import type {Knex} from 'knex';

export async function seed(knex: Knex) => {
    return knex.raw(`INSERT INTO tenants (name) VALUES ('default');`);
}
```

--------------------------------

### API: Initialize Database Connection

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Initializes PostgresKit with connection details and configuration options. It returns essential components for database interaction, model definition, and utility functions.

```APIDOC
## POST /api/initDB

### Description
Initializes PostgresKit with connection string(s) and configuration options. Returns database dispatcher, model class, termination function, and helper utilities.

### Method
POST

### Endpoint
/api/initDB

### Parameters
#### Request Body
- **connectionString** (string) - Required - The PostgreSQL connection string or a comma-separated list of strings for primary/replica setups.
- **dispatcherOptions** (object) - Optional - Options for the database dispatcher, including health check intervals and timeouts.
  - **healthcheckInterval** (number) - Optional - Interval in milliseconds for health checks.
  - **healthcheckTimeout** (number) - Optional - Timeout in milliseconds for health checks.
  - **suppressStatusLogs** (boolean) - Optional - Whether to suppress status logs.
  - **beforeTerminate** (function) - Optional - A hook function to run before termination.
- **knexOptions** (object) - Optional - Options for the underlying Knex.js instance.
  - **pool** (object) - Optional - Connection pool configuration.
  - **migrations** (object) - Optional - Migration configuration.
  - **seeds** (object) - Optional - Seeding configuration.
- **logger** (object) - Optional - Custom logger object with `info` and `error` methods.
- **modelParams** (object) - Optional - Parameters for Objection.js models.
  - **cancelOnTimeout** (boolean) - Optional - Whether to auto-cancel queries on timeout.
  - **useLimitInFirst** (boolean) - Optional - Whether to use LIMIT in first() queries.

### Request Example
```json
{
  "connectionString": "postgresql://user:password@localhost:5432/mydb",
  "dispatcherOptions": {
    "healthcheckInterval": 5000,
    "healthcheckTimeout": 700,
    "suppressStatusLogs": false
  },
  "knexOptions": {
    "pool": {
      "min": 0,
      "max": 10
    },
    "migrations": {
      "directory": "/path/to/migrations"
    }
  },
  "logger": {
    "info": "(message, extra) => console.log(message, extra)",
    "error": "(message, error, extra) => console.error(message, error, extra)"
  },
  "modelParams": {
    "cancelOnTimeout": true
  }
}
```

### Response
#### Success Response (200)
- **db** (object) - The database dispatcher instance.
- **CoreBaseModel** (class) - The base model class for Objection.js.
- **helpers** (object) - Utility functions for database operations.
- **terminate** (function) - A function to gracefully terminate the database connection.

#### Response Example
```json
{
  "db": "[Database Dispatcher Object]",
  "CoreBaseModel": "[Model Class]",
  "helpers": "[Helpers Object]",
  "terminate": "[Terminate Function]"
}
```
```

--------------------------------

### API: Database Ready Check

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Provides a mechanism to ensure that the database connection is fully initialized and ready to accept queries. It's useful for middleware or startup routines.

```APIDOC
## GET /api/db/ready

### Description
Waits for database initialization to complete before processing queries. Useful as middleware to ensure connections are established.

### Method
GET

### Endpoint
/api/db/ready

### Parameters
None

### Request Example
(No request body needed for this check)

### Response
#### Success Response (200)
- Indicates that the database is ready.

#### Error Response (503)
- **message** (string) - 'Database not ready' if the database is not available.

#### Response Example
(Success: No content, Error: 'Database not ready')
```

--------------------------------

### Initialize PostgresKit Database Connection (TypeScript)

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Initializes the PostgresKit database connection, returning the database dispatcher, model class, termination function, and helper utilities. Supports single or multi-host configurations with options for connection pooling, migrations, seeding, and custom logging. It handles automatic primary/replica detection and routing.

```typescript
import {initDB} from '@gravity-ui/postgreskit';
import * as path from 'path';

// Single host configuration
const {db, CoreBaseModel, helpers, terminate} = initDB({
  connectionString: 'postgresql://user:password@localhost:5432/mydb',

  dispatcherOptions: {
    healthcheckInterval: 5000,      // Check every 5 seconds
    healthcheckTimeout: 700,         // Timeout after 700ms
    suppressStatusLogs: false,       // Enable status logging
    beforeTerminate: async () => {   // Optional cleanup hook
      console.log('Cleaning up before shutdown');
    }
  },

  knexOptions: {
    pool: {
      min: 0,
      max: 10,
      acquireTimeoutMillis: 40000,
      createTimeoutMillis: 50000,
      idleTimeoutMillis: 45000,
      reapIntervalMillis: 1000
    },
    migrations: {
      directory: path.resolve(__dirname, 'migrations'),
      tableName: 'migrations',
      extension: 'js',
      loadExtensions: ['.js']
    },
    seeds: {
      directory: path.resolve(__dirname, 'seeds'),
      loadExtensions: ['.js']
    }
  },

  logger: {
    info: (message, extra) => console.log(message, extra),
    error: (message, error, extra) => console.error(message, error, extra)
  },

  modelParams: {
    cancelOnTimeout: true,    // Auto-cancel queries on timeout
    useLimitInFirst: true     // Use LIMIT in first() queries
  }
});

// Multi-host configuration with primary and replicas
const multiHost = initDB({
  connectionString:
    'postgresql://user:pass@primary-host:5432/db,' +
    'postgresql://user:pass@replica-1:5432/db,' +
    'postgresql://user:pass@replica-2:5432/db',
  dispatcherOptions: {
    healthcheckInterval: 5000,
    healthcheckTimeout: 2000
  }
});
// PGDispatcher automatically:
// - Identifies which host is primary via SELECT pg_is_in_recovery()
// - Routes writes to primary
// - Load-balances reads across replicas by latency
// - Falls back to primary if replicas are unavailable

```

--------------------------------

### Define Objection.js Models with CoreBaseModel

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

This snippet demonstrates how to create Objection.js models that extend CoreBaseModel to leverage primary and replica database connections. It includes defining model columns and custom static/instance methods for database interactions.

```typescript
import {initDB} from '@gravity-ui/postgreskit';

const {CoreBaseModel} = initDB({
  connectionString: process.env.POSTGRES_DSN_LIST
});

// Extend CoreBaseModel to create your own base
class Model extends CoreBaseModel {
  static DEFAULT_QUERY_TIMEOUT = 20000;
}

// Define model columns with constants
export const TeachersModelColumn = {
  SchoolId: 'school_id',
  Name: 'name',
  Email: 'email',
  Age: 'age'
} as const;

// Define model class
export class TeachersModel extends Model {
  static get tableName() {
    return 'teachers';
  }

  static get idColumn() {
    return TeachersModelColumn.SchoolId;
  }

  // Type-safe properties
  [TeachersModelColumn.SchoolId]!: number;
  [TeachersModelColumn.Name]!: string;
  [TeachersModelColumn.Email]!: string;
  [TeachersModelColumn.Age]!: number;

  // Custom static methods can access this.primary/this.replica
  static async findByEmail(email: string) {
    return await this.query(this.replica)
      .where(TeachersModelColumn.Email, email)
      .first()
      .timeout(this.DEFAULT_QUERY_TIMEOUT);
  }

  // Instance methods can access this.primary/this.replica
  async updateAge(newAge: number) {
    return await TeachersModel.query(this.primary)
      .patch({[TeachersModelColumn.Age]: newAge})
      .where(TeachersModelColumn.SchoolId, this[TeachersModelColumn.SchoolId])
      .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);
  }
}
```

--------------------------------

### Define a database model inheriting from CoreBaseModel

Source: https://github.com/gravity-ui/postgreskit/blob/main/README.md

This TypeScript code snippet demonstrates how to define a database model named 'Entry' by extending the CoreBaseModel. It specifies the table name and primary key column, and includes a custom asynchronous method 'yourCustomMethod' that uses a replica connection for a select query with a timeout.

```typescript
import {CoreBaseModel} from '../';

export default class Entry extends CoreBaseModel {
  static get tableName() {
    return 'entries';
  }

  static get idColumn() {
    return 'id';
  }

  static async yourCustomMethod() {
    await Entry.query(this.replica).select().timeout(10000);
  }
}
```

--------------------------------

### Access Primary DB Connection and Execute Queries

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Initializes a database connection pointing to the primary write host using initDB. Provides methods to execute direct Knex queries and transactions on the primary instance. Throws an error if no primary is available, indicating a read-only state.

```typescript
import {initDB} from '@gravity-ui/postgreskit';

const {db} = initDB({
  connectionString: process.env.POSTGRES_DSN_LIST
});

// Direct Knex query to primary
async function createUser(name, email) {
  try {
    const [userId] = await db.primary('users')
      .insert({name, email})
      .returning('id');

    return userId;
  } catch (error) {
    if (error.code === 'ERR_DB_READ_ONLY') {
      console.error('No primary database available - cluster is read-only');
      throw new Error('Database is in read-only mode');
    }
    throw error;
  }
}

// Transaction on primary
async function transferFunds(fromId, toId, amount) {
  const trx = await db.primary.transaction();

  try {
    await trx('accounts')
      .where('id', fromId)
      .decrement('balance', amount);

    await trx('accounts')
      .where('id', toId)
      .increment('balance', amount);

    await trx.commit();
    return {success: true};
  } catch (error) {
    await trx.rollback();
    throw error;
  }
}
```

--------------------------------

### Access Replica DB Connection and Execute Read Queries

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Initializes a database connection with primary and replica hosts using initDB. Enables executing read-only queries against the fastest healthy replica, with a fallback to the primary if no replicas are available. Handles errors for unavailable or unhealthy database connections.

```typescript
import {initDB} from '@gravity-ui/postgreskit';

const {db} = initDB({
  connectionString:
    'postgresql://user:pass@primary:5432/db,' +
    'postgresql://user:pass@replica-1:5432/db,' +
    'postgresql://user:pass@replica-2:5432/db'
});

// Read-only queries from replica
async function getUsers(limit = 100) {
  try {
    const users = await db.replica('users')
      .select('id', 'name', 'email')
      .limit(limit)
      .orderBy('created_at', 'desc');

    return users;
  } catch (error) {
    if (error.code === 'ERR_DB_UNAVAILABLE') {
      console.error('No database connections available');
      throw new Error('Database cluster is down');
    } else if (error.code === 'ERR_DB_NOT_AVAILABLE') {
      console.error('No healthy connections available');
      throw new Error('All database hosts are unhealthy');
    }
    throw error;
  }
}

// Complex read query
async function getUserStats() {
  const stats = await db.replica
    .select(
      db.replica.raw('COUNT(*) as total_users'),
      db.replica.raw('COUNT(CASE WHEN active = true THEN 1 END) as active_users')
    )
    .from('users')
    .first();

  return stats;
}

// Join query on replica
async function getUsersWithOrders() {
  return await db.replica('users')
    .leftJoin('orders', 'users.id', 'orders.user_id')
    .select(
      'users.id',
      'users.name',
      db.replica.raw('COUNT(orders.id) as order_count')
    )
    .groupBy('users.id', 'users.name');
}
```

--------------------------------

### Migrate Database Schema using PostgresKit

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Allows running pending migrations to update the database schema to its latest version. Migrations can be applied all at once or one step at a time. Requires database connection configuration.

```typescript
import {initDB} from '@gravity-ui/postgreskit';

const { helpers } = initDB({
  connectionString: process.env.POSTGRES_DSN_LIST,
  knexOptions: {
    migrations: {
      directory: './migrations',
      tableName: 'migrations'
    }
  }
});

// Migrate to latest version (all pending migrations)
async function migrateToLatest() {
  try {
    await helpers.migrateDatabase();
    console.log('Database migrated to latest version');
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  }
}

// Migrate one step forward
async function migrateOneStep() {
  try {
    await helpers.migrateDatabase({onlyOne: true});
    console.log('Applied one migration');
  } catch (error) {
    console.error('Migration failed:', error);
    process.exit(1);
  }
}

// CLI script example
// File: scripts/migrate.ts
import { helpers } from '../db';

helpers
  .migrateDatabase()
  .then(() => {
    console.log('✓ Migrations completed successfully');
    process.exit(0);
  })
  .catch((error) => {
    console.error('✗ Migration failed:', error);
    process.exit(1);
  });

// Run with: npx ts-node scripts/migrate.ts
```

--------------------------------

### PostgresKit Database Ready Check (TypeScript)

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Ensures the database connection is fully initialized and ready before processing queries. This function is useful as middleware in web frameworks like Express or for manual application startup checks. It blocks until all health checks complete successfully, preventing operations on an unprepared database.

```typescript
import {initDB} from '@gravity-ui/postgreskit';

const {db} = initDB({
  connectionString: process.env.POSTGRES_DSN_LIST
});

// Express middleware example
app.use(async (req, res, next) => {
  try {
    await db.ready();  // Blocks until health checks complete
    next();
  } catch (error) {
    res.status(503).send('Database not ready');
  }
});

// Manual usage
async function initialize() {
  await db.ready();
  console.log('Database is ready, starting application');
  // Start processing requests
}

initialize().catch(error => {
  console.error('Failed to initialize database:', error);
  process.exit(1);
});

```

--------------------------------

### Test PostgresKit Models with Jest in TypeScript

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

This snippet demonstrates how to write integration tests for your data models using Jest and PostgresKit. It covers inserting, querying, updating, and deleting records, as well as testing read operations from replica instances. Dependencies include PostgresKit's model definitions and Jest.

```typescript
// File: tests/cases/models.test.ts
import {TeachersModel, TeachersModelColumn} from '../../db/models/teachers';

const testData = {
  [TeachersModelColumn.Age]: 25,
  [TeachersModelColumn.Email]: 'mike.snow@school.edu',
  [TeachersModelColumn.Name]: 'Mike Snow'
};

describe('Models', () => {
  test('Insert teacher', async () => {
    const teacher = await TeachersModel.query(TeachersModel.primary)
      .insert(testData)
      .returning('*')
      .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

    expect(teacher.toJSON()).toStrictEqual({
      ...testData,
      [TeachersModelColumn.SchoolId]: expect.any(Number)
    });
  });

  test('Get inserted teacher', async () => {
    // First insert
    await TeachersModel.query(TeachersModel.primary)
      .insert(testData)
      .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

    // Then query from replica
    const teacher = await TeachersModel.query(TeachersModel.replica)
      .findOne({
        [TeachersModelColumn.Email]: testData[TeachersModelColumn.Email]
      })
      .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

    expect(teacher?.toJSON()).toStrictEqual({
      ...testData,
      [TeachersModelColumn.SchoolId]: expect.any(Number)
    });
  });

  test('Update teacher age', async () => {
    const teacher = await TeachersModel.query(TeachersModel.primary)
      .insert(testData)
      .returning('*')
      .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

    const updated = await TeachersModel.query(TeachersModel.primary)
      .patchAndFetchById(teacher.school_id, {age: 30})
      .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

    expect(updated.age).toBe(30);
  });

  test('Delete teacher', async () => {
    const teacher = await TeachersModel.query(TeachersModel.primary)
      .insert(testData)
      .returning('*')
      .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

    const deleted = await TeachersModel.query(TeachersModel.primary)
      .deleteById(teacher.school_id)
      .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

    expect(deleted).toBe(1);

    const found = await TeachersModel.query(TeachersModel.replica)
      .findById(teacher.school_id)
      .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

    expect(found).toBeUndefined();
  });
});

```

--------------------------------

### Terminate DB Connections Gracefully

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Initializes database connections and sets up a graceful termination process. The `terminate` function closes all database connections and can be manually called or is automatically registered for SIGINT signals. An optional `beforeTerminate` hook allows for custom cleanup tasks.

```typescript
import {initDB} from '@gravity-ui/postgreskit';

const {db, terminate} = initDB({
  connectionString: process.env.POSTGRES_DSN_LIST,
  dispatcherOptions: {
    beforeTerminate: async () => {
      console.log('Running cleanup tasks');
      // Complete pending operations
      await flushPendingWrites();
      // Close other resources
      await closeRedis();
    }
  }
});

// Automatic termination on SIGINT (Ctrl+C)
// Already registered by initDB

// Manual termination
async function shutdown() {
  console.log('Shutting down application');

  // Close HTTP server
  await httpServer.close();

  // Close database connections
  await terminate();  // Calls knex.destroy() on all connections

  console.log('Shutdown complete');
  process.exit(0);
}

process.on('SIGTERM', shutdown);

// Error handling during termination
terminate().catch(error => {
  console.error('Error during database termination:', error);
  process.exit(1);
});
```

--------------------------------

### Perform Write Operations with Primary Connection

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

This snippet illustrates how to execute INSERT, UPDATE, and DELETE operations using the primary database connection. It emphasizes explicitly passing the connection object to the query builder for all write operations.

```typescript
import {TeachersModel, TeachersModelColumn} from './models/teachers';

// INSERT operation
async function createTeacher(data) {
  const teacher = await TeachersModel.query(TeachersModel.primary)
    .insert({
      [TeachersModelColumn.Name]: data.name,
      [TeachersModelColumn.Email]: data.email,
      [TeachersModelColumn.Age]: data.age
    })
    .returning('*')
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return teacher;
  // Expected output:
  // {
  //   school_id: 123,
  //   name: 'John Doe',
  //   email: 'john@school.edu',
  //   age: 35
  // }
}

// UPDATE operation
async function updateTeacherEmail(schoolId, newEmail) {
  const updated = await TeachersModel.query(TeachersModel.primary)
    .patch({[TeachersModelColumn.Email]: newEmail})
    .where(TeachersModelColumn.SchoolId, schoolId)
    .returning('*')
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return updated;
}

// DELETE operation
async function deleteTeacher(schoolId) {
  const deleted = await TeachersModel.query(TeachersModel.primary)
    .delete()
    .where(TeachersModelColumn.SchoolId, schoolId)
    .returning('*')
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return deleted;
}

// Bulk insert
async function bulkCreateTeachers(teachersData) {
  const teachers = await TeachersModel.query(TeachersModel.primary)
    .insert(teachersData)
    .returning('*')
    .timeout(TeachersModel.DEFAULT_QUERY_TIMEOUT);

  return teachers;
}

// Transaction example
async function createTeacherWithCourse(teacherData, courseData) {
  const trx = await TeachersModel.startTransaction();

  try {
    const teacher = await TeachersModel.query(trx)
      .insert(teacherData)
      .returning('*');

    await CourseModel.query(trx)
      .insert({
        ...courseData,
        teacher_id: teacher.school_id
      });

    await trx.commit();
    return teacher;
  } catch (error) {
    await trx.rollback();
    throw error;
  }
}
```

--------------------------------

### Clear Database Schema using PostgresKit

Source: https://context7.com/gravity-ui/postgreskit/llms.txt

Resets the database to a zero state by rolling back all migrations, effectively clearing all schema and data. This is useful for testing or complete resets. Requires database connection configuration.

```typescript
import {initDB} from '@gravity-ui/postgreskit';

const { helpers } = initDB({
  connectionString: process.env.POSTGRES_DSN_LIST
});

// Clear entire database
async function clearDatabase() {
  try {
    await helpers.clearDatabase();
    console.log('Database cleared - all tables dropped');
  } catch (error) {
    console.error('Clear failed:', error);
    process.exit(1);
  }
}

// Test setup example
describe('Integration Tests', () => {
  beforeEach(async () => {
    // Clear and reset database before each test
    await helpers.clearDatabase();
    await helpers.migrateDatabase();
    await helpers.prepareDatabase();  // Run migrations and seeds
  });

  test('should create teacher', async () => {
    const teacher = await TeachersModel.query(TeachersModel.primary)
      .insert({name: 'Test Teacher', email: 'test@school.edu', age: 30})
      .returning('*');

    expect(teacher.name).toBe('Test Teacher');
  });
});

// CLI script for development reset
// File: scripts/reset.ts
import { helpers } from '../db';

async function resetDatabase() {
  console.log('⚠ WARNING: This will destroy all data!');

  await helpers.clearDatabase();
  console.log('✓ Database cleared');

  await helpers.migrateDatabase();
  console.log('✓ Migrations applied');

  await helpers.prepareDatabase();
  console.log('✓ Seeds applied');

  console.log('✓ Database reset complete');
}

resetDatabase()
  .then(() => process.exit(0))
  .catch(error => {
    console.error('Reset failed:', error);
    process.exit(1);
  });
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.