Table of Contents

  1. System SQLite vs Amalgamation
  2. FetchContent Approach
  3. Compile-Time Options
  4. SQLite Encryption Extension
  5. Building sqlite3 CLI Tool
  6. C++ Wrappers (SQLiteCpp)
  7. Testing with In-Memory DB
Back to CMake Mastery Series

SQLite

June 4, 2026 Wasil Zafar 8 min read

The definitive guide to embedding SQLite in your CMake project — from amalgamation bundling and compile-time tuning to C++ wrappers and in-memory test databases.

Database

System SQLite vs Amalgamation

SQLite can be consumed in two ways: linking against a system-installed shared library, or compiling the single-file "amalgamation" directly into your project. Each approach has distinct trade-offs.

The system library approach uses find_package or pkg-config to locate an installed SQLite:

# CMakeLists.txt — System SQLite via find_package
cmake_minimum_required(VERSION 3.20)
project(MyApp LANGUAGES C CXX)

set(CMAKE_CXX_STANDARD 17)

# CMake 3.14+ provides FindSQLite3 module
find_package(SQLite3 3.36 REQUIRED)

add_executable(myapp src/main.cpp src/database.cpp)
target_link_libraries(myapp PRIVATE SQLite::SQLite3)

message(STATUS "SQLite version: ${SQLite3_VERSION}")
message(STATUS "SQLite include: ${SQLite3_INCLUDE_DIRS}")

The amalgamation approach bundles SQLite source directly — giving you full control over compile options and version:

# CMakeLists.txt — SQLite amalgamation (vendored)
cmake_minimum_required(VERSION 3.20)
project(MyApp LANGUAGES C CXX)

# Build SQLite from amalgamation files
add_library(sqlite3 STATIC
    third_party/sqlite/sqlite3.c
)
target_include_directories(sqlite3 PUBLIC
    third_party/sqlite
)

# Essential compile-time options
target_compile_definitions(sqlite3 PRIVATE
    SQLITE_THREADSAFE=1
    SQLITE_ENABLE_FTS5
    SQLITE_ENABLE_JSON1
    SQLITE_ENABLE_RTREE
    SQLITE_DQS=0
)

add_executable(myapp src/main.cpp)
target_link_libraries(myapp PRIVATE sqlite3)

# SQLite needs pthread on Unix
if(UNIX)
    find_package(Threads REQUIRED)
    target_link_libraries(sqlite3 PRIVATE Threads::Threads ${CMAKE_DL_LIBS})
endif()
Key Insight: The amalgamation approach is preferred for most projects. It compiles in under 2 seconds, ensures consistent behavior across platforms, and lets you enable extensions (FTS5, JSON1, R-Tree) that system packages may not include.

FetchContent Approach

Rather than vendoring the amalgamation manually, use FetchContent to download it automatically during configuration:

# CMakeLists.txt — SQLite via FetchContent
cmake_minimum_required(VERSION 3.20)
project(MyApp LANGUAGES C CXX)

include(FetchContent)

# Download SQLite amalgamation
# Version 3.46.0 = year 2024, release 3460000
FetchContent_Declare(sqlite3
    URL https://www.sqlite.org/2024/sqlite-amalgamation-3460000.zip
    URL_HASH SHA256=... # Always pin the hash for reproducibility
)
FetchContent_MakeAvailable(sqlite3)

# Create library target from fetched source
add_library(sqlite3_lib STATIC
    ${sqlite3_SOURCE_DIR}/sqlite3.c
)
target_include_directories(sqlite3_lib PUBLIC
    ${sqlite3_SOURCE_DIR}
)
target_compile_definitions(sqlite3_lib PRIVATE
    SQLITE_THREADSAFE=1
    SQLITE_ENABLE_FTS5
    SQLITE_ENABLE_JSON1
    SQLITE_ENABLE_COLUMN_METADATA
    SQLITE_MAX_EXPR_DEPTH=10000
)

