nuxt-pg

PostgreSQL database module for Nuxt with automatic transaction management

nuxt-pg provides a clean, type-safe PostgreSQL integration for Nuxt 3+ with automatic transaction context tracking. Write cleaner code by eliminating transaction parameter passing throughout your application.

✨ Features

• 🔄 Automatic Transaction Context - No need to pass transaction objects through function calls
• 🎯 Clean API - Simple getDatabase() composable for all database operations
• 🔒 Type-Safe - Full TypeScript support with proper type inference
• ⚡ Connection Pooling - Built-in connection pool management with node-postgres
• ⚙️ Flexible Pool Configuration - Customize pool settings globally or per-environment
• 🛡️ Request Isolation - Transactions are automatically scoped per-request
• 🔌 Graceful Shutdown - Properly closes connections on server shutdown
• 🧪 Fully Tested - Comprehensive test coverage

📦 Installation

npm install nuxt-pg pg

🚀 Quick Start

1. Add to your nuxt.config.ts

export default defineNuxtConfig({
    modules: ['nuxt-pg'],
    
    runtimeConfig: {
        nuxtpg: {
            db: {
                connectionString: process.env.DATABASE_URL
            },
            dev: {
                db: {
                    connectionString: process.env.DEV_DATABASE_URL
                }
            }
        }
    },
    
    experimental: {
        asyncContext: true  // Required for automatic transaction tracking
    }
})

2. Use in your API routes

// server/api/users/[id].get.ts
export default defineEventHandler(async (event) => {
    const db = getDatabase()
    const id = getRouterParam(event, 'id')
    
    const users = await db.query(
        'SELECT * FROM users WHERE id = $1',
        [id]
    )
    
    return users[0]
})

📖 Usage

Basic Queries

const db = getDatabase()

// Simple query
const users = await db.query('SELECT * FROM users')

// With parameters
const user = await db.query(
    'SELECT * FROM users WHERE email = $1',
    ['[email protected]']
)

// With TypeScript types
interface User {
    id: string
    name: string
    email: string
}

const users = await db.query<User>(
    'SELECT * FROM users WHERE active = $1',
    [true]
)

Transactions

Automatic (Recommended)

export default defineEventHandler(async (event) => {
    const db = getDatabase()
    
    return await db.transactional(async (txn) => {
        // All queries automatically use the same transaction
        await txn.query('INSERT INTO users (name) VALUES ($1)', ['Alice'])
        await txn.query('INSERT INTO profiles (user_id) VALUES ($1)', [userId])
        
        // Automatically commits on success, rolls back on error
        return { success: true }
    })
})

Manual Control

export default defineEventHandler(async (event) => {
    const db = getDatabase()
    
    await db.createTransaction()
    
    try {
        // All queries in this request context use the transaction
        await db.query('INSERT INTO users ...')
        await db.query('UPDATE profiles ...')
        
        await db.commitTransaction()
        return { success: true }
    } catch (error) {
        await db.rollbackTransaction()
        throw error
    }
})

Context-Aware Queries (No Transaction Passing!)

// repository.ts
export class UserRepository {
    static async create(data: CreateUserData) {
        const db = getDatabase()
        // Automatically uses transaction if one is active in the request context
        return db.query('INSERT INTO users ...', [...])
    }
    
    static async updateProfile(userId: string, data: any) {
        const db = getDatabase()
        // Also uses the same transaction automatically
        return db.query('UPDATE profiles ...', [...])
    }
}

// handler.ts
export default defineEventHandler(async (event) => {
    const db = getDatabase()
    
    // Both repository methods use the same transaction automatically!
    return await db.transactional(async () => {
        await UserRepository.create({ name: 'Alice' })
        await UserRepository.updateProfile(userId, { bio: 'Hello' })
    })
})

🔧 Configuration

Basic Configuration

runtimeConfig: {
    nuxtpg: {
        db: {
            connectionString: string  // Production connection string
        },
        dev: {
            db: {
                connectionString: string  // Development connection string
            }
        }
    }
}

Connection Pool Options

