Sqlify

Fluent SQL builder library.

Just start build SQL query from Sql or PgSql classes.
Use Sqlify.Dapper library allowing Sqlify and Dapper to be used together.

Features:

SELECT, DELETE, INSERT, UPDATE queries
WHERE, JOIN, ORDER BY, GROUP BY, HAVING BY clauses
LIKE, EXISTS, IN, BETWEEN predicates
COUNT, SUM, MAX, MIN, AVG, CAST, COALESCE, NULLIF functions
UNION queries
Multiple queries
Table and Column aliases
SQL injections free
Partial PostgreSQL dialect support
Strongly typed (checked at compile time)
GC friendly

Getting started

// Create model for table Books with columns: Id, AuthorId, Rating
public interface IBooks : ITable
{
    public Column<int> Id { get; }
    public Column<int> AuthorId { get; }
    public Column<double> Rating { get; }
}

// Create model for table Authors with columns: Id, Name
public interface IAuthors : ITable
{
    public Column<int> Id { get; }
    public Column<int> Name { get; }
}

var a = Sql.Table<IAuthors>("a");
var b = Sql.Table<IBooks>("b");

var selectQuery = Sql
    .Select(a.Id, Sql.Count().As("Count"))
    .From(a)
    .Join(b, b.AuthorId == a.Id)
    .Where(b.Rating > 2.0)
    .Having(Sql.Count() >= 3)
    .OrderByDesc(Sql.Count());

// SELECT a.Id, COUNT(*) AS Count
// FROM Authors a 
// JOIN Books b ON b.AuthorId = a.Id 
// WHERE b.Rating > @p1 
// HAVING COUNT(*) >= @p2 
// ORDER BY COUNT(*) DESC

Examples

Schema definition
SELECT query
- Aliases
- Functions
- Computed column
- DISTINCT
- WHERE clause
  - Predicates (AND, OR, IS NULL and others)
  - LIKE predicate
  - IN predicate
  - EXISTS predicate
  - BETWEEN predicate
- JOIN ON clause
- ORDER BY clause
- GROUP BY clause
- HAVING clause
- Multiple queries
DELETE query
INSERT query
UPDATE query
PostreSQL dialect
- OFFSET and LIMIT clauses
- UPDATE RETURNING clause
- INSERT RETURNING clause
- DELETE RETURNING clause
- INSERT ON CONFLICT DO clause
- SELECT FOR clause

Schema definition

As an example, consider the following database schema (authors and books tables with one-to-many relationship):

CREATE TABLE authors (
    id integer PRIMARY KEY,
    name varchar(64)
)

CREATE TABLE books (
    id integer PRIMARY KEY,
    name varchar(512),
    author_id integer REFERENCES authors (id), -- one-to-many relationship
    rating real,
    qty integer
)

For these tables create corresponding interfaces:

[Table("authors")]
public interface IAuthorsTable : ITable
{
    [Column("id")]
    Column<int> Id { get; }

    [Column("name")]
    Column<string> Name { get; }
}

[Table("books")]
public interface IBooksTable : ITable
{
    [Column("id")]
    Column<int> Id { get; }

    [Column("name")]
    Column<string> Name { get; }

    [Column("author_id")]
    Column<int> AuthorId { get; }

    [Column("rating")]
    Column<double> Rating { get; }

    [Column("qty")]
    Column<int> Quantity { get; }
}

If the names of the columns in the database are the same as the names of the properties in models, then using TableAttribute and ColumnAttrubute are optional.

For example, for schema:

CREATE TABLE Authors (
    Id integer PRIMARY KEY,
    Name varchar(64),
    BooksCount integer
)

you can define table like:

public interface IAuthorsTable : ITable
{
    Column<int> Id { get; }

    Column<int> Name { get; }

    Column<int> BooksCount { get; }
}

up ↑

Aliases

var b = Sql.Table<IBooksTable>();
var query = Sql
    .Select(b.Id, b.Name)
    .From(b);

// SELECT books.id, books.name FROM books

var b = Sql.Table<IBooksTable>("t"); // table alias
var query = Sql
    .Select(b.Id, b.Name)
    .From(b);

// SELECT t.id, t.name FROM books t

var b = Sql.Table<IBooksTable>("t");
var query = Sql
    .Select(b.Id, b.Name.As("author_name")) // column alias
    .From(b);

// SELECT t.id, t.name AS author_name FROM books t

up ↑

Functions

var b = Sql.Table<IBooksTable>();
var query = Sql
    .Select(Sql.Count())
    .From(b);

// SELECT COUNT(*) FROM books

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select(Sql.Avg(b.Rating))
    .From(b);

