New database does not appear to be managed by rqlite. Are my queries just being piped directly into SQLite?

[l-monninger]

I was experimenting with porting an existing sqlite3 project over from sqlite to rqlite, and discovered behavior I don't understand.

Roughly and redactively speaking, the existing project would create a database on disk at /myapp/.temp/data.db. To "migrate" to rqlite the first thing I tried was simply pointing the application to the new rqlite port. This worked--at least at single node scale! The application runs flawlessly. However, on closer inspection, I found that rqlite CLI did not appear to be aware of the database (it was completely empty of any tables) and that /myapp/.temp/data.db was still being written.

I would have expected that, in switching to rqlite this either (a) should fail or (b) should be managed by rqlite. The latter does not appear to be the case. Are my queries just being piped directly into SQLite? The raft.db does not appear to be aware of /myapp/.temp/data.db, but I don't really know how to read it.

[otoolep]

I need much more information about what you are doing, to understand what you are seeing. I am also not sure how you are connecting /myapp/.temp/data.db to rqlite -- that makes no sense to me. You may have the wrong mental model of how rqlite works.

rqlite does not "manage" an existing SQLite database. It does something completely different -- it's a distributed database system that uses SQLite as its storage engine.

Can you show me step-by-step what you are doing?

• show me how your launch rqlite
• tell me how you've changed your app so it talks to rqlite instead of a SQLite database
• show me how you launch the CLI

[l-monninger]

rqlite does not "manage" an existing SQLite database. It does something completely different -- it's a distributed database system that uses SQLite as its storage engine.

This is what I have in mind.

show me how your launch rqlite

# from within the unzipped package.
# some joins, maybe.
./rqlited -on-disk ${APP_RQLITE}/node.1

tell me how you've changed your app so it talks to rqlite instead of a SQLite database

The applications are now configured to connect to sqlite on port 4001 and still use connections with a file name /myapp/.temp/data.db.

show me how you launch the CLI

I have tried both ./rqlite and ./rqlite /myapp/.temp/data.db. The behavior is the same.

In both cases, the sqlite client being used is bound to the sqlite C API via better_sqlite3.

[otoolep]

What you are outlining doesn't make much sense, I'm afraid. You seem to mixing a few different concepts, and I suspect you don't have the right mental model of what rqlite actually does.

If you run rqlited in "on-disk" mode you can read the SQLite file that rqlite will generate. However you cannot write to the SQLite file directly. If you write to so, you will break rqlite. All writes i.e. INSERTs, must be done via the HTTP API exposed by rqlited. If you have not changed your application and expect everything to work, you're not understanding what rqlite does.

The command line tool, rqlite is for talking to an rqlite node over HTTP. It does nothing with SQLite files, and passing a SQLite file to it is ignored by rqlite.

https://github.com/rqlite/rqlite/blob/master/cmd/rqlite/README.md

https://github.com/rqlite/rqlite#limitations

https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md#is-it-a-drop-in-replacement-for-sqlite

[l-monninger]

The same happens without -on-disk btw.

So, the fact that this writes the database properly on a single node as a "drop in replacement for sqlite" is not intended behavior? That's what I was confused by. I wasn't expecting it to work, but then it did. Why is this?

[l-monninger]

If you want to see what I'm seeing, I can drop you that terraform script. Just let me know...

[l-monninger]

Also, to be clear, I'm sure I don't have the right mental model haha. But, that certainly hasn't stopped me from being very confused by this behavior. If the HTTP API does some abstraction that I need to respect, then why does connecting this way kind of work?

Additionally, I don't want to indicate any false sense of urgency or imply that this is a bug. Switching over to one of the rqlite clients looks to be no big deal. I'm just curious what's going on.

[otoolep]

I think you have a very specific definition of "works" -- defined as "my client can read and write to a SQLite database", which is what you had before rqlite. From what I can gather you are writing directly to a SQLite file in each case. Sometimes that file is created by rqlited (when on disk mode), other times it's just created by your client (when running rqlited without on-disk mode).

rqlite is not involved in what you're doing, you are not interacting with rqlited in any meaningful sense. You start it, and then never talk to it. rqlited might as well not be running.

[l-monninger]

Very specific definition of "kind of works" is what I'd prefer haha. I'm aware this is not achieving any kind of consensus.

I'll try to take a closer look at the source to see I can answer my own question about why connecting directly to the port would skip the important bits of the HTTP API, but still manage to read and write.

[l-monninger]

Any permalinks here would be helpful.

[otoolep]

If you want to work correctly with rqlite, you must talk to its HTTP API. The client libraries will help with that, but they are not seamless drop-in placements for standard SQLite client libraries.