Limit SQLite type mapping changes in 5.0 possibly breaking change? about fluentmigrator

[jzabroski]

Thank you, this is a lot clearer. I have one follow-up question.

How does Dapper handle System.Data.Sqlite given the original PRs comments about type affinity? It seems like the short-term fix is to support both System.Data.Sqlite and Microsoft.Data.SQLite: https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/compare#data-types

If I understand this correctly, I think part of the problem may be that your particular SQLite db has a type affinity to an arbitrary type that doesnt really exist? So, if you created your db with Microsoft.Data.SQLite driver, and you used non-standard type names, and now want to talk to it with System.Data.Sqlite, is that a problem? Just trying to fully grasp the problem before I provide a fix, which should be pretty easy once I understand the exact root cause.

[nickeeex]

We've always used System.Data.Sqlite. The older version of FluentMigrator just seemed to create the columns with the arbitery types (in our case UNIQUEIDENTIFIER and DATETIME). I think System.Data.Sqlite automatically maps these arbitery types to corresponding .NET types. Haven't used any special connection string when creating the sqlite databases so this was just mostly OOTB functionality. I do not think this has anything to do with dapper directly.

The workaround was pretty simple instead of AsGuid() I used AsCustom("UNIQUEIDENTIFIER") to revert back to the pre 5.0 functionality and that made the db look the same as previously.

I can provide a minimal repro tomorrow if it makes it clearer

[jzabroski]

What does SELECT typeof(Id) FROM YourTable say the type is, assuming Id is the Guid Id parameter column name?

The reason I am asking is its not listed as a type in the example type affinity table, so I am not sure how SQLite "knows" what affinity to bind it to https://www.sqlite.org/datatype3.html

It looks like, from https://www.sqlite.org/stricttables.html , the behavior in #1581 may want to take into consideration whether the table is in STRICT mode. - That would be a bit tricky to support cleanly, as it requires knowing if the Table is in strict mode. We could probably allow users to configure it at a database-level through dependency injection - similar to how Entity Framework 6 used to require all DateTime to be either DateTime or DateTime2 and you could not mix and match across columns.

[nickeeex]

What does SELECT typeof(Id) FROM YourTable say the type is, assuming Id is the Guid Id parameter column name?

The type is Blob, seems like you are correct in your assumptions, also checked with a sqlite browser and the Guid is stored in binary format.

This is what the SQLite reader has defined for the columns:

After some debugging i found this under System.Data.Sqlite SQLiteConverters

Here is the full list of the System.Data.Sqlite type mappings:
https://github.com/haf/System.Data.SQLite/blob/8d2e5027723654e97d2ad208b9a076da496eee79/System.Data.SQLite/SQLiteConvert.cs#L1644

The github mirror to the System.Data.SQLite scm is old but the code still looks similar but there might be some changes to that list.

There seems to be multiple connection string flags under SQLiteConnectionFlags that modify this default behaviour for System.Data.Sqlite

[workgroupengineering]

Is there a way to customize SQLite column mapping without recompiling Fluentmigrator?

[jzabroski]

No, we have #1063 as a backlog item. I'm actually pretty close to making that a feature. I'm working on stuff right now in my local to make progress on idempotent migrations first.