SqlArtisan.ArrayBind 0.9.0-beta.1

SqlArtisan

Write SQL, in C#. A type-safe SQL query builder and deterministic guard rail for AI-assisted SQL — the SQL you write is the SQL that runs, allocation-light, fast, and automatically parameterized.

Why SqlArtisan?

Does this sound familiar?

• Your ORM can't express the query, so you fall back to raw SQL strings.
• Building a query dynamically means concatenating strings through your logic.
• Every value needs a hand-written DbParameter — tedious and easy to get wrong.

SqlArtisan is SQL, written in C#: a type-safe, SQL-like API where literals become bind parameters automatically and the SQL you write is the SQL that runs.

When an AI coding assistant writes your SQL, a new failure mode appears: plausible queries that look right but mix dialects, misname columns, or silently change semantics. SqlArtisan is a deterministic guard rail — four verification layers catch these mistakes before the query reaches a database:

1. Types — column names and statement structure are compile-checked; a wrong name fails the build.
2. Analyzer — the opt-in Roslyn analyzer flags constructs your target dialect does not support, catching the most common AI failure mode (mixing dialects from training data).
3. Exact-SQL tests — Build() is deterministic, so a unit test pins the reviewed SQL as a regression contract.
4. Integration matrix — analyzer entries are verified against live engines.

bool onlyActive = true;
UsersTable u = new();

SqlStatement sql =
    Select(u.Id, u.Name, u.CreatedAt)
    .From(u)
    .Where(u.Id > 0 & u.Name.Like("A%") & ConditionIf(onlyActive, u.StatusId == 1))
    .Build();

// SELECT id, name, created_at FROM users WHERE (id > :0) AND (name LIKE :1) AND (status_id = :2)

All the convenience, minimal overhead: an allocation-light, fast builder benchmarked. Focus on the query, not the plumbing.

Contents

• Key Features
• Performance
• Packages
• Getting Started
• Configuration
• Design Philosophy
• Documentation
• Versioning & Support
• Contributing
• Changelog
• License

Key Features

• SQL-like API: queries read like the SQL they emit.
• Schema IntelliSense: table/column completion from generated table classes — no stringly-typed names.
• Allocation-light & fast: pooled buffers keep it nearly as lean as a hand-written StringBuilder (benchmarks).
• Automatic parameterization: literals become bind parameters, preventing SQL injection through values.
• Dynamic conditions: add or drop WHERE parts at runtime with helpers like ConditionIf.
• Dapper integration: optional SqlArtisan.Dapper adds one-call execution.
• Oracle array-bind execution: optional SqlArtisan.ArrayBind runs SqlArtisan-built statements for thousands of rows in one round trip via ODP.NET array binding.
• Dialect-aware analyzer: an opt-in Roslyn analyzer that deterministically flags constructs your target dialect does not support — the second layer of the guard-rail stack (docs).

Performance

SqlArtisan minimizes heap allocations — string buffers are recycled from a pooled ArrayPool<T> — so it adds little GC pressure on hot paths. On a fair, like-for-like BenchmarkDotNet workload, where every entrant builds the same query's SQL string and its bind parameters, it is the lowest-allocation and fastest builder; only a hand-written StringBuilder (no type safety, no dialect handling) is lighter.

The allocation lead is firm (lightweight builders allocate the same bytes every run); treat the timing order as directional, since run-to-run variance grows for the heavier entrants.

¹ Raw StringBuilder + Dapper DynamicParameters — the floor, with no type safety or dialect handling. ² EF Core is a full-ORM reference (different work, caches compiled queries), shown only for scale. ³ Understated: this row predates a fix to the SqlKata entrant, which had been building a lighter query than the others; it now allocates about half again as much. Every other row is unaffected, and SqlKata was already the heaviest builder.

Measured on .NET 8.0.28, i5-1135G7 / 16 GB / Windows 11, PostgreSQL dialect. Query shape, library versions, and re-run instructions are in the benchmark project's README.

Packages

Package	Description	NuGet	Downloads
SqlArtisan	The core query builder library for writing SQL in C# with a SQL-like fluent experience.
SqlArtisan.ArrayBind	High-throughput Oracle array-bind execution for SqlArtisan-built statements via ODP.NET.
SqlArtisan.Dapper	Provides extension methods to seamlessly execute queries built by SqlArtisan using Dapper.
SqlArtisan.TableClassGen	A .NET tool that generates C# table classes from your database, enabling IntelliSense and type safety with SqlArtisan.

Getting Started

Prerequisites

• .NET 8.0 or later.
• Choose the API for your target DBMS (e.g. Systimestamp for Oracle vs CurrentTimestamp for PostgreSQL). Bind-parameter prefixes (: / @ / ?) are then handled for you — verified for MySQL, Oracle, PostgreSQL, SQLite, and SQL Server.
• (Optional) SqlArtisan.Dapper auto-detects the dialect from your IDbConnection and adds execution methods.
• (Optional) SqlArtisan.ArrayBind adds Oracle array-bind execution for SqlArtisan-built statements.

Installation

Packages are pre-release, so pass --prerelease:

dotnet add package SqlArtisan --prerelease           # core query builder
dotnet add package SqlArtisan.Dapper --prerelease    # optional: Dapper execution
dotnet add package SqlArtisan.ArrayBind --prerelease # optional: Oracle array-bind execution

Quick Start

