SqlKata Query Builder

Follow and Upvote SqlKata on Product Hunt to encourage the development of this project

SqlKata Query Builder is a powerful Sql Query Builder written in C#.

It's secure and framework agnostic. Inspired by the top Query Builders available, like Laravel Query Builder, and Knex.

SqlKata has an expressive API. it follows a clean naming convention, which is very similar to the SQL syntax.

By providing a level of abstraction over the supported database engines, that allows you to work with multiple databases with the same unified API.

SqlKata supports complex queries, such as nested conditions, selection from SubQuery, filtering over SubQueries, Conditional Statements and others. Currently it has built-in compilers for SqlServer, MySql, PostgreSql and Firebird.

The SqlKata.Execution package provides the ability to submit the queries to the database, using Dapper under the covers.

Checkout the full documentation on https://sqlkata.com

Installation

$ dotnet add package SqlKata
$ dotnet add package SqlKata.Execution # (optional) If you want the execution support

Quick Examples

Setup Connection

varconnection=newSqlConnection("...");varcompiler=newSqlCompiler();vardb=newQueryFactory(connection,compiler)

QueryFactory is provided by the SqlKata.Execution package.

Retrieve all records

varbooks=db.Query("Books").Get();

Retrieve published books only

varbooks=db.Query("Books").WhereTrue("IsPublished").Get();

Retrieve one book

varintroToSql=db.Query("Books").Where("Id",145).Where("Lang","en").First();

Retrieve recent books: last 10

varrecent=db.Query("Books").OrderByDesc("PublishedAt").Limit(10).Get();

Include Author information

varbooks=db.Query("Books").Include(db.Query("Authors"))// Assumes that the Books table have a `AuthorId` column.Get();

This will include the property "Author" on each "Book"

[{
"Id": 1,
"PublishedAt": "2019-01-01",
"AuthorId": 2,
"Author": { // <-- included property"Id": 2,
"...": ""
}
}]

Join with authors table

varbooks=db.Query("Books").Join("Authors","Authors.Id","Books.AuthorId").Select("Books.*","Authors.Name as AuthorName").Get();foreach(varbookinbooks){Console.WriteLine($"{book.Title}: {book.AuthorName}");}

Conditional queries

varisFriday=DateTime.Today.DayOfWeek==DayOfWeek.Friday;varbooks=db.Query("Books").When(isFriday, q =>q.WhereIn("Category",new[]{"OpenSource","MachineLearning"})).Get();

Pagination

varpage1=db.Query("Books").Paginate(10);foreach(varbookinpage1.List){Console.WriteLine(book.Name);}
...var page2 =page1.Next();

Insert

intaffected=db.Query("Users").Insert(new{Name="Jane",CountryId=1});

Update

intaffected=db.Query("Users").Where("Id",1).Update(new{Name="Jane",CountryId=1});

Delete

intaffected=db.Query("Users").Where("Id",1).Delete();

FAQ

How to know when a new release or a feature is available?

I announce updates on My Twitter Account, and you can subscribe to our news letters from the website https://sqlkata.com

The database that I want is not supported why?

Usually it's impossible to support all available database vendors, this why we focus on the major ones, and we are encourage you to create your own compiler for your database

Do you accept new compilers?

Unfortunetly no, the reason is this will add overhead for the project contributors, we prefer to improve the quality of the existing compilers instead

How can I support the project?

• Star the project here in Github, and share it with your friends
• Follow and upvote it on Product Hunt
• You can also donate to support the project financily on open collection.