Hacker News Reader

Comment by PaulWaldman | Hacker News Reader

SQLite integrates nicely with .Net and EF Core. Would there be any reason to use this over SQLite with a JSON field?

Saying a document database is the same as a JSON field is a bit reductive.

I think you chose this instead of MongoDB like you would chose SQLite instead of MSSQL. The library is very similar to Mongo's .Net library, so its almost a 1:1 code replacement.

> I think you chose this instead of MongoDB like you would chose SQLite instead of MSSQL. The library is very similar to Mongo's .Net library, so its almost a 1:1 code replacement.

I guess it depends on what's important.

Some people primarily care about the API. Other people focus on the operational story.

I agree. The way I see it, a document DB (I have used Firebase) has features that make it nicer to work with documents. For example Firebase let's you query and index based on the data within the "JSON" (in quotes because it is not really represented in Firebase as JSON, but is close in structure).

Another thing is you might want the lack of features. I.e. you want it so people don't do joins etc. Because you want the team to use the same paradigm with local data as with server hosted. I can think of reasons why.

You can create an index based on expressions so you can index fields inside JSON data with sqlite.

SQLite lets you do those JSON things as well.

Interop overhead makes SQLite in .NET a lot slower than an embedded database, in my experience - though this may be a problem with the stock SQLite wrappers and not an unfixable problem.

In my experience the overhead is only noticeable if you use Entity Framework Core to interface with Sqlite.

The native Sqlite wrapper libraries such as Microsoft's Microsoft.Data.Sqlite have faster performance than EF, but because you're forced to pass around SQL strings, most .NET dev's don't prefer to use them.

I would suggest using system.data.sqlite instead, it's the official version from sqlite and is actively maintained. Additionally, it's self contained whereas the Microsoft version requires separate dlls. Personally I've used dapper as a wrapper over sqlite and had decent performance but you do have to be aware of transactions and increase the cache size (and maybe set wal mode). There's also things like sqlite-net which looks promising but I haven't tried it so I can't comment on perf.

Perf with litedb is pretty awful in my experience if you're using it as a schemaful db. But it's probably the wrong tool for the job if you're comparing to sqlite. I've yet to come across any embedded db that can do schema + document with decent performance.

Microsoft.Data.Sqlite makes it easier to use encrypted databases compared to System.Data.Sqlite out of the box.

I am yet to find a modern up-to-date tutorial and implementation of System.Data.Sqlite that offers encrypted database support without making my brain melt.

A few years ago, I needed faster SQLite interop than Microsoft.Data.Sqlite, and ended up writing my own: https://github.com/zmj/sqlite-fast

The ergonomics could be better, but this enabled query execution to avoid heap allocations and reflection.

I wonder if anyone's working on compiling SQLite's C code into C# so it can run without interop.

One reason:

SQLite is slower than LiteDB in this benchmark project created by the LiteDB inventor

https://github.com/mbdavid/LiteDB-Benchmark

https://github.com/mbdavid/LiteDB/issues/291

Another (lesser) reason is the similarity to MongoDB methods, if that's what you are used to it will feel familiar, but no MongoDB server needed.

Reply on news.ycombinator.com