SQLProvider.Postgresql example

Program.fs

module Program

// ---------------------------------------------------------------------------
// Database setup (run once before building — SQLProvider reads this schema at
// COMPILE TIME). Run with psql, e.g.:
//   psql -h localhost -U postgres -d postgres -f setup.sql
//
//   CREATE TABLE IF NOT EXISTS demo_people (
//     id    SERIAL PRIMARY KEY,
//     name  TEXT NOT NULL,
//     email TEXT NOT NULL
//   );
//   TRUNCATE demo_people RESTART IDENTITY;
//   INSERT INTO demo_people (name, email) VALUES
//     ('Ada Lovelace',   'ada@example.com'),
//     ('Alan Turing',    'alan@example.com'),
//     ('Grace Hopper',   'grace@example.com');
//
// The query below is the strongly-typed equivalent of:
//   SELECT id, name, email FROM demo_people ORDER BY id;
// ---------------------------------------------------------------------------

open FSharp.Data.Sql            // Common enums (DatabaseProviderTypes, NullableColumnType)
open FSharp.Data.Sql.PostgreSql // the PostgreSql-specific SqlDataProvider lives here

// SQLProvider is a *type provider*: it connects to the database at COMPILE TIME
// using the literal below to read the schema and generate strongly-typed tables.
// Because it must be a compile-time constant, it has to be a [<Literal>] string.
[<Literal>]
let CompileTimeConnString =
    "Host=localhost;Port=5432;Database=postgres;Username=postgres;Password=postgres"

// The generated database type. POSTGRESQL vendor; SQLProvider.PostgreSql bundles
// the Npgsql driver, so no ResolutionPath is needed.
type Sql =
    SqlDataProvider<
        DatabaseVendor = Common.DatabaseProviderTypes.POSTGRESQL,
        ConnectionString = CompileTimeConnString,
        UseOptionTypes = Common.NullableColumnType.OPTION>

[<EntryPoint>]
let main _argv =
    // At runtime you can reuse the compile-time string or pass a different one
    // (e.g. read from config/env). Here we just reuse it.
    let ctx = Sql.GetDataContext CompileTimeConnString

    // A simple, strongly-typed SELECT over the demo_people table in schema "public".
    // This is LINQ-style query syntax; SQLProvider translates it to SQL.
    let people =
        query {
            for p in ctx.Public.DemoPeople do
            sortBy p.Id
            select (p.Id, p.Name, p.Email)
        }
        |> Seq.toList

    printfn "id | name          | email"
    printfn "---+---------------+-------------------"
    for (id, name, email) in people do
        printfn "%2d | %-13s | %s" id name email

    0