sqlc-gen-csharp

sqlc-gen-csharp is a .Net plugin for sqlc.
It leverages the SQLC plugin system to generate type-safe C# code for SQL queries, supporting PostgresSQL, MySQL & SQLite via the corresponding driver or suitable Dapper abstraction.

Quickstart

version: "2"
plugins:
- name: csharp
  wasm:
    url: https://github.com/DaredevilOSS/sqlc-gen-csharp/releases/download/v0.17.0/sqlc-gen-csharp.wasm
    sha256: 39df119c6f5bd5a82f30e48f296a0e0827668fb7659e87ba5da53c0943a10986
sql:
  # For PostgresSQL
  - schema: schema.sql
    queries: queries.sql
    engine: postgresql
    codegen:
      - plugin: csharp
        out: PostgresDalGen
  # For MySQL
  - schema: schema.sql
    queries: queries.sql
    engine: mysql
    codegen:
      - plugin: csharp
        out: MySqlDalGen
  # For SQLite
  - schema: schema.sql
    queries: queries.sql
    engine: sqlite
    codegen:
      - plugin: csharp
        out: SqliteDalGen

Usage

Options

Option	Possible values	Optional	Info
overrideDriverVersion	default: 2.3.6 for MySqlConnector (mysql) 8.0.3 for Npgsql (postgresql) 8.0.10 for Microsoft.Data.Sqlite (sqlite) values: The desired driver version	Yes	Allows you to override the version of DB driver to be used.
targetFramework	default: net8.0 values: netstandard2.0, netstandard2.1, net8.0	Yes	Determines the target framework for your generated code, meaning the generated code will be compiled to the specified runtime. For more information and help deciding on the right value, refer to the Microsoft .NET Standard documentation.
generateCsproj	default: true values: false,true	Yes	Assists you with the integration of SQLC and csharp by generating a .csproj file. This converts the generated output to a .dll, a project that you can easily incorporate into your build process.
namespaceName	default: the generated project name	Yes	Allows you to override the namespace name to be different than the project name
useDapper	default: false values: false,true	Yes	Enables Dapper as a thin wrapper for the generated code. For more information, please refer to the Dapper documentation.
overrideDapperVersion	default: 2.1.35 values: The desired Dapper version	Yes	If useDapper is set to true, this option allows you to override the version of Dapper to be used.

Supported Features

• ✅ means the feature is fully supported.
• 🚫 means the database does not support the feature.
• ❌ means the feature is not supported by the plugin (but could be supported by the database).

Query Annotations

Basic functionality - same for all databases:

• :one - returns 0...1 records
• :many - returns 0...n records
• :exec - DML / DDL that does not return anything
• :execrows - returns number of affected rows by DML

Advanced functionality - varies between databases:

• :execlastid - INSERT with returned last inserted id
• :copyfrom - batch insert, implementation varies greatly

Annotation	PostgresSQL	MySQL	SQLite
:one	✅	✅	✅
:many	✅	✅	✅
:exec	✅	✅	✅
:execrows	✅	✅	✅
:execlastid	✅	✅	✅
:copyfrom	✅	✅	✅

More info can be found in here.

Macro Annotations

• sqlc.arg - Attach a name to a parameter in a SQL query
• sqlc.narg - The same as sqlc.arg, but always marks the parameter as nullable
• sqlc.slice - For databases that do not support passing arrays to the IN operator, generates a dynamic query at runtime with the correct number of parameters
• sqlc.embed - Embedding allows you to reuse existing model structs in more queries

Annotation	PostgresSQL	MySQL	SQLite
sqlc.arg	✅	✅	✅
sqlc.narg	✅	✅	✅
sqlc.slice	🚫	✅	✅
sqlc.embed	✅	✅	✅

More info can be found in here.

PostgresSQL

:execlastid - Implementation

Implemented via a RETURNING clause, allowing the INSERT command to return the newly created id, which can be of any data type that can have a unique constraint.

:copyfrom - Implementation

Implemented via the COPY FROM command which can load binary data directly from stdin.

Supported Data Types