if(UNIX)
    find_package(Threads REQUIRED)
    target_link_libraries(sqlite3_lib PRIVATE Threads::Threads ${CMAKE_DL_LIBS})
endif()

# Application
add_executable(myapp src/main.cpp)
target_link_libraries(myapp PRIVATE sqlite3_lib)
Pitfall: SQLite's download URLs embed the version number in a non-standard format (3460000 for version 3.46.0). Always pin the URL hash with URL_HASH SHA256=... to prevent silent corruption or supply-chain attacks. The hash is listed on SQLite's download page.

Compile-Time Options

SQLite's behavior is extensively configurable via compile-time defines. These are set as target_compile_definitions on the sqlite3 library target:

# Comprehensive compile-time configuration
target_compile_definitions(sqlite3_lib PRIVATE
    # Threading
    SQLITE_THREADSAFE=1              # 0=single-thread, 1=serialized, 2=multi-thread

    # Extensions
    SQLITE_ENABLE_FTS5               # Full-text search v5
    SQLITE_ENABLE_JSON1              # JSON functions
    SQLITE_ENABLE_RTREE              # R-Tree spatial index
    SQLITE_ENABLE_GEOPOLY            # Geopolygon extension
    SQLITE_ENABLE_COLUMN_METADATA    # Column origin metadata
    SQLITE_ENABLE_STAT4              # Advanced query planner stats
    SQLITE_ENABLE_UPDATE_DELETE_LIMIT # LIMIT on UPDATE/DELETE

    # Performance tuning
    SQLITE_DEFAULT_MEMSTATUS=0       # Disable memory tracking
    SQLITE_DEFAULT_WAL_SYNCHRONOUS=1 # WAL mode: NORMAL sync
    SQLITE_LIKE_DOESNT_MATCH_BLOBS   # LIKE skips BLOBs
    SQLITE_MAX_EXPR_DEPTH=10000      # Complex query support
    SQLITE_OMIT_DECLTYPE             # Omit if not using column types
    SQLITE_USE_ALLOCA                # Use alloca() for temp allocations

    # Security
    SQLITE_DQS=0                     # Disable double-quoted strings
    SQLITE_MAX_LENGTH=1000000000     # 1GB max string/blob
    SQLITE_MAX_SQL_LENGTH=1000000    # 1MB max SQL statement
)
// src/database.cpp — Verify compile options at runtime
#include <sqlite3.h>
#include <iostream>

void printSqliteConfig() {
    std::cout << "SQLite version: " << sqlite3_libversion() << "\n";
    std::cout << "Thread-safe: " << sqlite3_threadsafe() << "\n";

    // Check if FTS5 is available
    sqlite3* db = nullptr;
    sqlite3_open(":memory:", &db);

    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(db,
        "SELECT fts5(?1)", -1, &stmt, nullptr);
    std::cout << "FTS5 available: " << (rc == SQLITE_OK ? "yes" : "no") << "\n";

    sqlite3_finalize(stmt);
    sqlite3_close(db);
}

SQLite Encryption Extension

SQLite's official encryption (SEE) is a commercial product. Open-source alternatives like SQLCipher provide AES-256 encryption. Integration requires replacing the amalgamation with the encrypted variant:

# CMakeLists.txt — SQLCipher (open-source encryption)
cmake_minimum_required(VERSION 3.20)
project(SecureApp LANGUAGES C CXX)

include(FetchContent)

# SQLCipher depends on OpenSSL for crypto
find_package(OpenSSL REQUIRED)

FetchContent_Declare(sqlcipher
    GIT_REPOSITORY https://github.com/niccokunzmann/sqlcipher-amalgamation.git
    GIT_TAG        master
)
FetchContent_MakeAvailable(sqlcipher)

