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.15.2/sqlc-gen-csharp.wasm
    sha256: 251cfaaeccaf726aa06103fcdab500006b4bc5cd0be523eb6a8894db8d520147
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.

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	✅	✅	❌

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

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	✅	✅	✅

• ✅ 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).

More info can be found in here.

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

Engine postgresql: NpgsqlExample

Schema | Queries | End2End Test

Config

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

Engine postgresql: NpgsqlDapperExample

Schema | Queries | End2End Test

Config

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

Engine postgresql: NpgsqlLegacyExample

Schema | Queries | End2End Test

Config

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

Engine postgresql: NpgsqlDapperLegacyExample

Schema | Queries | End2End Test

Config

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

Engine mysql: MySqlConnectorExample

Schema | Queries | End2End Test

Config

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

Engine mysql: MySqlConnectorDapperExample

Schema | Queries | End2End Test

Config

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

Engine mysql: MySqlConnectorLegacyExample

Schema | Queries | End2End Test

Config

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

Engine mysql: MySqlConnectorDapperLegacyExample

Schema | Queries | End2End Test

Config

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

Engine sqlite: SqliteExample

Schema | Queries | End2End Test

Config

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

Engine sqlite: SqliteDapperExample

Schema | Queries | End2End Test

Config

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

Engine sqlite: SqliteLegacyExample

Schema | Queries | End2End Test

Config

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

Engine sqlite: SqliteDapperLegacyExample

Schema | Queries | End2End Test

Config

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