Since in batch insert the data is not validated by the SQL itself but written in a binary format, we consider support for the different data types separately for batch inserts and everything else.

DB Type	Supported?	Supported in Batch?
boolean	✅	✅
smallint	✅	✅
integer	✅	✅
bigint	✅	✅
real	✅	✅
decimal, numeric	✅	✅
double precision	✅	✅
date	✅	✅
timestamp, timestamp without time zone	✅	✅
timestamp with time zone	✅	✅
time, time without time zone	✅	✅
time with time zone	❌	❌
interval	❌	❌
char	✅	✅
bpchar	❌	❌
varchar, character varying	✅	✅
text	✅	✅
bytea	✅	✅
2-dimensional arrays (e.g text[],int[])	✅	❌
money	✅	✅
point	✅	❌
line	✅	❌
lseg	✅	❌
box	✅	❌
path	✅	❌
polygon	✅	❌
circle	✅	❌
cidr	❌	❌
inet	❌	❌
macaddr	❌	❌
macaddr8	❌	❌
tsvector	❌	❌
tsquery	❌	❌
uuid	❌	❌
json	❌	❌
jsonb	❌	❌
jsonpath	❌	❌

MySQL

:execlastid - Implementation

The implementation differs if we're using Dapper or not.

Driver - MySqlConnector

The driver provides a LastInsertedId property to get the latest inserted id in the DB. When accessing the property, it automatically performs the below query:

SELECT LAST_INSERT_ID();

That will work only when the id column is defined as serial or bigserial, and the generated method will always return a long value.

Dapper

Since the LastInsertedId is DB specific and hence not available in Dapper, the LAST_INSERT_ID query is simply appended to the original query like this:

INSERT INTO tab1 (field1, field2) VALUES ('a', 1); 
SELECT LAST_INSERT_ID();

The generated method will return int & long for serial & bigserial respectively.

:copyfrom - Implementation

Implemented via the `LOAD DATA` command which can load data from a `CSV` file to a table. Requires us to first save the input batch as a CSV, and then load it via the driver.

Supported Data Types

Since in batch insert the data is not validated by the SQL itself but written and read from a CSV, we consider support for the different data types separately for batch inserts and everything else.

DB Type	Supported?	Supported in Batch?
bool, boolean, tinyint(1)	✅	✅
bit	✅	✅
tinyint	✅	✅
smallint	✅	✅
mediumint	✅	✅
integer, int	✅	✅
bigint	✅	✅
real	✅	✅
numeric	✅	✅
decimal	✅	✅
double precision	✅	✅
year	✅	✅
date	✅	✅
timestamp	✅	✅
char	✅	✅
nchar, national char	✅	✅
varchar	✅	✅
tinytext	✅	✅
mediumtext	✅	✅
text	✅	✅
longtext	✅	✅
binary	✅	✅
varbinary	✅	✅
tinyblob	✅	✅
blob	✅	✅
mediumblob	✅	✅
longblob	✅	✅
enum	❌	❌
set	❌	❌
json	❌	❌
geometry	❌	❌
point	❌	❌
linestring	❌	❌
polygon	❌	❌
multipoint	❌	❌
multilinestring	❌	❌
multipolygon	❌	❌
geometrycollection	❌	❌

SQLite3

:execlastid - Implementation

:execlastid - Implementation

Implemented via a RETURNING clause, allowing the INSERT command to return the newly created id, which can be of any data type that can have a unique constraint.

INSERT INTO tab1 (field1, field2) VALUES ('a', 1) RETURNING id_field;

:copyfrom - Implementation

Implemented via a multi `VALUES` clause, like this:

INSERT INTO tab1 (field1, field2) VALUES 
('a', 1),
('b', 2),
('c', 3);

Supported Data Types

DB Type	Supported?
integer	✅
real	✅
text	✅
blob	✅

Contributing

Local plugin development

Prerequisites

Make sure that the following applications are installed and added to your path.

Follow the instructions in each of these:

• Dotnet CLI - Dotnet Installation - use version .NET 8.0 (latest)
  
