[Plugin] omp-postgresql — Asynchronous PostgreSQL Component for open.mp (v0.1.0 Experimental)

[itskoleban]

omp-postgresql - Asynchronous PostgreSQL Component for open.mp
Version 0.1.0 Experimental

Hello everyone!

I'd like to introduce omp-postgresql, an open-source PostgreSQL database component built specifically for open.mp. It is written in C++20, uses libpq, and provides an asynchronous, parameterized Pawn API through the native open.mp Component SDK.

The project focuses on predictable concurrency, safe resource management, explicit error handling, and maintainability.

The first experimental release includes downloadable Windows x86 and Linux x86 packages.

Important: This is an experimental release for evaluation and testing. It is not yet recommended for production environments.

Features

- Native open.mp component (not a legacy SA-MP plugin).
- Asynchronous database operations with Pawn callbacks dispatched on the server's main thread.
- libpq service-profile connections and configurable connection pools.
- FIFO execution lanes with dedicated session workers.
- Prepared statements and typed, bound parameters.
- Homogeneous executions and heterogeneous batches.
- Transactions, configurable isolation, rollback, and savepoints.
- PostgreSQL types including BIGINT, NUMERIC, UUID, JSONB, BYTEA, and arrays.
- SQL loading from files with path confinement and validation.
- Bounded result streaming with backpressure.
- Cancellation, timeouts, reconnection, and explicit job outcomes.
- Resource ownership tied to Pawn VM lifetimes.
- Job diagnostics, result metadata, and connection statistics.

A key design goal is to handle failures safely. The API distinguishes confirmed, failed, cancelled, and uncertain operations. Writes with uncertain outcomes are not automatically replayed.

Quick Example

This example connects using a libpq service profile, submits a parameterized query, and reads the result asynchronously:

Code:

#include <open.mp>
#include <omp-postgresql>

new g_Database;

public OnGameModeInit()
{
    g_Database = pg_connect("game", 2);

    if (!g_Database)
    {
        print("[PostgreSQL] Connection initialization failed.");
    }

    return 1;
}

public OnPgConnection(connection, connection_state)
{
    if (connection != g_Database || connection_state != PG_CONNECTED)
    {
        return 1;
    }

    new statement = pg_prepare(
        connection,
        "SELECT $1::integer AS answer",
        1
    );

    if (!statement)
    {
        return 1;
    }

    new job;
    if (pg_bind_int(statement, 1, 42) && pg_add(statement))
    {
        job = pg_execute(statement);
    }

    pg_statement_close(statement);

    if (!job)
    {
        print("[PostgreSQL] Query submission failed.");
    }

    return 1;
}

public OnPgComplete(job, context)
{
    new answer;

    if (pg_job_status(job) == PG_CONFIRMED &&
        pg_result_rows(job) > 0 &&
        pg_result_int(job, 0, 0, 0, answer))
    {
        printf("[PostgreSQL] Answer: %d", answer);
    }

    pg_job_close(job);
    return 1;
}

public OnGameModeExit()
{
    if (g_Database)
    {
        pg_connection_close(g_Database);
    }

    return 1;
}

The "game" connection profile must be configured through libpq before starting the server. Credentials do not need to be embedded in the gamemode. See the installation guide and the complete runnable examples in the repository.

Testing and Validation

The project has automated validation covering:

- Unit tests and PostgreSQL integration tests.
- Windows and Linux x86 packages tested with official open.mp servers.
- Engine tests on Windows and Linux x64.
- AddressSanitizer and UndefinedBehaviorSanitizer on supported Linux builds.
- Connection failures, cancellation, reconnection, and transaction recovery.
- Pawn VM unload and callback lifecycle scenarios.
- Concurrent workloads and resource teardown stress tests.
- Package architecture, dependencies, checksums, and installation checks.

The experimental release passed six CI jobs. The default stress suite covers 4,640 SQL operations, and a separate local run completed 11,600 operations. These are test results, not production guarantees or general throughput benchmarks.

Installation

1. Download the Windows x86 or Linux x86 package from GitHub Releases.
2. Place the component in the open.mp server's components/ directory.
3. Install omp-postgresql.inc in the Pawn compiler's include directory.
4. Configure PGSERVICEFILE and the required libpq authentication settings.
5. Review the installation guide for platform-specific runtime dependencies.

The published packages are not fully self-contained. Compatible system runtime libraries may still be required, especially for x86 deployments.

Current Status and Roadmap

The project is under active development. Before the first Release Candidate, priorities include longer-duration failure and stress tests, malformed AMX input testing, additional performance measurements, and an independent audit.

The initial scope intentionally excludes an ORM, synchronous Pawn queries, COPY transfers, LISTEN/NOTIFY reception, and PostgreSQL pipeline mode.

Downloads and Documentation

GitHub repository:
https://github.com/itskoleban/omp-postgresql

Experimental release:
https://github.com/itskoleban/omp-postgr...0fe2f23787

Pawn API documentation:
https://github.com/itskoleban/omp-postgr...ocs/api.md

Installation guide:
https://github.com/itskoleban/omp-postgr...llation.md

Validation and known limitations:
https://github.com/itskoleban/omp-postgr...idation.md

Bug reports and suggestions:
https://github.com/itskoleban/omp-postgresql/issues

License: MIT

Feedback Welcome

This is the first public experimental release, and feedback from the open.mp community would be greatly appreciated.

I'm particularly interested in feedback about the Pawn API, installation, compatibility, concurrency behavior, and edge cases. If you encounter a problem, please open a GitHub issue with reproduction steps, your operating system, and relevant logs.

Contributions and pull requests are welcome.

Thanks for taking a look!