SQLCute β€” Usage Guide

🌍 Language: English | Español

Introduction

SQLCute builds parameterized SQL through a fluent method chain. The entry point is always TQuery.New:

uses Daf.SQLCute;

var Q := TQuery.New          // IQuery β€” ref-counted, no Free needed
  .From('products')
  .Where('active', True)
  .OrderBy('name')
  .Limit(50);

var R := TAnsiSqlCompiler.Create.Compile(Q);
// R.SQL      β†’ 'SELECT * FROM products WHERE active = ? ORDER BY name LIMIT 50'
// R.Bindings β†’ [True]

TSQLResult always contains:

Field Type Description
SQL string The compiled SQL string with ? placeholders (dialect compilers may use $1, :p1, @p1)
Bindings TArray<Variant> Positional values matching the placeholders

Pass R.SQL and R.Bindings to your database driver β€” SQLCute never executes queries itself.

Table of Contents

  1. SELECT & FROM β€” columns, DISTINCT, aggregates, ORDER BY, LIMIT/OFFSET
  2. WHERE β€” equality, comparisons, NULL, BETWEEN, IN, EXISTS, groups, columns
  3. JOINs β€” INNER, LEFT, RIGHT, CROSS, FULL OUTER, callbacks
  4. GROUP BY / HAVING β€” grouping and aggregate filters
  5. Set Operations β€” UNION, INTERSECT, EXCEPT, pagination
  6. DML β€” INSERT, UPDATE, DELETE
  7. Subqueries & CTEs β€” derived tables, WITH, recursive CTEs
  8. String Operations β€” LIKE, starts/ends/contains
  9. Date Operations β€” WhereDate, per-dialect date casting
  10. Dialect Compilers β€” quoting, placeholders, dialect matrix

The Compiler Pattern

Every IQuery is compiler-agnostic. You choose the dialect at compile time:

uses Daf.SQLCute.Compiler;             // TAnsiSqlCompiler (default)
uses Daf.SQLCute.Compiler.Postgres;    // TPostgresCompiler
uses Daf.SQLCute.Compiler.MySql;       // TMySqlCompiler
// … etc.

var R := TPostgresCompiler.Create.Compile(Q);

You can also call Q.Compile(MyCompiler) as a shorthand when you have a compiler instance.

Clone β€” Query Variants

Clone creates a deep copy, useful for building related queries from a shared base:

var Base := TQuery.New.From('orders').Where('year', 2024);

var Pending  := Base.Clone.Where('status', 'pending');
var Shipped  := Base.Clone.Where('status', 'shipped');

Null Safety

All Where* methods that take a Variant accept Null values. When the value is Null, the generated SQL uses IS NULL (or IS NOT NULL for negated variants).

.Where('deleted_at', Null)      // β†’ WHERE deleted_at IS NULL
.WhereNot('deleted_at', Null)   // β†’ WHERE deleted_at IS NOT NULL

See Also

SELECT & FROM

SELECT & FROM

🌍 Language: English | Español

← Back to Guide

From

Sets the source table. All queries start here.

TQuery.New.From('orders')
// β†’ SELECT * FROM orders

With alias:

TQuery.New.From('orders', 'o')
// β†’ SELECT * FROM orders o

Select β€” Column List

TQuery.New.From('users')
  .Select(['id', 'name', 'email'])
// β†’ SELECT id, name, email FROM users

Single column:

.Select('name')
// β†’ SELECT name FROM users

With alias:

.SelectAs('full_name', 'name')
// β†’ SELECT full_name AS name FROM users

Select * (default)

When no Select call is made, SELECT * is emitted:

TQuery.New.From('products')
// β†’ SELECT * FROM products

Distinct

TQuery.New.From('orders')
  .Select('customer_id')
  .Distinct
// β†’ SELECT DISTINCT customer_id FROM orders

SelectRaw

Emit a raw SQL expression in the SELECT list (no quoting or escaping applied):

TQuery.New.From('sales')
  .SelectRaw('YEAR(created_at) AS year')
  .SelectRaw('COUNT(*) AS total')
