A stable alternative to SQLite for offline desktop app?

I prefer to use JavaScript/TypeScript if that matters. Also, it would be better if it is ACID-guaranteed. (I have seen LokiDB breaks, and all is lost.)

I wouldn't want to use Docker. (otherwise I can easily use MongoDB, with pseudo-file based.) It should be easy to install for end-users.

If I use Kotlin, I might try h2 database; but Kotlin is harder to code than TypeScript in general...

Some other criteria are

• User input queries (Actually, I converted it to JSON, before inputting to ORM.)
• Joins. I am trying to develop an app compatible with Anki (schema), and it involves a lot of joining.
• Async.
• Maintenance. NeDB and LokiDB seem not to be actively maintained anymore. Have tried PouchDB, but not sure about stability; although CouchDB should be well maintained.
• JSON support, in order to evaluate Record<string, string> -- can even be something like JSON1 extension

Comments

[rhymes]

Hi Pacharapol! What issues are you encountering with SQLite? If there's a battle tested and stable embeddable DB, that is SQLite. It's used as an embedded DB in all iPhones and Androids (literally all Android programmers have SQLite as a default storage for their apps), Ligthroom installations, Windows 10, Firefox and so many other programs :)

It is also ACID guaranteed, it uses serializable transactions for that:

A transactional database is one in which all changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID). SQLite implements serializable transactions that are atomic, consistent, isolated, and durable, even if the transaction is interrupted by a program crash, an operating system crash, or a power failure to the computer.

What it means is that transactions are basically serial and thus consistency is always guaranteed.

What does it mean in practice? Let's see what the isolation page tells us:

If the same database is being read and written using two different database connections (two different sqlite3 objects returned by separate calls to sqlite3_open()) and the two database connections do not have a shared cache, then the reader is only able to see complete committed transactions from the writer. Partial changes by the writer that have not been committed are invisible to the reader.

which is exactly what the "serializable" type of isolation means. The reader can only see stuff that's safely stored in the DB, not in flight. If that's not acceptable to you, yeah SQLite is not the DB you seek, but it's still ACID.

There's a way to see partial results by forcing SQLite in "read uncommitted" mode with the configuration though, you should read this page closely.

User input queries (Actually, I converted it to JSON, before inputting to ORM.)

Not sure I understood the requirement but SQLite supports both JSON and full text search

Joins

You can definitely join tables with SQLite

Async

You might not need asynchronicity if you use SQLite in WAL mode:

WAL is significantly faster in most scenarios.
WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
WAL uses many fewer fsync() operations

You can also take a look at PRAGMA synchronous

Maintenance.

It's 20 years old with billions of installations and its latest release is from January. I think it has proven his stability. It might not be the DB you're looking for though, let's see the alternatives:

• PouchDB seems nice and it's active, but it's an API over other DBs which means that in a desktop app will be a layer over SQLite or CouchDB. Regardless of the API the questions are how maintained are the adapters? The one over SQLite seems to be external to the project and CouchDB seems to be supported natively but limited to CouchDB 2 (we're at 3, well it just came out to be honest). I have the feeling that you don't need PouchDB if you're developing an offline desktop app as you are in control of the entire environment, you don't need to interface with N types of browsers for storage

• CouchDB is a great document DB but it's a totally different DB from SQLite. CouchDB is a document store, SQLite is a SQL DBMS. SQLite is a library, CouchDB is a server you talk to via HTTP. Not as embeddable 👀

• LevelDB is a fast embeddedable key valye store, written in C++ by Google, not sure if there are NodeJS bindings. It seems is what Bitcoin Core uses as storage for metadata. Don't know much about this.

• RocksDB is a fast embeddable key value store that started as a fork of LevelDB, and it's written and maintained by Facebook. I don't think there's much of a reason to use this directly (unless you have an app that very intensively writes gigabytes to a storage and it has to do it super fast), it's increasingly used as a storage engine for higher level DBs. FB developed it because all available engines for MySQL weren't fast enough (well, they have petabytes of data) with SSD disks in server farms. It's also the storage engine of the marvelous CockroachDB (one day we need to create a conference for developers about naming stuff)

I think they are both faster than SQLite and both synchronous, I'd take a look at RocksDB between these last two.

Basically it depends on what you need to store :)

