Sqlify 0.3.14

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