Database Connection Pool Library | Libzdb

11 min read Original article ↗

A small, easy to use Open Source Database Connection Pool Library with the following features:

  • Thread safe Database Connection Pool
  • Connect to multiple database systems simultaneously
  • Zero runtime configuration, connect using a URL scheme
  • Supports MySQL, PostgreSQL, SQLite and Oracle

Download

Requirements: Runs on iOS, Linux, macOS, FreeBSD, Solaris, OpenBSD and other POSIX systems. A C11 compiler is required to build the library.

Compatible with and can be included in C++ or Objective-C projects

Compatible with and can be included in C++ or Objective-C projects.

Modern, Object Oriented API design. Fully documented.

The library is licensed under a Free Open Source Software License.

Can I use libzdb in my iOS or macOS app?

Yes, libzdb can be used from and included in any C, C++ or Objective-C project. The Xcode project file used to develop libzdb is available from the repository and can be included in your own Xcode project.

Is the library thread-safe?

Libzdb is thread-safe and designed to be used in a multi-threaded program.

Is the connection pool dynamic?

Yes, the pool can be setup to dynamically change the number of Connections in the pool depending on the load.

Can I connect to multiple database systems at the same time?

Of course, libzdb is the perfect glue layer between different database systems or databases. For instance, you can read data from MySQL and write to PostgresSQL in just a few lines of code

Public code repository?

The project is hosted at Bitbucket. Click the r icon in the footer below to visit

Are there plans to support additional database systems?

Libzdb currently supports SQLite, MySQL, PostgreSQL and Oracle. At the moment there are no plans to support additional database systems.

C

To use libzdb in your C project (C11 or later), include zdb.h: 

#include <zdb.h>

Start by reading the documentation for the Connection Pool.

You can also view the file list directly. Each C module is extensively documented with example code.

Read below about the Connection URL that is used to specify the Database connection.

C++

To use libzdb in your C++ project (C++20 or later), import zdbpp.h and use the namespace zdb: 

#include <zdbpp.h>
using namespace zdb;

Start by reading the introduction to libzdb for C++. At the bottom of that page is an overview of the classes in the library.

You can also view the namespace class list directly. Each class is extensively documented with example code.

The URL given to a Connection Pool at creation time specify a database connection on the standard URL format. The format of the connection URL is defined as:

database://[user:password@][host][:port]/database[?name=value][&name=value]...

The property names user and password are always recognized and specify how to login to the database. Other properties depends on the database server in question. User name and password can alternatively be specified in the auth-part of the URL. If port number is omitted, the default port number for the database server is used. Reserved characters used in the Connection URL must be URL encoded.

MySQL:

Here is an example on how to connect to a MySQL database server:

mysql://localhost:3306/test?user=root&password=swordfish

In this case the username, root and password, swordfish are specified as properties to the URL. An alternative is to use the auth-part of the URL to specify authentication information:

mysql://root:swordfish@localhost:3306/test

See mysql options for all properties that can be set for a mysql connection URL.

SQLite:

For a SQLite database the connection URL should simply specify a database file, since a SQLite database is just a file in the filesystem. SQLite uses pragma commands for performance tuning and other special purpose database commands. Pragma syntax on the form, name=value can be added as properties to the URL and will be set when the Connection is created. In addition to pragmas, the following properties are supported:

  • shared-cache=true - Make SQLite use shared-cache if value is true (default is false). Using shared cache can significantly reduce database lock errors and improve concurrency in multi-threaded scenarios. It is also recommended to build libsqlite and libzdb with unlock notify.
  • serialized=true - Make SQLite switch to serialized mode if value is true, otherwise multi-thread mode is used (the default).

An URL for connecting to a SQLite database might look like:

sqlite:///var/sqlite/test.db?synchronous=normal&foreign_keys=on&journal_mode=wal&temp_store=memory

PostgreSQL:

The URL for connecting to a PostgreSQL database server might look like:

postgresql://localhost:5432/test?user=root&password=swordfish

As with the MySQL URL, the username and password are specified as properties to the URL. Likewise, the auth-part of the URL can be used instead to specify the username and the password:

postgresql://root:swordfish@localhost/test?use-ssl=true

In this example we have also omitted the port number to the server, in which case the default port number, 5432, for PostgreSQL is used. In addition we have added an extra parameter to the URL, so connection to the server is done over a secure SSL connection.

See postgresql options for all properties that can be set for a postgresql connection URL.

Oracle:

The URL for connecting to an Oracle database server might look like:

oracle://localhost:1521/servicename?user=scott&password=tiger

Instead of a database name, Oracle uses a service name. The information in the url above is typically specified in a tnsnames.ora configuration file, pointed to by the environment variable TNS_ADMIN. In the example below, instead of host, port and service name, we use a tnsname as defined in tnsnames.ora. We also use the auth-part of the URL to specify the username and the password. Finally, we specify that we want to connect to Oracle with the SYSDBA role.

oracle://sys:secret@/tnsname?sysdba=true

See oracle options for all properties that can be set for an oracle connection URL.

Bridging Your Database Systems

Libzdb allows you to create multiple ConnectionPool objects, each connecting to different database systems as needed. This makes libzdb an excellent tool for querying, copying, and moving data between various database systems and instances.

The example below demonstrates this capability by copying data from a MySQL database to a PostgreSQL database. While simplified, it illustrates how libzdb can effectively serve as a connecting layer between your different databases.

Copying Apples to Oranges:

try {
    // Start a ConnectionPool to the Apples Warehouse Database 
    ConnectionPool apple_store("mysql://root:fruit@192.168.1.101:3306/apples");
    apple_store.start();

    // Start a ConnectionPool to the Oranges Warehouse Database                     
    ConnectionPool orange_store("postgresql://root:ninja@192.168.5.210/oranges");
    orange_store.start();

    // Get a Connection to the Orange store
    Connection orange_connection = orange_store.getConnection();

    // Select Apples we want to copy to Oranges
    Connection apple_connection = apple_store.getConnection();
    ResultSet apples = apple_connection.executeQuery(
        "SELECT name, color, weight FROM apples"
    );

    // Create a Prepared statement for storing Oranges
    PreparedStatement orange = orange_connection.prepareStatement(
        "INSERT INTO oranges (name, color, weight) VALUES (?, ?, ?)"
    );

    // Copy all Apples to Oranges under a transaction
    try {
        orange_connection.beginTransaction();
        while (apples.next()) {
            orange.bindValues(
                apples.getString("name").value_or(""),
                apples.getString("color").value_or(""),
                apples.getDouble("weight")
            );
            orange.execute();
        }
        orange_connection.commit();
        std::cout << "Transfer successful" << std::endl;
    } catch (const sql_exception& e) {
        orange_connection.rollback();
        std::cerr << "Transfer failed: " << e.what() << std::endl;
    }

    // Connections are automatically returned to their respective pools when 
    // they go out of scope
} catch (const sql_exception& e) {
    std::cerr << "Database error: " << e.what() << std::endl;
}

Version 3.5.0

Released on 17 December 2025

  • New: Exception_frame.errorCode provides the numeric error code from the underlying database when available. This allows for more robust error handling.
  • New: Added SQLState.h interface providing SQLSTATE error code constants for PostgreSQL.
  • New: Added ResultSet_isnullByName() to complement ResultSet_isnull().
  • New: Libzdb now automatically detects and blocks connections to MySQL Router, ProxySQL, MariaDB MaxScale, and other MySQL proxies due to fundamental protocol limitations. Connect directly to your MySQL/MariaDB server instead. Advanced users who understand these limitations can override this restriction using the allow-proxy=true URL parameter, though connection failures or unpredictable behavior may result.
  • Enhancement: Improved documentation around Exception and error codes.
  • Enhancement: Oracle: Optimized and cleaned up. Libzdb now supports Oracle version 18c and later. Fixed a bug in transaction handling.
  • Fixed: PostgreSQL: If Connection_beginTransaction(), Connection_commit(), or Connection_rollback() functions failed (e.g., due to an offline database), and an exception was thrown, the exception description contained either "Unknown error" or "?".
  • Fixed: PostgreSQL: Connection_ping() relied solely on local status and couldn't detect whether the server had failed or recovered.
  • Fixed: MySQL: Explicitly disabled auto-reconnect due to its unsafe handling of transactions and session state. This also makes Connection_ping() fail fast. The connection pool handles server recovery by creating new connections when needed.
  • Fixed: Memory corruption when libzdb was used in a multi-threaded application, the database was down, the connection pool was empty, and two threads were trying to call ConnectionPool_getConnection().
  • Fixed: Eliminated potential lock contention in the connection pool reaper thread by removing periodic ping tests on idle connections. The connection pool performs the ping test before returning a connection.

Version 3.4.1

