Hi sqlite-wasm team, I'm working on wasm code of a web application that we are developing. We want to use sqlite with OPFS to manage our persistent data. I'm writting a test application to verify that everything is working or not. But I'm at a point. I have taken SQLite 3.45 and written a sample c++ main file on top of it then I built it using em++. This main function spawns a new thread inits the OPFS here again using emsdk's c++ api. Create a new file, open it using sqlit3_open_v2 API and then try to execute a CREATE TABLE, at this point I get an error that database is locked even though it was just created. I do not understand what is wrong here.

Here is the sample code

int CreateTable() { sqlite3 *db; wasmfs_backend = wasmfs_create_opfs_backend(); std::cout<<"Called Create table";

char *zErrMsg = 0;

// Save the result of opening the file
int rc;

// Save any SQL
string sql;

// Save the result of opening the file

//int result = wasmfs_create_file("/example.txt",555,  wasmfs_backend);
auto result = wasmfs_create_directory("/persistent", 0777, wasmfs_backend);
result = wasmfs_create_directory("/persistent/dbs", 0555, wasmfs_backend);
result = wasmfs_create_file("/persistent/dbs/somename.db",0555,  wasmfs_backend);

std::string platformPath = "/persistent/dbs/somename.db";
rc = sqlite3_open_v2(platformPath.c_str(),
                             &db,
                             SQLITE_OPEN_NOMUTEX | // multithreaded mode vs. serialized
                             SQLITE_OPEN_READWRITE,
                             NULL);

if( rc != SQLITE_OK){
    // Show an error message
    cout << "DB OPen Error: " << sqlite3_errmsg(db) << endl;
    // Close the connection
    sqlite3_close(db);
    // Return an error
    return(1);
}
sql = "BEGIN TRANSACTION";
rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
if(rc != SQLITE_OK)
{
    // Show an error message
    cout << "DB Table Error: " << sqlite3_errmsg(db) << endl;
    // Close the connection
    sqlite3_close(db);
    // Return an error
    return(1);
}
// Save SQL to create a table
sql = "CREATE TABLE PEOPLE ("  \
  "ID INT PRIMARY KEY     NOT NULL," \
  "NAME           TEXT    NOT NULL);";

// Run the SQL (convert the string to a C-String with c_str() )
//rc = sqlite3_exec(db, sql.c_str(), callback, 0, &zErrMsg);
sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, NULL);
if(rc != SQLITE_OK)
{
    // Show an error message
    cout << "DB Table Error: " << sqlite3_errmsg(db) << endl;
    // Close the connection
    sqlite3_close(db);
    // Return an error
    return(1);
}
rc = sqlite3_step(stmt);
 if (rc != SQLITE_OK) {
        std::cout<<"Execution failed: " <<sqlite3_errmsg(db);
        return 0;
}
rc = sqlite3_finalize(stmt);

if (rc != SQLITE_DONE) {
        std::cout<<"Execution failed: " <<sqlite3_errmsg(db);
        return 0;
}

sql = "INSERT INTO PEOPLE(ID, NAME) VALUES(1,\"Anil\");";

sqlite3_stmt *pStmt;
    rc = sqlite3_prepare(db, sql.c_str(), -1, &pStmt, 0);
    if (rc != SQLITE_OK) {
        std::cout<<"Execution failed: " <<sqlite3_errmsg(db);

       return 0;
    }

    //sqlite3_bind_blob(pStmt, 1, data, len, SQLITE_STATIC);
    rc = sqlite3_step(pStmt);

    if (rc != SQLITE_DONE) {
        std::cout<<"Execution failed: " <<sqlite3_errmsg(db);
        return 0;
    }
    sqlite3_finalize(pStmt);
    if (rc != SQLITE_DONE) {
        std::cout<<"Execution failed: " <<sqlite3_errmsg(db);
        return 0;
    }
// Close the SQL connection
sqlite3_close(db);
return 0;

}

