InkPG - PostgreSQL Adapter for FiveM

High-performance PostgreSQL database adapter designed specifically for FiveM/FXServer environments. Built with TypeScript and postgres.js, InkPG provides a modern, feature-rich API for game server database operations.

Features

• Modern PostgreSQL Driver: Built on postgres.js with binary protocol support
• Connection Pooling: Optimized pool management with health monitoring
• Multiple Placeholder Styles: Support for ?, :named, and @named parameters
• Promise & Callback: Dual API support for flexibility
• Type Safety: Full TypeScript support with type definitions
• FXServer Optimized: Thread-safe operations with proper async handling
• Transactions: Full ACID transaction support with isolation levels
• Batch Operations: Efficient bulk insert/update/upsert operations
• Query Builder: Fluent API for dynamic SQL generation
• Connection Monitoring: Real-time pool stats, health checks, slow query detection
• LISTEN/NOTIFY: Real-time database event notifications
• PostGIS Integration: Geospatial queries for game coordinates
• PgBouncer Support: Connection pooling optimization and mode detection

Installation

1. Clone or download this resource to your FiveM server:

cd resources
git clone https://github.com/yourusername/ink_postgresql.git

2. Install dependencies:

cd ink_postgresql
pnpm install

3. Build the resource:

pnpm build

4. Add to your server.cfg before any resources that use it:

ensure ink_postgresql
set postgresql_connection_string "postgresql://user:password@localhost:5432/database"

# Then ensure other resources that depend on it
ensure your_gamemode

Important: ink_postgresql must be started before any resource that uses it.

Configuration

Set your PostgreSQL connection string in server.cfg:

# Basic connection
set postgresql_connection_string "postgresql://username:password@localhost:5432/database"

# With SSL
set postgresql_connection_string "postgresql://user:pass@host:5432/db?sslmode=require"

# With custom pool settings
set postgresql_connection_string "postgresql://user:pass@host:5432/db?max=20&idleTimeout=30"

Connection Parameters

• sslmode: SSL mode (disable, require, verify-ca, verify-full)
• max: Maximum pool size (default: 10)
• idleTimeout: Idle connection timeout in seconds (default: 30)
• connectionTimeout: Connection timeout in seconds (default: 10)

Quick Start

Initialization

Always wait for the database to be ready before executing queries:

local InkPG = exports.ink_postgresql

-- Wait for database connection
InkPG:ready(function()
    print('Database is ready!')
    
    -- Safe to execute queries here
    InkPG:execute([[
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255),
            created_at TIMESTAMP DEFAULT NOW()
        )
    ]], {})
end)

Basic Queries

local InkPG = exports.ink_postgresql

-- Query with promise (await)
local users = InkPG:query('SELECT * FROM users WHERE active = $1', {true})

-- Query with callback
InkPG:query('SELECT * FROM players WHERE id = $1', {playerId}, function(result)
    if result and #result > 0 then
        print('Found player:', result[1].name)
    end
end)

-- Single row query
local user = InkPG:single('SELECT * FROM users WHERE id = $1', {userId})

-- Insert with RETURNING
local result = InkPG:insert(
    'INSERT INTO players (name, money) VALUES ($1, $2) RETURNING id',
    {'John Doe', 5000}
)
print('Inserted ID:', result.insertId)

Note: Use the wrapper pattern local InkPG = exports.ink_postgresql for better performance and cleaner code.

Transactions

-- Automatic transaction with rollback on error
InkPG:transaction(function(tx)
    tx:query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', {100, senderId})
    tx:query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', {100, receiverId})
end)

Batch Operations

-- Batch insert (efficient for multiple rows)
InkPG:batchInsert('players', 
    {'name', 'money', 'created_at'},
    {
        {'Player 1', 5000, os.date()},
        {'Player 2', 3000, os.date()},
        {'Player 3', 7000, os.date()}
    }
)

Real-time Events (LISTEN/NOTIFY)

-- Listen for database changes
InkPG:listen('player_updates', function(notification)
    local data = json.decode(notification.payload)
    print('Player updated:', data.player_id)
end)

Geospatial Queries (PostGIS)

local postgis = InkPG:postgis()

-- Find players within radius
local nearbyPlayers = postgis:findWithinDistance(
    'players', 'position',
    {x = -1234.5, y = 678.9},
    100.0  -- radius in meters
)

Documentation

• API Reference - Complete API documentation
• Examples - Usage examples and patterns
• Migration Guide - Migrating from MySQL/oxmysql

Performance

• Binary protocol for faster data transfer
• Connection pooling (default: 10 connections)
• Prepared statement caching
• Batch operations for bulk inserts/updates
• Query monitoring and optimization tools

Benchmark results (compared to traditional approaches):

• Batch insert: ~10x faster than individual inserts
• Prepared statements: ~30% faster than dynamic queries
• Connection pooling: Handles 1000+ concurrent queries efficiently

Development

# Install dependencies
pnpm install

# Development mode (watch)
pnpm dev

# Build
pnpm build

# Run benchmarks
pnpm benchmark

License

GPL-3.0 License - see LICENSE file for details.

Contributing

Contributions welcome! Please open an issue or pull request.

---

InkPG - Built for the FiveM community