SQLite - C/C++

SQLite - Modern C/C++ Integration

This tutorial covers the modern, recommended practices for using SQLite from C and C++ programs. We will focus on safety, efficiency, and clarity by using prepared statements and proper error handling.

Project Setup

First, ensure you have the SQLite3 development library installed on your system. On Debian/Ubuntu, you can install it with sudo apt-get install libsqlite3-dev. On other systems, the package name will be similar.

To compile your C/C++ code, you must link against the SQLite3 library. The compile command is:

# For C
$ gcc your_program.c -o your_program -lsqlite3

# For C++
$ g++ your_program.cpp -o your_program -lsqlite3

For real projects, using a build system like CMake is highly recommended to handle dependencies and build steps automatically.

The Core API: Prepared Statements

While sqlite3_exec() exists for simple, one-off commands, the modern and secure way to interact with SQLite is through prepared statements. This approach separates the SQL logic from the data, which prevents SQL injection attacks and is more efficient for queries that are run multiple times.

The typical workflow is:

1. sqlite3_open(): Open a connection to the database file.
2. sqlite3_prepare_v2(): Compile an SQL statement into a byte-code program.
3. sqlite3_bind_*(): Bind application values (e.g., from variables) to the parameters in the prepared statement.
4. sqlite3_step(): Execute the prepared statement. For SELECT queries, this is called repeatedly to fetch each row.
5. sqlite3_column_*(): For SELECT queries, retrieve data from the current result row.
6. sqlite3_finalize(): Destroy the prepared statement object to free resources.
7. sqlite3_close(): Close the database connection.

Full C Example: A Complete CRUD Application

Below is a complete, well-commented C program demonstrating Create, Read, Update, and Delete (CRUD) operations using prepared statements.

#include <stdio.h>
#include <sqlite3.h>

// A helper function to check for and print errors
void check_error(int rc, sqlite3 *db, char *action_msg) {
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error during %s: %s\n", action_msg, sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }
}

int main() {
    sqlite3 *db;
    sqlite3_stmt *stmt;
    char *db_file = "test.db";

    // 1. Open the database
    int rc = sqlite3_open(db_file, &db);
    check_error(rc, db, "database open");
    printf("Database opened successfully.\n");

    // 2. Create a table (using sqlite3_exec for simplicity here)
    char *create_sql = "CREATE TABLE IF NOT EXISTS USERS("
                       "ID INTEGER PRIMARY KEY AUTOINCREMENT, "
                       "NAME TEXT NOT NULL, "
                       "EMAIL TEXT UNIQUE NOT NULL);";
    rc = sqlite3_exec(db, create_sql, 0, 0, 0);
    check_error(rc, db, "table creation");
    printf("Table created or already exists.\n");

    // 3. INSERT data using a prepared statement (prevents SQL injection)
    char *insert_sql = "INSERT INTO USERS (NAME, EMAIL) VALUES (?, ?);";
    rc = sqlite3_prepare_v2(db, insert_sql, -1, &stmt, 0);
    check_error(rc, db, "insert prepare");

    // Bind values to the statement
    sqlite3_bind_text(stmt, 1, "Alice", -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, "alice@example.com", -1, SQLITE_STATIC);

    // Execute the statement
    if (sqlite3_step(stmt) != SQLITE_DONE) {
         fprintf(stderr, "SQL error during insert: %s\n", sqlite3_errmsg(db));
    }
    sqlite3_finalize(stmt); // Finalize statement
    printf("New user inserted.\n");

    // 4. SELECT data
    char *select_sql = "SELECT ID, NAME, EMAIL FROM USERS;";
    rc = sqlite3_prepare_v2(db, select_sql, -1, &stmt, 0);
    check_error(rc, db, "select prepare");

    printf("\n--- User List ---\n");
    // Loop through results
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        int id = sqlite3_column_int(stmt, 0);
        const unsigned char *name = sqlite3_column_text(stmt, 1);
        const unsigned char *email = sqlite3_column_text(stmt, 2);
        printf("ID: %d, Name: %s, Email: %s\n", id, name, email);
    }
    if (rc != SQLITE_DONE) {
        check_error(rc, db, "select step");
    }
    sqlite3_finalize(stmt);

    // 5. UPDATE and DELETE operations would follow a similar pattern:
    //    prepare -> bind (e.g., ID in WHERE clause) -> step -> finalize

    // 6. Close the database
    sqlite3_close(db);
    printf("\nDatabase closed.\n");

    return 0;
}

Best Practices for C++

While you can use the C API directly in C++, it’s not idiomatic. Modern C++ development favors RAII (Resource Acquisition Is Initialization) to manage resources like database connections and statements, ensuring they are always cleaned up correctly, even when errors occur.

For serious projects, consider using a dedicated C++ wrapper library for SQLite, such as:

• SQLiteCpp: A popular, lightweight, and easy-to-use wrapper.
• sqlite_orm: A header-only library that provides an Object-Relational Mapping layer.

These libraries handle the low-level C API calls for you, provide type-safe interfaces, and manage resource lifetimes automatically using C++ constructors and destructors. This results in code that is safer, cleaner, and much easier to maintain.

Simple C++ RAII Wrapper Example

Here is a conceptual example of how you might wrap the sqlite3 handle in a C++ class to manage its lifetime automatically.

#include <iostream>
#include <string>
#include <memory>
#include <stdexcept>
#include <sqlite3.h>

// A simple RAII wrapper for the sqlite3 handle
class SQLiteDB {
public:
    SQLiteDB(const std::string& db_path) {
        if (sqlite3_open(db_path.c_str(), &db) != SQLITE_OK) {
            throw std::runtime_error("Cannot open database: " + std::string(sqlite3_errmsg(db)));
        }
    }

    ~SQLiteDB() {
        if (db) {
            sqlite3_close(db);
        }
    }

    // Prevent copying
    SQLiteDB(const SQLiteDB&) = delete;
    SQLiteDB& operator=(const SQLiteDB&) = delete;

    sqlite3* get() const { return db; }

private:
    sqlite3* db = nullptr;
};

int main() {
    try {
        SQLiteDB db("test_cpp.db");
        std::cout << "Database opened successfully via C++ RAII wrapper." << std::endl;

        // You can now use db.get() to get the raw pointer for other API calls
        char *err_msg = nullptr;
        const char* sql = "CREATE TABLE IF NOT EXISTS CONTACTS(NAME TEXT);";
        if (sqlite3_exec(db.get(), sql, 0, 0, &err_msg) != SQLITE_OK) {
            std::cerr << "SQL error: " << err_msg << std::endl;
            sqlite3_free(err_msg);
        }

    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
        return 1;
    }

    // db is automatically closed here when it goes out of scope
    return 0;
}