If I change the last two lines of code to read:

    rc = sqlite3_exec(db, "select rowid from sqlite_master limit 1", nullptr, nullptr, nullptr);
    sqlite3_close(db);
    return rc == SQLITE_OK;

Then it is seems more reliable, except my runtime just doubled!
I'm testing about 5'500 files for SQLite-ness (among other things).

I shouldn't have to run a query to reliably ascertain a file is an SQLite DB, no?
That's the point of magic cookies after all. Can I avoid the query engine and rely solely on the 100-bytes header somehow?

The 16 byte magic at the file's beginning is very stable for SQLite 3.

Before you become too dissatisfied with your doubled runtime, I advise timing your "select ... sqlite_master" with a simple prepare. If you try that with a recent version of the CLI, you will get an error such as: Error: near line 1: in prepare, file is not a database (26) , which shows that no execution is necessary (or possible.)

Then, before retaining your dissatisfaction, compare that new runtime with what is needed to actually open the file and read in the first 16 bytes. I'm pretty sure that the comparison will be nowhere near as shocking as "doubled".

Edited to answer the middle question.

I shouldn't have to run a query to reliably ascertain a file is an SQLite DB, no?

No. But you need to prepare to run one (or find a pragma which requires consulting the DB header.) The library defers actual reading of the DB until it is needed. This saves some time for normal usage patterns.

Well, there's plenty of SQLITE_OPEN_... flags already.
I really wish there was one that forced checking the 100-bytes header for correctness.
E.g. SQLITE_OPEN_READ_AND_CHECK_HEADER.

That way, it remains opt-in and backwards compatible.
And establishes a clear best practice for SQLite-ness of a file going forward.

That way no need for some work-around to indirectly reading that header. My $0.02.

I really wish there was one that forced checking the 100-bytes header for correctness. E.g. SQLITE_OPEN_READ_AND_CHECK_HEADER.

That would only work for plain, unencrypted SQLite databases. However, there are several SQLite encryption extensions out there like SEE (SQLite Encryption Extension) provided by the SQLite developers or SQLCipher provided by the Zetetic company. For an encrypted SQLite database file it is usually impossible to identify the file as a SQLite database file by just looking at the database header. Therefore such an Open flag wouldn't make much sense in the general case, since even if the test fails the file could still be a valid SQLite database file.

I would think that the SQLITE_OPEN_READ_AND_CHECK_HEADER shouldn't be expected to work for encrypted databases anyways, unless the VFS decrypts it at the time that it is being opened. Since, if you are using this flag then it would be that you could not use such a database file anyways.

I'm pretty sure you know this, but for the benefit of others:

This select statement will likely succeed if the file is empty.

In that sense, the file is a valid sqlite file, even without the header.

Of course it is also a valid (if not very exciting) script for many scripting languages including sh, python, and sqlite3.

So depending on what you want out of your test, you may want to add special handling for empty files.

This select statement will likely succeed if the file is empty.

It will not succeed in the sense of surviving a sqlite3_prepare().

In that sense, the file is a valid sqlite file, even without the header.

Impossible. The header is written along with all other per-database info. Until the first page is written, there is no way to call the file a valid SQLite3 database.

So depending on what you want out of your test, you may want to add special handling for empty files.

It suffices to either verify presence of the 16-byte header or call sqlite3_prepare_v2() with "SELECT * FROM sqlite_schema" and see if that succeeds. Of course, a stat() call could be used for empty files, by why special-case such rarities?

It will not succeed in the sense of surviving a sqlite3_prepare().

Actually, even sqlite3_prepare() for a simple "pragma user_version" succeeds...
One must actually sqlite3_step() the statement to get an error.

FWIW, here's the code I ended up using, which is faster than the previous one using sqlite_master.
I still think it's a shame to have to parse SQL and involve the SQL and perhaps VDBE engine just
to check the header of a file to ascertain whether it is an SQLite DB or not.
There should be an easier and official way to do this IMHO...

PS: The point about encrypted DBs is moot and beside the point IMHO.

bool isSQLiteDB(const fs::path& path, int* p_user_version = nullptr) {
    assert(fs::is_regular_file(path));
    auto filename = path.string();

    sqlite3* db = nullptr;
    int errc = sqlite3_open_v2(filename.c_str(), &db, SQLITE_OPEN_READONLY, nullptr);
    if (errc != SQLITE_OK) {
        sqlite3_close(db); // must close even on error
        return false;
    }
    sqlite3_stmt* stmt = nullptr;
    const char* sql_end = nullptr;
    constexpr char sql[] = "pragma user_version";
    errc = sqlite3_prepare_v2(db, sql, sizeof(sql) - 1, &stmt, &sql_end);
    if ((errc == SQLITE_OK) && stmt) {
        assert((sql_end - sql) == sizeof(sql) - 1);
        if (SQLITE_ROW != sqlite3_step(stmt)) {
            errc = SQLITE_NOTADB;
        } else if (p_user_version) {
            *p_user_version = sqlite3_column_int(stmt, 0);
        }
        sqlite3_finalize(stmt);
    } else {
        errc = SQLITE_NOTADB;
    }
    sqlite3_close(db);
    return errc == SQLITE_OK;
}