In this code it fails at this line rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, NULL); With error Database is locked. I'm stuck on this error for a while now. Any help is highly appreciated.

Create a new file, open it using sqlit3_open_v2 API and then try to execute a CREATE TABLE, at this point I get an error that database is locked even though it was just created. I do not understand what is wrong here.

That is likely a quirk of using OPFS via WASMFS, which is an Emscripten-specific approach to accessing OPFS. Unfortunately, there's not much i can say about that, as we only use OPFS via the JS API. We have tinkered with wasmfs before but it has been changed in incompatible ways, breaking our code, since then so it is not a configuration we directly support.

Note that, without a good deal of client-side hoop-jumping to coordinate file handles, opening an OPFS file exclusively locks it. Your code does not appear to be opening your file but wasm_create_file() might be holding it open for some reason. You might (just speculating) be encountering a race condition between create_file() closing (asynchronously) the file and trying to sqlite_open() it. We encountered that issue early on in the development of our OPFS VFS. You can test that hypothesis by sleeping for a second or so before trying to open the db. No other ideas currently come to mind.

You might (just speculating) be encountering a race condition between create_file() closing (asynchronously) the file and trying to sqlite_open() it.

Apologies, but i misunderstood, because of how this tiny screen just happened to truncate your code, that it was failing at open(), not the CREATE TABLE. i will need to look at it more closely from a real computer in the morning - no obvious problem sticks out to me but it's difficult to read on a tiny screen.

Edit: PS: this part is wrong:

    rc = sqlite3_step(stmt);
     if (rc != SQLITE_OK)

step() never returns OK.

... at this point I get an error that database is locked ...

(This time from a proper screen and keyboard...)

After porting the main logic of your example to JS (see below) and trying it out, there's no problem with what you're trying to do or, as far as the sqlite3 API is concerned, how you're trying to do it. Since the locking error is not reproducible that way, my conclusion is either...

• You're running into some as-yet-uncharacterized WASMFS-specific quirk or...

• Code you have running somewhere (perhaps paused in a debugger, in an interactive JS console, or an older instance of this code in a now-stale browser tab?) is holding that file open and the locking error isn't detected until your CREATE TABLE is run. Whether the lock may be detected immediately when sqlite3_open() is run depends on black-box details of WASMFS and when, precisely, it actually asks OPFS to open the file. If WASMFS delays that part until the first I/O, we would expect to see the locking error first show up after open() instead via open().

Note that merely open()ing a file does not cause the library to actually try to do anything with it, as can be seen using the sqlite3 CLI app:

$ ls index.html
index.html
$ sqlite3 index.html 
SQLite version 3.44.0 2023-10-02 15:37:55
Enter ".help" for usage hints.

<Notice that there's no error until we try to read the db...>

sqlite> .tables
Error: file is not a database

There is no fundamental reason why it shouldn't be possible to use WASMFS with sqlite the way you are attempting to do, but that approach is not one we've explored in this project.

For completeness's sake: shorn of the JS-side complexity of using the C-style API, your example boils down to the following in JS:

const db = new sqlite3.oo1.OpfsDb("foo.db");
db.exec([
// Sidebar: this is a very poor way to manage a transaction in this API
// particular API because it's not exception-safe. and can leave the
// transaction in a broken state.
  "begin transaction;",
  "create table if not exists people(id,name);",
  "insert into people(id,name) values(1,'Anim');",
  "commit;"
]);
console.log(db.selectValues("select name from people"));
db.close();

Or, if for the sake of explaining that "sidebar" comment:

const db = new S.oo1.OpfsDb("foo.db");
db.transaction(function(theDb){
  theDb.exec([
    "create table if not exists people(id,name);",
    "insert into people(id,name) values(1,'Anim');"
  ]);
});
console.log(db.selectValues("select name from people"));
db.close();

And it outputs:

['Anim']