[Pacharapol Withayasakpunt]

You might not need asynchronicity if you use SQLite in WAL mode:
You can also take a look at PRAGMA synchronous

Thanks. I will try to build on top of SQLite, then. Probably with an embedded Node.js server.

User input queries

My current API for a single line string is like this -- github.com/patarapolw/qsearch#usage ; although I prefer Elasticsearch-ish query_string.

When will I need a real full-text search?

PouchDB in Node.js is CouchDB-syncable with LevelDB bindings. There is also experimental PouchDB server, which probably use the same API as CouchDB.

RocksDB seems to have Java binding, but it still might not be an answer for me.

CockroachDB needs a server.

Basically it depends on what you need to store :)

I need to query a table with dynamic columns. That's why I say Record<string, string>. It is definitely doable in SQLite (JSON1 extension) and PostGRES (which is a server).

[rhymes]

Yeah I think SQLite is what you are looking for. Why do you need a Node server though? Can't you just access it from the desktop app?

[Pacharapol Withayasakpunt]

I can use ipcMain/ipcRenderer for Electron as well, but

• I want to avoid Electron, and use web server, which is much smaller instead. And, I don't have a very good experience with zserge/webview...
• I want to enable user-defined CSS styling, which is much better supported in real web browser, or Electron; than perhaps Qt or JavaFX.
• I want to app to be programmable, so I expose the API via REST + OpenAPI / Swagger.
• I do fear a little about security on localhost. Perhaps I will use a token-based authentication.

[Victor Sergienko]

I'm shopping for a local database for my project, and I have a big problem with SQLite - Unicode collation, indexing and search. Or, rather, the lack thereof.

• You have to install third-party extensions to have Unicode comparison. In year 2022.
• FTS5 doesn't work with ICU (tbf, FTS4 probably does).
• There is on out-of-box collation for Unicode.

[Alex Leung]

SQLite has 1 main issue for me specifically: it is written in C++ so if I try and ship an OSS tool which uses SQLite, there's a chance that users of the tool will be unable to build the SQLite portions. This brought me to SQL.js, but there I found that the entire DB is held in-memory, which defeats the purpose of having a DB in the first place from my perspective. I wish someone would have a SQL.js alternative which actually writes to disk instead of storing everything in-memory.

[rhymes]

What if you ship SQLite precompiled with the tool you distribute?

[Alex Leung]

You you compile SQLite you have to compile it for a specific platform. I wouldn't be able to anticipate all the possible client platforms.

[rhymes]

But SQLite is already available for many platforms and if a client asks you for a new platform you can compile it for that too: sqlite.org/download.html

Would that work?

[Yawar Amin]

SQLite is written in C, not C++. sqlite.org/whyc.html

[Pacharapol Withayasakpunt]

What do you think about this review?

SQLite review

Ryan Westlund ・ Mar 12 ・ 5 min read

#sql #sqlite

[Adam Genshaft]

I’ll have to add on top of it that SQLite supports a very low load and sometime having 2 parallel requests will break it. Which also brings the testability issue - it’s nearly impossible to test with modern frameworks like jest.

[Pacharapol Withayasakpunt]

That's true. If one needs embedded, they should consider alternatives, like H2 SQL database.

[Adam Genshaft]

Do you know of other databases that don't require any runtime? That's one of the big advantages of sqlite

[rhymes]

It feels like they were trying to replace PostgreSQL which is a client server relational DBMS with an embedded solution. Apples and oranges.

Again, depends on what your requirements are. Is it a single user desktop app? Because in here you talk about RESt and other things.

Can you tell me what the app does or should do? That's how you understand if an embeddable DB is okay or not

[Lukáš Kovář]

NeDB Is still ok

[Pacharapol Withayasakpunt]

NeDB doesn't support TypeScript as good as @types/lokijs.

Also, far less consistency guaranteed than SQLite.

• Multiple column unique indices
• Foreign key integrity
• Not Null

Not that lokijs or @lokidb/lokidb is better. However, lokijs seems to be one-man's work, yet recently updated in < 1 month. (vs NeDB.)

As I have destroyed my database with lokijs before (i.e. cannot read anymore), I cannot be sure about NeDB -- especially on ACID guarantee.

[Alex Leung]

NeDB keeps everything in-memory which makes it a no-go for me.