• Buf build - Buf Build
  
• WASM (follow this guide) - WASM libs

Protobuf

SQLC protobuf are defined in sqlc-dev/sqlc repository. Generating C# code from protocol buffer files:

make protobuf-generate

Generating code

SQLC utilizes our process / WASM plugin to generate code:

make sqlc-generate-process
make sqlc-generate-wasm

Testing generated code

Testing the SQLC generated code via a predefined flow:

make test-process-plugin
make test-wasm-plugin

Release flow

The release flow in this repo follows the semver conventions, building tag as v[major].[minor].[patch]. In order to create a release you need to add [release] somewhere in your commit message when merging to master.

Version bumping (built on tags)

By default, the release script will bump the patch version. Adding [release] to your commit message results in a new tag with v[major].[minor].[patch]+1.

• Bump minor version by adding [minor] to your commit message resulting in a new tag with v[major].[minor]+1.0
  
• Bump major version by adding [major] to your commit message resulting in a new tag with v[major]+1.0.0
  

Release structure

The new created tag will create a draft release with it, in the release there will be the wasm plugin embedded in the release.

Examples

Npgsql

Engine postgresql: NpgsqlExample

Schema | Queries | End2End Test

Config

useDapper: false
targetFramework: net8.0
generateCsproj: true
namespaceName: NpgsqlExampleGen

NpgsqlDapper

Engine postgresql: NpgsqlDapperExample

Schema | Queries | End2End Test

Config

useDapper: true
targetFramework: net8.0
generateCsproj: true
namespaceName: NpgsqlDapperExampleGen

NpgsqlLegacy

Engine postgresql: NpgsqlLegacyExample

Schema | Queries | End2End Test

Config

useDapper: false
targetFramework: netstandard2.0
generateCsproj: true
namespaceName: NpgsqlLegacyExampleGen

NpgsqlDapperLegacy

Engine postgresql: NpgsqlDapperLegacyExample

Schema | Queries | End2End Test

Config

useDapper: true
targetFramework: netstandard2.0
generateCsproj: true
namespaceName: NpgsqlDapperLegacyExampleGen

MySqlConnector

Engine mysql: MySqlConnectorExample

Schema | Queries | End2End Test

Config

useDapper: false
targetFramework: net8.0
generateCsproj: true
namespaceName: MySqlConnectorExampleGen

MySqlConnectorDapper

Engine mysql: MySqlConnectorDapperExample

Schema | Queries | End2End Test

Config

useDapper: true
targetFramework: net8.0
generateCsproj: true
namespaceName: MySqlConnectorDapperExampleGen

MySqlConnectorLegacy

Engine mysql: MySqlConnectorLegacyExample

Schema | Queries | End2End Test

Config

useDapper: false
targetFramework: netstandard2.0
generateCsproj: true
namespaceName: MySqlConnectorLegacyExampleGen

MySqlConnectorDapperLegacy

Engine mysql: MySqlConnectorDapperLegacyExample

Schema | Queries | End2End Test

Config

useDapper: true
targetFramework: netstandard2.0
generateCsproj: true
namespaceName: MySqlConnectorDapperLegacyExampleGen

Sqlite

Engine sqlite: SqliteExample

Schema | Queries | End2End Test

Config

useDapper: false
targetFramework: net8.0
generateCsproj: true
namespaceName: SqliteExampleGen

SqliteDapper

Engine sqlite: SqliteDapperExample

Schema | Queries | End2End Test

Config

useDapper: true
targetFramework: net8.0
generateCsproj: true
namespaceName: SqliteDapperExampleGen

SqliteLegacy

Engine sqlite: SqliteLegacyExample

Schema | Queries | End2End Test

Config

useDapper: false
targetFramework: netstandard2.0
generateCsproj: true
namespaceName: SqliteLegacyExampleGen

SqliteDapperLegacy

Engine sqlite: SqliteDapperLegacyExample

Schema | Queries | End2End Test

Config

useDapper: true
targetFramework: netstandard2.0
generateCsproj: true
namespaceName: SqliteDapperLegacyExampleGen