As there's no locking error there, the assumption has to be that something on your end is provoking it, but where exactly that "something" is is anyone's guess.

I wanted to know if there are some special preprocessor macros or runtime flags we should consider while building sqlite for a wasm target.

We don't set any specifically for that purpose, so presumably no.

One thing we do differently than you is we don't compile sqlite3.c directly, but instead compile sqlite3-wasm.c, which does #include "sqlite3.c". All of that that code is there to support the JS-level infrastructure, though, so is not applicable to you. Nonetheless, perhaps scanning the top section of that file, where numerous build-time config flags are set, will trigger a hint?

BTW:

SQLITE_ENABLE_JSON1=1

That flag is no longer needed. That API got wrapped up into the core a couple of years ago.

I suspect this because we are building sqlite ourselves because we need to call these API's from c++ and the build artifact of SQLite contains a pre linked wasm file which is not really suitable for our use case.

FWIW, building it yourself should pose no problem at all. sqlite3.c should "just work" when compiled to wasm. We initially had to make literally zero changes to the library to get it working with wasm and have since made only one very small set of patches to get it working with the wasi-sdk.

I did look at the GNU make file in ext/wasm folder but that a bit complicated for my expertise.

Yeah, i do love GNU Make and have been known to go a bit overboard with it :).

Here is what our CMAKE looks like

My experience with cmake is literally zero, but nothing in what you've posted sticks out to me as wrong in any way.

It's not clear to me whether sqlite3.c is being compiled as C or C++? The two are not 100% compatible, and there's no guaranty that compiling it as C++ will produce a working library. Assuming, however, that it's really compiling as C, it should "just work."

C_STANDARD 11

We have never tested building it as C11. If you don't specifically need C11 for other pieces, you can try C99 (which our own wasm build uses) just to rule out any strange incompatibility between the two. None are expected, but it's a variable which can be easily ruled out.

The way WAL is omitted from WASI builds is pretty heavy handed, especially since WAL can work without shared memory.

My apologies, but i apparently missed your response until it was linked to from 0deac9f6b9192afe. That bit has now been changed to:

#if defined(__wasi__)
# undef SQLITE_WASI
# define SQLITE_WASI 1
# ifndef SQLITE_OMIT_LOAD_EXTENSION
#  define SQLITE_OMIT_LOAD_EXTENSION
# endif
# ifndef SQLITE_THREADSAFE
#  define SQLITE_THREADSAFE 0
# endif
#endif

Hopefully that won't cause anyone any grief.

Edit: it breaks the --with-wasi-sdk builds, so was rolled back.

No problem, I can keep doing what I'm doing. Any way to reproduce that, though? I'd like to see if I can suggest an alternative.

There's nothing in WASI that should prevent WAL from working (in exclusive mode). I mean, there's no file locking, but that can just as easily corrupt a rollback database.

Any way to reproduce that, though? I'd like to see if I can suggest an alternative.

That would be wonderful. It requires that the wasi-sdk is installed:

https://github.com/WebAssembly/wasi-sdk

and then...

$ ./configure --with-wasi-sdk=/opt/wasi-sdk=/opt/wasi-sdk
$ make clean
$ make
...
sqlite3.c:43290:36: error: use of undeclared identifier 'PROT_READ'
            pShmNode->isReadonly ? PROT_READ : PROT_READ|PROT_WRITE,
                                   ^
sqlite3.c:43290:48: error: use of undeclared identifier 'PROT_READ'
            pShmNode->isReadonly ? PROT_READ : PROT_READ|PROT_WRITE,
                                               ^
sqlite3.c:43290:58: error: use of undeclared identifier 'PROT_WRITE'
            pShmNode->isReadonly ? PROT_READ : PROT_READ|PROT_WRITE,
                                                         ^
sqlite3.c:43291:13: error: use of undeclared identifier 'MAP_SHARED'
            MAP_SHARED, pShmNode->hShm, szRegion*(i64)pShmNode->nRegion
            ^