1. Define your Table Class

   Create a C# table class for each database table to enable IntelliSense and prevent typos in names. Write it manually (see below) or generate it from an existing database with the SqlArtisan.TableClassGen tool. (For a one-off query you can skip the class and name a table inline with DbTable.)

   using SqlArtisan;
   // ...
   
   internal sealed class UsersTable : DbTableBase
   {
       public UsersTable(string tableAlias = "") : base("users", tableAlias)
       {
           Id = new DbColumn(this, "id");
           Name = new DbColumn(this, "name");
           CreatedAt = new DbColumn(this, "created_at");
       }
   
       public DbColumn Id { get; }
       public DbColumn Name { get; }
       public DbColumn CreatedAt { get; }
   }
   

2. Define your DTO Class

   A plain class to map query results onto:

   internal sealed class UserDto(int id, string name, DateTime createdAt)
   {
       public int Id => id;
       public string Name => name;
       public DateTime CreatedAt => createdAt;
   }
   

3. Build and Execute your Query

   Add using static SqlArtisan.Sql; for the entry-point methods (Select, InsertInto, …), build the query, and execute it — here with Dapper:

   using SqlArtisan;
   using SqlArtisan.Dapper;
   using static SqlArtisan.Sql;
   // ...
   
   UsersTable u = new();
   
   ISqlBuilder sql =
       Select(u.Id, u.Name, u.CreatedAt)
       .From(u)
       .Where(u.Id > 0 & u.Name.Like("A%"))
       .OrderBy(u.Id);
   
   // Dapper: Set true to map snake_case columns to PascalCase/camelCase C# members.
   Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;
   
   // 'connection' is your IDbConnection. SqlArtisan auto-detects the DBMS
   // (MySQL, Oracle, PostgreSQL, SQLite, SQL Server) & applies
   // the correct bind-parameter prefix (e.g., ':' or '@').
   IEnumerable<UserDto> users = await connection.QueryAsync<UserDto>(sql);
   

   Note: sql above is single-use — Build() runs inside QueryAsync (and every other Dapper call) the moment you call it, so passing the same sql to a second call, e.g. to fetch another page, throws. Build a fresh query per call instead. See Reusing a builder chain.

   Alternative: Manual Execution

   Not using Dapper? Call Build() to get the SQL string and its parameters for raw ADO.NET, another micro-ORM, or debugging. Build() takes an optional Dbms (default PostgreSql) that sets dialect details like the parameter prefix.

   SqlStatement sql = Select(u.Id, u.Name).From(u).Where(u.Id == 10).Build();
   // sql.Text       => "SELECT id, name FROM users WHERE id = :0"
   // sql.Parameters => ":0" is 10
   

   Note: the single-use rule above applies here too — calling Build() twice on the same instance throws.

Configuration

Setting the Default DBMS

Build()'s Dbms argument defaults to PostgreSQL. Set a global default once at startup instead of passing it in every call.

// At application startup
SqlArtisanConfig.SetDefaultDbms(Dbms.SqlServer);

// Now, Build() without arguments will generate SQL Server-compatible SQL
SqlStatement sql = Select(u.Name).From(u).Build();

Note: SqlArtisanConfig is not thread-safe and should be configured only once at application startup.

Design Philosophy

SqlArtisan's principle is simple: the SQL you write is the SQL that runs. Your C# maps directly to SQL — no translation layer second-guesses your intent.

Full ORMs hide SQL behind object graphs; portability-focused builders rewrite your query per database. SqlArtisan does neither. It's for developers who want to write SQL — type-safely and composably in C#, not as fragile strings.

It normalizes only mechanical dialect details — bind-parameter markers (:0 vs @0), identifier quoting (" vs MySQL's backtick), and same-meaning token spellings such as the UPSERT excluded-row name — and never rewrites SQL grammar. Where dialects diverge, it exposes distinct, dialect-faithful APIs:

Sequence("users_id_seq").Nextval   // Oracle:      users_id_seq.NEXTVAL
Nextval("users_id_seq")            // PostgreSQL:  NEXTVAL('users_id_seq')
NextValueFor("users_id_seq")       // SQL Server:  NEXT VALUE FOR users_id_seq

So cross-database portability is a deliberate non-goal: target Oracle and you write Oracle SQL; target PostgreSQL and you write PostgreSQL SQL. You trade portability for fidelity — the full power of your database's SQL, never flattened to a lowest common denominator.

Faithful emission is the foundation of a broader mission: SqlArtisan is a deterministic guard rail for SQL written alongside AI, where each verification layer rests on the one below. See Why SqlArtisan? for the full stack.

Documentation

The same type-safe C# emits idiomatic SQL for MySQL, Oracle, PostgreSQL, SQLite, and SQL Server — each example shows the C# you write and the exact SQL it produces. The full reference lives in docs/; signatures and emitted SQL are also visible inline via IntelliSense.

Using an AI coding assistant? Point it at llms.txt for an LLM-friendly index of this documentation, or llms-full.txt for the full-text dump when your tool ingests one file instead of following links. Tools integrated with Context7 can resolve "SqlArtisan" by name instead. See the AI coding assistants guide for the setup that makes generated queries verifiable.

Versioning & Support

• Versioning, breaking changes, deprecation, support window: see the versioning & support policy.
• Security vulnerabilities: report privately per SECURITY.md — never in a public issue.

Contributing

Feedback, bug reports, and ideas are welcome.

• Bugs or feature requests: open an issue.
• Questions or ideas: start a discussion.

Changelog

Please see the CHANGELOG.md file for all notable changes.

License

This project is licensed under the MIT License. See the LICENSE file for the full license text.