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
Top comments (18)
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:
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:
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.
Not sure I understood the requirement but SQLite supports both JSON and full text search
You can definitely join tables with SQLite
You might not need asynchronicity if you use SQLite in WAL mode:
You can also take a look at PRAGMA synchronous
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 :)
Thanks. I will try to build on top of SQLite, then. Probably with an embedded Node.js server.
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.
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).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?
I can use ipcMain/ipcRenderer for Electron as well, but
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.
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.
What if you ship SQLite precompiled with the tool you distribute?
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.
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?
SQLite is written in C, not C++. sqlite.org/whyc.html
What do you think about this review?
SQLite review
Ryan Westlund ・ Mar 12 ・ 5 min read
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.
That's true. If one needs embedded, they should consider alternatives, like H2 SQL database.
Do you know of other databases that don't require any runtime? That's one of the big advantages of sqlite
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
NeDB Is still ok
NeDB doesn't support TypeScript as good as
@types/lokijs.Also, far less consistency guaranteed than SQLite.
Not that lokijs or
@lokidb/lokidbis 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.
NeDB keeps everything in-memory which makes it a no-go for me.