5 ms·

Rezoom.SQL: Statically typed SQL for F#

138 points by Bognar 9y ago

Bognar 9y ago
Some discussion on reddit: https://www.reddit.com/r/programming/comments/6qizpb/statically_typed_sql_for_f/ https://www.reddit.com/r/programming/comments/6qizpb/statica...
- kbenson 9y ago
  LOL at how a chunk of the comments ended up being the author or this and an author of Diesel for Rust discussing cool capabilities of Diesel. Personally, I prefer my ORM/query builders to be more structures and adhere to the parent language more, with sane escape hatches for small bits of raw SQL as needed. This does look like it would be cool for a project that took the inline SQL or SQL phrasebook approach that wants to add a bit of structure, as it might be easier to put this in place over that?
  - jackmott 9y ago
    You might prefer: http://fsprojects.github.io/SQLProvider/ http://fsprojects.github.io/SQLProvider/
wyc 9y ago
Thank you for this! I think SQL is a great language, and I've always wanted to write straight SQL with compiler guarantees, as projects like Esqueleto almost let me do. For rapid prototyping, I think ORMs are still the best solution. However, their benefits start to diminish when scaling the team and product start becoming priorities. Working /with/ the database becomes a lot more fruitful than trying to wrangle it to your format. After all, web applications are just database skins.
- hungerstrike 9y ago
  > ...I've always wanted to write straight SQL with compiler guarantees... SQL Server Data Tools does this exceedingly well. It's a Microsoft-built addon to Visual Studio. Catching errors during compilation of SQL files is certainly very helpful! They also provide a very nice diff tool where you can diff between your project SQL files and a given server/database or between 2 different projects or 2 different databases... It also has a nice data-diff tool. (All for SQL Server only of course.) This tool helped me very much to replace heavy ORMs (such as EntityFramework and so on) with just Dapper and a bunch of query procedures that are kept in the database itself (known as Stored Procedures in SQL Server). More detail on my setup in this comment - https://news.ycombinator.com/item?id=14852425 https://news.ycombinator.com/item?id=14852425
  - kbenson 9y ago
    One of the things I was really excited about with the Drizzle MySQL fork a long time ago was how they were planning to make the server side SQL language supported through plugins. Being able to use the same language on the front-end and for stored procedures would be really nice. For me, that's Perl, but for a lot of people it's probably Python, Ruby, Java or some JVM, derivative, etc. To be sure, there are downsides (not the least of which is making it easier to load a lot of logic on the SQL server which may be much harder to scale if you aren't careful), but it would also make it much easier to do some complex SQL operations.
default-kramer 9y ago
This might be a dream come true for me. That Visual Studio integration is awesome - I did not know about F# Type Providers but I'm clearly overdue to learn about them.
phillipcarter 9y ago
This is really cool. Yet another example of how Type Providers are such a game-changer for people. Relevant for folks: FSharp.Data: http://fsharp.github.io/FSharp.Data/ http://fsharp.github.io/FSharp.Data/ SqlProvider: http://fsprojects.github.io/SQLProvider/ http://fsprojects.github.io/SQLProvider/ FSharp.Data.SqlClient: http://fsprojects.github.io/FSharp.Data.SqlClient/ http://fsprojects.github.io/FSharp.Data.SqlClient/ Azure Storage Type Provider: https://github.com/fsprojects/AzureStorageTypeProvider https://github.com/fsprojects/AzureStorageTypeProvider
- runeks 9y ago
  > Yet another example of how Type Providers are such a game-changer for people. I’ve seen this statement twice on HN over the past few days, so I’m getting curious about F#. I would like to understand, however, exactly how type providers change the game. “An F# type provider is a component that provides types, properties, and methods for use in your program.”[1] Is there an essential difference between an F# type provider and a Haskell module (which also exports types, properties and methods)? [1] https://docs.microsoft.com/en-us/dotnet/fsharp/tutorials/type-providers/ https://docs.microsoft.com/en-us/dotnet/fsharp/tutorials/typ...
  - marpstar 9y ago
    You ever been working with a JSON response, a CSV file, or a SQL query and you're just flipping between the data and your text editor, mapping each field into your model type? Type Providers automate that mapping so that you can "teach" your code the structure of that data and work with it without having to write the boilerplate.
  - omaranto 9y ago
    I've never used F#, so I might get this completely wrong, but I think they are a practical form of dependently typed programming: a type provider builds new types that depend on values in your program. If you have some sort of schema for data, a type provider could parse it and build appropriate types to hold data that conforms to the schema. A type provider for a database, say, could take a string that specifies a database connection, connect to the database, and then query it to figure out what sorts of tables it holds and then construct F# types capable of holding a record from one of the tables.
rspeele 9y ago
Hi, I am the author of this project. The choice to typecheck a SQL dialect directly instead of using an embedded DSL like LINQ is uncommon enough that it gets the majority of the focus in my own documentation. However, it wasn't what motivated me to create the project. I wanted to have an ORM that could take full advantage of my resumption monad library, Rezoom. This library is similar in concept to Facebook's Haxl or Dataloader, in that it can combine independent requests into batches and cache ones that have already been resolved. The main difference (besides being F#) is that it is intended to be used for mutations, not just queries. This is a bit tricky because when you mutate the stuff in the database, that may invalidate the cached results of previous queries. The problem isn't too bad since, as with Haxl and Dataloader, the cache is per-request so it's very short lived, but it could still cause problems. Contrived example: // domain layer line item logic, includes business rules // returns the updated invoice let addLineItem invoiceId lineItem = plan { // query for the invoice let! invoice = DB.getInvoiceById invoiceId if invoice.IsSent then failwith "Can't edit this invoice because it has been sent" // run INSERT/UPDATE statement(s) do! DB.addInvoiceLineItem invoiceId lineItem // re-query for the invoice to get its current state return! DB.getInvoiceById invoiceId } `DB.addInvoiceLineItem` needs to invalidate the cached result of `DB.getInvoiceById`. Otherwise this code won't return the updated invoice, but the invoice before updating. Again, contrived example, but when the "reload the invoice" line is in a whole other function somewhere else it could be a real problem. The way Rezoom solves this is to have "errands" (its word for a batchable data dependency) specify 2 128-bit masks. One represents its dependencies, the other represents its invalidations. When you run a command with a non-zero invalidation mask, all the cached results whose dependency bits overlap with the invalidation bits get evicted. The caches are local to a "category" which is pretty much just a tag for which library created the errand, so that the errands wrapping, say, a web API don't stomp on the caches for the errands talking to a SQL database (actually the connection string is involved too so you can deal with multiple databases from one app). For the errands generated by Rezoom.SQL, each bit in the mask represents one table, so running an UPDATE/INSERT/DELETE invalidates the cache for all commands that read from the affected tables. If you have more than 128 tables in your database the IDs wrap around so you'll get some false invalidations possibly hurting performance, but never a false cached result hurting correctness. This all could make it very easy to write a GraphQL server backed by Rezoom + Rezoom.SQL.
youdontknowtho 9y ago
That's really impressive. Seriously. I think type providers are a really fantastic feature. I just wish that I had more cause to write f#.
omi 9y ago
Is this similar to slick in scala?
Risord 9y ago
How production ready this is and how could you compare this to SqlClient (addition to wider DB support)? Is there any possibilities to compose queries? I am currently with SqlProvider but raw SQL would be interesting option =)

138 points by Bognar 9y ago

Some discussion on reddit: https://www.reddit.com/r/programming/comments/6qizpb/statically_typed_sql_for_f/ https://www.reddit.com/r/programming/comments/6qizpb/statica...

kbenson 9y ago
LOL at how a chunk of the comments ended up being the author or this and an author of Diesel for Rust discussing cool capabilities of Diesel. Personally, I prefer my ORM/query builders to be more structures and adhere to the parent language more, with sane escape hatches for small bits of raw SQL as needed. This does look like it would be cool for a project that took the inline SQL or SQL phrasebook approach that wants to add a bit of structure, as it might be easier to put this in place over that?
- jackmott 9y ago
  You might prefer: http://fsprojects.github.io/SQLProvider/ http://fsprojects.github.io/SQLProvider/

wyc 9y ago

Thank you for this! I think SQL is a great language, and I've always wanted to write straight SQL with compiler guarantees, as projects like Esqueleto almost let me do. For rapid prototyping, I think ORMs are still the best solution. However, their benefits start to diminish when scaling the team and product start becoming priorities. Working /with/ the database becomes a lot more fruitful than trying to wrangle it to your format. After all, web applications are just database skins.

hungerstrike 9y ago
> ...I've always wanted to write straight SQL with compiler guarantees... SQL Server Data Tools does this exceedingly well. It's a Microsoft-built addon to Visual Studio. Catching errors during compilation of SQL files is certainly very helpful! They also provide a very nice diff tool where you can diff between your project SQL files and a given server/database or between 2 different projects or 2 different databases... It also has a nice data-diff tool. (All for SQL Server only of course.) This tool helped me very much to replace heavy ORMs (such as EntityFramework and so on) with just Dapper and a bunch of query procedures that are kept in the database itself (known as Stored Procedures in SQL Server). More detail on my setup in this comment - https://news.ycombinator.com/item?id=14852425 https://news.ycombinator.com/item?id=14852425
- kbenson 9y ago
  One of the things I was really excited about with the Drizzle MySQL fork a long time ago was how they were planning to make the server side SQL language supported through plugins. Being able to use the same language on the front-end and for stored procedures would be really nice. For me, that's Perl, but for a lot of people it's probably Python, Ruby, Java or some JVM, derivative, etc. To be sure, there are downsides (not the least of which is making it easier to load a lot of logic on the SQL server which may be much harder to scale if you aren't careful), but it would also make it much easier to do some complex SQL operations.

default-kramer 9y ago

This might be a dream come true for me. That Visual Studio integration is awesome - I did not know about F# Type Providers but I'm clearly overdue to learn about them.

phillipcarter 9y ago

This is really cool. Yet another example of how Type Providers are such a game-changer for people. Relevant for folks: FSharp.Data: http://fsharp.github.io/FSharp.Data/ http://fsharp.github.io/FSharp.Data/ SqlProvider: http://fsprojects.github.io/SQLProvider/ http://fsprojects.github.io/SQLProvider/ FSharp.Data.SqlClient: http://fsprojects.github.io/FSharp.Data.SqlClient/ http://fsprojects.github.io/FSharp.Data.SqlClient/ Azure Storage Type Provider: https://github.com/fsprojects/AzureStorageTypeProvider https://github.com/fsprojects/AzureStorageTypeProvider

runeks 9y ago
> Yet another example of how Type Providers are such a game-changer for people. I’ve seen this statement twice on HN over the past few days, so I’m getting curious about F#. I would like to understand, however, exactly how type providers change the game. “An F# type provider is a component that provides types, properties, and methods for use in your program.”[1] Is there an essential difference between an F# type provider and a Haskell module (which also exports types, properties and methods)? [1] https://docs.microsoft.com/en-us/dotnet/fsharp/tutorials/type-providers/ https://docs.microsoft.com/en-us/dotnet/fsharp/tutorials/typ...
- marpstar 9y ago
  You ever been working with a JSON response, a CSV file, or a SQL query and you're just flipping between the data and your text editor, mapping each field into your model type? Type Providers automate that mapping so that you can "teach" your code the structure of that data and work with it without having to write the boilerplate.
- omaranto 9y ago
  I've never used F#, so I might get this completely wrong, but I think they are a practical form of dependently typed programming: a type provider builds new types that depend on values in your program. If you have some sort of schema for data, a type provider could parse it and build appropriate types to hold data that conforms to the schema. A type provider for a database, say, could take a string that specifies a database connection, connect to the database, and then query it to figure out what sorts of tables it holds and then construct F# types capable of holding a record from one of the tables.

rspeele 9y ago

Hi, I am the author of this project. The choice to typecheck a SQL dialect directly instead of using an embedded DSL like LINQ is uncommon enough that it gets the majority of the focus in my own documentation. However, it wasn't what motivated me to create the project. I wanted to have an ORM that could take full advantage of my resumption monad library, Rezoom. This library is similar in concept to Facebook's Haxl or Dataloader, in that it can combine independent requests into batches and cache ones that have already been resolved. The main difference (besides being F#) is that it is intended to be used for mutations, not just queries. This is a bit tricky because when you mutate the stuff in the database, that may invalidate the cached results of previous queries. The problem isn't too bad since, as with Haxl and Dataloader, the cache is per-request so it's very short lived, but it could still cause problems. Contrived example: // domain layer line item logic, includes business rules // returns the updated invoice let addLineItem invoiceId lineItem = plan { // query for the invoice let! invoice = DB.getInvoiceById invoiceId if invoice.IsSent then failwith "Can't edit this invoice because it has been sent" // run INSERT/UPDATE statement(s) do! DB.addInvoiceLineItem invoiceId lineItem // re-query for the invoice to get its current state return! DB.getInvoiceById invoiceId } `DB.addInvoiceLineItem` needs to invalidate the cached result of `DB.getInvoiceById`. Otherwise this code won't return the updated invoice, but the invoice before updating. Again, contrived example, but when the "reload the invoice" line is in a whole other function somewhere else it could be a real problem. The way Rezoom solves this is to have "errands" (its word for a batchable data dependency) specify 2 128-bit masks. One represents its dependencies, the other represents its invalidations. When you run a command with a non-zero invalidation mask, all the cached results whose dependency bits overlap with the invalidation bits get evicted. The caches are local to a "category" which is pretty much just a tag for which library created the errand, so that the errands wrapping, say, a web API don't stomp on the caches for the errands talking to a SQL database (actually the connection string is involved too so you can deal with multiple databases from one app). For the errands generated by Rezoom.SQL, each bit in the mask represents one table, so running an UPDATE/INSERT/DELETE invalidates the cache for all commands that read from the affected tables. If you have more than 128 tables in your database the IDs wrap around so you'll get some false invalidations possibly hurting performance, but never a false cached result hurting correctness. This all could make it very easy to write a GraphQL server backed by Rezoom + Rezoom.SQL.

youdontknowtho 9y ago

That's really impressive. Seriously. I think type providers are a really fantastic feature. I just wish that I had more cause to write f#.

omi 9y ago

Is this similar to slick in scala?

Risord 9y ago

How production ready this is and how could you compare this to SqlClient (addition to wider DB support)? Is there any possibilities to compose queries? I am currently with SqlProvider but raw SQL would be interesting option =)