// β†’ SELECT YEAR(created_at) AS year, COUNT(*) AS total FROM sales

Aggregate Functions

Method Default alias SQL generated
SelectCount(col, alias) 'count' COUNT(col) AS alias
SelectSum(col, alias) 'sum' SUM(col) AS alias
SelectAvg(col, alias) 'avg' AVG(col) AS alias
SelectMin(col, alias) 'min' MIN(col) AS alias
SelectMax(col, alias) 'max' MAX(col) AS alias 
TQuery.New.From('orders')
  .SelectCount('*', 'total_orders')
  .SelectSum('amount', 'revenue')
  .SelectAvg('amount', 'avg_order')
  .GroupBy('status')
// β†’ SELECT COUNT(*) AS total_orders, SUM(amount) AS revenue, AVG(amount) AS avg_order
//   FROM orders GROUP BY status

Subquery as Column

var Sub := TQuery.New.From('order_items')
  .SelectSum('qty')
  .Where('order_id', '=', TQuery.New.From('...'));  // conceptual β€” use as scalar subquery

TQuery.New.From('orders')
  .Select(Sub, 'item_count')
// β†’ SELECT (SELECT SUM(qty) FROM order_items) AS item_count FROM orders

ORDER BY

.OrderBy('name')          // ASC
.OrderByDesc('created_at') // DESC
.OrderByRaw('FIELD(status, ''pending'', ''shipped'', ''closed'')')

Multiple ORDER BY: call OrderBy/OrderByDesc multiple times:

TQuery.New.From('products')
  .OrderBy('category')
  .OrderByDesc('price')
// β†’ SELECT * FROM products ORDER BY category ASC, price DESC

LIMIT and OFFSET

TQuery.New.From('logs').Limit(100)
// β†’ SELECT * FROM logs LIMIT 100

TQuery.New.From('logs').Limit(20).Offset(40)
// β†’ SELECT * FROM logs LIMIT 20 OFFSET 40

Take and Skip are aliases for Limit and Offset:

.Take(20).Skip(40)   // same as Limit(20).Offset(40)

For page-based pagination see Set Operations.

WHERE

WHERE

🌍 Language: English | Español

← Back to Guide

Basic Equality

.Where('status', 'active')
// β†’ WHERE status = ?   Bindings: ['active']

With an explicit operator:

.Where('price', '>', 100)
.Where('stock', '<=', 0)
// β†’ WHERE price > ? AND stock <= ?   Bindings: [100, 0]

OR connector:

.Where('status', 'pending')
.OrWhere('status', 'processing')
// β†’ WHERE status = ? OR status = ?

WhereNot

.WhereNot('role', 'guest')
// β†’ WHERE NOT (role = ?)

.OrWhereNot('status', 'closed')
// β†’ OR NOT (status = ?)

NULL Checks

.WhereNull('deleted_at')       // β†’ WHERE deleted_at IS NULL
.WhereNotNull('email')         // β†’ WHERE email IS NOT NULL
.OrWhereNull('archived_at')    // β†’ OR archived_at IS NULL

Passing Null directly to Where also generates IS NULL:

.Where('deleted_at', Null)     // β†’ WHERE deleted_at IS NULL

Boolean Shorthand

.WhereTrue('active')    // β†’ WHERE active = TRUE
.WhereFalse('blocked')  // β†’ WHERE blocked = FALSE

BETWEEN

.WhereBetween('price', 10, 100)
// β†’ WHERE price BETWEEN ? AND ?   Bindings: [10, 100]

.WhereNotBetween('age', 13, 17)
.OrWhereBetween('score', 90, 100)

WHERE IN

.WhereIn('status', ['pending', 'processing', 'shipped'])
// β†’ WHERE status IN (?, ?, ?)

.WhereNotIn('id', [1, 2, 3])
.OrWhereIn('category_id', [10, 20])
.OrWhereNotIn('tag', ['spam', 'bot'])

WHERE EXISTS (subquery)

var Sub := TQuery.New.From('order_items').Where('order_id', '=', 'orders.id');

TQuery.New.From('orders')
  .WhereExists(Sub)
