4 ms·

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

jermaustin1 4y ago
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.
- nordsieck 4y ago
  > 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.
- quickthrower2 4y ago
  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.
  - JamesSwift 4y ago
    SQLite lets you do those JSON things as well.
  - RedShift1 4y ago
    You can create an index based on expressions so you can index fields inside JSON data with sqlite.
kevingadd 4y ago
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.
- miffy900 4y ago
  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.
  - eyegor 4y ago
    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.
    - halfbrite 4y ago
      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.
  - zmj 4y ago
    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 https://github.com/zmj/sqlite-fast The ergonomics could be better, but this enabled query execution to avoid heap allocations and reflection.
mmerlin 4y ago
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-Benchmark https://github.com/mbdavid/LiteDB/issues/291 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.

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.

nordsieck 4y ago
> 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.
quickthrower2 4y ago
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.
- JamesSwift 4y ago
  SQLite lets you do those JSON things as well.
- RedShift1 4y ago
  You can create an index based on expressions so you can index fields inside JSON data with sqlite.

kevingadd 4y ago

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.

miffy900 4y ago
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.
- eyegor 4y ago
  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.
  - halfbrite 4y ago
    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.
- zmj 4y ago
  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 https://github.com/zmj/sqlite-fast The ergonomics could be better, but this enabled query execution to avoid heap allocations and reflection.

mmerlin 4y ago

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-Benchmark https://github.com/mbdavid/LiteDB/issues/291 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.