Released on 3 June 2025

  • New: Enum CONNECTIONPOOL_TYPE and ConnectionPool_getType() function to identify the database type that the ConnectionPool is connected to (MySQL, PostgreSQL, SQLite, etc.).
  • New: Reintroduced configurable shared cache support for SQLite. While shared cache is deprecated, it remains beneficial in multi-threaded scenarios. When used together with the unlock notify API, shared cache can significantly reduce database lock errors and improve concurrency. Note: For optimal performance, SQLite should be compiled with:
    • Threading support (--enable-threadsafe)
    • Unlock notify API (-DSQLITE_ENABLE_UNLOCK_NOTIFY)
    When unlock notify is available, libzdb uses efficient thread synchronization. Without it, libzdb automatically falls back to an exponential backoff strategy to handle database locks. Most package-distributed SQLite libraries include these features, but some distributions may disable them. To enable shared cache in libzdb, use the shared-cache=true URL parameter.
  • New: Added SSL/TLS configuration options to connection URLs for MySQL and PostgreSQL:
    • ssl-cert - SSL client certificate file path
    • ssl-ca - Certificate Authority (CA) certificate file path
    • ssl-key - SSL client private key file path
    Example: mysql://user:pass@host/db?use-ssl=true&ssl-cert=/path/to/cert.pem&ssl-ca=/path/to/ca.pem&ssl-key=/path/to/key.pem
  • Fixed: Removed heap_limit as a special URL parameter for SQLite connections. This change aligns with SQLite's introduction of the soft_heap_limit pragma alongside hard_heap_limit in SQLite version 3.7.3. Users should now use the standard SQLite pragma syntax instead. See SQLite pragma documentation for details.

Version 3.4.0

Released on 1 August 2024

  • New: Connection_beginTransactionType() can be used to specify a transaction's isolation level and characteristics when beginning a new transaction.
  • New: valueOr macro for safe handling of API return values:
    • Provides a default for NULL, 0, or negative returns.
    • Works with pointers, integers, and floats.
    • Safely evaluates expressions only once.
    • Compatible with GCC and Clang (uses GNU C extension).
    Example: const char* host = valueOr(URL_getHost(url), "localhost");
  • New: Enabled multi-thread mode for SQLite connections by default. This can improve performance in multi-threaded applications by allowing concurrent access to the database. Works with stock libsqlite builds (SQLITE_THREADSAFE=1 or 2). The URL property, serialized=true, can be used to set the connection back in serialized thread mode.
  • New: Added PreparedStatement_setSString() for setting a sized string value. If the string length is known, using this method is more efficient.
  • Fixed: Disabled use of sqlite3_enable_shared_cache and SQLITE_OPEN_SHAREDCACHE with open_v2(). Although we did see concurrency benefits with shared cache, it is best not to use it due to potential side-effects. SQLite also discourages its use.
  • Fixed: C++, added missing support for binding a floating point value in PreparedStatement::bind()
  • Fixed: C++, Use PreparedStatement_setSString() when binding a std::string_view which is more efficient and safer.
  • Fixed: Plus minor improvements and fixes

Version 3.3.0

Released on 23 July 2024

  • New: The pool now starts the reaper thread by default, while previously it was started on request. The function ConnectionPool_setReaper() has changed purpose from previously starting the reaper thread to now being able to disable the reaper thread by setting sweep interval to 0.
  • New: The C++ API has been updated to have better ergonomics and provide more modern features. C++20 is required, and the API is not backward compatible. See test/zdbpp.cpp for examples and use of libzdb in a C++ project. The C API remains backward compatible and does not require any changes to your code.
  • New: Added ConnectionPool_isFull() for improved connection pool management. This method allows users to check if the pool is at capacity before attempting to get a connection. It's recommended to use ConnectionPool_isFull() before calling ConnectionPool_getConnection() to proactively manage pool resources and handle full pool scenarios efficiently, such as increasing max connections
  • New: Added PreparedStatement_setNull() to explicit set a parameter in a prepared statements to SQL NULL.
  • New: Changed default timeout before an inactive connection is evicted from the pool, from 30 seconds to 90 seconds. ConnectionPool_setConnectionTimeout() can be used to change this value.
  • Fixed: Improved Connection Pool Concurrency and Reliability. Significantly reduced lock contention in the connection pool when multiple threads request connections simultaneously. Connections returned from the pool are still guaranteed to be active and connected to the database.
  • Fixed: Plus minor improvements and fixes