Fastify Database Plugin

Fastify plugin for PostgreSQL database connection management in QAuth. This plugin wraps the @qauth-labs/infra-db library and provides database connection lifecycle management within Fastify applications.

Overview

The @qauth-labs/fastify-plugin-db plugin integrates PostgreSQL into your Fastify application by:

Decorating the Fastify instance with db (Drizzle ORM) and dbPool (PostgreSQL connection pool) properties
Managing database connection lifecycle (connection, verification, graceful shutdown)
Providing automatic connection testing on server ready
Handling graceful shutdown on server close

Installation

This library is part of the QAuth monorepo and is automatically available to other projects within the workspace.

import { databasePlugin } from '@qauth-labs/fastify-plugin-db';

Usage

Basic Registration

import Fastify from 'fastify';
import { databasePlugin } from '@qauth-labs/fastify-plugin-db';
import { env } from '@qauth-labs/server-config';

const fastify = Fastify();

// Register the database plugin
await fastify.register(databasePlugin, {
  config: {
    connectionString: env.DATABASE_URL,
    pool: {
      max: env.DB_POOL_MAX,
      min: env.DB_POOL_MIN,
      idleTimeoutMillis: env.DB_POOL_IDLE_TIMEOUT,
      connectionTimeoutMillis: env.DB_POOL_CONNECTION_TIMEOUT,
    },
  },
});

// Start the server
await fastify.listen({ port: 3000 });

With Other Plugins

import Fastify from 'fastify';
import { databasePlugin } from '@qauth-labs/fastify-plugin-db';
import { cachePlugin } from '@qauth-labs/fastify-plugin-cache';
import { env } from '@qauth-labs/server-config';

const fastify = Fastify();

// Register plugins in order
await fastify.register(databasePlugin, {
  config: {
    connectionString: env.DATABASE_URL,
    pool: {
      max: env.DB_POOL_MAX,
      min: env.DB_POOL_MIN,
      idleTimeoutMillis: env.DB_POOL_IDLE_TIMEOUT,
      connectionTimeoutMillis: env.DB_POOL_CONNECTION_TIMEOUT,
    },
  },
});
await fastify.register(cachePlugin, {
  config: {
    url: env.REDIS_URL,
    host: env.REDIS_HOST,
    port: env.REDIS_PORT,
    password: env.REDIS_PASSWORD,
    db: env.REDIS_DB,
    maxRetriesPerRequest: env.REDIS_MAX_RETRIES,
    connectTimeout: env.REDIS_CONNECTION_TIMEOUT,
    commandTimeout: env.REDIS_COMMAND_TIMEOUT,
    lazyConnect: true,
  },
});

await fastify.listen({ port: 3000 });

Using Database in Routes

Once registered, both Drizzle ORM and the connection pool are available on the Fastify instance:

// Using Drizzle ORM
import { schema } from '@qauth-labs/infra-db';
const { users, realms, oauthClients } = schema;

fastify.get('/users', async (request, reply) => {
  // Access Drizzle ORM via fastify.db
  const allUsers = await fastify.db.select().from(users);
  return { users: allUsers };
});

// Using connection pool directly
fastify.get('/health', async (request, reply) => {
  const client = await fastify.dbPool.connect();
  try {
    const result = await client.query('SELECT NOW()');
    return { timestamp: result.rows[0].now };
  } finally {
    client.release();
  }
});

Using Repositories

For type-safe database operations with proper error handling, use the repository pattern:

import { usersRepository, realmsRepository } from '@qauth-labs/infra-db';
import { NotFoundError, UniqueConstraintError } from '@qauth-labs/shared-errors';

// Get user by ID
fastify.get('/users/:id', async (request, reply) => {
  const { id } = request.params as { id: string };

  try {
    const user = await usersRepository.findByIdOrThrow(id);
    return { user };
  } catch (error) {
    if (error instanceof NotFoundError) {
      // Use the statusCode property from the error class
      reply.code(error.statusCode).send({ error: error.message });
      return;
    }
    throw error;
  }
});

// Create user
fastify.post('/users', async (request, reply) => {
  const userData = request.body as NewUser;

  try {
    const user = await usersRepository.create(userData);
    reply.code(201).send({ user });
  } catch (error) {
    if (error instanceof UniqueConstraintError) {
      // Use the statusCode property from the error class (409 Conflict)
      reply.code(error.statusCode).send({
        error: 'User already exists',
        constraint: error.constraint,
      });
      return;
    }
    throw error;
  }
});

