Idiomatic data access for F#
SqlFun is a tool for writing data access code in F# functional way. It's fast, type safe and gives you all powers of SQL, no custom query language constraints you. It's also lightweight, you need to know a general idea and few functions (and, of course SQL).
It's available as a Nuget package There are also extensions for MS SQL ([1], [2]), PostgreSQL and Oracle databases.
- Works with any ADO.NET provider
- All SQL features available
- Type safety
- High performance
- Compound, hierarchical query parameters
- Compound, hierarchical query results
- Support for parameter conversions
- Support for result transformations
- Support for enum types
- Asynchronous queries
- Composable, template-based queries
- Auto-generated CRUD operations
- Computation expressions for connection and transaction handling
- Support for large dataset processing
In its core SqlFun does not use any features specific to some db provider, so it works with any ADO.NET provider.
The only limitation is possibility of execution of commands in SchemaOnly mode.
It was tested against MS SqlServer, PostgreSQL, Oracle, MySQL and SQLite.
There are four extensions, enabling provider-specific features:
- the extension for MS SQL, that allows to use table valued parameters
- the extension for PostgreSQL, making use of array parameters possible and adding more comfortable Bulk Copy mechanism
- the extension for Oracle, adding some adaptations, like binding parameters by name, and allowing to use array parameters
- the extension for SQLite, that allows to use date and time values
Not all databases manage SchemaOnly behavior properly:
- MySQL, PostgreSQL and Oracle performs well only for queries that return some results - commands not returning any are executed as with
Defaultbehavior - MS SQL doesn't recognize temporary tables in
SchemaOnlyexecutions, although, you can use table variables instead
Most of us think about data access code as a separate layer. We don't like to spread SQL queries across all the application. Better way is to build an API exposing your database, consisting of structures representing database data, and functions responsible for processing this data (great object-oriented example is Insight.Database automatic interface implementation). SqlFun makes it a design requirement.
SqlFun can be added to your solution from Package Manager Console:
PM> Install-Package SqlFunFirst step is to define function creating database connection and config record:
let createConnection () = new SqlConnection(<your database connection string>)
let generatorConfig = createDefaultConfig createConnectionand wire it up with functions responsible for generating queries (using partial application):
let sql commandText = sql generatorConfig commandText
let proc name = proc generatorConfig nameand for executing them:
let run f = DbAction.run createConnection f
let runAsync f = AsyncDb.run createConnection fThen, data structures should be defined for results of your queries.
type Post = {
id: int
blogId: int
name: string
title: string
content: string
author: string
createdAt: DateTime
modifiedAt: DateTime option
modifiedBy: string option
status: PostStatus
}
type Blog = {
id: int
name: string
title: string
description: string
owner: string
createdAt: DateTime
modifiedAt: DateTime option
modifiedBy: string option
posts: Post list
}The most preferrable way is to use F# record types. Record fields should reflect query result columns, because they are mapped by name.
The best way of defining queries is to create variables for them and place in some module:
module Blogging =
let getBlog: int -> DbAction<Blog> =
sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
from Blog
where id = @id"
let getPosts: int -> DbAction<Post list> =
sql "select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
from post
where blogId = @blogId"The functions executing queries are generated during a first access to the module contents.
At that stage, all the type checking is performed, so it's easy to make type checking part of automatic testing - one line of code for each module is needed.
The generating process uses reflection heavily, but no reflection is used while processing a query, since generated code is executed.
Since your queries return DbAction<'t>, they can be passed to the run function after applying preceding parameters.
let blog = Blogging.getBlog 1 |> runThe preferrable way is to define query as asynchronous:
let getBlog: int -> AsyncDb<Blog> =
sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
from Blog
where id = @id"and then, execute as async:
async {
let! blog = Blogging.getBlog 1 |> runAsync
...
}Since the ADO.NET allows to execute many sql commands at once, it's possible to utilize it with SqlFun. The result is a tuple:
let getBlogWithPosts: int -> AsyncDb<Blog * Post list> =
sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
from Blog
where id = @id;
select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
from post
where blogId = @id"The call of sql returns some function, thus it can be composed with another function, possibly performing result transformations.
Let extend the blog type with a posts: Post list property. In this case, two results can be combined with simple function:
let getBlogWithPosts: int -> AsyncDb<Blog> =
sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
from Blog
where id = @id;
select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
from post
where blogId = @id"
>> AsyncDb.map (fun b pl -> { b with posts = pl })In simple cases, when code follows conventions, transormations can be specified more declarative way:
let getBlogWithPosts: int -> AsyncDb<Blog> =
sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
from Blog
where id = @id;
select id, blogId, name, title, content, author, createdAt, modifiedAt, modifiedBy, status
from post
where blogId = @id"
>> AsyncDb.map combine<_, Post>There are also functions that allow to combine multi-row results by joining many results or grouping wide results.
Records can be parameters as well:
let insertPost: Post -> AsyncDb<int> =
sql "insert into post
(blogId, name, title, content, author, createdAt, status)
values (@blogId, @name, @title, @content, @author, @createdAt, @status);
select scope_identity()"The record fields are mapped to query parameters by name.
The result of a function calling stored procedure should be a three-element tuple (return code, output params, result):
let findPosts: (PostSearchCriteria * SignatureSearchCriteria) -> AsyncDb<int * unit * Post list> =
proc "FindPosts"but there are transformers, that allow to ignore parts of it:
let findPosts: (PostSearchCriteria * SignatureSearchCriteria) -> Post list AsyncDb =
proc "FindPosts"
>> AsyncDb.map resultOnlyIt's easy to execute one query with runAsync or run function. To execute more queries in a context of one open connection, computation expression can be used:
asyncdb {
let! postId = Blogging.insertPost post
do! Blogging.insertComments postId comments
do! Blogging.insertTags postId tags
} |> runAsyncThe synchronous equivalent of this expression is dbaction.
To execute some queries in transaction, the inTransaction function should be used:
asyncdb {
let! postId = Blogging.insertPost post
do! Blogging.insertComments postId comments
do! Blogging.insertTags postId tags
}
|> AsyncDb.inTransaction
|> runAsyncIts synchronous equivalent is DbAction.inTransaction.
For more comprehensive documentation refer project github pages.
For more examples refer test project.
![dependabot[bot] avatar](https://avatars.githubusercontent.com/in/29110?v=4 "dependabot[bot]")
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent