Database and connection management

Managing DuckDB database instances and connections in C

The DuckDB C API uses two main types for database access: duckdb_database for the database instance and duckdb_connection for active connections.

Database Operations

Opening a Database

Create or open a DuckDB database file:

duckdb_state duckdb_open(const char *path, duckdb_database *out_database);

path · const char*

Path to the database file. Pass NULL for an in-memory database.

out_database · duckdb_database* · required

Output parameter for the database handle.

Returns: DuckDBSuccess on success, DuckDBError on failure.

Examples

In-Memory

duckdb_database db;
if (duckdb_open(NULL, &db) == DuckDBError) {
    fprintf(stderr, "Failed to open in-memory database\n");
    return 1;
}

Opening with Configuration

For advanced configuration:

duckdb_state duckdb_open_ext(
    const char *path,
    duckdb_database *out_database,
    duckdb_config config,
    char **out_error
);

config · duckdb_config

Configuration options (can be NULL for defaults).

out_error · char**

Output parameter for error message. Must be freed with duckdb_free() if not NULL.

Closing a Database

Close the database and free resources:

void duckdb_close(duckdb_database *database);

Close all connections before closing the database. Closing a database with active connections results in undefined behavior.

Connection Operations

Creating a Connection

Establish a connection to the database:

duckdb_state duckdb_connect(duckdb_database database, duckdb_connection *out_connection);

database · duckdb_database · required

The database instance to connect to.

out_connection · duckdb_connection* · required

Output parameter for the connection handle.

Example:

duckdb_connection con;
if (duckdb_connect(db, &con) == DuckDBError) {
    fprintf(stderr, "Failed to create connection\n");
    duckdb_close(&db);
    return 1;
}

Multiple Connections

You can create multiple connections to the same database:

duckdb_database db;
duckdb_connection con1, con2;

duckdb_open("my_database.duckdb", &db);
duckdb_connect(db, &con1);
duckdb_connect(db, &con2);

// Both connections can be used simultaneously
duckdb_query(con1, "CREATE TABLE test(i INTEGER)", NULL);
duckdb_query(con2, "INSERT INTO test VALUES (42)", NULL);

// Clean up
duckdb_disconnect(&con1);
duckdb_disconnect(&con2);
duckdb_close(&db);

Disconnecting

Close a connection and free its resources:

void duckdb_disconnect(duckdb_connection *connection);

Lifecycle Management

Proper resource management is critical:

1. Open database

   duckdb_database db;
   duckdb_open("my_database.duckdb", &db);
   
2. Create connections

   duckdb_connection con;
   duckdb_connect(db, &con);
   
3. Use the connection

   duckdb_result result;
   duckdb_query(con, "SELECT 42", &result);
   // Process result...
   duckdb_destroy_result(&result);
   
4. Clean up in reverse order

   duckdb_disconnect(&con);  // Close connection first
   duckdb_close(&db);        // Then close database
   

Thread Safety

Database: Thread-safe. A single duckdb_database can be shared across multiple threads.

Connection: Not thread-safe. Each thread should create its own duckdb_connection.

// Multi-threaded example (pseudocode)
duckdb_database shared_db;
duckdb_open("shared.duckdb", &shared_db);

// Thread 1
void* thread1_func(void* arg) {
    duckdb_connection con1;
    duckdb_connect(shared_db, &con1);
    // Use con1...
    duckdb_disconnect(&con1);
    return NULL;
}

// Thread 2
void* thread2_func(void* arg) {
    duckdb_connection con2;
    duckdb_connect(shared_db, &con2);
    // Use con2...
    duckdb_disconnect(&con2);
    return NULL;
}

Configuration Options

Common configuration options when opening a database:

Option	Values	Description
access_mode	READ_WRITE, READ_ONLY	Database access mode
threads	Integer	Number of threads to use
max_memory	String (e.g., “1GB”)	Maximum memory limit
default_order	ASC, DESC	Default ORDER BY direction

Example with configuration:

duckdb_config config;
duckdb_create_config(&config);

// Set options
duckdb_set_config(config, "threads", "4");
duckdb_set_config(config, "max_memory", "2GB");

// Open with config
duckdb_database db;
char *error = NULL;
if (duckdb_open_ext("my_database.duckdb", &db, config, &error) == DuckDBError) {
    fprintf(stderr, "Error: %s\n", error);
    duckdb_free(error);
}

// Clean up config (database retains the settings)
duckdb_destroy_config(&config);

Error Handling

Always check return values:

duckdb_database db;
duckdb_connection con;
char *error = NULL;

if (duckdb_open_ext("my_database.duckdb", &db, NULL, &error) == DuckDBError) {
    if (error) {
        fprintf(stderr, "Database error: %s\n", error);
        duckdb_free(error);
    }
    return 1;
}

if (duckdb_connect(db, &con) == DuckDBError) {
    fprintf(stderr, "Connection error\n");
    duckdb_close(&db);
    return 1;
}

// Use connection...

duckdb_disconnect(&con);
duckdb_close(&db);

Next Steps

Query Execution

Execute SQL queries

Prepared Statements

Use parameterized queries