// β†’ WHERE EXISTS (SELECT * FROM order_items WHERE order_id = ?)

WHERE IN (subquery)

var ActiveUsers := TQuery.New.From('users').Select('id').Where('active', True);

TQuery.New.From('orders')
  .WhereInQuery('customer_id', ActiveUsers)
// β†’ WHERE customer_id IN (SELECT id FROM users WHERE active = ?)

WhereRaw

Append a raw SQL fragment (AND connector). Use with caution β€” no escaping applied:

.WhereRaw('created_at > NOW() - INTERVAL ''7 days''')

Grouped WHERE (Callback)

Wrap conditions in parentheses using a callback:

TQuery.New.From('products')
  .Where('active', True)
  .Where(function(Q: IQuery): IQuery
    begin
      Result := Q.Where('stock', '>', 0)
                 .OrWhere('backorder_allowed', True);
    end)
// β†’ WHERE active = ? AND (stock > ? OR backorder_allowed = ?)

OR-connected group:

.OrWhere(function(Q: IQuery): IQuery
  begin
    Result := Q.Where('role', 'admin').OrWhere('role', 'moderator');
  end)
// β†’ OR (role = ? OR role = ?)

WhereColumns

Column-to-column comparison (no binding β€” values are emitted verbatim):

.WhereColumns('start_date', '<', 'end_date')
// β†’ WHERE start_date < end_date

.OrWhereColumns('price', '=', 'list_price')

⚠️ Only pass developer-controlled column names to WhereColumns. Never pass user input.

When β€” Conditional Chaining

Apply conditions only when a runtime flag is true:

var IncludeInactive := False;

TQuery.New.From('users')
  .When(not IncludeInactive, function(Q: IQuery): IQuery
    begin
      Result := Q.Where('active', True);
    end)
// β†’ WHERE active = ?   (only when IncludeInactive is False)

With an else branch:

.When(IsAdmin,
  function(Q: IQuery): IQuery begin Result := Q; end,        // no extra filter
  function(Q: IQuery): IQuery begin Result := Q.Where('tenant_id', TenantId); end)

String / LIKE Conditions

See String Operations for WhereLike, WhereStarts, WhereEnds, WhereContains.

Date Conditions

See Date Operations for WhereDate, WhereTime, WhereDatePart.

JOINs

JOINs

🌍 Language: English | Español

← Back to Guide

Basic Joins

All join methods follow the same signature: JoinType(table, col1, col2, op). The default operator is =.

INNER JOIN

TQuery.New.From('orders')
  .Join('customers', 'orders.customer_id', 'customers.id')
// β†’ SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id

LEFT JOIN

.LeftJoin('addresses', 'users.id', 'addresses.user_id')
// β†’ LEFT JOIN addresses ON users.id = addresses.user_id

RIGHT JOIN

.RightJoin('departments', 'employees.dept_id', 'departments.id')
// β†’ RIGHT JOIN departments ON employees.dept_id = departments.id

CROSS JOIN

.CrossJoin('sizes')
// β†’ CROSS JOIN sizes

FULL OUTER JOIN

.FullOuterJoin('b', 'a.id', 'b.a_id')
// β†’ FULL OUTER JOIN b ON a.id = b.a_id

Join with Raw Condition String

Pass a raw SQL ON clause when the condition cannot be expressed as column=column:

.Join('prices', 'prices.product_id = products.id AND prices.active = 1')
// β†’ INNER JOIN prices ON prices.product_id = products.id AND prices.active = 1

Join with Callback (Complex ON)

Use a callback for multiple conditions joined with AND/OR:

TQuery.New.From('orders')
  .Join('order_items', function(Q: IQuery): IQuery
    begin
      Result := Q
        .Where('order_items.order_id', '=', 'orders.id')
        .OrWhere('order_items.alt_order_id', '=', 'orders.id');
    end)
// β†’ INNER JOIN order_items ON (order_items.order_id = orders.id
//                               OR order_items.alt_order_id = orders.id)

LeftJoin, RightJoin, and FullOuterJoin all support the same callback overload.

Subquery Join

