InkPG API Reference

Complete API documentation for InkPG PostgreSQL adapter.

Initialization
Query Methods
Parameter Formats
Return Types
Error Handling

Initialization

Setup

Add to server.cfg (before any resources that use it):

ensure ink_postgresql
set postgresql_connection_string "postgresql://username:password@host:port/database"

Use in your resource:

local InkPG = exports.ink_postgresql

ready(callback)

Wait for database connection to be established before executing queries.

Important: Always use ready() before executing any database operations on resource start.

Syntax:

InkPG:ready(function()
    -- Database is connected and ready
    -- Safe to execute queries here
end)

Example:

local InkPG = exports.ink_postgresql

InkPG:ready(function()
    print('Database connected!')
    
    -- Create tables, run migrations, etc.
    InkPG:execute([[
        CREATE TABLE IF NOT EXISTS players (
            id SERIAL PRIMARY KEY,
            identifier VARCHAR(255) UNIQUE NOT NULL,
            data JSONB DEFAULT '{}'::jsonb
        )
    ]], {})
end)

Parameters:

callback (function): Called when database is ready

Returns: void

isReady()

Check if database is currently connected and ready.

Syntax:

local ready = InkPG:isReady()

Example:

if InkPG:isReady() then
    local users = InkPG:query('SELECT * FROM users', {})
else
    print('Database not ready yet!')
end

Parameters: None

Returns: boolean - true if ready, false otherwise

Query Methods

query(sql, params, callback?)

Execute a query and return all matching rows.

Syntax:

InkPG:query(sql, params, callback?)

Parameters:

sql (string) - SQL query with placeholders
params (table|array) - Query parameters (optional)
callback (function) - Callback function receiving results (optional)

Returns: Array of result rows (if no callback provided)

Examples:

-- Async/await style
local users = InkPG:query('SELECT * FROM users WHERE active = $1', {true})
for _, user in ipairs(users) do
    print(user.name)
end