// SELECT AVG(b.rating) FROM books b

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select(b.Rating.Cast<int>("INTEGER"))
    .From(b);

// SELECT CAST(b.rating AS INTEGER) FROM books b

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select(Sql.Coalesce(b.Name, "n/a"))
    .From(b);

// SELECT COALESCE(b.name, @p1) FROM books b

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select(Sql.NullIf(b.Name, "n/a"))
    .From(b);

// SELECT NULLIF(b.name, @p1) FROM books b

up ↑

Computed column

Define a table orders with computed column total = qty * price:

[Table("orders")]
public interface IOrder : ITable
{
    [Column("id")]
    Column<int> Id { get; }

    [Column("qty")]
    Column<int> Qty { get; }

    [Column("price")]
    Column<int> Price { get; }

    [Column("total")]
    Expression<int> Total => Qty * Price; // <- computed column
}

var o = Sql.Table<IOrder>("o");

var query = Sql
    .Select(o.Id, o.Total)
    .From(o);

// SELECT o.id, o.qty * o.price AS total FROM orders o

up ↑

DISTINCT

var a = Sql.Table<IAuthorsTable>("a");
var query = Sql
    .Select(a.Name)
    .Distinct()
    .From(a);

// SELECT DISTINCT a.name FROM authors a

up ↑

Predicates

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select()
    .From(b)
    .Where(b.Name.IsNull.And(b.Rating <= 0));

// SELECT * FROM books b WHERE b.name IS NULL AND b.rating <= @p1

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select()
    .From(b)
    .Where(
        b.Name.IsNull, 
        b.Rating <= 0
    );

// SELECT * FROM books b WHERE b.name IS NULL AND b.rating <= @p1

up ↑

LIKE predicate

var a = Sql.Table<IAuthorsTable>("a");
var query = Sql
    .Select()
    .From(a)
    .Where(a.Name.Like("A%")); // started with 'A'

// SELECT * FROM authors a WHERE a.name LIKE @p1

up ↑

IN predicate

var a = Sql.Table<IAuthorsTable>("a");
var query = Sql
    .Select()
    .From(a)
    .Where(a.Id.In(new[] {1, 2})); // where id==1 OR id==2

// SELECT * FROM authors a WHERE a.id IN @p1

var a = Sql.Table<IAuthorsTable>("a");
var b = Sql.Table<IBooksTable>("b");

var subQuery = Sql
    .Select(b.AuthorId)
    .From(b)
    .Where(b.Rating > 3);

var query = Sql
    .Select()
    .From(a)
    .Where(a.Id.In(subQuery)); // IN sub-query
                   
// SELECT * FROM authors a WHERE a.id IN (SELECT b.author_id FROM books b WHERE b.rating > @p1)");

up ↑

EXISTS predicate

var a = Sql.Table<IAuthorsTable>("a");
var b = Sql.Table<IBooksTable>("b");

var subQuery = Sql
    .Select()
    .From(b)
    .Where((a.Id == b.AuthorId).And(b.Rating > 3));

var query = Sql
    .Select()
    .From(a)
    .WhereExists(subQuery);

// SELECT * FROM authors a WHERE EXISTS (SELECT * FROM books b WHERE a.id = b.author_id AND b.rating > @p1

up ↑

BETWEEN predicate

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select()
    .From(b)
    .Where(b.Rating.Between(2, 4));

// SELECT * FROM books b WHERE b.rating BETWEEN @p1 AND @p2

up ↑

JOIN ON clause

var a = Sql.Table<IAuthorsTable>("a");
var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select()
    .Join(b, a.Id == b.AuthorId) // also LEFT, RIGHT, FULL JOIN
    .From(a);

// SELECT * FROM authors a JOIN books b ON a.id = b.author_id

up ↑

ORDER BY clause

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select()
    .OrderByDesc(b.Rating)
    .From(b);

// SELECT * FROM books b ORDER BY b.rating DESC

up ↑

GROUP BY clause

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select(b.AuthorId, Sql.Count())
    .GroupBy(b.AuthorId)
    .From(b);

// SELECT b.author_id, COUNT(*) FROM books b GROUP BY b.author_id

up ↑

Multiple queries

var a = Sql.Table<IAuthorsTable>();
var b = Sql.Table<IBooksTable>();
MultipleQuery query = Sql
    .Multiple(
        Sql.Select().From(a),
        Sql.Select().From(b)
    );
    
// SELECT * FROM authors; SELECT * FROM books

up ↑

HAVING clause

