Ngb.SqlBuilder 0.9.1

SQL Builder

Generate a parameterized SQL query with fluent function in Dotnet.

Supported Database

• PostgreSQL

Example

// can be registered as singleton
var cache = new QueryCache(); // optional

// DI lifetime must be scoped or transient
var compiler = new PostgresCompiler();           
var factory = new QueryFactory(compiler, cache); // cache is optional; can be null

// simple example input object here, can be a proper class
var obj = new {
    Id = "1ace3f58-00ec-432b-b71e-2332da2ae8d8",
    Limit = 10,
    Offset = 0
};

var query = factory.NewQuery(
    obj, (q, s) => q
        .Select("c.PostId as Id", "c.Title", "a.Author")
        .From("Post as p")
        .Join("Author a", j => j.Using("AuthorId"))
        .Where("p.Id", s.Id)
        .Limit(s.Limit)
        .Offset(s.Offset)
);

IDbConnection connection;

// the input object "obj" is already integrated inside "query" 
var result = await connection.QueryAsync<Post>(query); 

Generated SQL

SELECT c."PostId" Id, c."Title", a."Author" 
FROM "Post" p JOIN "Author" a USING("AuthorId") 
WHERE p."Id"=@Id OFFSET @Offset LIMIT @Limit

*Line breaks were added for clarity purposes. Those line breaks are not present in the actual result.

Do not use literal or constant value. Instead, use properties of the injected object inside the lambda. The function will capture the expression instead of its value.

To use literal value, use Sql.Value(). Example:

.Select(Sql.Value(1), Sql.Value("1 hour", "interval"))

The statement above will be converted to:

SELECT 1, '1 hour'::interval

That said, only basic filtering was implemented to avoid SQL injection. So try to avoid putting direct input from the user request without any filtering.

See UseCases.cs in Tests for more examples.

Implemented Features

Select

• Select
  • Distinct
  • Column t.Column::cast as Alias → t."Column"::cast Alias
  • Aggregate (count,min,max, etc.)
  • Subquery
  • By model (Supports ColumnAttribute and NotMappedAttribute)
• From
  • Table "Table" as t
  • Subquery
  • Unnest with ordinality
• Join
  • Left, right
  • Method ON, USING, NATURAL, LATERAL
• Where
  • Operator: =,>=,<=,>,<,in,any,not,is null,is not null
  • Column vs Column
  • Column vs Value
  • Column vs Subquery
• Order by
  • Column ASC,DESC
• Pagination (LIMIT, OFFSET)

Insert

• Normal insert
  • Manual columns declaration
  • Columns by model (Supports ColumnAttribute and NotMappedAttribute)
• On conflict handling
• Returning

Update

• Normal update
  • Set by manual declaration
  • Set by model (Supports ColumnAttribute and NotMappedAttribute)
• From select (?)
• Returning

Delete

(Not implemented)

Limitation

Inputs other than value parameters value, such as tableName, column, etc. are not properly checked for possible SQL injection. Each parameter only goes through simple checks to prevent basic injection. So be careful with direct value.

The object Query itself is mutable. Cached queries does not work with dynamic queries.

Each action won't be checked for any invalid SQL syntax and some combination may generate an invalid SQL structure.