Migration Guide

Guide for migrating existing database implementations to InkPG.

General Migration Steps

Install InkPG: Add InkPG to your server resources and build it
Configure Connection: Set PostgreSQL connection string in server.cfg
Load Order: Ensure InkPG loads before resources that use it
Update Database Schema: Convert MySQL schema to PostgreSQL
Update Queries: Replace existing database calls with InkPG API
Initialize Properly: Use ready() callback for initialization
Test Thoroughly: Verify all database operations work correctly

Resource Setup

server.cfg Load Order

Critical: InkPG must load before resources that use it:

# 1. Load InkPG first
ensure ink_postgresql
set postgresql_connection_string "postgresql://user:password@localhost:5432/database"

# 2. Then load dependent resources
ensure your_gamemode
ensure your_framework

Resource Dependency

In your resource's fxmanifest.lua:

fx_version 'cerulean'
game 'gta5'

dependency 'ink_postgresql'

server_scripts {
    'server.lua'
}

Initialization Pattern

Critical: Always use ready() callback before executing queries on resource start.

Before (MySQL):

-- MySQL adapters often connect automatically
MySQL.ready(function()
    -- Some adapters use this
end)

-- Or queries work immediately
local result = MySQL.Sync.fetchAll('SELECT * FROM users', {})

After (InkPG):

-- Use wrapper pattern
local InkPG = exports.ink_postgresql

-- ALWAYS wait for ready on resource start
InkPG:ready(function()
    print('Database connected!')
    
    -- Create tables, run migrations
    InkPG:execute([[
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            identifier VARCHAR(255) UNIQUE NOT NULL
        )
    ]], {})
    
    -- Other initialization code
end)

-- After initialization, queries can be used anywhere
RegisterCommand('getuser', function(source, args)
    local user = InkPG:single('SELECT * FROM users WHERE id = $1', {args[1]})
    print('User:', json.encode(user))
end)

Connection Configuration

Before (Various adapters)

# MySQL connection string
set mysql_connection_string "mysql://user:password@localhost/database"

After (InkPG)

# PostgreSQL connection string
set postgresql_connection_string "postgresql://user:password@localhost:5432/database"

Schema Migration

Data Type Conversions

MySQL	PostgreSQL	Notes
`INT AUTO_INCREMENT`	`SERIAL` or `BIGSERIAL`	Auto-incrementing integers
`VARCHAR(n)`	`VARCHAR(n)`	Same
`TEXT`	`TEXT`	Same
`LONGTEXT`	`TEXT`	PostgreSQL TEXT has no size limit
`TINYINT(1)`	`BOOLEAN`	Use proper boolean type
`DATETIME`	`TIMESTAMP`	PostgreSQL preferred
`JSON`	`JSONB`	Binary JSON for better performance
`BLOB`	`BYTEA`	Binary data

Example Schema Conversion

MySQL:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    data LONGTEXT,
    active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

PostgreSQL:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    data JSONB,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW()
);

Index Migration

MySQL:

CREATE INDEX idx_users_email ON users(email);

PostgreSQL:

CREATE INDEX idx_users_email ON users(email);

-- For JSONB data, use GIN indexes
CREATE INDEX idx_users_data ON users USING GIN(data);

Query Syntax Updates

Basic Queries

Most simple queries work without changes:

-- Works in both
local users = InkPG:query('SELECT * FROM users WHERE active = $1', {true})

INSERT with Auto-Increment ID

Before:

-- Various adapters
MySQL.Async.insert('INSERT INTO users (name) VALUES (?)', {'John'}, function(id)
    print('Inserted ID:', id)
end)

After (InkPG):

local result = InkPG:insert('INSERT INTO users (name) VALUES ($1)', {'John'})
print('Inserted ID:', result.insertId)

-- Or with callback
InkPG:insert('INSERT INTO users (name) VALUES ($1)', {'John'}, function(result)
    print('Inserted ID:', result.insertId)
end)

UPDATE Queries

Before:

