Soma - Sql Oriented MApping framework for F#

  • Article

One of the things I haven't blogged about much is the great collection of F# projects up on CodePlex.

The one that caught my eye today is Soma - a "Sql Oriented Mapping Framework". (Note, Soma is documented in Japanese  - I have seen from Twitter and elsewhere the many F# tweets in Japanese, and its great to see a community developing there)

Soma implements an F#-oriented, code-first approach to O/R mapping. Queries are expressed in SQL strings, with some interesting use of F# quotations. Here's the description of the project from the home page, along with a sample that looks very compelling:

Soma is an O/R mapping framework develeped in F#.
Supported programming languages and RDBMS are followings:
SomaはF#で開発されたO/Rマッピングフレームワークです。
サポートされるプログラミング言語とRDBMSは次の通りです。
languages

  • F# 2.0
  • C# 4.0
  • Visual Basic 2010

RDBMS

  • Microsoft SQL Server 2008
  • MySQL 5.x.
  • Oracle Database 11g

Main Features

Main features are followings:

  • 2 way SQL - SQL is executable in programs and in sql tools out of the box
  • SQL log handling
  • automatic primary key generation
  • optimistic lock
  • pagination
  • support for F# immutable record type
  • support for muttable POCO
  • arbitrary SQL execution and result mapping
  • stored procedure call
  • no configuration file
  • stateless architecture

主要な機能は以下の通りです。

  • 2 way SQL - SQLはプログラムとツールで実行可能
  • SQLのログハンドリング
  • 主キーの自動生成
  • 楽観的ロック
  • ページング
  • F#のイミュータブルなレコード型のサポート
  • ミュータブルなPOCOのサポート
  • 任意のSQLの実行と結果のマッピング
  • ストアドプロシージャの実行
  • 設定ファイルを必要としない
  • ステートレスなアーキテクチャ

Sample Code in F#

Samples in other languages are included in the distribution zip file.
他の言語で作られたサンプルは配布zipファイルに含まれます。

 






open System<br>open System.Transactions<br>open Soma.Core<br>// define a module wraps Soma.Core.Db module<br>module MyDb =   let config = <br>    { new MsSqlConfig() with<br>      member this.ConnectionString = "Data Source=.;Initial Catalog=Soma.Tutorial;Integrated Security=True" }  let query<'T> = Db.query<'T> config  let queryOnDemand<'T> = Db.queryOnDemand<'T> config  let execute sql expr = Db.execute config sql expr  let find<'T when 'T : not struct> = Db.find<'T> config  let tryFind<'T when 'T : not struct> = Db.tryFind<'T> config  let insert<'T when 'T : not struct> = Db.insert<'T> config  let update<'T when 'T : not struct> = Db.update<'T> config  let delete<'T when 'T : not struct> = Db.delete<'T> config  let call<'T when 'T : not struct> = Db.call<'T> config<br>// define a record mapped to a table <br>type Employee = <br>  { [<Id(IdKind.Identity)>]<br>    EmployeeId : int     EmployeeName : string<br>    DepartmentId : int<br>    [<Version>]<br>    VersionNo : int }<br>// define a record mapped to a procedure<br>type ProcResultAndOut = <br>  { EmployeeId : int<br>    [<ProcedureParam(Direction = Direction.Output)>]<br>    EmployeeCount : int<br>    [<ProcedureParam(Direction = Direction.Result)>]<br>    Employees : Employee list }<br>let main =  // execute following code in a transaction, but don't commit<br>  use tx = new TransactionScope()    // find by id  let emp = MyDb.find<Employee> [1]<br>  printfn "FOUND RECORD : \n%A\n" emp  // update  let emp = MyDb.update { emp with EmployeeName = "Hoge" }<br>  printfn "UPDATED RECORD : \n%A\n" emp  // delete<br>  MyDb.delete emp<br>  printfn "DELETED RECORD : \n%A\n" emp  // insert  let emp = MyDb.insert { EmployeeId = 0; EmployeeName = "Allen"; DepartmentId = 2; VersionNo = 0}<br>  printfn "INSERTED RECORD : \n%A\n" emp  // query by SQL. parameters are bindable with "Code Quotations".   let empList = <br>    MyDb.query<string * Employee> "    select       d.DepartmentName, e.EmployeeId, e.EmployeeName, e.DepartmentId, e.VersionNo       from       Employee e <br>    inner join       Department d     on       e.DepartmentId = d.DepartmentId     where       e.DepartmentId = /* emp.DepartmentId */0 <br>    " <@ let emp = emp in () @><br>  printfn "QUERIED TUPLES : \n%A\n" empList  // call procedure  let result = MyDb.call<ProcResultAndOut> { EmployeeId = 1; EmployeeCount = 0; Employees = [] }<br>  printfn "PROCEDURE OUTPUT : \n%A\n" result  // exequte arbitrary SQL  let rows = <br>    MyDb.execute "<br>    delete from Employee <br>    " <@ () @><br>  printfn "AFFECTED ROWS : \n%A\n" rows<br>  Console.ReadKey()