// Update user
fastify.put('/users/:id', async (request, reply) => {
  const { id } = request.params as { id: string };
  const updateData = request.body as UpdateUser;

  try {
    const user = await usersRepository.update(id, updateData);
    return { user };
  } catch (error) {
    if (error instanceof NotFoundError) {
      // Use the statusCode property from the error class
      reply.code(error.statusCode).send({ error: error.message });
      return;
    }
    throw error;
  }
});

Using Repositories with Transactions

Repositories support transactions for atomic operations:

import { db } from '@qauth-labs/infra-db';
import { usersRepository, realmsRepository } from '@qauth-labs/infra-db';

fastify.post('/setup', async (request, reply) => {
  const { realmName, adminEmail } = request.body;

  // All operations in a single transaction
  const result = await db.transaction(async (tx) => {
    // Create realm
    const realm = await realmsRepository.create({ name: realmName }, tx);

    // Create admin user in the same transaction
    const admin = await usersRepository.create(
      {
        realmId: realm.id,
        email: adminEmail,
        passwordHash: await hashPassword('admin123'),
        emailVerified: true,
      },
      tx
    );

    return { realm, admin };
  });

  return result;
});

API

Plugin Registration

await fastify.register(databasePlugin, options?);

Options:

interface DatabasePluginOptions {
  /**
   * Database configuration with connection string and pool settings
   */
  config: DatabaseConfig;
}

The plugin requires a config object with database connection settings. Configuration is passed explicitly (factory pattern), not read directly from environment variables.

Fastify Instance Decorators

The plugin decorates the Fastify instance with two properties:

`fastify.db`

Type: typeof db (Drizzle ORM instance)

The Drizzle ORM instance. This is the same instance exported from @qauth-labs/infra-db.

Example:

// Using Drizzle ORM
import { schema } from '@qauth-labs/infra-db';
const { users, realms, oauthClients } = schema;

fastify.get('/users', async (request, reply) => {
  const allUsers = await fastify.db.select().from(users);
  return { users: allUsers };
});

// Query with relations
fastify.get('/users/:id', async (request, reply) => {
  const { id } = request.params as { id: string };
  const user = await fastify.db.query.users.findFirst({
    where: (users, { eq }) => eq(users.id, id),
    with: {
      realm: true,
    },
  });
  return { user };
});

`fastify.dbPool`

Type: Pool (from pg)

The PostgreSQL connection pool. This is the same pool instance exported from @qauth-labs/infra-db.

Example:

// Using connection pool directly
fastify.get('/query', async (request, reply) => {
  const client = await fastify.dbPool.connect();
  try {
    const result = await client.query('SELECT * FROM users WHERE id = $1', [userId]);
    return { user: result.rows[0] };
  } finally {
    client.release();
  }
});

// Or using pool.query (auto-releases connection)
fastify.get('/simple-query', async (request, reply) => {
  const result = await fastify.dbPool.query('SELECT NOW()');
  return { timestamp: result.rows[0].now };
});

TypeScript Support

The plugin includes TypeScript type definitions. Both fastify.db and fastify.dbPool are automatically typed:

import { FastifyInstance } from 'fastify';

async function myRoute(fastify: FastifyInstance) {
  // TypeScript knows about fastify.db and fastify.dbPool
  const result = await fastify.dbPool.query('SELECT * FROM users');
  const users = result.rows;
}

Configuration

The plugin uses the same environment variables as @qauth-labs/infra-db. Configure database connection in your .env file:

# Database URL (recommended)
DATABASE_URL=postgresql://username:password@host:port/database

# Connection Pool Settings
DB_POOL_MAX=20
DB_POOL_MIN=2
DB_POOL_IDLE_TIMEOUT=10000
DB_POOL_CONNECTION_TIMEOUT=2000

For detailed configuration options, see the @qauth-labs/infra-db README.

Lifecycle Hooks

The plugin automatically manages database connection lifecycle:

`onReady` Hook

When the Fastify server becomes ready, the plugin:

Tests the database connection
Logs a warning if the connection test fails
Logs an info message if the connection is verified

// This happens automatically
fastify.addHook('onReady', async () => {
  const isConnected = await testConnection();
  if (!isConnected) {
    fastify.log.warn('Database connection test failed on ready');
  } else {
    fastify.log.info('Database connection verified');
  }
});

`onClose` Hook

When the Fastify server is closing, the plugin:

Logs that it's closing the database connection
Calls pool.end() to gracefully close all connections
Logs confirmation that the connection is closed

// This happens automatically
fastify.addHook('onClose', async () => {
  fastify.log.info('Closing database connection...');
  await pool.end();
  fastify.log.info('Database connection closed');
});

Integration with @qauth-labs/infra-db

This plugin wraps the @qauth-labs/infra-db library. The underlying database connection is managed by @qauth-labs/infra-db, and this plugin provides Fastify-specific lifecycle management.

You can still use utilities from @qauth-labs/infra-db directly:

import { db, pool, testConnection } from '@qauth-labs/infra-db';

// These use the same database connection
const isConnected = await testConnection();
const result = await pool.query('SELECT NOW()');

Error Handling

The plugin handles errors gracefully:

Connection failures: Logged as warnings, but don't prevent server startup
Shutdown errors: Logged but don't prevent graceful shutdown
Database operations: Should be wrapped in try-catch blocks in your route handlers

fastify.get('/safe-query', async (request, reply) => {
  try {
    const result = await fastify.dbPool.query('SELECT * FROM users');
    return { users: result.rows };
  } catch (error) {
    fastify.log.error(error, 'Database operation failed');
    reply.code(500).send({ error: 'Database unavailable' });
  }
});

Best Practices

Register Early: Register the database plugin early in your plugin registration order, especially if other plugins depend on the database.
Connection Pooling: Always use the connection pool (fastify.dbPool) rather than creating new connections. The pool manages connections efficiently.
Release Connections: When using pool.connect(), always release the connection in a finally block to prevent connection leaks.
Error Handling: Always wrap database operations in try-catch blocks in production code.
Connection Testing: The plugin automatically tests connections on ready, but you can also test manually using utilities from @qauth-labs/infra-db.
Graceful Shutdown: The plugin handles graceful shutdown automatically. Ensure your Fastify server properly handles SIGTERM and SIGINT signals.
Use Drizzle ORM: For type-safe database operations, prefer using Drizzle ORM (fastify.db) over raw SQL queries when possible.

Example: Complete Integration

import Fastify from 'fastify';
import { databasePlugin } from '@qauth-labs/fastify-plugin-db';

const fastify = Fastify();

// Register database plugin
await fastify.register(databasePlugin);

// Health check using connection pool
fastify.get('/health', async (request, reply) => {
  try {
    const result = await fastify.dbPool.query('SELECT NOW()');
    return {
      status: 'healthy',
      timestamp: result.rows[0].now,
    };
  } catch (error) {
    fastify.log.error(error, 'Health check failed');
    reply.code(503).send({ status: 'unhealthy' });
  }
});

// Example route with proper connection management
fastify.get('/users/:id', async (request, reply) => {
  const { id } = request.params as { id: string };

  const client = await fastify.dbPool.connect();
  try {
    const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);

    if (result.rows.length === 0) {
      reply.code(404).send({ error: 'User not found' });
      return;
    }

    return { user: result.rows[0] };
  } catch (error) {
    fastify.log.error(error, 'Failed to fetch user');
    reply.code(500).send({ error: 'Internal server error' });
  } finally {
    client.release();
  }
});

await fastify.listen({ port: 3000 });

Migrations

Database migrations are managed through the @qauth-labs/infra-db library. See the @qauth-labs/infra-db README for migration commands.

Development

Running Tests

nx test fastify-plugin-db

Linting

nx lint fastify-plugin-db

Dependencies

@qauth-labs/infra-db: Core database connection and Drizzle ORM
fastify-plugin: Fastify plugin wrapper
pg: PostgreSQL client (via @qauth-labs/infra-db)
drizzle-orm: TypeScript ORM (via @qauth-labs/infra-db)

Related Libraries

@qauth-labs/infra-db: Core database utilities, Drizzle ORM, and repository pattern
@qauth-labs/shared-errors: Error classes used by repositories
@qauth-labs/fastify-plugin-cache: Cache plugin for Fastify

License

Apache-2.0

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Fastify Database Plugin

Overview

Installation

Usage

Basic Registration

With Other Plugins

Using Database in Routes

Using Repositories

Using Repositories with Transactions

API

Plugin Registration

Fastify Instance Decorators

`fastify.db`

`fastify.dbPool`

TypeScript Support

Configuration

Lifecycle Hooks

`onReady` Hook

`onClose` Hook

Integration with @qauth-labs/infra-db

Error Handling

Best Practices

Example: Complete Integration

Migrations

Development

Running Tests

Linting

Dependencies

Related Libraries

License

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls

Fastify Database Plugin

Overview

Installation

Usage

Basic Registration

With Other Plugins

Using Database in Routes

Using Repositories

Using Repositories with Transactions

API

Plugin Registration

Fastify Instance Decorators

fastify.db

fastify.dbPool

TypeScript Support

Configuration

Lifecycle Hooks

onReady Hook

onClose Hook

Integration with @qauth-labs/infra-db

Error Handling

Best Practices

Example: Complete Integration

Migrations

Development

Running Tests

Linting

Dependencies

Related Libraries

License

`fastify.db`

`fastify.dbPool`

`onReady` Hook

`onClose` Hook