Customize the connection pool behavior using the pool configuration. Pool settings can be defined at three levels with the following priority (highest to lowest):

1. Environment-specific (db.pool or dev.db.pool) - Highest priority
2. Global (pool) - Applies to both dev and production
3. Defaults - Built-in fallback values

Available Pool Options

| Option | Type | Default | Description | |--------|------|---------|-------------| | maxConnections | number | 40 | Maximum number of clients in the pool | | minConnections | number | 2 | Minimum number of clients to maintain | | idleTimeoutMs | number | 30000 | Time (ms) a client can be idle before being closed | | connectionTimeoutMs | number | 2000 | Time (ms) to wait for a connection before timing out | | ssl | boolean | true | Enable SSL connection (uses rejectUnauthorized: false) |

Configuration Examples

Global Pool Settings (applies to both dev and production):

export default defineNuxtConfig({
    runtimeConfig: {
        nuxtpg: {
            pool: {
                maxConnections: 25,
                minConnections: 5,
                idleTimeoutMs: 60000,
                ssl: false
            },
            db: {
                connectionString: process.env.DATABASE_URL
            },
            dev: {
                db: {
                    connectionString: process.env.DEV_DATABASE_URL
                }
            }
        }
    }
})

Environment-Specific Pool Settings:

export default defineNuxtConfig({
    runtimeConfig: {
        nuxtpg: {
            // Production settings
            db: {
                connectionString: process.env.DATABASE_URL,
                pool: {
                    maxConnections: 50,
                    minConnections: 10,
                    ssl: true
                }
            },
            // Development settings
            dev: {
                db: {
                    connectionString: process.env.DEV_DATABASE_URL,
                    pool: {
                        maxConnections: 10,
                        minConnections: 2,
                        ssl: false
                    }
                }
            }
        }
    }
})

Mixed Configuration (global defaults with environment-specific overrides):

export default defineNuxtConfig({
    runtimeConfig: {
        nuxtpg: {
            // Global defaults
            pool: {
                minConnections: 3,
                idleTimeoutMs: 45000,
                connectionTimeoutMs: 3000
            },
            // Production overrides only what's needed
            db: {
                connectionString: process.env.DATABASE_URL,
                pool: {
                    maxConnections: 60,
                    ssl: true
                }
            },
            // Dev uses global settings but overrides maxConnections
            dev: {
                db: {
                    connectionString: process.env.DEV_DATABASE_URL,
                    pool: {
                        maxConnections: 8
                    }
                }
            }
        }
    }
})

📚 API Reference

getDatabase()

Returns the database service instance with the following methods:

query<T>(sql: string, params?: any[]): Promise<T[]>

Execute a SQL query. Automatically uses active transaction if available.

createTransaction(timeoutMs?: number): Promise<DBTransaction>

Start a new transaction in the current request context.

commitTransaction(): Promise<void>

Commit the active transaction.

rollbackTransaction(): Promise<void>

Rollback the active transaction.

transactional<T>(fn: (txn: DBTransaction) => Promise<T>, timeoutMs?: number): Promise<T>

Execute a function within a transaction with automatic commit/rollback.

isHealthy(): boolean

Check if the database connection pool is healthy.

shutdown(): Promise<void>

Gracefully shutdown the database service.

⚙️ How It Works

nuxt-pg leverages Nuxt's experimental asyncContext feature to track database transactions across async boundaries. When you start a transaction, it's stored in the request context and automatically used by all subsequent queries in that request - no need to pass transaction objects around!

// ❌ Old way (manual transaction passing)
await userService.create(data, transaction)
await profileService.create(profileData, transaction)

// ✅ New way (automatic context tracking)
await userService.create(data)
await profileService.create(profileData)

🧪 Testing

Tests are written using Vitest with full coverage of all components:

npm test

📝 Requirements

• Nuxt 3.0+
• Node.js 18+
• PostgreSQL 12+
• experimental.asyncContext: true must be enabled

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

📄 License

MIT License

Credits

Built with:

• node-postgres (pg)
• Nuxt 4
• h3