var Sub := TQuery.New.From('order_items')
  .Select('order_id')
  .SelectSum('qty', 'total_qty')
  .GroupBy('order_id');

TQuery.New.From('orders')
  .Join(Sub, 'oi', function(Q: IQuery): IQuery
    begin
      Result := Q.Where('oi.order_id', '=', 'orders.id');
    end)
// β†’ INNER JOIN (SELECT order_id, SUM(qty) AS total_qty
//               FROM order_items GROUP BY order_id) oi
//   ON oi.order_id = orders.id

Multiple Joins

Chain as many joins as needed β€” they are emitted in declaration order:

TQuery.New.From('invoices')
  .Join('customers', 'invoices.customer_id', 'customers.id')
  .LeftJoin('discounts', 'invoices.discount_id', 'discounts.id')
  .Select(['invoices.id', 'customers.name', 'discounts.pct'])

GROUP BY & Aggregates

GROUP BY / HAVING

🌍 Language: English | Español

← Back to Guide

GROUP BY

TQuery.New.From('sales')
  .SelectCount('*', 'qty')
  .GroupBy('product_id')
// β†’ SELECT COUNT(*) AS qty FROM sales GROUP BY product_id

Multiple columns β€” call GroupBy multiple times or pass an array:

.GroupBy(['year', 'month', 'category'])
// β†’ GROUP BY year, month, category

Raw expression:

.GroupByRaw('YEAR(created_at), MONTH(created_at)')
// β†’ GROUP BY YEAR(created_at), MONTH(created_at)

HAVING

Having filters groups after aggregation. The column argument accepts any aggregate expression.

TQuery.New.From('orders')
  .GroupBy('customer_id')
  .SelectCount('*', 'order_count')
  .SelectSum('total', 'revenue')
  .Having('count(*)', '>', 5)
  .Having('sum(total)', '>=', 1000)
// β†’ SELECT COUNT(*) AS order_count, SUM(total) AS revenue
//   FROM orders
//   GROUP BY customer_id
//   HAVING count(*) > ? AND sum(total) >= ?
//   Bindings: [5, 1000]

OR connector:

.Having('count(*)', '>', 10)
// not yet exposed as OrHaving β€” use HavingRaw for OR
.HavingRaw('sum(amount) > 500 OR count(*) > 100')

Raw HAVING expression:

.HavingRaw('AVG(response_time) < 200')

Combining with WHERE

WHERE filters rows before grouping; HAVING filters groups after:

TQuery.New.From('logs')
  .Where('level', 'error')          // applied before GROUP BY
  .GroupBy('service')
  .SelectCount('*', 'errors')
  .Having('count(*)', '>', 50)      // applied after GROUP BY
// β†’ SELECT COUNT(*) AS errors FROM logs
//   WHERE level = ?
//   GROUP BY service
//   HAVING count(*) > ?

Set Operations

Set Operations & Pagination

🌍 Language: English | Español

← Back to Guide

UNION

var Q1 := TQuery.New.From('active_users').Select(['id', 'name']);
var Q2 := TQuery.New.From('archived_users').Select(['id', 'name']);

Q1.Union(Q2)
// β†’ SELECT id, name FROM active_users
//   UNION
//   SELECT id, name FROM archived_users

UNION ALL (keeps duplicates):

Q1.UnionAll(Q2)
// β†’ … UNION ALL …

INTERSECT / EXCEPT

Q1.Intersect(Q2)
// β†’ … INTERSECT …

Q1.&Except(Q2)    // & prefix avoids conflict with Delphi keyword
// β†’ … EXCEPT …

Q1.IntersectAll(Q2)
Q1.ExceptAll(Q2)

CombineRaw

Append a raw set-operation string:

Q1.CombineRaw('MINUS SELECT id, name FROM deleted_users')
// β†’ … MINUS SELECT id, name FROM deleted_users

Pagination

Manual LIMIT / OFFSET

.Limit(20).Offset(40)
// β†’ LIMIT 20 OFFSET 40

// Aliases
.Take(20).Skip(40)

Typical pagination loop

const PageSize = 50;
var Page := 1;

