SqlArtisan

Write SQL, in C#. A SQL query builder that provides a SQL-like experience, designed for developers who value the clarity and control of direct SQL syntax.
Why SqlArtisan?
Does this sound familiar?
- You write raw SQL strings by hand when your ORM can't handle a query.
- You build dynamic queries by stitching strings together, cluttering your core application logic.
- You write boilerplate code for
DbParameter
objects, a process prone to subtle bugs and type errors.
SqlArtisan is designed to resolve these frustrations. It lets you write that same SQL directly in C# with a SQL-like API, a clean way to build dynamic queries, and automatic parameters—like this:
bool onlyActive = true;
UsersTable u = new();
ISqlBuilder sql =
Select(
u.Id,
u.Name,
u.CreatedAt)
.From(u)
.Where(
u.Id > 0
& u.Name.Like("A%")
& ConditionIf(onlyActive, u.StatusId == 1))
.OrderBy(u.Id);
So you can focus on the query logic, not the boilerplate. That’s why SqlArtisan.
Table of Contents
- Changelog
- Packages
- Key Features
- Getting Started
- Configuration
- Performance
- Usage Examples
- SELECT Query
- SELECT Clause: Column Aliases,
DISTINCT
, Hints
- FROM Clause: FROM-less,
DUAL
- WHERE Clause
- JOIN Clause:
INNER JOIN
, LEFT JOIN
, RIGHT JOIN
, FULL JOIN
, CROSS JOIN
- ORDER BY Clause:
ASC
, DESC
, NULLS FIRST/LAST
- GROUP BY and HAVING Clause
- Set Operators:
UNION [ALL]
, EXCEPT [ALL]
, MINUS [ALL]
, INTERSECT [ALL]
- FOR UPDATE Clause
- DELETE Statement
- UPDATE Statement
- INSERT Statement: Standard, SET-like,
INSERT SELECT
- WITH Clause (Common Table Expressions):
WITH
, WITH RECURSIVE
, CTEs with DML
- Expressions
- NULL Literal
- Arithmetic Operators:
+
, -
, *
, /
, %
- Conditions: Logical, Comparison,
NULL
, LIKE
, REGEXP_LIKE
, BETWEEN
, IN
, EXISTS
, Dynamic Conditions
- CASE Expressions: Simple CASE, Searched CASE
- Window Functions
- Sequence:
CURRVAL
, NEXTVAL
, NEXT VALUE FOR
- Additional Query Details
- Bind Parameter Types
- Functions
- Numeric Functions:
ABS
, MOD
, TRUNC
- Character Functions:
CONCAT
, INSTR
, LPAD
, LTRIM
, LENGTH
, LENGTHB
, LOWER
, RPAD
, RTRIM
, REGEXP_COUNT
, REGEXP_REPLACE
, REGEXP_SUBSTR
, REPLACE
, SUBSTR
, SUBSTRB
, TRIM
, UPPER
- Date and Time Functions:
ADD_MONTHS
, CURRENT_DATE
, CURRENT_TIME
, CURRENT_TIMESTAMP
, EXTRACT
, LAST_DAY
, MONTHS_BETWEEN
, SYSDATE
, SYSTIMESTAMP
, TRUNC
- Conversion Functions:
COALESCE
, DECODE
, NVL
, TO_CHAR
, TO_DATE
, TO_NUMBER
, TO_TIMESTAMP
- Aggregate Functions:
AVG
, COUNT
, MAX
, MIN
, SUM
- Window Functions:
CUME_DIST
, DENSE_RANK
, PERCENT_RANK
, RANK
, ROW_NUMBER
- Contributing
- License
Changelog
Please see the CHANGELOG.md file for all notable changes.
Packages
SqlArtisan | The core query builder library for writing SQL in C# with a SQL-like fluent experience. |  |  |
SqlArtisan.Dapper | Provides extension methods to seamlessly execute queries built by SqlArtisan using Dapper. |  |  |
SqlArtisan.TableClassGen | A .NET tool that generates C# table schema classes from your database, enabling IntelliSense and type-safety with SqlArtisan. |  |  |
Key Features
- SQL-like API: Write queries naturally, mirroring SQL syntax and structure.
- Schema IntelliSense: Provides code completion for table/column names via table schema classes, improving development speed and accuracy.
- Automatic Parameterization: Converts literals to bind variables, boosting security (SQLi prevention) and query performance.
- Dynamic Query Conditions: Dynamically include or exclude
WHERE
conditions (and other query parts) using simple helpers like ConditionIf
.
- Low-Allocation Design: Minimizes heap allocations and GC load for superior performance.
- Seamless Dapper Integration: The optional
SqlArtisan.Dapper
library provides Dapper extensions that enable effortless SQL execution.
Getting Started
Prerequisites
- .NET Version: .NET 8.0 or later.
- Dialect-Specific API Usage: SqlArtisan provides dialect-specific C# APIs that map to DBMS features. For example, use
Systimestamp
for Oracle's SYSTIMESTAMP
and CurrentTimestamp
for PostgreSQL's CURRENT_TIMESTAMP
. Developers should select the C# API appropriate for their target database.
- Bind Parameter Handling: SqlArtisan adjusts bind parameter prefixes (e.g.,
:
or @
) to suit the target DBMS. Currently, this behavior is verified for MySQL, Oracle, PostgreSQL, SQLite, and SQL Server.
- (Optional) Dapper Integration: Install
SqlArtisan.Dapper
for seamless Dapper execution. It auto-detects the dialect from your IDbConnection
to apply correct settings (like bind parameter prefixes) and provides helpful execution methods.
Installation
You can install SqlArtisan and its optional Dapper integration library via NuGet Package Manager.
(Note: These packages are currently in their pre-release phase, so use the --prerelease flag when installing.)
For the core query building functionality:
dotnet add package SqlArtisan --prerelease
For seamless execution with Dapper (recommended):
dotnet add package SqlArtisan.Dapper --prerelease
Quick Start
-
Define your Table Schema Class
Create C# classes for your database tables to enable IntelliSense and prevent typos in names. You can write these manually (see example below) or generate them from an existing database with the SqlArtisan.TableClassGen
tool.
using SqlArtisan;
internal sealed class UsersTable : DbTableBase
{
public UsersTable(string tableAlias = "") : base("users", tableAlias)
{
Id = new DbColumn(tableAlias, "id");
Name = new DbColumn(tableAlias, "name");
CreatedAt = new DbColumn(tableAlias, "created_at");
}
public DbColumn Id { get; }
public DbColumn Name { get; }
public DbColumn CreatedAt { get; }
}
-
Define your DTO Class
Create a Data Transfer Object (DTO) class. This class will be used to map the results of your SQL query.
internal sealed class UserDto(int id, string name, DateTime createdAt)
{
public int Id => id;
public string Name => name;
public DateTime CreatedAt => createdAt;
}
-
Build and Execute your Query
Construct your query using SqlArtisan's SQL-like API. For convenient access to entry point methods like Select()
or InsertInto()
, add a static using for SqlArtisan.Sql
, which provides these static helper methods.
Once built, execute the query. This example uses Dapper with SqlArtisan.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.DefaultTypeMap.MatchNamesWithUnderscores = true;
IEnumerable<UserDto> users = await connection.QueryAsync<UserDto>(sql);
Alternative: Manual Execution (Accessing SQL and Parameters)
Alternatively, access the SQL string and parameters directly for use with raw ADO.NET, other micro-ORMs, or for debugging, instead of using SqlArtisan.Dapper
.
ISqlBuilder.Build()
accepts an optional Dbms
argument (defaulting to Dbms.PostgreSql
) to specify the SQL dialect. This affects features like the bind parameter prefix (e.g., :
for PostgreSQL, @
for SQL Server).
Example (Default - PostgreSQL):
UsersTable u = new();
SqlStatement sql =
Select(u.Id, u.Name)
.From(u)
.Where(u.Id == 10 & u.Name == "Alice")
.Build();
Example (Specifying SQL Server):
UsersTable u = new();
SqlStatement sql =
Select(u.Id, u.Name)
.From(u)
.Where(u.Id == 20 & u.Name == "Bob")
.Build(Dbms.SqlServer);
Configuration
Setting the Default DBMS
As shown in the Quick Start, the Build()
method can accept a Dbms
argument. However, its default is PostgreSQL. To avoid specifying the DBMS in every call, you can set a global default once at application startup.
Example:
SqlArtisanConfig.SetDefaultDbms(Dbms.SqlServer);
SqlStatement sql = Select(u.Name).From(u).Build();
Note: SqlArtisanConfig
is not thread-safe and should be configured only once at application startup.
Performance
SqlArtisan is engineered for efficient performance, primarily by minimizing heap allocations. Our core strategy is efficient buffer management using ArrayPool<T>
: internal buffers, particularly for string construction, are recycled from a shared pool. This approach leads to fewer garbage collection (GC) pauses and improved application throughput.
To illustrate this, we benchmarked our ArrayPool<T>
-based internal string building against common approaches using BenchmarkDotNet.
Benchmark Details
- Environment:
- .NET Version: .NET 8
- CPU: Intel Core i5-1135G7 @ 2.40GHz
- RAM: 16 GB
- OS: Windows 11 Pro 24H2
- Source Code: Benchmark code is available at Benchmark Source Code. We encourage review and custom testing.
Benchmark Result
StringBuilder_DapperDynamicParams | 206.5 ns | 1.38 KB |
DapperQbNet_NoParams | 2,700.3 ns | 7.47 KB |
DapperSqlBuilder_DapperDynamicParams | 1,333.3 ns | 5.12 KB |
InterpolatedSql_SpecificParams | 1,568.4 ns | 5.17 KB |
SqExpress_NoParams | 2,091.0 ns | 4.56 KB |
Sqlify_SpecificParams | 1,001.6 ns | 3.13 KB |
SqlKata_SpecificParams | 29,072.0 ns | 40.54 KB |
SqlArtisan_SpecificParams | 1,433.0 ns | 2.66 KB |
SqlArtisan_DapperDynamicParams | 1,568.5 ns | 3.23 KB |
Disclaimer
This benchmark highlights the memory efficiency of a specific internal operation within SqlArtisan by comparing it to fundamental string handling techniques. It is not intended as a direct, comprehensive performance benchmark against other SQL builder libraries, as each library has different design goals, features, and may perform optimally under different conditions or workloads.
Usage Examples
SqlArtisan allows you to construct a wide variety of SQL queries in a type-safe and intuitive manner. Below are examples demonstrating common SQL operations and how to achieve them with SqlArtisan.
SELECT Query
SELECT Clause
Column Aliases
UsersTable u = new();
SqlStatement sql =
Select(
u.Id.As("user_id"),
u.Name.As("user_name"))
.From(u)
.Build();
DISTINCT
UsersTable u = new();
SqlStatement sql =
Select(Distinct, u.Id)
.From(u)
.Build();
Hints
UsersTable u = new("u");
SqlStatement sql =
Select(Hints("/*+ INDEX(u users_ix) */"), u.Id)
.From(u)
.Build();
FROM Clause
FROM-less Queries
SqlStatement sql =
Select(CurrentTimestamp)
.Build();
Using DUAL (Oracle)
SqlStatement sql =
Select(Sysdate)
.From(Dual)
.Build();
WHERE Clause
Example
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(u.Id == 1)
.Build();
For a detailed guide on constructing various types of conditions (like Logical, Comparison, NULL
, Pattern Matching, BETWEEN
, IN
, EXISTS
), including how to use Dynamic Conditions (ConditionIf
), check out the Expressions: Conditions section.
JOIN Clause
Example using INNER JOIN
UsersTable u = new("u");
OrdersTable o = new("o");
SqlStatement sql =
Select(u.Name)
.From(u)
.InnerJoin(o)
.On(u.Id == o.UserId)
.Build();
Supported JOIN APIs
InnerJoin()
for INNER JOIN
LeftJoin()
for LEFT JOIN
RightJoin()
for RIGHT JOIN
FullJoin()
for FULL JOIN
CrossJoin()
for CROSS JOIN
ORDER BY Clause
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.OrderBy(
1,
u.Id,
u.Id.NullsFirst,
u.Id.NullsLast,
u.Id.Asc,
u.Id.Asc.NullsFirst,
u.Id.Asc.NullsLast,
u.Id.Desc,
u.Id.Desc.NullsFirst,
u.Id.Desc.NullsLast)
.Build();
GROUP BY and HAVING Clause
UsersTable u = new();
SqlStatement sql =
Select(
u.Id,
u.Name,
Count(u.Id))
.From(u)
.GroupBy(u.Id, u.Name)
.Having(Count(u.Id) > 1)
.Build();
Set Operators
Example using UNION
UsersTable u = new();
ArchivedUsersTable a = new();
SqlStatement sql =
Select(u.Id, u.Name)
.From(u)
.Union
.Select(a.Id, a.Name)
.From(a)
.Build();
Example using UNION ALL
UsersTable u = new();
ArchivedUsersTable a = new();
SqlStatement sql =
Select(u.Id, u.Name)
.From(u)
.UnionAll
.Select(a.Id, a.Name)
.From(a)
.Build();
Supported Set Operators APIs
Union
for UNION
UnionAll
for UNION ALL
Except
for EXCEPT
ExceptAll
for EXCEPT ALL
Minus
for MINUS
MinusAll
for MINUS ALL
Intersect
for INTERSECT
IntersectAll
for INTERSECT ALL
FOR UPDATE Clause
Basic Example
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(u.Id == 1)
.ForUpdate()
.Build();
Example with Options
UsersTable u = new("u");
OrdersTable o = new("o");
SqlStatement sql =
Select(u.Id, o.Id)
.From(u)
.InnerJoin(o)
.On(u.Id == o.UserId)
.Where(u.Id == 1)
.ForUpdate(Of(u.Id), Wait(5))
.Build();
Supported Options
Of()
for OF
Nowait
for NOWAIT
SkipLocked
for SKIP LOCKED
Wait()
for WAIT
DELETE Statement
UsersTable u = new();
SqlStatement sql =
DeleteFrom(u)
.Where(u.Id == 1)
.Build();
UPDATE Statement
UsersTable u = new();
SqlStatement sql =
Update(u)
.Set(
u.Name == "newName",
u.CreatedAt == CurrentTimestamp)
.Where(u.Id == 1)
.Build();
Note: SqlArtisan's Set()
method uses Column == Value
for SQL-like assignment, unlike standard C# ==
(comparison). In Where()
clauses, ==
is used for comparison as expected.
INSERT Statement
Standard Syntax
UsersTable u = new();
SqlStatement sql =
InsertInto(u, u.Id, u.Name, u.CreatedAt)
.Values(1, "newName", CurrentTimestamp)
.Build();
Alternative Syntax (SET-like)
SqlArtisan also offers an alternative INSERT
syntax, similar to UPDATE
's Set()
method, for clearer column-value pairing.
UsersTable u = new();
SqlStatement sql =
InsertInto(u)
.Set(
u.Id == 1,
u.Name == "newName",
u.CreatedAt == CurrentTimestamp)
.Build();
Note: Generates standard INSERT INTO ... (columns) VALUES (values)
SQL, not MySQL's INSERT ... SET ...
, for broad database compatibility.
INSERT SELECT Syntax
UsersTable u = new();
ArchivedUsersTable a = new();
SqlStatement sql =
InsertInto(a, a.Id, a.Name, a.CreatedAt)
.Select(u.Id, u.Name, u.CreatedAt)
.From(u)
.Build();
WITH Clause (Common Table Expressions)
- Define your CTE Schema Class
internal sealed class SeniorUsersCte : CteSchemaBase
{
public SeniorUsersCte(string name) : base(name)
{
SeniorId = new DbColumn(name, "senior_id");
SeniorName = new DbColumn(name, "senior_name");
SeniorAge = new DbColumn(name, "senior_age");
}
public DbColumn SeniorId { get; }
public DbColumn SeniorName { get; }
public DbColumn SeniorAge { get; }
}
UsersTable users = new("users");
SeniorUsersCte seniors = new("seniors");
OrdersTable orders = new("orders");
SqlStatement sql =
With(
seniors.As(
Select(
users.Id.As(seniors.SeniorId),
users.Name.As(seniors.SeniorName),
users.Age.As(seniors.SeniorAge))
.From(users)
.Where(users.Age > 40)))
.Select(
orders.Id,
orders.OrderDate,
seniors.SeniorId,
seniors.SeniorName,
seniors.SeniorAge
)
.From(orders)
.InnerJoin(seniors)
.On(orders.UserId == seniors.SeniorId)
.Build();
SqlArtisan also supports more advanced WITH clause scenarios, including:
- Recursive CTEs using the
WithRecursive()
method.
- CTEs with DML statements (
INSERT
, UPDATE
, and DELETE
).
Expressions
NULL Literal
SqlStatement sql =
Select(
Null,
Null.As("NoValue"))
.Build();
Arithmetic Operators
UsersTable u = new();
SqlStatement sql =
Select(
u.Age + 1,
u.Age - 2,
u.Age * 3,
u.Age / 4,
u.Age % 5)
.From(u)
.Build();
Conditions
Logical Condition
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
(u.Id == 1 & u.Id == 2)
| (u.Id == 3 & Not(u.Id == 4)))
.Build();
Note: SqlArtisan's logical conditions use &
for SQL AND
and |
for SQL OR
, unlike their standard C# meanings (bitwise or non-short-circuiting logic).
Comparison Condition
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Id == 1
& u.Id != 2
& u.Id > 3
& u.Id >= 4
& u.Id < 5
& u.Id <= 6)
.Build();
NULL Condition
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(u.Id.IsNull
| u.Id.IsNotNull)
.Build();
Pattern Matching Condition
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Name.Like("%a")
| u.Name.NotLike("%b")
| RegexpLike(u.Name, "^.*c$"))
.Build();
BETWEEN Condition
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Id.Between(1, 2)
| u.Id.NotBetween(3, 4))
.Build();
IN Condition
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Id.In(1, 2, 3)
| u.Id.NotIn(4, 5, 6))
.Build();
IN Condition with Subquery
UsersTable a = new("a");
UsersTable b = new("b");
UsersTable c = new("c");
SqlStatement sql =
Select(a.Name)
.From(a)
.Where(
a.Id.In(Select(b.Id).From(b))
| a.Id.NotIn(Select(c.Id).From(c)))
.Build();
EXISTS Condition
UsersTable a = new("a");
UsersTable b = new("b");
UsersTable c = new("c");
SqlStatement sql =
Select(a.Name)
.From(a)
.Where(
Exists(Select(b.Id).From(b))
& NotExists(Select(c.Id).From(c)))
.Build();
Dynamic Condition
SqlArtisan allows you to dynamically include or exclude conditions using a helper like ConditionIf
. This is useful when parts of your WHERE
clause depend on runtime logic.
Case 1: Condition is Included
bool filterUnderTen = true;
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Id > 0
& ConditionIf(filterUnderTen, u.Id < 10))
.Build();
Case 2: Condition is Excluded
bool filterUnderTen = false;
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Id > 0
& ConditionIf(filterUnderTen, u.Id < 10))
.Build();
CASE Expressions
Simple CASE Expression
UsersTable u = new();
SqlStatement sql =
Select(
u.Id,
u.Name,
Case(
u.StatusId,
When(1).Then("Active"),
When(2).Then("Inactive"),
When(3).Then("Pending"),
Else("Unknown"))
.As("StatusDescription"))
.From(u)
.Build();
Searched CASE Expression
UsersTable u = new();
SqlStatement sql =
Select(
u.Id,
u.Name,
Case(
When(u.Age < 18).Then("Minor"),
When(u.Age >= 18 & u.Age < 65).Then("Adult"),
Else("Senior"))
.As("AgeGroup"))
.From(u)
.Build();
Window Functions
Example using ROW_NUMBER
UsersTable u = new();
SqlStatement sql =
Select(
u.Id,
u.Name,
u.DepartmentId,
RowNumber().Over(
PartitionBy(u.DepartmentId)
.OrderBy(u.Salary.Desc)))
.From(u)
.Build();
For a comprehensive list of all available window functions, please refer to the Additional Query Details: Window Functions section.
Sequence
Oracle Example
SqlStatement sql =
Select(
Sequence("users_id_seq").Currval,
Sequence("users_id_seq").Nextval)
.Build();
PostgreSQL Example
SqlStatement sql =
Select(
Currval("users_id_seq"),
Nextval("users_id_seq"))
.Build();
SQL Server Example
SqlStatement sql =
Select(
NextValueFor("users_id_seq"))
.Build();
Additional Query Details
This section provides supplementary information on specific elements and features within SqlArtisan that offer fine-grained control and enhance query expressiveness. These details build upon the fundamental query structures covered in the Usage Examples section.
Bind Parameter Types
SqlArtisan automatically converts C# literal values into bind parameters. Supported types are as follows:
- Boolean:
bool
- Character/String:
char
, string
- Date/Time:
DateTime
, DateOnly
, TimeOnly
- Numeric:
sbyte
, byte
, short
, ushort
, int
, uint
, nint
, nuint
, long
, ulong
, float
, double
, decimal
, Complex
- Enum: Any
enum
type
Functions
SqlArtisan provides C# APIs that map to various SQL functions, enabling you to use them seamlessly within your queries. Here's a list of supported functions by category:
Numeric Functions
Abs()
for ABS
Mod()
for MOD
Trunc()
for TRUNC
(Numeric Overload)
Character Functions
Concat()
for CONCAT
Instr()
for INSTR
Lpad()
for LPAD
Ltrim()
for LTRIM
Length()
for LENGTH
Lengthb()
for LENGTHB
Lower()
for LOWER
Rpad()
for RPAD
Rtrim()
for RTRIM
RegexpCount()
for REGEXP_COUNT
RegexpReplace()
for REGEXP_REPLACE
RegexpSubstr()
for REGEXP_SUBSTR
Replace()
for REPLACE
Substr()
for SUBSTR
Substrb()
for SUBSTRB
Trim()
for TRIM
Upper()
for UPPER
Date and Time Functions
AddMonths()
for ADD_MONTHS
CurrentDate
for CURRENT_DATE
CurrentTime
for CURRENT_TIME
CurrentTimestamp
for CURRENT_TIMESTAMP
Datepart()
for DATEPART
Extract()
for EXTRACT
(Date/Time Overload)
LastDay()
for LAST_DAY
MonthsBetween()
for MONTHS_BETWEEN
Sysdate
for SYSDATE
Systimestamp
for SYSTIMESTAMP
Trunc()
for TRUNC
(Date/Time Overload)
Conversion Functions
Coalesce()
for COALESCE
Decode()
for DECODE
Nvl()
for NVL
ToChar()
for TO_CHAR
ToDate()
for TO_DATE
ToNumber()
for TO_NUMBER
ToTimestamp()
for TO_TIMESTAMP
Aggregate Functions
Avg()
for AVG
Count()
for COUNT
Max()
for MAX
Min()
for MIN
Sum()
for SUM
Window Functions
CumeDist()
for CUME_DIST()
DenseRank()
for DENSE_RANK()
PercentRank()
for PERCENT_RANK()
Rank()
for RANK()
RowNumber()
for ROW_NUMBER()
Contributing
We welcome your feedback, suggestions, and bug reports! Your contributions help make SqlArtisan better for everyone.
Your collaboration is greatly appreciated!
License
This project is licensed under the MIT License. See the LICENSE file for the full license text.