add_library(sqlcipher_lib STATIC
    ${sqlcipher_SOURCE_DIR}/sqlite3.c
)
target_include_directories(sqlcipher_lib PUBLIC
    ${sqlcipher_SOURCE_DIR}
)
target_compile_definitions(sqlcipher_lib PRIVATE
    SQLITE_HAS_CODEC
    SQLITE_THREADSAFE=1
    SQLITE_ENABLE_FTS5
    SQLITE_ENABLE_JSON1
)
target_link_libraries(sqlcipher_lib PRIVATE OpenSSL::Crypto)

if(UNIX)
    find_package(Threads REQUIRED)
    target_link_libraries(sqlcipher_lib PRIVATE Threads::Threads ${CMAKE_DL_LIBS})
endif()

add_executable(secure_app src/main.cpp)
target_link_libraries(secure_app PRIVATE sqlcipher_lib)

Building sqlite3 CLI Tool

The sqlite3 command-line tool is useful for development and debugging. Build it alongside your project from the amalgamation:

# CMakeLists.txt — Optional sqlite3 CLI tool
option(BUILD_SQLITE3_CLI "Build the sqlite3 command-line tool" OFF)

if(BUILD_SQLITE3_CLI)
    add_executable(sqlite3_cli
        ${sqlite3_SOURCE_DIR}/shell.c
        ${sqlite3_SOURCE_DIR}/sqlite3.c
    )
    target_compile_definitions(sqlite3_cli PRIVATE
        SQLITE_THREADSAFE=0
        SQLITE_ENABLE_FTS5
        SQLITE_ENABLE_JSON1
        SQLITE_ENABLE_EXPLAIN_COMMENTS
        SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION
    )

    if(UNIX)
        find_package(Threads REQUIRED)
        target_link_libraries(sqlite3_cli PRIVATE
            Threads::Threads
            ${CMAKE_DL_LIBS}
        )
        # readline for interactive editing
        find_library(READLINE_LIB readline)
        if(READLINE_LIB)
            target_link_libraries(sqlite3_cli PRIVATE ${READLINE_LIB})
            target_compile_definitions(sqlite3_cli PRIVATE HAVE_READLINE)
        endif()
    endif()

    set_target_properties(sqlite3_cli PROPERTIES OUTPUT_NAME sqlite3)
    install(TARGETS sqlite3_cli RUNTIME DESTINATION bin)
endif()

C++ Wrappers (SQLiteCpp)

SQLiteCpp provides a modern C++ RAII wrapper around the SQLite C API, with proper exception handling and type safety:

# CMakeLists.txt — SQLiteCpp wrapper via FetchContent
cmake_minimum_required(VERSION 3.20)
project(CppDbApp LANGUAGES CXX)

set(CMAKE_CXX_STANDARD 17)

include(FetchContent)

# SQLiteCpp bundles its own SQLite or can use external
set(SQLITECPP_INTERNAL_SQLITE ON CACHE BOOL "Use bundled SQLite")

FetchContent_Declare(sqlitecpp
    GIT_REPOSITORY https://github.com/SRombauts/SQLiteCpp.git
    GIT_TAG        3.3.1
)
FetchContent_MakeAvailable(sqlitecpp)

add_executable(cpp_app src/main.cpp)
target_link_libraries(cpp_app PRIVATE SQLiteCpp)
// src/main.cpp — SQLiteCpp usage example
#include <SQLiteCpp/SQLiteCpp.h>
#include <iostream>

int main() {
    try {
        // Open or create database
        SQLite::Database db("example.db",
            SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE);

        // Create table
        db.exec(
            "CREATE TABLE IF NOT EXISTS users ("
            "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
            "  name TEXT NOT NULL,"
            "  email TEXT UNIQUE"
            ")"
        );

        // Insert with prepared statement
        SQLite::Statement insert(db,
            "INSERT INTO users (name, email) VALUES (?, ?)");
        insert.bind(1, "Alice");
        insert.bind(2, "alice@example.com");
        insert.exec();

        // Query
        SQLite::Statement query(db, "SELECT id, name, email FROM users");
        while (query.executeStep()) {
            std::cout << query.getColumn(0).getInt() << " | "
                      << query.getColumn(1).getText() << " | "
                      << query.getColumn(2).getText() << "\n";
        }
    } catch (const SQLite::Exception& e) {
        std::cerr << "SQLite error: " << e.what() << "\n";
        return 1;
    }

    return 0;
}

