Defining queries

Query functions

SqlFun provides two functions responsible for defining queries:

val sql<'q> (commandText: string): 'q

that generates a function of type 'q executing query specified by the commandText parameter. The second function:

val storedproc<'q> (procedureName: string): 'q

that generates a function of type 'q executing stored procedure specified by procedureName parameter. Typically, these functions are used to define variables of modules, responsible for data access:

module Blogging = 
        
   let getBlog: int -> DataContext -> Blog Async = 
       sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
            from Blog 
            where id = @id"

   let findPosts: PostSearchCriteria -> DataContext -> Async<int * unit * Post list> =
       storedproc "FindPosts"

The one common constraint of query function is, that it must contain a parameter of the IDbConnection type (potentially indirectly) and if it can be executed within a transaction, IDbTransaction type parameter. For convenience, there is the DataContext structure, that satisfies these constraints, since it contains fields of these type.
Additionally, functions executing stored procedures have to return 3-element tuples, since stored procedures return integer code, output parameters and query results.

DataContext parameter can be hidden for readeability using DbAction or AsyncDb alias, e.g.:

module Blogging = 
        
   let getBlog: int -> Blog AsyncDb = 
       sql "select id, name, title, description, owner, createdAt, modifiedAt, modifiedBy
            from Blog 
            where id = @id"

   let findPosts: PostSearchCriteria -> AsyncDb<int * unit * Post list> =
       storedproc "FindPosts"

Parameters

Function parameters are mapped to query parameters. Valid parametr types are:

• basic types
• enums
• records
• tuples
• lists of records (for MsSql extension)
• lists of basic types (for PostgreSQL extension)
• options

Tuple elements and parameters of basic types are mapped positionally, fields of records are mapped by name. When mapping records with fields of record type, hierarchy is not reflected in name, unless the Prefixed attribute is used. Each query parameter must be reflected by some function parameter.

Results

Query results are mapped to function return types. Valid return types are:

• basic types
• enums
• records
• tuples (used for queries returning multiple results)
• lists of records
• lists of tuples
• options

Each return type element must be represented by some result value, except record fields of type list. They are meant as a basis for result transformations, e.g. joining two lists by key, etc.