-- Callback style
InkPG:query('SELECT * FROM users WHERE age > $1', {18}, function(users)
    print('Found ' .. #users .. ' adult users')
end)

-- No parameters
local allUsers = InkPG:query('SELECT * FROM users', {})

single(sql, params, callback?)

Execute a query and return only the first matching row.

Syntax:

InkPG:single(sql, params, callback?)

Returns: Single row object or nil if no match

Examples:

-- Get single user
local user = InkPG:single('SELECT * FROM users WHERE id = $1', {5})
if user then
    print('Found:', user.name)
else
    print('User not found')
end

-- With callback
InkPG:single('SELECT COUNT(*) as total FROM users', {}, function(result)
    print('Total users:', result.total)
end)

insert(sql, params, callback?)

Execute an INSERT query and return insert information.

Syntax:

InkPG:insert(sql, params, callback?)

Returns:

{
    affectedRows = number,
    insertId = number | string
}

Examples:

-- Insert with auto-generated ID
local result = InkPG:insert(
    'INSERT INTO users (name, email, age) VALUES ($1, $2, $3)',
    {'John Doe', 'john@example.com', 25}
)
print('New user ID:', result.insertId)
print('Rows inserted:', result.affectedRows)

-- With callback
InkPG:insert('INSERT INTO logs (message) VALUES ($1)', {'User login'}, function(result)
    print('Log ID:', result.insertId)
end)

Note: InkPG automatically adds RETURNING * clause if not present to retrieve the insert ID.

update(sql, params, callback?)

Execute an UPDATE query and return affected rows count.

Syntax:

InkPG:update(sql, params, callback?)

Returns:

{
    affectedRows = number
}

Examples:

-- Update user
local result = InkPG:update(
    'UPDATE users SET last_login = NOW() WHERE id = $1',
    {123}
)
print('Updated', result.affectedRows, 'rows')

-- Bulk update
local updated = InkPG:update(
    'UPDATE users SET active = $1 WHERE last_login < $2',
    {false, '2024-01-01'}
)

execute(sql, params, callback?)

Execute a query without returning result data. Useful for DELETE or other operations.

Syntax:

InkPG:execute(sql, params, callback?)

Returns:

{
    affectedRows = number
}

Examples:

-- Delete rows
local result = InkPG:execute('DELETE FROM logs WHERE created_at < $1', {'2024-01-01'})
print('Deleted', result.affectedRows, 'old logs')

-- Create table
InkPG:execute([[
    CREATE TABLE IF NOT EXISTS sessions (
        id SERIAL PRIMARY KEY,
        user_id INT NOT NULL,
        token VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT NOW()
    )
]], {})

ready(callback)

Syntax:

InkPG:ready(callback)

Example:

InkPG:ready(function()
    print('Database connection established')
    -- Initialize your tables, load data, etc.
end)

isReady()

Check if the database connection is ready.

Syntax:

InkPG:isReady()

Returns: boolean

Example:

if not InkPG:isReady() then
    print('Database not ready, please wait...')
    return
end

Parameter Formats

InkPG supports multiple placeholder formats for maximum compatibility:

1. Positional ($n) - PostgreSQL Native

InkPG:query('SELECT * FROM users WHERE age > $1 AND active = $2', {18, true})

2. Question Mark (?) - Compatibility Format

InkPG:query('SELECT * FROM users WHERE name = ? AND age = ?', {'John', 25})

Automatically converted to $1, $2, etc.

3. Named Parameters (:name or @name)

-- Using colon notation
InkPG:query('SELECT * FROM users WHERE name = :name AND age > :minAge', {
    name = 'John',
    minAge = 18
})

-- Using @ notation
InkPG:query('SELECT * FROM users WHERE email = @email', {
    email = 'john@example.com'
})

Important: When using named parameters, pass a table (not an array).

Return Types

Query Results

All query results are arrays (tables) of row objects:

local users = InkPG:query('SELECT id, name, age FROM users', {})
-- users = {
--     { id = 1, name = 'John', age = 25 },
--     { id = 2, name = 'Jane', age = 30 }
-- }

for _, user in ipairs(users) do
    print(user.id, user.name, user.age)
end

Single Row Results

local user = InkPG:single('SELECT * FROM users WHERE id = $1', {1})
-- user = { id = 1, name = 'John', age = 25 } or nil

Insert Results

{
    affectedRows = 1,
    insertId = 123  -- The ID of the inserted row
}

Update/Execute Results

{
    affectedRows = 5  -- Number of rows affected
}

Error Handling

Try-Catch Pattern

local success, result = pcall(function()
    return InkPG:query('SELECT * FROM users WHERE id = $1', {123})
end)

if success then
    print('Query succeeded:', json.encode(result))
else
    print('Query failed:', result)
end

Callback Error Handling

InkPG:query('SELECT * FROM users', {}, function(result)
    if not result then
        print('Query returned no results')
        return
    end
    
    print('Got', #result, 'users')
end)

Common Errors

Database Not Ready

[InkPG] Database not initialized. Call initialize() first.

Solution: Ensure your connection string is set in server.cfg and wait for resource to start.

Connection Failed

[InkPG] Failed to connect to PostgreSQL

Solution: Check connection string, PostgreSQL server status, and network connectivity.

Invalid Placeholder

Parameter 'name' not found in parameters object

Solution: Ensure all named parameters in the query exist in the parameters object.

Advanced Usage

Transactions

InkPG provides full ACID transaction support with isolation levels:

-- Automatic transaction with rollback on error
InkPG:transaction(function(tx)
    tx:insert('INSERT INTO accounts (balance) VALUES ($1)', {100})
    tx:update('UPDATE accounts SET balance = balance - $1 WHERE id = $2', {50, 1})
    -- Automatically commits if all succeed, rolls back on error
end)

-- Manual transaction control
local tx = InkPG:beginTransaction('SERIALIZABLE')
tx:insert('INSERT INTO logs (message) VALUES ($1)', {'Transaction started'})
tx:update('UPDATE users SET balance = balance + $1 WHERE id = $2', {100, 5})

if someCondition then
    tx:commit()
else
    tx:rollback()
end

-- With savepoints
InkPG:transaction(function(tx)
    tx:insert('INSERT INTO users (name) VALUES ($1)', {'John'})
    
    local sp = tx:savepoint('before_risky')
    local success, err = pcall(function()
        tx:execute('SOME RISKY OPERATION')
    end)
    
    if not success then
        tx:rollbackTo(sp)
    end
end)

Batch Operations

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

-- Batch update
InkPG:batchUpdate('players',
    {
        {id = 1, money = 10000, level = 5},
        {id = 2, money = 8000, level = 3}
    },
    'id' -- ID column name
)

-- Batch upsert (insert or update on conflict)
InkPG:batchUpsert('player_stats',
    {'player_id', 'kills', 'deaths'},
    {
        {1, 10, 5},
        {2, 15, 8}
    },
    {'player_id'}, -- Conflict columns
    {'kills', 'deaths'} -- Columns to update
)

Query Builder

local qb = InkPG:queryBuilder()

-- Fluent API for complex queries
local richPlayers = qb
    :select({'id', 'name', 'money'})
    :from('players')
    :where('money', '>', 10000)
    :orderBy('money', 'DESC')
    :limit(10)
    :get()

-- With joins
local inventory = qb
    :select({'players.name', 'items.name as item_name'})
    :from('players')
    :join('inventory', 'players.id', 'inventory.player_id')
    :where('players.id', '=', playerId)
    :get()

LISTEN/NOTIFY (Real-time Events)

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

-- Send notification
InkPG:notify('player_updates', json.encode({
    player_id = 123,
    action = 'money_changed'
}))

PostGIS (Geospatial Queries)

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
)

-- Find nearest players
local nearest = postgis:findNearest(
    'players', 'position',
    {x = playerX, y = playerY},
    5  -- limit
)

-- Check if point is in polygon
local isInZone = postgis:pointInPolygon(
    {x = playerX, y = playerY},
    {
        {x = 0, y = 0},
        {x = 100, y = 0},
        {x = 100, y = 100},
        {x = 0, y = 100}
    }
)

Connection Monitoring

-- Health check
local health = InkPG:healthCheck()
print('Database healthy:', health.healthy)

-- Pool statistics
local stats = InkPG:getPoolStats()
print('Active connections:', stats.active)

-- Set slow query threshold
InkPG:setSlowQueryThreshold(50)

-- Listen to pool events
InkPG:onPoolEvent('slow_query', function(data)
    print('Slow query:', data.query, data.duration)
end)

JSON Data

PostgreSQL's JSONB support:

-- Insert JSON data
InkPG:insert(
    'INSERT INTO players (identifier, data) VALUES ($1, $2)',
    {'license:abc123', json.encode({inventory = {}, position = {x = 100, y = 200}})}
)

-- Query JSON fields
local player = InkPG:single(
    "SELECT * FROM players WHERE data->>'identifier' = $1",
    {'license:abc123'}
)

Array Parameters

-- Using PostgreSQL arrays
InkPG:query(
    'SELECT * FROM users WHERE id = ANY($1::int[])',
    {'{1,2,3,4,5}'}
)

Performance Best Practices

Use Prepared Statements: InkPG automatically prepares frequently used queries
Batch Operations: Use batchInsert, batchUpdate, batchUpsert for multiple rows
Index Your Tables: Create indexes on frequently queried columns
Limit Results: Use LIMIT clause to avoid retrieving large datasets
Connection Pooling: Adjust max parameter in connection string based on load
Monitor Performance: Use healthCheck() and getSlowQueryStats() to track performance
Use Query Builder: For complex dynamic queries to avoid SQL injection
PgBouncer: Consider using PgBouncer for 100+ concurrent connections

Connection String Options

postgresql://user:pass@host:port/database?option=value&option2=value2

Available options:

ssl=true - Enable SSL connection
sslmode=require|verify-ca|verify-full - SSL verification mode
max=10 - Maximum connection pool size (default: 10)
idle_timeout=30 - Idle connection timeout in seconds (default: 30)
connection_timeout=10 - Connection timeout in seconds (default: 10)

Example:

set postgresql_connection_string "postgresql://gameserver:secret@db.example.com:5432/fivem?ssl=true&max=20&idle_timeout=60"

FilesExpand file tree

API.md

Latest commit

History

API.md

File metadata and controls

InkPG API Reference

Table of Contents

Initialization

Setup

ready(callback)

isReady()

Query Methods

query(sql, params, callback?)

single(sql, params, callback?)

insert(sql, params, callback?)

update(sql, params, callback?)

execute(sql, params, callback?)

ready(callback)

isReady()

Parameter Formats

1. Positional ($n) - PostgreSQL Native

2. Question Mark (?) - Compatibility Format

3. Named Parameters (:name or @name)

Return Types

Query Results

Single Row Results

Insert Results

Update/Execute Results

Error Handling

Try-Catch Pattern

Callback Error Handling

Common Errors

Advanced Usage

Transactions

Batch Operations

Query Builder

LISTEN/NOTIFY (Real-time Events)

PostGIS (Geospatial Queries)

Connection Monitoring

JSON Data

Array Parameters

Performance Best Practices

Connection String Options