Testing with In-Memory DB

SQLite's :memory: database is perfect for unit tests — it's fast, isolated, and requires no filesystem cleanup:

# CMakeLists.txt — Database unit tests
enable_testing()

add_executable(db_tests
    tests/test_schema.cpp
    tests/test_queries.cpp
    tests/test_migrations.cpp
)
target_link_libraries(db_tests PRIVATE
    sqlite3_lib
    GTest::gtest_main
)

# Tests use :memory: database — no fixtures needed
add_test(NAME DatabaseTests COMMAND db_tests)
set_tests_properties(DatabaseTests PROPERTIES
    LABELS "unit"
    TIMEOUT 30
)
// tests/test_schema.cpp — In-memory database testing
#include <gtest/gtest.h>
#include <sqlite3.h>
#include <string>

class DatabaseTest : public ::testing::Test {
protected:
    sqlite3* db = nullptr;

    void SetUp() override {
        // Fresh in-memory database for each test
        int rc = sqlite3_open(":memory:", &db);
        ASSERT_EQ(rc, SQLITE_OK);

        // Apply schema
        const char* schema = R"(
            CREATE TABLE users (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
            CREATE INDEX idx_users_name ON users(name);
        )";
        char* err = nullptr;
        rc = sqlite3_exec(db, schema, nullptr, nullptr, &err);
        ASSERT_EQ(rc, SQLITE_OK) << "Schema error: " << (err ? err : "");
        sqlite3_free(err);
    }

    void TearDown() override {
        sqlite3_close(db);
    }
};

TEST_F(DatabaseTest, InsertAndRetrieve) {
    const char* sql = "INSERT INTO users (name) VALUES ('Alice')";
    ASSERT_EQ(sqlite3_exec(db, sql, nullptr, nullptr, nullptr), SQLITE_OK);

    sqlite3_stmt* stmt = nullptr;
    sqlite3_prepare_v2(db, "SELECT name FROM users WHERE id = 1", -1, &stmt, nullptr);
    ASSERT_EQ(sqlite3_step(stmt), SQLITE_ROW);

    std::string name(reinterpret_cast<const char*>(sqlite3_column_text(stmt, 0)));
    EXPECT_EQ(name, "Alice");

    sqlite3_finalize(stmt);
}

TEST_F(DatabaseTest, UniqueConstraint) {
    sqlite3_exec(db, "CREATE UNIQUE INDEX idx_name ON users(name)", nullptr, nullptr, nullptr);
    sqlite3_exec(db, "INSERT INTO users (name) VALUES ('Bob')", nullptr, nullptr, nullptr);

    int rc = sqlite3_exec(db, "INSERT INTO users (name) VALUES ('Bob')", nullptr, nullptr, nullptr);
    EXPECT_EQ(rc, SQLITE_CONSTRAINT);
}
Key Insight: In-memory databases are created and destroyed per test fixture — guaranteeing test isolation without filesystem I/O. For testing WAL mode or file locking behavior, use temporary files with std::tmpnam() or CMake's TEST_WORKING_DIRECTORY property.

Related Deep Dives

CMake Mastery Series

Return to the complete series index covering all parts of modern CMake mastery.

View Series
CURL — CMake Integration

Integrate libcurl for HTTP networking with TLS backend selection and CTest HTTP endpoint testing.

Read Deep Dive
fmt — CMake Integration

Add the modern formatting library to your CMake project with FetchContent and header-only mode.

Read Deep Dive