sqlite3.c:43293:19: error: use of undeclared identifier 'MAP_FAILED'
        if( pMem==MAP_FAILED ){

That's caused by the timing of the forced SQLITE_OMIT_WAL.

The problem is twofold, IMO:

1. You want to use os_unix.c for WASI, and it assumes mmap always exists (it's a pretty basic POSIX thing);
2. SQLITE_OMIT_WAL is a really blunt tool.

WASI can support WAL without shared memory just fine, with EXCLUSIVE locking mode. test_demovfs.c works fine, I tried.

But to compile os_unix.c, the only way to omit the shared memory methods is SQLITE_OMIT_WAL, which also omits WAL entirely.

I guess the solution is a new SQLITE_OMIT_SHM or SQLITE_OMIT_SHMWAL or something, and this would make WASI builds more useful (and maybe people want to use it even on other Unix/Windows). But it's one more variant to test.

For my purpose, I guess something like the below would do:

#if defined(__wasi__) && !defined(SQLITE_OS_OTHER)

OK, I think I have a better suggestion than the above (that's more useful to WASI users).

--- os_unix.c.orig
+++ os_unix.c
@@ -3987,7 +3987,7 @@
 
 /* Forward declaration */
 static int unixGetTempname(int nBuf, char *zBuf);
-#ifndef SQLITE_OMIT_WAL
+#if !defined(SQLITE_OMIT_WAL) && !defined(SQLITE_WASI)
  static int unixFcntlExternalReader(unixFile*, int*);
 #endif
 
@@ -4114,7 +4114,7 @@
 #endif /* SQLITE_ENABLE_LOCKING_STYLE && defined(__APPLE__) */
 
     case SQLITE_FCNTL_EXTERNAL_READER: {
-#ifndef SQLITE_OMIT_WAL
+#if !defined(SQLITE_OMIT_WAL) && !defined(SQLITE_WASI)
       return unixFcntlExternalReader((unixFile*)id, (int*)pArg);
 #else
       *(int*)pArg = 0;
@@ -4287,7 +4287,7 @@
 
 #endif /* !defined(SQLITE_OMIT_WAL) || SQLITE_MAX_MMAP_SIZE>0 */
 
-#ifndef SQLITE_OMIT_WAL
+#if !defined(SQLITE_OMIT_WAL) && !defined(SQLITE_WASI)
 
 /*
 ** Object used to represent an shared memory buffer.

Basically, this "omits" WAL (for WASI) only from os_unix.c.

This means WAL support goes into the build, but the VFS has a NULL xShmMap, which triggers the correct code path in sqlite3PagerWalSupported.

Basically, this "omits" WAL (for WASI) only from os_unix.c.

Thank you very much for this. i will definitely try this out sometime in the next couple of days and let you know.

Basically, this "omits" WAL (for WASI) only from os_unix.c.

An equivalent patch has been checked in and doesn't seem to break anything. Thank you very much for digging in to this.

That's great!

But I'm a bit lost, the unconditional SQLITE_OMIT_WAL at the bottom of sqlite.h.in is still in trunk, right?

www:/cgi/src/file?name=src/sqlite.h.in&ci=trunk

The idea was to remove that, which with the new #if !defineds shouldn't cause an issue.

I just made that change (remove SQLITE_OMIT_WAL from sqlite.h.in) on a GitHub clone from master, adding a #warning to the top of wal.c in the amalgamation, and confirmed it builds with wasi-sdk with WAL in there.

But I'm a bit lost, the unconditional SQLITE_OMIT_WAL at the bottom of sqlite.h.in is still in trunk, right?

Indeed, my apologies for the oversight. That was removed, then re-added after it broke the wasi-sdk builds, but is now, in trunk, removed again (thank goodness for cherry-pick merges).

Thank you for following up on this!

Thank you!

One less patch/weirdness on my side, and making stuff useful to others, what more could I want?

:)