While SQL Server is well supported in F# through the built in type provider FSharp.Data.SqlClient, SQL Server itself has the drawback of cost, which can be difficult to justify in a small business or an environment where its richer features are not required. The type provider I’ve used thus far, SQLProvider, supports a wider array of database engines, and this post explores using it with MySQL.
MySQL Workbench includes tooling to migrate from another database. To access this, open the Database menu and select Migration Wizard…. To migrate follow the Migration Task List noting the following:
- In Source Selection use Database System = Microsoft SQL Server, Connection Method = ODBC (native)
- In Manual Editing select Column Mappings from the View dropdown (top-right):
- change the target types to suit MySQL. For instance, where Source Type is UNIQUEIDENTIFIER, set Target Type to CHAR(36).
- remove the UNIQUE target flag from columns which are not primary keys.
After completing migration and verifying the creation of the tables and copying of data, the calling code needs to change to support MySQL:
[<Literal>] let resPath = __SOURCE_DIRECTORY__ + @"../../packages/MySql.Data/lib/net45" type Sql = SqlDataProvider< ConnectionString="Server=192.168.56.101;Database=AssetManager;User=USERNAME;Password=PASSWORD", DatabaseVendor=Common.DatabaseProviderTypes.MYSQL, ResolutionPath = resPath, UseOptionTypes=true>
Unfortunately when I did this I kept getting an error “Unable to resolve assemblies. One of MySql.Data.dll … must exist in the paths …”. Debugging errors with type providers is difficult as they run at build. In this case I directly created a MySQLCommand by typing
let x = MySql.Data.MySqlClient.MySqlCommand which gave more useful errors: “The type X is required here and unavailable. You must add a reference to System.Y…”, thus adding those missing references helped. At some stage I also needed to restart Visual Studio after correctly setting the reference path for the MySQL dll.
Changing the database engine changes the objects generated by the type provider so some find-and-replace is required to correct these. There are also some type changes, such as changing bytes to signed bytes.
The first, and frankly show-stopping, problem is with Guids.
MySql.Data interprets CHAR(36) as a
System.Guid (it can instead use BINARY(16) via a connection string property: OldGuids). However SqlProvider created a
string property and the end result is a runtime conversion error between the two within SqlProvider. My impression, although questions to that effect have not been answered, is this is a limitation of SqlProvider. Likely the best solution is to use CHAR(37) instead of CHAR(36) and then add a lot of manual
A question I’ve not yet answered is whether this works on Mac and Linux. In theory I believe it should as the MySQL client dll can run against Mono as described here. However given the poor GUID support my inclination is to try a different database engine at this stage.