How to ensure proper destruction order using RAII for SQLite database?

Question

In my class which holds a SQLite database handle I have:

struct SQLitePreparedStatement
    {
        sqlite3_stmt* stmt = nullptr;
        ~SQLitePreparedStatement()
        {
            if (stmt)
                sqlite3_finalize(stmt);
        }
    };
    SQLitePreparedStatement create_table_statement{};
    SQLitePreparedStatement exists_statement{};
    SQLitePreparedStatement insert_statement{};
    SQLitePreparedStatement insert_or_replace_statement{};
    SQLitePreparedStatement get_value_statement{};
    SQLitePreparedStatement remove_value_statement{};
    SQLitePreparedStatement count_rows_prepared_stmt{};
    SQLitePreparedStatement get_row_id_stmt{};
    sqlite3* sqlite_db;

Until now I have simply called sqlite3_close on the database in the destructor, without calling finalize on the statements. I think this is wrong, and that you have to call finalize on each statement associated with the database handle BEFORE calling close on the database. This is why I wrapped the statements in a RAII struct. But if I call close on the database in the destructor this will happen BEFORE the statements have been destroyed. Is the solution here to wrap the database handle in yet another struct? And then make sure sqlite_db always comes BEFORE the statements in the declaration order and the statement destructors will run first. That's guaranteed, right? All of this seems very flimsy and prone to breakage.

If you use RAII consistently, it all comes naturally. Let's say your RAII wrapper for the sqlite3* handle has a member function for creating the RAII wrapper for the prepared statement, then order of initialization (and the automatic reverse order for destruction) comes easy and dependencies also become clear. — Homer512
– Homer512, Commented Oct 30 at 10:19
Just make sure you construct the objects that need to live longest first, then they will be destructed last. This order will make sense to anyone who knows RAII — Pepijn Kramer
– Pepijn Kramer, Commented Oct 30 at 12:43
@PepijnKramer That can only be done by placing the objects that should be destructed later first in the declaration order. This is what I've done along with a very lengthy warning message not to move the database handle below the statement declarations. This seems like a bad solution, but it's what I've done. — Zebrafish
– Zebrafish, Commented Oct 30 at 19:30

Shawn · Accepted Answer · 2025-10-30 12:41:39Z

3

Close the database handle with sqlite3_close_v2(). From the documentation:

If sqlite3_close_v2() is called with unfinalized prepared statements, unclosed BLOB handlers, and/or unfinished sqlite3_backups, it returns SQLITE_OK regardless, but instead of deallocating the database connection immediately, it marks the database connection as an unusable "zombie" and makes arrangements to automatically deallocate the database connection after all prepared statements are finalized, all BLOB handles are closed, and all backups have finished. The sqlite3_close_v2() interface is intended for use with host languages that are garbage collected, and where the order in which destructors are called is arbitrary.

Thus, with it (and unlike sqlite3_close()) it doesn't matter if the prepared statements are finalized after the database is closed, as long as they are finalized.

answered Oct 30 at 12:41

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zebrafish Oct 30 at 19:27

That's probably the easiest solution I guess. If all the statements are finalized after it's closed, I'm wondering how sqlite 'knows' this, and to release the file. I'm guessing it does some sort of reference counting or something maybe?

Shawn Oct 30 at 21:21

AFAIK it's just a reference count, yeah.

Toby Speight · Accepted Answer · 2025-10-30 10:15:08Z

It might make sense for your statement object to hold a shared pointer on the database connection. Then the connection can't be destructed while there are still statements that need it.

Approximately like (untested):

struct SQLitePreparedStatement
    {
        std::shared_ptr<sqlite3> db;
        std::unique_ptr<sqlite3_stmt, sqlite3_finalize> stmt = nullptr;

        explicit SQLitePreparedStatement(std::shared_ptr<sqlite3> db)
          : db{std::move(db)}
        {}
    };


    auto const sqlite_db = std::shared_ptr{new sqlite3{}, close_db};

    SQLitePreparedStatement create_table_statement{sqlite_db};
    SQLitePreparedStatement exists_statement{sqlite_db};
    SQLitePreparedStatement insert_statement{sqlite_db};
    SQLitePreparedStatement insert_or_replace_statement{sqlite_db};
    SQLitePreparedStatement get_value_statement{sqlite_db};
    SQLitePreparedStatement remove_value_statement{sqlite_db};
    SQLitePreparedStatement count_rows_prepared_stmt{sqlite_db};
    SQLitePreparedStatement get_row_id_stmt{sqlite_db};

Collectives™ on Stack Overflow

How to ensure proper destruction order using RAII for SQLite database?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related