dotnet
Support SQLite jsonb
SQLite version 3.45.0 (2024-01-15) introduced support for stored JSON data in its internal representation format, as a BLOB (docs), similar to PG jsonb or the new SQL Azure JSON type. We should make sure to support this:
- Understand how a client encodes JSON (string) data to write it into a SQLite JSON BLOB column. Since there's no column type (like in PG, SQL Server), I'm assuming some function call converts the string data into the internal binary format. This function call would need to be integrated into our update pipeline, etc.
- Make sure we allow users to map primitive collections and JSON-mapped complex/owned entity types to BLOB.
- Include partial update support in the update pipeline
- Include partial update support in ExecuteUpdate
+1 for this feature. Wondering if this has had any discussion within the project team?
It would be helpful to match other providers like NpgSql, as the EFCore Sqlite provider is commonly used as an IntegrationTest backend (https://learn.microsoft.com/en-us/ef/core/testing/testing-without-the-database#sqlite-in-memory).
JsonDocument is currently supported by NpgSql as a core type: https://www.npgsql.org/efcore/mapping/json.html?tabs=data-annotations%2Cjsondocument#jsondocument-dom-mapping
Currently, users with a Postgres production backend and Sqlite test backend will run into compatibility issues.
Currently, users with a Postgres production backend and Sqlite test backend will run into compatibility issues.
This is something we explicitly discourage - these two databases are very different, and there are many behavioral discrepancies beyond the support for jsonb.
Thanks @roji. As you can see in the link I provided, MSDN explicitly suggests this method of testing, so I am confused what you mean by "we explicitly discourage".
SQLite can easily be configured as the EF Core provider for your test suite instead of your production database system (e.g. SQL Server); consult the SQLite provider docs for details.
If behavioral discrepancies are not supported by the EFCore team, I would highly suggest updating the documentation to remove this section or make this clear. The main reason our project chose SQLite as the test backend is because MSDN implies it is supported as a first-class pattern.
It would be useful if you could include a link to these docs? I have no idea where that sentence comes from, but I don't think it's in the EF docs. Yes, many people use SQLite as a database testing fake; and if you only ever do very simple operations, that can work. But the moment you go beyond very minimal SQL constructs, that approach breaks down.
All that is very extensively covered in the EF testing docs, which I'd encourage you to read.
If behavioral discrepancies are not supported by the EFCore team [...]
This doesn't really have anything to do with EF Core. Once again, SQLite is simply a different database with different features and different behaviors, and EF simply can't make it behave the same way as SQL Server (or any other database does). SQLite has its own type system that's completely different from PostgreSQL's, its own JSON support functions - it really is a different database.
Hi @roji , I included the link to the EFCore doc I quoted from in my original reply. My apologies for any confusion. https://learn.microsoft.com/en-us/ef/core/testing/testing-without-the-database#sqlite-in-memory
I appreciate the type disparities between databases. The "support" I was referring to is your own words when you created this efcore issue:
SQLite version 3.45.0 (2024-01-15) introduced support for stored JSON data in its internal representation format, as a BLOB (docs), similar to PG jsonb or the new SQL Azure JSON type. We should make sure to support this:
- Understand how a client encodes JSON (string) data to write it into a SQLite JSON BLOB column. Since there's no column type (like in PG, SQL Server), I'm assuming some function call converts the string data into the internal binary format. This function call would need to be integrated into our update pipeline, etc.
- Make sure we allow users to map primitive collections and JSON-mapped owned entity types to BLOB.
(emphasis mine)
Based on your description, I assumed that this issue exists to track support for an EFCore property mapping to SQLite JSON types. I can only speculate about the implementation details this might involve, and my original comment was simply to add weight and suggest it would be useful to match other provider APIs such as that of Npgsql.
Hi @roji , I included the link to the EFCore doc I quoted from in my original reply. My apologies for any confusion. https://learn.microsoft.com/en-us/ef/core/testing/testing-without-the-database#sqlite-in-memory
That text is written with the assumption that you've already chosen to use a database fake (rather than use the repository pattern for proper unit testing with mocking, or test against your production database system), which are the better ways to test. I agree it reads overly positive and sounds like we recommend this.
The "support" I was referring to is your own words when you created this efcore issue:
Oh of course - jsonb is a new SQLite feature, so EF should support it - as a feature for people using SQLite as their database; there's no quesiton around this (and the issue is indeed open). The point is here is that this isn't about using SQLite as a database fake (stand-in) for other databases, which is something we discourage and simply cannot work in the general case - I was reacting to this sentence you wrote:
It would be helpful to match other providers like NpgSql, as the EFCore Sqlite provider is commonly used as an IntegrationTest backend
Hi,
since this feature did not make it into EFCore 10.0, are there any plans for EFCore 11.0?
Best, Anze
@avidenic that remains to be seen... There's generally less interest in this support, but it's indeed something that we'd ideally get in.
@roji ok that makes sense. Thank you for quick reply. I'll keep watching this issue =)