var b = Sql.Table<IBooksTable>("b");
var query = Sql
    .Select(b.AuthorId, Sql.Count())
    .GroupBy(b.AuthorId)
    .Having(Sql.Count() > 3)
    .From(b);

// SELECT b.author_id, COUNT(*) FROM books b GROUP BY b.author_id HAVING COUNT(*) > @p1

up ↑

DELETE query

var b = Sql.Table<IBooksTable>();
var query = Sql
    .Delete(b)
    .Where(b.Id == 1);

// DELETE FROM books WHERE books.id = @p1

up ↑

INSERT query

var a = Sql.Table<IAuthorsTable>();
var query = Sql
    .Insert(a)
    .Values(a.Id, 1)
    .Values(a.Name, "Adam");
                
// INSERT INTO authors (id, name) VALUES (@p1, @p2)

up ↑

UPDATE query

var b = Sql.Table<IBooksTable>();
var query = Sql
    .Update(b)
    .Set(b.Rating, b.Rating + 1)
    .Where(b.AuthorId == 1);

// UPDATE books SET rating = books.rating + @p1 WHERE books.author_id = @p2

up ↑

PostgreSQL OFFSET and LIMIT clauses

var a = Sql.Table<IAuthorsTable>("a");
PgSelectQuery query = PgSql
    .Select()
    .From(a)
    .OrderBy(a.Name)
    .Offset(5)
    .Limit(10)

// SELECT * FROM authors a ORDER BY a.name OFFSET @p1 LIMIT @p2

up ↑

PostgreSQL UPDATE RETURNING clause

var b = Sql.Table<IBooksTable>();
PgUpdateQuery query = PgSql
    .Update(b)
    .Set(b.Rating, b.Rating + 1)
    .Returning(b.Id, b.Rating);

// UPDATE books SET rating = books.rating + @p1 RETURNING books.id, books.rating

up ↑

PostgreSQL INSERT RETURNING clause

var b = Sql.Table<IBooksTable>();
PgInsertQuery query = PgSql
    .Insert(b)
    .Values(b.Name, "name")
    .Returning();

// INSERT INTO books (name) VALUES (@p1) RETURNING *

up ↑

PostgreSQL DELETE RETURNING clause

var b = Sql.Table<IBooksTable>();
PgInsertQuery query = PgSql
    .Delete(b)
    .Returning();

// DELETE FROM books RETURNING *

up ↑

PostgreSQL INSERT ON CONFLICT DO clause

var b = Sql.Table<IBooksTable>("b");
PgInsertQuery query = PgSql
    .Insert(b)
    .Values(b.Id, 1)
    .Values(b.Name, "foo bar")
    .Values(b.Quantity, 5)
    .OnConflict(
        PgConflict.Columns(b.Name),
        PgConflict
            .DoUpdate()
            .Set(b.Quantity, b.Quantity + 5)
    );

// INSERT INTO books AS b (id, name, qty) VALUES (@p1, @p2, @p3) 
// ON CONFLICT (b.name) 
// DO UPDATE SET qty = b.qty + @p4"

up ↑

PostgreSQL SELECT FOR clause

var b = Sql.Table<IBooksTable>("b");
PgSelectQuery query = PgSql
    .Select()
    .From(b)
    .Where(b.Id == 3)
    .For(PgLockMode.Update); // mode: UPDATE, NO KEY UPDATE, SHARE, KEY SHARE

// SELECT * FROM books b WHERE b.id = @p1 FOR UPDATE

up ↑

How to build

# build
dotnet build ./src

# running tests
dotnet test ./src

# pack
dotnet pack ./src -c=release

Name		Name	Last commit message	Last commit date
Latest commit History 61 Commits
src		src
.editorconfig		.editorconfig
.gitignore		.gitignore
Directory.Build.props		Directory.Build.props
LICENSE		LICENSE
README.md		README.md
appveyor.yml		appveyor.yml

License

oakio/Sqlify

Folders and files

Latest commit

History

Repository files navigation

Sqlify

Features:

Getting started

Examples

Schema definition

Aliases

Functions

Computed column

DISTINCT

Predicates

LIKE predicate

IN predicate

EXISTS predicate

BETWEEN predicate

JOIN ON clause

ORDER BY clause

GROUP BY clause

Multiple queries

HAVING clause

DELETE query

INSERT query

UPDATE query

PostgreSQL OFFSET and LIMIT clauses

PostgreSQL UPDATE RETURNING clause

PostgreSQL INSERT RETURNING clause

PostgreSQL DELETE RETURNING clause

PostgreSQL INSERT ON CONFLICT DO clause

PostgreSQL SELECT FOR clause

How to build

About

Topics

Resources

License

Stars

Watchers

Forks

Languages