In my last post I introduced the basics of FsSql, a functional wrapper over ADO.NET for F#. Any code samples in this post will use what was defined in the previous one. To recap, here are the important definitions again:
let openConn() =
let conn = new System.Data.SQLite.SQLiteConnection("Data Source=test.db;Version=3;New=True;")
conn.Open()
conn :> IDbConnection
let connMgr = Sql.withNewConnection openConn
let execScalar sql = Sql.execScalar connMgr sql
let execReader sql = Sql.execReader connMgr sql
let execReaderf sql = Sql.execReaderF connMgr sql
let execNonQueryf sql = Sql.execNonQueryF connMgr sql
let execNonQuery sql p = Sql.execNonQuery connMgr sql p |> ignore
let exec sql = execNonQuery sql []
Transactions
Ambient transactions alla TransactionScope / J2EE are implemented... through functions, of course!
Let's start with a simple function that inserts a record.
let insertUser connMgr =
Sql.execNonQueryF connMgr "insert into user (id,name) values (%d,%s)"
Note that it's parameterized by the connection manager. Now we make it require a transaction or throw if there is no current transaction:
let txInsertUser = Tx.mandatory insertUser
And we insert 50 users:
let insert50 connMgr =
for i in 1..50 do
txInsertUser connMgr i "John" |> ignore
If we run insert50, we'll get an exception "Transaction required!" since we haven't started any transaction. We need to wrap insert50:
let txInsert50 = Tx.required insert50
Tx.required will create the transaction if there isn't a previous one. Now we can run txInsert50 and each txInsertUser will run within this transaction.
Other transactional functions are Tx.never (the opposite of Tx.mandatory), Tx.supports (doesn't care if there's a transaction or not) and Tx.transactional (always starts a new transaction).
These are "ambient" or "implicit" transactions in the sense that they're transparent, i.e. txInsert50 has the same signature as insert50. There is no explicit commit or rollback: if the function ends successfully, it commits (or not, depending on transaction semantics); if there is an exception, it rolls back. Transaction semantics are defined outside the function definition. The actual transaction is carried over in the connection manager.
The library also includes a transaction computation expression. Here's an example:
let tx = Tx.TransactionBuilder()
let tran1() = tx {
do! Tx.execNonQueryi
"insert into user (id,name) values (@id,@name)"
[P("@id", 99); P("@name", "John Doe")]
}
let tran() = tx {
do! tran1()
do! Tx.execNonQueryi "insert into blabla" [] // invalid SQL
return 0
}
match tran() connMgr with // run transaction
| Tx.Commit a -> printfn "Transaction successful, return value %d" a
| Tx.Rollback a -> printfn "Transaction rolled back, return value %A" a
| Tx.Failed e -> printfn "Transaction failed with exception:\n %s" e.Message
This transaction will of course fail. Transaction expressions are currently composed with Tx.required semantics (this might be user-definable in the future). So the exception actually rolls back the whole thing, including the record inserted in tran1.
Mapping
FsSql is not a real ORM and doesn't pretend to be one. Still, I included a few mapping functions, but since there is no real schema definition in code, they're quite verbose to use. But this also makes things more flexible.
In my previous post I defined this function:
let selectById = execReaderf "select * from user where id = %d";;
val selectById : (int -> IDataReader)
This gives us a IDataReader... not the easiest thing to handle, we'd better map it to something more usable, at least a sequence of name*value pairs:
let selectByIdAsNameValue = selectById >> (Sql.mapFirst Sql.asNameValue);;
val selectByIdAsNameValue : (int -> seq<string * obj> option)
selectByIdAsNameValue 20;;
val it : seq<string * obj> option =
Some (seq [("id", 20); ("name", "John"); ("address", )])
Or since there are only three fields we could map it as a tuple:
let selectByIdAsTuple = selectById >> (Sql.mapFirst Sql.asTuple3<int,string,string option>);;
val selectByIdAsTuple : (int -> (int * string * string option) option)
selectByIdAsTuple 20;;
val it : (int * string * string option) option = Some (20, "John", null)
Or map it to a record:
type User = {
id: int
name: string
address: string option
}
let asUser (r: #IDataRecord) =
{id = (r?id).Value; name = (r?name).Value; address = r?address}
let selectByIdAsRecord = selectById >> (Sql.mapFirst asUser);;
val selectByIdAsRecord : (int -> User option)
If your database field names happen to coincide with the record field names, you can use this convenience function as your mapper:
let asUser r = Sql.asRecord<User> "" r
So far we've only seen how to map a single record from the result set (using Sql.mapFirst). Let's see now how we would map something more complex, like a joined query. First we create another table:
exec "create table animal (id int primary key not null, name varchar not null, owner int null, animalType varchar not null)"
Where the owner field will be a foreign key to the USER table. Now the corresponding record type:
type Animal = {
id: int
name: string
animalType: string
owner: int option
}
Let's insert some records:
let insertAnimal (animal: Animal) =
let toNull = function Some x -> x.ToString() | _ -> "null"
execNonQueryf
"insert into animal (id, name, owner) values (%d, %s, %s)"
animal.id animal.name (toNull animal.owner) |> ignore
// inserting sample data
insertAnimal {id = 1; name = "Seymour"; owner = Some 1}
insertAnimal {id = 2; name = "Nibbler"; owner = Some 1}
insertAnimal {id = 3; name = "Tramp"; owner = None}
Now we'd like to list people with pets. First we create the SQL:
let innerJoinSql = sprintf "select %s,%s from user u join animal a on a.owner = u.id"
(Sql.recordFieldsAlias typeof<User> "u")
(Sql.recordFieldsAlias typeof<Animal> "a")
This generates the following SQL:
select u.id u_id,u.name u_name,u.address u_address,a.id a_id,a.name a_name,a.owner a_owner
from user u join animal a on a.owner = u.id
Here's the mapping function we'll use:
let asUserWithAnimal (r: #IDataRecord) =
Sql.asRecord<User> "u" r, Sql.asRecord<Animal> "a" r
val asUserWithAnimal : (IDataRecord -> User * Animal)
We'll also use a helper function (included in FsSql):
val Seq.groupByFst : (seq<'a * 'b> -> seq<'a * seq<'b>>
This does exactly what the name and signature suggest: group a sequence of tuples by the first element of the tuple.
Now we have everything we need to run and map the query:
execReader innerJoinSql []
|> Sql.map asUserWithAnimal
|> Seq.groupByFst
|> Seq.iter (fun (person, animals) ->
printfn "%s has pets %s" person.name (String.Join(", ", animals |> Seq.map (fun a -> a.name))))
Which will print something like "Fry has pets Seymour, Nibbler"
Conclusions
FsSql aims to wrap ADO.NET to make it more idiomatic for F# consumers, providing several fine-grained functions meant to be reused or combined as necessary, as is usual in functional programming.
It's not an ORM by any means, it operates at roughly the same level as ADO.NET, so you don't get typical ORM features like type safety, automatic SQL generation and automatic mapping of query results. Maybe a proper ORM could be built on top of this library.
Other relational data access projects specific to F# include:
Full source code is here.
UPDATE 3/30/2011: I recently released FsSql 0.1, binaries available in github and NuGet.