repeat
  var R := Compiler.Compile(
    TQuery.New.From('orders').OrderBy('id').ForPage(Page, PageSize));
  // execute R, process rows…
  Inc(Page);
until RowCount < PageSize;

DML

DML β€” INSERT / UPDATE / DELETE

🌍 Language: English | Español

← Back to Guide

INSERT β€” Single Row

Pass parallel arrays of column names and values:

TQuery.New.From('users')
  .AsInsert(
    ['name',    'email',             'role'],
    ['Alice',   'alice@example.com', 'admin'])
// β†’ INSERT INTO users (name, email, role) VALUES (?, ?, ?)
//   Bindings: ['Alice', 'alice@example.com', 'admin']

INSERT β€” Multiple Rows

TQuery.New.From('tags')
  .AsInsertRows(
    ['name', 'color'],
    [['Urgent', 'red'],
     ['Normal', 'green'],
     ['Low',    'gray']])
// β†’ INSERT INTO tags (name, color) VALUES (?, ?), (?, ?), (?, ?)
//   Bindings: ['Urgent', 'red', 'Normal', 'green', 'Low', 'gray']

INSERT … SELECT

Copy rows from one table to another using a subquery:

var Sub := TQuery.New.From('temp_users')
  .Select(['name', 'email'])
  .Where('imported', True);

TQuery.New.From('users')
  .AsInsertFrom(['name', 'email'], Sub)
// β†’ INSERT INTO users (name, email)
//   SELECT name, email FROM temp_users WHERE imported = ?

UPDATE

TQuery.New.From('products')
  .Where('id', 42)
  .AsUpdate(
    ['price', 'stock'],
    [29.99,   100])
// β†’ UPDATE products SET price = ?, stock = ? WHERE id = ?
//   Bindings: [29.99, 100, 42]

⚠️ Without a Where clause, ALL rows are updated. Add a WHERE condition to scope the update.

DELETE

TQuery.New.From('sessions')
  .Where('expired', True)
  .AsDelete
// β†’ DELETE FROM sessions WHERE expired = ?

Soft-delete pattern using UPDATE:

TQuery.New.From('users')
  .Where('id', UserId)
  .AsUpdate(['deleted_at'], [Now])
// β†’ UPDATE users SET deleted_at = ? WHERE id = ?

Combining DML with Subqueries

// Delete orders older than 1 year whose items are all shipped
TQuery.New.From('orders')
  .Where('created_at', '<', EncodeDate(Year - 1, Month, Day))
  .WhereNotExists(
    TQuery.New.From('order_items')
      .Where('order_id', '=', 'orders.id')
      .WhereNot('status', 'shipped'))
  .AsDelete

Subqueries

Subqueries & CTEs

🌍 Language: English | Español

← Back to Guide

Derived Table (FROM subquery)

Use an IQuery as the FROM source with an alias:

var Inner := TQuery.New.From('order_items')
  .Select('order_id')
  .SelectSum('qty', 'total_qty')
  .GroupBy('order_id');

TQuery.New
  .From(Inner, 'oi')
  .Select(['oi.order_id', 'oi.total_qty'])
  .Where('oi.total_qty', '>', 10)
// β†’ SELECT oi.order_id, oi.total_qty
//   FROM (SELECT order_id, SUM(qty) AS total_qty
//         FROM order_items GROUP BY order_id) oi
//   WHERE oi.total_qty > ?

FROM Raw

Use a raw SQL expression as the FROM source (e.g. table-valued functions):

TQuery.New
  .FromRaw('generate_series(1, 100) AS s(n)', [])
  .Select('n')
// β†’ SELECT n FROM generate_series(1, 100) AS s(n)

With bindings:

.FromRaw('fn_get_events(?, ?) AS e', [StartDate, EndDate])

Subquery in WHERE IN

var RecentOrders := TQuery.New.From('orders')
  .Select('customer_id')
  .Where('created_at', '>', LastWeek);

TQuery.New.From('customers')
  .WhereInQuery('id', RecentOrders)
// β†’ SELECT * FROM customers
//   WHERE id IN (SELECT customer_id FROM orders WHERE created_at > ?)