MySQL.Async.execute('UPDATE users SET active = ? WHERE id = ?', {false, 5}, function(affectedRows)
    print('Updated:', affectedRows)
end)

After:

local result = InkPG:update('UPDATE users SET active = $1 WHERE id = $2', {false, 5})
print('Updated:', result.affectedRows)

UPSERT Operations

MySQL (ON DUPLICATE KEY UPDATE):

INSERT INTO users (id, name, score) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE score = VALUES(score)

PostgreSQL (ON CONFLICT):

INSERT INTO users (id, name, score) VALUES ($1, $2, $3)
ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score

Lua Example:

InkPG:execute([[
    INSERT INTO users (id, name, score) VALUES ($1, $2, $3)
    ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score
]], {1, 'John', 100})

LIMIT and OFFSET

MySQL:

SELECT * FROM users LIMIT 10, 20  -- Skip 10, take 20

PostgreSQL:

SELECT * FROM users LIMIT 20 OFFSET 10  -- Take 20, skip 10

Lua Example:

local users = InkPG:query('SELECT * FROM users LIMIT $1 OFFSET $2', {20, 10})

String Functions

MySQL	PostgreSQL	Example
`CONCAT(a, b)`	`a \|\| b`	`'SELECT name \|\| ' ' \|\| surname FROM users'`
`IFNULL(val, default)`	`COALESCE(val, default)`	`COALESCE(name, 'Unknown')`
`NOW()`	`NOW()`	Same
`CURDATE()`	`CURRENT_DATE`	PostgreSQL
`REGEXP 'pattern'`	`~ 'pattern'`	Regex operator

JSON Operations

MySQL:

MySQL.Async.fetchAll("SELECT * FROM players WHERE JSON_EXTRACT(data, '$.job') = ?", {'police'}, function(result)
    -- ...
end)

PostgreSQL (JSONB):

local players = InkPG:query("SELECT * FROM players WHERE data->>'job' = $1", {'police'})

JSON Operators:

-> Extract JSON object field
->> Extract JSON object field as text
@> Contains
? Key exists

-- Check if JSON contains key
InkPG:query("SELECT * FROM players WHERE data ? 'inventory'", {})

-- Check if JSON contains value
InkPG:query("SELECT * FROM players WHERE data @> $1", {json.encode({job = 'police'})})

Common Framework Migrations

ESX Framework

Before:

MySQL.Async.fetchAll('SELECT * FROM users WHERE identifier = @identifier', {
    ['@identifier'] = xPlayer.identifier
}, function(result)
    -- handle result
end)

After:

local result = InkPG:query('SELECT * FROM users WHERE identifier = $1', {xPlayer.identifier})
-- or with named params
local result = InkPG:query('SELECT * FROM users WHERE identifier = :identifier', {
    identifier = xPlayer.identifier
})

QB-Core Framework

Before:

exports.oxmysql:execute('UPDATE players SET money = ? WHERE citizenid = ?', {newAmount, citizenid})

After:

InkPG:update('UPDATE players SET money = $1 WHERE citizenid = $2', {newAmount, citizenid})

Parameter Format Changes

InkPG supports multiple formats for compatibility:

Positional Placeholders

-- ? style (auto-converted to $1, $2)
InkPG:query('SELECT * FROM users WHERE name = ? AND age > ?', {'John', 18})

-- $n style (native PostgreSQL)
InkPG:query('SELECT * FROM users WHERE name = $1 AND age > $2', {'John', 18})

Named Placeholders

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

-- @name style
InkPG:query('SELECT * FROM users WHERE name = @name', {
    name = 'John'
})

Async/Await vs Callbacks

InkPG supports both patterns:

Callback Style

InkPG:query('SELECT * FROM users WHERE id = $1', {1}, function(result)
    if result and #result > 0 then
        print('User found:', result[1].name)
    end
end)

Async/Await Style (Recommended)

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

Testing Your Migration

1. Test Connection

RegisterCommand('testdb', function()
    if InkPG:isReady() then
        print('Database connected successfully')
    else
        print('Database not ready')
    end
end, false)

2. Test Basic Query

RegisterCommand('testquery', function()
    local result = InkPG:query('SELECT COUNT(*) as total FROM users', {})
    if result and result[1] then
        print('Total users:', result[1].total)
    end
end, false)

3. Test Insert

RegisterCommand('testinsert', function()
    local result = InkPG:insert('INSERT INTO test_table (name) VALUES ($1)', {'Test User'})
    print('Inserted ID:', result.insertId)
end, false)

Troubleshooting

Issue: Queries returning no results

Cause: PostgreSQL is case-sensitive for table/column names in quotes.

Solution: Don't quote identifiers or use lowercase:

-- Bad
InkPG:query('SELECT * FROM "Users"', {})

-- Good
InkPG:query('SELECT * FROM users', {})

Issue: Date/Time format errors

Cause: PostgreSQL uses different date formats.

Solution: Use proper PostgreSQL date functions:

-- Use NOW() instead of CURRENT_TIMESTAMP
InkPG:execute('UPDATE users SET last_login = NOW() WHERE id = $1', {userId})

-- Use proper date casting
InkPG:query("SELECT * FROM logs WHERE created_at > $1::timestamp", {'2024-01-01 00:00:00'})

Issue: Boolean values not working

Cause: MySQL uses TINYINT(1), PostgreSQL uses BOOLEAN.

Solution: Update schema and use true/false:

-- MySQL style (don't do this)
InkPG:update('UPDATE users SET active = $1', {1})

-- PostgreSQL style (correct)
InkPG:update('UPDATE users SET active = $1', {true})

Issue: JSON data not queryable

Cause: Using JSON instead of JSONB.

Solution: Alter table to use JSONB and create GIN index:

ALTER TABLE players ALTER COLUMN data TYPE JSONB USING data::jsonb;
CREATE INDEX idx_players_data ON players USING GIN(data);

Performance Optimization After Migration

Create Indexes: Identify slow queries and add indexes

CREATE INDEX idx_users_identifier ON users(identifier);
CREATE INDEX idx_players_data ON players USING GIN(data); -- For JSONB

Use JSONB: Convert JSON columns to JSONB for better performance
Analyze Tables: Update PostgreSQL statistics

ANALYZE users;
ANALYZE players;

Connection Pooling: Adjust pool size in connection string

set postgresql_connection_string "postgresql://user:pass@host/db?max=20"

Use Prepared Statements: InkPG does this automatically for repeated queries

Getting Help

If you encounter issues during migration:

Check PostgreSQL logs for detailed error messages
Verify your schema matches PostgreSQL syntax
Test queries directly in PostgreSQL client (psql, pgAdmin)
Review InkPG console output for connection/query errors

Common resources:

PostgreSQL Documentation: https://www.postgresql.org/docs/
PostgreSQL vs MySQL Comparison: https://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL

FilesExpand file tree

MIGRATION.md

Latest commit

History

MIGRATION.md

File metadata and controls

Migration Guide

General Migration Steps

Resource Setup

server.cfg Load Order

Resource Dependency

Initialization Pattern

Connection Configuration

Before (Various adapters)

After (InkPG)

Schema Migration

Data Type Conversions

Example Schema Conversion

Index Migration

Query Syntax Updates

Basic Queries

INSERT with Auto-Increment ID

UPDATE Queries

UPSERT Operations

LIMIT and OFFSET

String Functions

JSON Operations

Common Framework Migrations

ESX Framework

QB-Core Framework

Parameter Format Changes

Positional Placeholders

Named Placeholders

Async/Await vs Callbacks

Callback Style

Async/Await Style (Recommended)

Testing Your Migration

1. Test Connection

2. Test Basic Query

3. Test Insert

Troubleshooting

Issue: Queries returning no results

Issue: Date/Time format errors

Issue: Boolean values not working

Issue: JSON data not queryable

Performance Optimization After Migration

Getting Help