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.
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
DbParameterobjects, 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.
DISTINCT, HintsDUALINNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOINASC, DESC, NULLS FIRST/LASTUNION [ALL], EXCEPT [ALL], MINUS [ALL], INTERSECT [ALL]INSERT SELECTWITH, WITH RECURSIVE, CTEs with DML+, -, *, /, %NULL, LIKE, REGEXP_LIKE, BETWEEN, IN, EXISTS, Dynamic ConditionsCURRVAL, NEXTVAL, NEXT VALUE FORABS, MOD, TRUNCCONCAT, INSTR, LPAD, LTRIM, LENGTH, LENGTHB, LOWER, RPAD, RTRIM, REGEXP_COUNT, REGEXP_REPLACE, REGEXP_SUBSTR, REPLACE, SUBSTR, SUBSTRB, TRIM, UPPERADD_MONTHS, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT, LAST_DAY, MONTHS_BETWEEN, SYSDATE, SYSTIMESTAMP, TRUNCCOALESCE, DECODE, NVL, TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTAMPAVG, COUNT, MAX, MIN, SUMCUME_DIST, DENSE_RANK, PERCENT_RANK, RANK, ROW_NUMBERPlease see the CHANGELOG.md file for all notable changes.
| Package | Description | NuGet | Downloads |
|---|---|---|---|
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. |  |  |
WHERE conditions (and other query parts) using simple helpers like ConditionIf.SqlArtisan.Dapper library provides Dapper extensions that enable effortless SQL execution.Systimestamp for Oracle's SYSTIMESTAMP and CurrentTimestamp for PostgreSQL's CURRENT_TIMESTAMP. Developers should select the C# API appropriate for their target database.: or @) to suit the target DBMS. Currently, this behavior is verified for MySQL, Oracle, PostgreSQL, SQLite, and SQL Server.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.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
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: 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);
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();
// No args; defaults to Dbms.PostgreSql, uses ':' prefix
SqlStatement sql =
Select(u.Id, u.Name)
.From(u)
.Where(u.Id == 10 & u.Name == "Alice")
.Build();
// sql.Text is
// SELECT id, name
// FROM users
// WHERE (id = :0) AND (name = :1)
//
// sql.Parameters.Get<int>(":0") is 10
// sql.Parameters.Get<string>(":1") is "Alice"
Example (Specifying SQL Server):
UsersTable u = new();
// With Dbms.SqlServer; uses '@' prefix
SqlStatement sql =
Select(u.Id, u.Name)
.From(u)
.Where(u.Id == 20 & u.Name == "Bob")
.Build(Dbms.SqlServer);
// sql.Text is
// SELECT id, name
// FROM users
// WHERE (id = @0) AND (name = @1)
//
// sql.Parameters.Get<int>("@0") is 20
// sql.Parameters.Get<string>("@1") is "Bob"
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.
| Method | Mean | Allocated |
|---|---|---|
| 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 |
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.
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.
UsersTable u = new();
SqlStatement sql =
Select(
u.Id.As("user_id"),
u.Name.As("user_name"))
.From(u)
.Build();
// SELECT id "user_id",
// name "user_name"
// FROM users
UsersTable u = new();
SqlStatement sql =
Select(Distinct, u.Id)
.From(u)
.Build();
// SELECT DISTINCT id
// FROM users
// The hint below refers to this alias "u".
UsersTable u = new("u");
SqlStatement sql =
Select(Hints("/*+ INDEX(u users_ix) */"), u.Id)
.From(u)
.Build();
// SELECT /*+ INDEX(u users_ix) */ "u".id
// FROM users "u"
SqlStatement sql =
Select(CurrentTimestamp)
.Build();
// SELECT CURRENT_TIMESTAMP
SqlStatement sql =
Select(Sysdate)
.From(Dual)
.Build();
// SELECT SYSDATE FROM DUAL
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(u.Id == 1)
.Build();
// SELECT name
// FROM users
// WHERE id = :0
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.
UsersTable u = new("u");
OrdersTable o = new("o");
SqlStatement sql =
Select(u.Name)
.From(u)
.InnerJoin(o)
.On(u.Id == o.UserId)
.Build();
// SELECT "u".name
// FROM users "u"
// INNER JOIN orders "o"
// ON "u".id = "o".user_id
InnerJoin() for INNER JOINLeftJoin() for LEFT JOINRightJoin() for RIGHT JOINFullJoin() for FULL JOINCrossJoin() for CROSS JOINUsersTable 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();
// SELECT name
// FROM users
// ORDER BY
// 1,
// id,
// id NULLS FIRST,
// id NULLS LAST,
// id ASC,
// id ASC NULLS FIRST,
// id ASC NULLS LAST,
// id DESC,
// id DESC NULLS FIRST,
// id DESC NULLS LAST
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();
// SELECT id, name, COUNT(id)
// FROM users
// GROUP BY id, name
// HAVING COUNT(id) > :0
UsersTable u = new();
ArchivedUsersTable a = new();
SqlStatement sql =
Select(u.Id, u.Name)
.From(u)
.Union
.Select(a.Id, a.Name)
.From(a)
.Build();
// SELECT id, name
// FROM users
// UNION
// SELECT id, name
// FROM archived_users
UsersTable u = new();
ArchivedUsersTable a = new();
SqlStatement sql =
Select(u.Id, u.Name)
.From(u)
.UnionAll
.Select(a.Id, a.Name)
.From(a)
.Build();
// SELECT id, name
// FROM users
// UNION ALL
// SELECT id, name
// FROM archived_users
Union for UNIONUnionAll for UNION ALLExcept for EXCEPTExceptAll for EXCEPT ALLMinus for MINUSMinusAll for MINUS ALLIntersect for INTERSECTIntersectAll for INTERSECT ALLUsersTable u = new();
SqlStatement sql =
DeleteFrom(u)
.Where(u.Id == 1)
.Build();
// DELETE FROM users
// WHERE id = :0
UsersTable u = new();
SqlStatement sql =
Update(u)
.Set(
u.Name == "newName",
u.CreatedAt == Sysdate)
.Where(u.Id == 1)
.Build();
// UPDATE users
// SET name = :0,
// created_at = SYSDATE
// WHERE id = :1
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.
UsersTable u = new();
SqlStatement sql =
InsertInto(u, u.Id, u.Name, u.CreatedAt)
.Values(1, "newName", Sysdate)
.Build();
// INSERT INTO users
// (id, name, created_at)
// VALUES
// (:0, :1, SYSDATE)
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 == Sysdate)
.Build();
// INSERT INTO users
// (id, name, created_at)
// VALUES
// (:0, :1, SYSDATE)
Note: Generates standard INSERT INTO ... (columns) VALUES (values) SQL, not MySQL's INSERT ... SET ..., for broad database compatibility.
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();
// INSERT INTO archived_users
// (id, name, created_at)
// SELECT id, name, created_at
// FROM users
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();
// WITH seniors AS
// (SELECT "users".id "senior_id",
// "users".name "senior_name",
// "users".age "senior_age"
// FROM users "users" WHERE "users".age > :0)
// SELECT "orders".id,
// "orders".order_date,
// "seniors".senior_id,
// "seniors".senior_name,
// "seniors".senior_age
// FROM orders "orders"
// INNER JOIN seniors
// ON "orders".user_id = "seniors".senior_id
SqlArtisan also supports more advanced WITH clause scenarios, including:
WithRecursive() method.INSERT, UPDATE, and DELETE).SqlStatement sql =
Select(
Null,
Null.As("NoValue"))
.Build();
// SELECT
// NULL,
// NULL "NoValue"
UsersTable u = new();
SqlStatement sql =
Select(
u.Age + 1,
u.Age - 2,
u.Age * 3,
u.Age / 4,
u.Age % 5)
.From(u)
.Build();
// SELECT
// (age + :0),
// (age - :1),
// (age * :2),
// (age / :3),
// (age % :4)
// FROM users
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();
// SELECT name
// FROM users
// WHERE ((id = :0) AND (id = :1))
// OR ((id = :2) AND (NOT (id = :3)))
Note: SqlArtisan's logical conditions use & for SQL AND and | for SQL OR, unlike their standard C# meanings (bitwise or non-short-circuiting logic).
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();
// SELECT name
// FROM users
// WHERE (id = :0)
// AND (id <> :1)
// AND (id > :2)
// AND (id >= :3)
// AND (id < :4)
// AND (id <= :5)
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(u.Id.IsNull
| u.Id.IsNotNull)
.Build();
// SELECT name
// FROM users
// WHERE (id IS NULL)
// OR (id IS NOT NULL)
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Name.Like("%a")
| u.Name.NotLike("%b")
| RegexpLike(u.Name, "^.*c$"))
.Build();
// SELECT name
// FROM users
// WHERE (name LIKE :0)
// OR (name NOT LIKE :1)
// OR (REGEXP_LIKE(name, :2))
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Id.Between(1, 2)
| u.Id.NotBetween(3, 4))
.Build();
// SELECT name
// FROM users
// WHERE (id BETWEEN :0 AND :1)
// OR (id NOT BETWEEN :2 AND :3)
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Id.In(1, 2, 3)
| u.Id.NotIn(4, 5, 6))
.Build();
// SELECT name
// FROM users
// WHERE (id IN (:0, :1, :2))
// OR (id NOT IN (:3, :4, :5))
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();
// SELECT "a".name
// FROM users "a"
// WHERE (EXISTS (SELECT "b".id FROM users "b"))
// AND (NOT EXISTS (SELECT "c".id FROM users "c"))
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.
bool filterUnderTen = true;
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Id > 0
& ConditionIf(filterUnderTen, u.Id < 10))
.Build();
// SELECT name
// FROM users
// WHERE (id > :0)
// AND (id < :1)
bool filterUnderTen = false;
UsersTable u = new();
SqlStatement sql =
Select(u.Name)
.From(u)
.Where(
u.Id > 0
& ConditionIf(filterUnderTen, u.Id < 10))
.Build();
// SELECT name
// FROM users
// WHERE (id > :0)
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();
// SELECT id, name,
// CASE status_id
// WHEN :0 THEN :1
// WHEN :2 THEN :3
// WHEN :4 THEN :5
// ELSE :6
// END "StatusDescription"
// FROM users
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();
// SELECT id, name,
// CASE
// WHEN (age < :0) THEN :1
// WHEN ((age >= :2) AND (age < :3)) THEN :4
// ELSE :5
// END "AgeGroup"
// FROM users
UsersTable u = new();
SqlStatement sql =
Select(
u.Id,
u.Name,
u.DepartmentId,
RowNumber().Over(
PartitionBy(u.DepartmentId)
.OrderBy(u.Salary.Desc)))
.From(u)
.Build();
// SELECT id, name, department_id,
// ROW_NUMBER() OVER (
// PARTITION BY department_id
// ORDER BY salary DESC)
// FROM users
For a comprehensive list of all available window functions, please refer to the Additional Query Details: Window Functions section.
SqlStatement sql =
Select(
Sequence("users_id_seq").Currval,
Sequence("users_id_seq").Nextval)
.Build();
// SELECT
// users_id_seq.CURRVAL,
// users_id_seq.NEXTVAL
SqlStatement sql =
Select(
Currval("users_id_seq"),
Nextval("users_id_seq"))
.Build();
// SELECT
// CURRVAL('users_id_seq'),
// NEXTVAL('users_id_seq')
SqlStatement sql =
Select(
NextValueFor("users_id_seq"))
.Build();
// SELECT
// NEXT VALUE FOR users_id_seq
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.
SqlArtisan automatically converts C# literal values into bind parameters. Supported types are as follows:
boolchar, stringDateTime, DateOnly, TimeOnlysbyte, byte, short, ushort, int, uint, nint, nuint, long, ulong, float, double, decimal, Complexenum typeSqlArtisan 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:
Abs() for ABSMod() for MODTrunc() for TRUNC (Numeric Overload)Concat() for CONCATInstr() for INSTRLpad() for LPADLtrim() for LTRIMLength() for LENGTHLengthb() for LENGTHBLower() for LOWERRpad() for RPADRtrim() for RTRIMRegexpCount() for REGEXP_COUNTRegexpReplace() for REGEXP_REPLACERegexpSubstr() for REGEXP_SUBSTRReplace() for REPLACESubstr() for SUBSTRSubstrb() for SUBSTRBTrim() for TRIMUpper() for UPPERAddMonths() for ADD_MONTHSCurrentDate for CURRENT_DATECurrentTime for CURRENT_TIMECurrentTimestamp for CURRENT_TIMESTAMPDatepart() for DATEPARTExtract() for EXTRACT (Date/Time Overload)LastDay() for LAST_DAYMonthsBetween() for MONTHS_BETWEENSysdate for SYSDATESystimestamp for SYSTIMESTAMPTrunc() for TRUNC (Date/Time Overload)Coalesce() for COALESCEDecode() for DECODENvl() for NVLToChar() for TO_CHARToDate() for TO_DATEToNumber() for TO_NUMBERToTimestamp() for TO_TIMESTAMPAvg() for AVGCount() for COUNTMax() for MAXMin() for MINSum() for SUMCumeDist() for CUME_DIST()DenseRank() for DENSE_RANK()PercentRank() for PERCENT_RANK()Rank() for RANK()RowNumber() for ROW_NUMBER()We welcome your feedback, suggestions, and bug reports! Your contributions help make SqlArtisan better for everyone.
Your collaboration is greatly appreciated!
This project is licensed under the MIT License. See the LICENSE file for the full license text.
FAQs
Unknown package
We found that sqlartisan demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
The EU Cyber Resilience Act is prompting compliance requests that open source maintainers may not be obligated or equipped to handle.
Security News
Crates.io adds Trusted Publishing support, enabling secure GitHub Actions-based crate releases without long-lived API tokens.
Research
/Security News
Undocumented protestware found in 28 npm packages disrupts UI for Russian-language users visiting Russian and Belarusian domains.