Variants: WhereNotInQuery, OrWhereInQuery, OrWhereNotInQuery.

Subquery in WHERE EXISTS

TQuery.New.From('orders')
  .WhereExists(
    TQuery.New.From('payments')
      .Where('order_id', '=', 'orders.id')
      .Where('status', 'complete'))
// β†’ WHERE EXISTS (SELECT * FROM payments
//                WHERE order_id = orders.id AND status = ?)

Scalar Subquery in SELECT

var ItemCount := TQuery.New.From('order_items')
  .SelectCount('*')
  .Where('order_id', '=', 'orders.id');

TQuery.New.From('orders')
  .Select(ItemCount, 'item_count')
// β†’ SELECT (SELECT COUNT(*) FROM order_items WHERE order_id = orders.id)
//          AS item_count FROM orders

WITH (CTE)

var RecentSales := TQuery.New.From('sales')
  .Where('year', 2024)
  .Select(['product_id', 'amount']);

TQuery.New
  .&With('recent', RecentSales)
  .From('recent')
  .SelectSum('amount', 'total')
// β†’ WITH recent AS (SELECT product_id, amount FROM sales WHERE year = ?)
//   SELECT SUM(amount) AS total FROM recent

RECURSIVE CTE

var Anchor := TQuery.New.From('categories')
  .Where('parent_id', Null);
var Recursive := TQuery.New.From('categories')
  .Join('tree', 'categories.parent_id', 'tree.id');

TQuery.New
  .WithRecursive('tree', Anchor.Union(Recursive))
  .From('tree')
// β†’ WITH RECURSIVE tree AS (
//     SELECT * FROM categories WHERE parent_id IS NULL
//     UNION
//     SELECT categories.* FROM categories INNER JOIN tree ON categories.parent_id = tree.id
//   ) SELECT * FROM tree

WITH Raw

TQuery.New
  .WithRaw('cte_name', 'SELECT id FROM legacy_table WHERE flag = ?', [1])
  .From('cte_name')

Nesting Depth

SQLCute supports arbitrary nesting β€” subqueries can themselves contain subqueries. Bindings are flattened into a single array in the order they appear in the compiled SQL.

String Operations

String Operations

🌍 Language: English | Español

← Back to Guide

Overview

All string-match methods wrap the value in LIKE patterns. By default (CaseSensitive = False) SQLCute wraps both column and value in LOWER(…) to achieve case-insensitive matching. Pass True as the last argument to skip the LOWER wrapping.

WhereLike β€” Raw Pattern

Supply the full LIKE pattern yourself:

.WhereLike('name', '%john%')
// Case-insensitive (default):
// β†’ WHERE LOWER(name) LIKE lower('%john%')

.WhereLike('code', 'ABC-%', True)
// Case-sensitive:
// β†’ WHERE code LIKE 'ABC-%'

Variants: WhereNotLike, OrWhereLike, OrWhereNotLike.

WhereStarts β€” Prefix Match

.WhereStarts('email', 'admin')
// β†’ WHERE LOWER(email) LIKE lower('admin%')

Variants: WhereNotStarts, OrWhereStarts, OrWhereNotStarts.

WhereEnds β€” Suffix Match

.WhereEnds('filename', '.pdf')
// β†’ WHERE LOWER(filename) LIKE lower('%.pdf')

Variants: WhereNotEnds, OrWhereEnds, OrWhereNotEnds.

WhereContains β€” Substring Match

.WhereContains('description', 'urgent')
// β†’ WHERE LOWER(description) LIKE lower('%urgent%')

Variants: WhereNotContains, OrWhereContains, OrWhereNotContains.

Combining String Filters

TQuery.New.From('products')
  .WhereContains('name', 'coffee')
  .OrWhereContains('tags', 'coffee')
  .WhereNotStarts('sku', 'DISC-')
// β†’ WHERE (LOWER(name) LIKE lower('%coffee%')
//          OR LOWER(tags) LIKE lower('%coffee%'))
//     AND NOT (LOWER(sku) LIKE lower('DISC-%'))

Dialect Notes on Case Sensitivity

