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 proc<'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> =
       proc "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 type 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> =
       proc "FindPosts"

Parameters

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

• basic types
• enums
• records
• tuples
• lists/arrays/sets/sequences of records (for MsSql extension)
• arrays of basic types (for PostgreSQL and Oracle extensions)
• 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/arrays/sets/sequences of records
• lists/arrays/sets/sequences of tuples
• options
• ResultStream for queries returning single results

Valid record field types are:

• basic types
• enums
• records
• tuples of records
• lists/arrays/sets/sequences of records
• lists/arrays/sets/sequences of tuples
• options

Consider following record type:

type Blog = {
    id: int
    name: string
    title: string
    description: string
    owner: string
    createdAt: DateTime
    modifiedAt: DateTime option
    modifiedBy: string option
    posts: Post list
}

The query function result can be Blog itself, option<Blog>, list<Blog>, array<Blog>, ResultStream<Blog>, or even list<Blog * Post>.

The record can contain subrecords:

type Signature = {
    owner: string
    createdAt: DateTime
    modifiedAt: DateTime option
    modifiedBy: string option
} 

type Blog = {
    id: int
    name: string
    title: string
    description: string
    signature: Signature
    posts: Post list
}

If some subrecord is used more, than once, it can be prefixed to avoid name clashes:

type Blog = {
    id: int
    name: string
    title: string
    description: string
    [<Prefixed("blog_sig_")>] signature: Signature
    posts: Post list
}

In this case, names of columns, corresponding to Signature fields, must be blog_sig_owner, blog_sig_createdAt, blog_sig_modifiedAt and blog_sig_modifiedBy.

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