Storing data structure in a MySql database using F#

Question

I need to store large and complex data structure in a MySql database (I use F#). Right now I had two ideas:

Using only structs (limiting), then create a nativeptr<_> to the struct, convert to nativeptr and load all into a byte [] which is a MySql blob.

Using F# Reflection library to convert everything to and from JSON and store as string (slooow).

Are there better ways which I've missed? Which way of those two is better?

Thanks!

Answer 1

I think storing F# data structures as binary blobs or as textual data is not a good idea - If you're using relational database, you should store the data into tables with (reasonable) columns.

The most straightforward way would be to use ADO.NET (MySQL has some providers that allow that). The code to call ADO.NET from F# will be essentially the same as code calling it from C#. See for example code in this tutorial (PDF) that uses MySqlConnection.

You can make reading of data a little nicer by using ? (dynamic) operator in F#. I wrote an example demonstrating this in MS SQL, but it can be adapted to work with MySQL too.

Finally, it appears that MySQL also has provider for Entity Framework (see here), so you may be able to use F# LINQ support (but the current version has somewhat limited support for EF queries, so it would work well only in simple scenarios).

• Ramon Snir: For simple things, that's what I do (I wrote a nice library using reflection which converts tables to records and the other way around). But I'm talking about large data structures which would waste lots of time and memory stored as tables.
• Tomas Petricek: databases are designed to store data in tables, so they are optimized for that. Take a look at some presentations about F# adCenter project (e.g. research.microsoft.com/en-us/um/cambridge/projects/fsharp/…). AFAIK, they used similar way as you suggest (map records into tables) and it worked well for them. The scale is pretty amazing - 6TB of data.
• Ramon Snir: (Reading...) But what about, for example, large trees or graphs? In order to rebuild a tree from a table, I'd need to read all related nodes (I could give each tree its ID) and start constructing it myself. Not hard (in F#) but seems to me (and I'm no expert) slower than working with pointers and blobs.
• Tomas Petricek: @Ramon: I suppose trees (or other more complicated data structures) could be an exception. AFAIK, the adCenter project worked mostly with lists. Perhaps NoSQL may be also worth considering (but I don't have any experience with these technologies)
• Ramon Snir: For future readers: since all I had was those 3 ways, I went with Tomas' way (in a nutshell - binding each table to a record type), because the blob way is too limited and the JSON is just really really slow. I have a compiled library to manage both simple bindings (1 table to 1 record type) and joint queries' bindings (several tables to 1 record type). If anyone needs it, s/he can contact me.