SnazzGenerator (SnazzGen)

Dapper is a fast & user friendly light ORM.

SnazzGenerator is a snazzy generator for SQL to feed to Dapper.

In general writing raw SQL is the intuitive path forward - especially for queries. But for inserts (and to a lesser degree updates), there's a bit of unwelcome boilerplate. Yet using runtime reflection is expensive. That's where SnazzGenerator comes in.

Installation

dotnet add package SnazzGenerator

Usage Guide

open SnazzGenerator

type Example { ... }

// Initialization code
let insertSql = SnazzGen<{Type}>(primaryKey="{PrimaryKeyFieldName}", table="{tableName}", setByteA={Bool: Use ::bytea notation}).BuildInsert()
let updateSql = SnazzGen<{Type}>(primaryKey="{PrimaryKeyFieldName}", table="{tableName}", setByteA={Bool: Use ::bytea notation}).BuildUpdate([|string array of propery names|])
let updateSqlAllFields = SnazzGen<{Type}>(primaryKey="{PrimaryKeyFieldName}", table="{tableName}", setByteA={Bool: Use ::bytea notation}).BuildUpdate()

// Application code (examples with dapper)
// Insert:
let example = { ... }
connection.ExecuteAsync(insertSql, example).Result |> ignore

// Bulk Insert
let examples = // Generate a list of examples to insert
connection.ExecuteAsync(insertSql, examples).Result |> ignore

// Update all :
let updatedExample = { ... }
// Update just the fields specified above:
connection.ExecuteAsync(updateSql, updateExample).Result |> ignore
// Update all the fields
connection.ExecuteAsync(updateSqlAllFields, updateExample).Result |> ignore

Examples

Let's start with a simple Photo object:

type Photo = {
    Id: int;
    Name: string;
    Author: string;
    Location: string;
    BinaryData: byte[];
    CommaSeparatedTags: string;
    Likes: int;
}

Here's the sql you'd need to pass into Dapper for an insert:

INSERT INTO photo(name, author, location, binary_data, comma_separated_tags, likes) VALUES (@Name, @Author, @Location, @BinaryData::bytea, @CommaSeparatedTags, @Likes)

You can imagine how a sufficiently large table becomes tedious to write out for every type/table.

SnazzGenerator allows you to generate that SQL programmatically through reflection:

Insert

// Initialization code:
let sql = SnazzGen<Photo>().BuildInsert()
// App code:
// Use the insert SQL

That's it. It will automatically remove the primary key field in this example: "Id". Optionally you can also set "::bytea" to be used for byte arrays. It's still advisable not to do the sql generation in a "hot" path, but rather during initialization.

Custom table names:

// Initialization code:
let sql = SnazzGen("Id", "photographs").BuildInsert()
// App code:
// Use the insert SQL

Automatically setting "::bytea" for byte[]:

// Initialization code:
let sql = SnazzGen<Photo>("Id", setByteA=true).BuildInsert()
// App code:
// Use the insert SQL

// Initialization code:
let sql = SnazzGen<Photo>("Id", "photographs", true).BuildInsert()
// App code:
// Use the insert SQL

Update

You can also generate UPDATE statements ahead of time

// Initialization code:
let sql = SnazzGen<Photo>("Id").BuildUpdate([|"Name", "Author", "Likes"|])
let sqlAllFields = SnazzGen<Photo>("Id").BuildUpdate()
// App code:
// Use the insert SQL

License

SnazzGenerator is made available through the Apache License Version 2.0