The LOWER-wrapping approach works reliably across all SQL dialects, but each database has its own native LIKE behavior:

Dialect LIKE case-sensitive? Notes
ANSI / ANSI SQL Yes Standard behavior
PostgreSQL Yes Use ILIKE natively; SQLCute uses LOWER(…) instead
MySQL No (by default) LOWER wrapping is harmless
SQLite No for ASCII Unicode chars are case-sensitive unless ICU extension enabled
SQL Server Depends on collation Latin1_General_CI_AS β†’ case-insensitive
Oracle Yes LOWER wrapping recommended
Firebird Yes LOWER wrapping recommended

When CaseSensitive = True, no LOWER wrapping is emitted regardless of dialect.

Date Operations

Date Operations

🌍 Language: English | Español

← Back to Guide

WhereDate β€” Exact Date Match

Filters rows where the DATE part of a datetime column equals a value, ignoring the time component. The cast expression is dialect-specific (see table below).

TQuery.New.From('orders')
  .WhereDate('created_at', '2024-06-15')
// ANSI: WHERE CAST(created_at AS DATE) = ?
// Bindings: ['2024-06-15']

OR connector:

.OrWhereDate('updated_at', '2024-06-15')

WhereTime β€” Time Comparison

TQuery.New.From('appointments')
  .WhereTime('start_time', '>=', '09:00:00')
// ANSI: WHERE CAST(start_time AS TIME) >= ?

OR variant: OrWhereTime.

WhereDatePart β€” Extract a Part

Filter by year, month, day, hour, or minute:

uses Daf.SQLCute.Clauses;  // TDatePart

TQuery.New.From('events')
  .WhereDatePart(TDatePart.dpYear,  'event_date', 2024)
  .WhereDatePart(TDatePart.dpMonth, 'event_date', 12)
// ANSI: WHERE EXTRACT(year FROM event_date) = ? AND EXTRACT(month FROM event_date) = ?

Available TDatePart values: dpDate, dpTime, dpYear, dpMonth, dpDay, dpHour, dpMinute.

OR variant: OrWhereDatePart.

Dialect Date-Cast Matrix

Each dialect compiler overrides the cast expression for WhereDate:

Dialect WhereDate('col', val) generates
ANSI (default) CAST(col AS DATE) = ?
PostgreSQL col::date = ?
MySQL DATE(col) = ?
SQLite date(col) = ?
SQL Server CAST(col AS date) = ?
Oracle TRUNC(col) = ?
Firebird CAST(col AS DATE) = ?

PostgreSQL example

uses Daf.SQLCute.Compiler.Postgres;

var R := TPostgresCompiler.Create.Compile(
  TQuery.New.From('t').WhereDate('d', '2024-01-01'));
// β†’ WHERE "d"::date = $1

MySQL example

uses Daf.SQLCute.Compiler.MySql;

var R := TMySqlCompiler.Create.Compile(
  TQuery.New.From('t').WhereDate('d', '2024-01-01'));
// β†’ WHERE DATE(`d`) = ?

Dialect DatePart Matrix

Dialect EXTRACT year EXTRACT month EXTRACT day EXTRACT hour
ANSI EXTRACT(year FROM col) EXTRACT(month FROM col) EXTRACT(day FROM col) EXTRACT(hour FROM col)
PostgreSQL same as ANSI same same same
MySQL YEAR(col) MONTH(col) DAY(col) HOUR(col)
SQLite strftime('%Y', col) strftime('%m', col) strftime('%d', col) strftime('%H', col)
SQL Server DATEPART(year, col) DATEPART(month, col) DATEPART(day, col) DATEPART(hour, col)
Oracle EXTRACT(YEAR FROM col) EXTRACT(MONTH FROM col) EXTRACT(DAY FROM col) β€”
Firebird EXTRACT(YEAR FROM col) EXTRACT(MONTH FROM col) EXTRACT(DAY FROM col) EXTRACT(HOUR FROM col)

Pass date values as strings ('2024-06-15') or as Delphi TDateTime values β€” the database driver handles the type conversion from the Variant binding.

Dialect Reference

Dialect Compilers

🌍 Language: English | Español

← Back to Guide

What a Dialect Compiler Does

The default TAnsiSqlCompiler generates generic ANSI SQL with ? placeholders and no identifier quoting. Dialect compilers extend it to handle:

  • Identifier quoting β€” wrapping table/column names to avoid keyword conflicts
  • Parameter placeholders β€” ?, $1, :p1, @p1, etc.
  • Date casting β€” dialect-specific expressions for WhereDate/WhereDatePart
  • Pagination β€” LIMIT/OFFSET vs FETCH NEXT … ROWS ONLY vs ROWNUM

Dialect Matrix

Dialect class Quote char Placeholder Package unit
TAnsiSqlCompiler none ? Daf.SQLCute.Compiler
TPostgresCompiler " $1, $2, … Daf.SQLCute.Compiler.Postgres
TMySqlCompiler ` ? Daf.SQLCute.Compiler.MySql
TSQLiteCompiler " ? Daf.SQLCute.Compiler.SQLite
TSqlServerCompiler [ … ] @p1, @p2, … Daf.SQLCute.Compiler.SqlServer
TOracleCompiler " :p1, :p2, … Daf.SQLCute.Compiler.Oracle
TFirebirdCompiler " ? Daf.SQLCute.Compiler.Firebird

All dialect compilers are in the SQLCute.Compilers package β€” add it to your project alongside SQLCute.Abstractions and SQLCute.

Using a Dialect Compiler

uses
  Daf.SQLCute,
  Daf.SQLCute.Compiler.Postgres;

var Q := TQuery.New
  .From('users')
  .Where('active', True)
  .OrderBy('name');

var Compiler := TPostgresCompiler.Create;
var R := Compiler.Compile(Q);
// R.SQL      β†’ 'SELECT * FROM "users" WHERE "active" = $1 ORDER BY "name"'
// R.Bindings β†’ [True]

Or use the shorthand Q.Compile(Compiler):

var R := Q.Compile(TPostgresCompiler.Create);

Dependency Injection

Register the compiler as a singleton and inject IQueryCompiler wherever needed:

// Composition root
Services.AddSingleton<IQueryCompiler, TPostgresCompiler>;

// In a repository
type
  TOrderRepository = class
  private
    FCompiler: IQueryCompiler;
  public
    constructor Create(Compiler: IQueryCompiler);
    function FindByStatus(const Status: string): TSQLResult;
  end;

function TOrderRepository.FindByStatus(const Status: string): TSQLResult;
begin
  Result := FCompiler.Compile(
    TQuery.New.From('orders').Where('status', Status));
end;

Implementing a Custom Compiler

Extend TAnsiSqlCompiler and override only what differs:

uses Daf.SQLCute.Compiler;

type
  TMyDbCompiler = class(TAnsiSqlCompiler)
  protected
    function WrapColumn(const Col: string): string; override;
    function WrapTable(const Table: string): string; override;
    function ParamPlaceholder: string; override;
  private
    FIndex: Integer;
  public
    function Compile(const Query: IQuery): TSQLResult; override;
  end;

function TMyDbCompiler.WrapColumn(const Col: string): string;
begin
  Result := '[' + Col + ']';
end;

function TMyDbCompiler.ParamPlaceholder: string;
begin
  Inc(FIndex);
  Result := ':param' + IntToStr(FIndex);
end;

function TMyDbCompiler.Compile(const Query: IQuery): TSQLResult;
begin
  FIndex := 0;
  Result := inherited Compile(Query);
end;

Pagination per Dialect

SQLCute emits LIMIT … OFFSET … by default. SQL Server and Oracle compilers override pagination to use their native syntax:

Dialect Pagination SQL
ANSI / Postgres / MySQL / SQLite / Firebird LIMIT n OFFSET m
SQL Server ORDER BY … OFFSET m ROWS FETCH NEXT n ROWS ONLY
Oracle OFFSET m ROWS FETCH NEXT n ROWS ONLY

SQL Server requires an ORDER BY clause when using OFFSET/FETCH. Add .OrderBy(…) before calling .ForPage(…) with TSqlServerCompiler.