DEV Community: David Lastrucci

Change tracking and soft delete: audit trails without the boilerplate

David Lastrucci — Sun, 19 Apr 2026 09:02:53 +0000

In most business applications, you need to answer questions like: Who created this record? When was it last modified? Can we undo this deletion?

Implementing this by hand means adding columns, writing triggers or hooks, and remembering to update them on every operation. Trysil does it with six attributes.

The change tracking attributes

Attribute	Set on	Required field type
`[TCreatedAt]`	Insert	`TTNullable<TDateTime>`
`[TCreatedBy]`	Insert	`String`
`[TUpdatedAt]`	Update	`TTNullable<TDateTime>`
`[TUpdatedBy]`	Update	`String`
`[TDeletedAt]`	Delete	`TTNullable<TDateTime>`
`[TDeletedBy]`	Delete	`String`

You add them to your entity fields, and Trysil fills them in automatically.

Adding an audit trail

unit Article.Model;

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

interface

uses
  Trysil.Types,
  Trysil.Attributes,
  Trysil.Validation.Attributes;

type
  [TTable('Articles')]
  [TSequence('ArticlesID')]
  TTArticle = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TRequired]
    [TMaxLength(200)]
    [TColumn('Title')]
    FTitle: String;

    [TColumn('Body')]
    FBody: String;

    [TCreatedAt]
    [TColumn('CreatedAt')]
    FCreatedAt: TTNullable<TDateTime>;

    [TCreatedBy]
    [TColumn('CreatedBy')]
    FCreatedBy: String;

    [TUpdatedAt]
    [TColumn('UpdatedAt')]
    FUpdatedAt: TTNullable<TDateTime>;

    [TUpdatedBy]
    [TColumn('UpdatedBy')]
    FUpdatedBy: String;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;
  public
    property ID: TTPrimaryKey read FID;
    property Title: String read FTitle write FTitle;
    property Body: String read FBody write FBody;
    property CreatedAt: TTNullable<TDateTime> read FCreatedAt;
    property CreatedBy: String read FCreatedBy;
    property UpdatedAt: TTNullable<TDateTime> read FUpdatedAt;
    property UpdatedBy: String read FUpdatedBy;
  end;

Now when you insert or update:

var
  LArticle: TTArticle;
begin
  LArticle := LContext.CreateEntity<TTArticle>();
  LArticle.Title := 'Getting started with Trysil';
  LArticle.Body := 'In this article...';
  LContext.Insert<TTArticle>(LArticle);
  // CreatedAt is now set to the current timestamp
  // CreatedBy is set to the current user name
end;

LArticle.Title := 'Getting started with Trysil (updated)';
LContext.Update<TTArticle>(LArticle);
// UpdatedAt is now set to the current timestamp
// UpdatedBy is set to the current user name
// CreatedAt and CreatedBy remain unchanged

Providing the current user

The *By fields need to know who the current user is. You provide this via a callback on TTContext:

LContext.OnGetCurrentUser := function: String
begin
  result := 'david.lastrucci';
end;

In a real application, this might read from an authentication token, a session variable, or a thread-local user context. If you do not assign the callback, Trysil writes an empty string.

Soft delete

Traditional DELETE removes the row from the database. In many scenarios — audit compliance, undo functionality, data recovery — you want to keep the record but mark it as deleted.

Trysil supports this natively. Add [TDeletedAt] (and optionally [TDeletedBy]) to your entity:

  [TTable('Articles')]
  [TSequence('ArticlesID')]
  TTArticle = class
  strict private
    // ... other fields ...

    [TDeletedAt]
    [TColumn('DeletedAt')]
    FDeletedAt: TTNullable<TDateTime>;

    [TDeletedBy]
    [TColumn('DeletedBy')]
    FDeletedBy: String;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;
  public
    // ... properties ...
    property DeletedAt: TTNullable<TDateTime> read FDeletedAt;
    property DeletedBy: String read FDeletedBy;
  end;

What changes

When an entity has a [TDeletedAt] field, calling Delete<T> no longer executes a SQL DELETE. Instead it executes:

UPDATE Articles
SET DeletedAt = :DeletedAt, DeletedBy = :DeletedBy, VersionID = VersionID + 1
WHERE ID = :ID AND VersionID = :VersionID

The record stays in the database, but it is marked as deleted.

Automatic exclusion

All SELECT queries on that entity automatically add DeletedAt IS NULL to the WHERE clause. Soft-deleted records are invisible by default — your application code does not need to change at all.

// This only returns non-deleted articles
LContext.SelectAll<TTArticle>(LArticles);

Including deleted records

Sometimes you need to see deleted records (admin panels, audit logs). Use the filter builder:

var
  LBuilder: TTFilterBuilder<TTArticle>;
  LFilter: TTFilter;
begin
  LBuilder := LContext.CreateFilterBuilder<TTArticle>();
  try
    LFilter := LBuilder
      .IncludeDeleted
      .OrderByDesc('DeletedAt')
      .Build;

    LContext.Select<TTArticle>(LAllArticles, LFilter);
  finally
    LBuilder.Free;
  end;
end;

Relation checks and soft delete

When you soft-delete a parent record, Trysil skips the child relation check. This makes sense: the record is not being physically removed, so foreign key integrity is preserved.

Putting it all together

Here is the SQL table that supports full change tracking with soft delete:

CREATE TABLE Articles (
  ID INTEGER PRIMARY KEY,
  Title TEXT NOT NULL,
  Body TEXT,
  CreatedAt TEXT,
  CreatedBy TEXT,
  UpdatedAt TEXT,
  UpdatedBy TEXT,
  DeletedAt TEXT,
  DeletedBy TEXT,
  VersionID INTEGER NOT NULL DEFAULT 1
);

And the complete entity lifecycle:

var
  LArticle: TTArticle;
begin
  // Create
  LArticle := LContext.CreateEntity<TTArticle>();
  LArticle.Title := 'My article';
  LArticle.Body := 'Content here';
  LContext.Insert<TTArticle>(LArticle);
  // CreatedAt = 2026-04-09 10:30:00, CreatedBy = 'david.lastrucci'

  // Update
  LArticle.Title := 'My article (revised)';
  LContext.Update<TTArticle>(LArticle);
  // UpdatedAt = 2026-04-09 11:15:00, UpdatedBy = 'david.lastrucci'

  // Soft delete
  LContext.Delete<TTArticle>(LArticle);
  // DeletedAt = 2026-04-09 14:00:00, DeletedBy = 'david.lastrucci'
  // Record is still in the database, but invisible to normal queries
end;

Series recap

Over these six articles we have covered the core of Trysil:

First contact — entity, connection, CRUD
Entity mapping — attributes, types, nullable fields, optimistic locking
Validation — declarative rules, custom validators, error handling
Filtering — fluent query builder, sorting, pagination
Relations — lazy loading, cascade delete, parent-child patterns
Change tracking — audit trails, soft delete, automatic exclusion

Trysil also includes a JSON serialization module, an HTTP/REST hosting module with attribute-based routing and JWT authentication, and a Unit of Work pattern via TTSession<T>. These are topics for future articles.

If you want to explore further, the GitHub repo contains full demo projects, a cookbook with 17 copy-paste recipes, and complete API documentation.

Trysil is open-source and available on GitHub. If this series helped you, consider giving the project a star — it helps other Delphi developers discover it!

Relations and lazy loading: modeling real-world data

David Lastrucci — Fri, 17 Apr 2026 11:57:13 +0000

Real applications rarely have isolated tables. Orders belong to customers. Products belong to brands. Order details reference both an order and a product. In this article we will model these relationships in Trysil using TTLazy<T> and TTLazyList<T>.

The data model

Let's build a classic order management scenario:

Customer  1──N  Order  1──N  OrderDetail  N──1  Product

A Customer has many Orders
An Order has many OrderDetails
An OrderDetail references one Product

Many-to-one: TTLazy<T>

When an entity has a foreign key pointing to another entity, use TTLazy<T>:

unit Order.Model;

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

interface

uses
  System.SysUtils,
  Trysil.Types,
  Trysil.Attributes,
  Trysil.Validation.Attributes,
  Trysil.Lazy,
  Trysil.Generics.Collections;

type
  [TTable('Customers')]
  [TSequence('CustomersID')]
  TTCustomer = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TRequired]
    [TMaxLength(100)]
    [TColumn('CompanyName')]
    FCompanyName: String;

    [TMaxLength(100)]
    [TColumn('City')]
    FCity: String;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;
  public
    function ToString: String; override;

    property ID: TTPrimaryKey read FID;
    property CompanyName: String read FCompanyName write FCompanyName;
    property City: String read FCity write FCity;
  end;

Now the TTOrder entity, which references a TTCustomer:

  [TTable('Orders')]
  [TSequence('OrdersID')]
  [TRelation('OrderDetails', 'OrderID', True)]
  TTOrder = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TRequired]
    [TColumn('OrderDate')]
    FOrderDate: TDateTime;

    [TRequired]
    [TDisplayName('Customer')]
    [TColumn('CustomerID')]
    FCustomer: TTLazy<TTCustomer>;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;

    [TDetailColumn('ID', 'OrderID')]
    FDetails: TTLazyList<TTOrderDetail>;

    function GetCustomer: TTCustomer;
    procedure SetCustomer(const AValue: TTCustomer);
    function GetDetails: TTList<TTOrderDetail>;
  public
    property ID: TTPrimaryKey read FID;
    property OrderDate: TDateTime read FOrderDate write FOrderDate;
    property Customer: TTCustomer read GetCustomer write SetCustomer;
    property Details: TTList<TTOrderDetail> read GetDetails;
  end;

The key points:

FCustomer: TTLazy<TTCustomer> — the field type is TTLazy<T>, not TTCustomer. The [TColumn('CustomerID')] attribute maps it to the foreign key column.
Getter/setter — you expose the related entity through a property backed by methods that access FCustomer.Entity:

function TTOrder.GetCustomer: TTCustomer;
begin
  result := FCustomer.Entity;
end;

procedure TTOrder.SetCustomer(const AValue: TTCustomer);
begin
  FCustomer.Entity := AValue;
end;

When you read Order.Customer for the first time, Trysil executes a SELECT to load the customer. On subsequent accesses, the cached reference is returned. This is lazy loading — the related entity is fetched only when you actually need it.

One-to-many: TTLazyList<T>

To load the child collection (order details), use TTLazyList<T> with [TDetailColumn]:

[TDetailColumn('ID', 'OrderID')]
FDetails: TTLazyList<TTOrderDetail>;

TDetailColumn takes two parameters:

The parent's primary key column ('ID')
The foreign key column in the child table ('OrderID')

The getter exposes the list:

function TTOrder.GetDetails: TTList<TTOrderDetail>;
begin
  result := FDetails.List;
end;

Like TTLazy<T>, the list is loaded on first access.

The TRelation attribute

On the TTOrder class, notice:

[TRelation('OrderDetails', 'OrderID', True)]

This declares that OrderDetails is a child table linked via the OrderID foreign key. The third parameter (True) enables cascade delete — when you delete an order, Trysil automatically deletes its order details first.

If you set it to False, Trysil will check for existing child records before deleting. If any exist, it raises an exception to prevent orphaned data.

The child entity

  [TTable('OrderDetails')]
  [TSequence('OrderDetailsID')]
  TTOrderDetail = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TColumn('OrderID')]
    FOrderID: TTPrimaryKey;

    [TRequired]
    [TDisplayName('Product')]
    [TColumn('ProductID')]
    FProduct: TTLazy<TTProduct>;

    [TRequired]
    [TMaxLength(100)]
    [TColumn('Description')]
    FDescription: String;

    [TGreater(0.00)]
    [TColumn('Quantity')]
    FQuantity: Double;

    [TMinValue(0.00)]
    [TColumn('Price')]
    FPrice: Double;

    [TColumn('Delivered')]
    FDelivered: TTNullable<TDateTime>;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;

    function GetProduct: TTProduct;
    procedure SetProduct(const AValue: TTProduct);
  public
    property ID: TTPrimaryKey read FID;
    property OrderID: TTPrimaryKey read FOrderID write FOrderID;
    property Product: TTProduct read GetProduct write SetProduct;
    property Description: String read FDescription write FDescription;
    property Quantity: Double read FQuantity write FQuantity;
    property Price: Double read FPrice write FPrice;
    property Delivered: TTNullable<TDateTime>
      read FDelivered write FDelivered;
  end;

Note the FOrderID field — it stores the foreign key value and is typed as TTPrimaryKey. This is the link back to the parent order.

Using relations in practice

Loading an order with its details

var
  LOrder: TTOrder;
  LDetail: TTOrderDetail;
begin
  LOrder := LContext.Get<TTOrder>(1);

  WriteLn(Format('Order #%d — %s', [
    LOrder.ID,
    LOrder.Customer.CompanyName]));  // triggers lazy load of customer

  for LDetail in LOrder.Details do   // triggers lazy load of details
    WriteLn(Format('  %s x%.0f @ %.2f', [
      LDetail.Description,
      LDetail.Quantity,
      LDetail.Price]));
end;

Creating an order with details

var
  LOrder: TTOrder;
  LDetail: TTOrderDetail;
  LCustomer: TTCustomer;
  LProduct: TTProduct;
begin
  LCustomer := LContext.Get<TTCustomer>(1);
  LProduct := LContext.Get<TTProduct>(5);

  LOrder := LContext.CreateEntity<TTOrder>();
  LOrder.OrderDate := Now;
  LOrder.Customer := LCustomer;
  LContext.Insert<TTOrder>(LOrder);

  LDetail := LContext.CreateEntity<TTOrderDetail>();
  LDetail.OrderID := LOrder.ID;
  LDetail.Product := LProduct;
  LDetail.Description := LProduct.Description;
  LDetail.Quantity := 10;
  LDetail.Price := LProduct.Price;
  LContext.Insert<TTOrderDetail>(LDetail);
end;

Deleting with cascade

// Because TTOrder has [TRelation('OrderDetails', 'OrderID', True)],
// this deletes the order AND all its details:
LContext.Delete<TTOrder>(LOrder);

Important things to remember

The context must stay alive while you access lazy-loaded fields. If you free the context and then access Order.Customer, you will get an access violation.
Beware the N+1 problem. If you load 100 orders and access .Customer on each one, that is 100 additional SELECT queries. For bulk operations, consider loading customers separately and matching them in memory.
Lazy fields use the identity map. If the same customer is referenced by multiple orders, only one instance is loaded (when the identity map is enabled).

What is next

We have modeled a real-world domain with parent-child relationships and lazy loading. In the next article we will look at change tracking and soft delete — how Trysil can automatically record who changed what, and how to delete records without actually removing them.

Trysil is open-source and available on GitHub. If this series is helping you, a star on the repo would be much appreciated!

Filtering, sorting, and pagination with the fluent query builder

David Lastrucci — Thu, 16 Apr 2026 06:07:04 +0000

So far in this series we have mapped entities, validated them, and performed basic CRUD. But real applications rarely load all records from a table. You need to search, filter, sort, and paginate.

Trysil provides a fluent API for this: TTFilterBuilder<T>. You chain method calls to build a filter, then pass it to Select<T>. The builder generates parameterized SQL behind the scenes — no string concatenation, no injection risk.

Basic filtering

uses
  Trysil.Filter;

var
  LBuilder: TTFilterBuilder<TTContact>;
  LFilter: TTFilter;
  LContacts: TTList<TTContact>;
begin
  LContacts := TTList<TTContact>.Create;
  try
    LBuilder := LContext.CreateFilterBuilder<TTContact>();
    try
      LFilter := LBuilder
        .Where('Lastname').Equal('Smith')
        .Build;

      LContext.Select<TTContact>(LContacts, LFilter);
    finally
      LBuilder.Free;
    end;

    for LContact in LContacts do
      WriteLn(Format('%s %s', [LContact.Firstname, LContact.Lastname]));
  finally
    LContacts.Free;
  end;
end;

The flow is always the same:

Create a builder with LContext.CreateFilterBuilder<T>
Chain conditions with .Where, .AndWhere, .OrWhere
Apply an operator (.Equal, .Like, .Greater, etc.)
Call .Build to get a TTFilter
Pass the filter to LContext.Select<T>

Available operators

Method	SQL
`.Equal(value)`	`= :param`
`.NotEqual(value)`	`<> :param`
`.Greater(value)`	`> :param`
`.GreaterOrEqual(value)`	`>= :param`
`.Less(value)`	`< :param`
`.LessOrEqual(value)`	`<= :param`
`.Like(pattern)`	`LIKE :param`
`.NotLike(pattern)`	`NOT LIKE :param`
`.IsNull`	`IS NULL`
`.IsNotNull`	`IS NOT NULL`

Combining conditions

Use .AndWhere and .OrWhere to combine multiple conditions:

LFilter := LBuilder
  .Where('Lastname').Equal('Smith')
  .AndWhere('Email').IsNotNull
  .Build;

LFilter := LBuilder
  .Where('City').Equal('Rome')
  .OrWhere('City').Equal('Milan')
  .Build;

Pattern matching with LIKE

LFilter := LBuilder
  .Where('Lastname').Like('Sm%')
  .Build;

Standard SQL wildcards apply: % matches any sequence of characters, _ matches a single character.

Sorting

LFilter := LBuilder
  .Where('Country').Equal('Italy')
  .OrderByAsc('Lastname')
  .Build;

You can chain multiple sort clauses:

LFilter := LBuilder
  .Where('Country').Equal('Italy')
  .OrderByAsc('Lastname')
  .OrderByAsc('Firstname')
  .Build;

For descending order, use .OrderByDesc:

LFilter := LBuilder
  .OrderByDesc('Price')
  .Build;

Note: you can sort without filtering — just skip the .Where call.

Pagination

For large datasets, load data in pages:

const
  PageSize = 20;
var
  LPage: Integer;
begin
  LPage := 3; // zero-based page index

  LFilter := LBuilder
    .OrderByAsc('Lastname')
    .Limit(PageSize)
    .Offset(LPage * PageSize)
    .Build;

  LContext.Select<TTContact>(LContacts, LFilter);
end;

.Limit(n) sets the maximum number of rows to return. .Offset(n) skips the first n rows. Combined with sorting, this gives you clean, predictable pagination.

Counting records

Sometimes you need the total count (for example, to display "Page 3 of 12"):

var
  LCount: Integer;
begin
  LFilter := LBuilder
    .Where('Country').Equal('Italy')
    .Build;

  LCount := LContext.SelectCount<TTContact>(LFilter);
end;

SelectCount<T> returns the number of matching rows without loading the entities into memory.

A complete example

Here is a realistic search function that combines filtering, sorting, pagination, and counting:

procedure TContactService.Search(
  const ASearchText: String;
  const APage: Integer;
  const APageSize: Integer;
  const AContacts: TTList<TTContact>;
  out ATotalCount: Integer);
var
  LBuilder: TTFilterBuilder<TTContact>;
  LFilter: TTFilter;
begin
  LBuilder := FContext.CreateFilterBuilder<TTContact>();
  try
    if not ASearchText.IsEmpty then
      LBuilder
        .Where('Lastname').Like(Format('%s%%', [ASearchText]))
        .OrWhere('Firstname').Like(Format('%s%%', [ASearchText]));

    LBuilder
      .OrderByAsc('Lastname')
      .OrderByAsc('Firstname');

    LFilter := LBuilder.Build;
    ATotalCount := FContext.SelectCount<TTContact>(LFilter);

    LBuilder
      .Limit(APageSize)
      .Offset(APage * APageSize);

    LFilter := LBuilder.Build;
    FContext.Select<TTContact>(AContacts, LFilter);
  finally
    LBuilder.Free;
  end;
end;

What is next

We can now search, sort, and paginate our data with a clean fluent API. In the next article we will tackle relations and lazy loading — how to model parent-child relationships and load related entities on demand.

Trysil is open-source and available on GitHub. Stars and feedback are always appreciated!

Declarative validation: keep bad data out of your database

David Lastrucci — Tue, 14 Apr 2026 07:06:36 +0000

In the previous article we covered entity mapping and types. But mapping alone does not guarantee data quality. What happens if someone inserts a product with a negative price, or a contact with an empty name?

Trysil provides a set of validation attributes that you place directly on entity fields. The ORM checks them automatically before every Insert and Update — if validation fails, no SQL is executed and you get a clear exception.

Available validation attributes

Attribute	Applies to	Rule
`TRequired`	String, DateTime, TTNullable, TTLazy	Field cannot be empty, zero, or null
`TMaxLength(n)`	String	Length must be <= n
`TMinLength(n)`	String	Length must be >= n
`TMaxValue(n)`	Integer, Double	Value must be <= n
`TMinValue(n)`	Integer, Double	Value must be >= n
`TGreater(n)`	Integer, Double	Value must be > n
`TLess(n)`	Integer, Double	Value must be < n
`TRange(min, max)`	Integer, Double	Value must be in [min, max]
`TRegex(pattern)`	String	Value must match the regex
`TEmail`	String	Valid email format (built-in regex)

All attributes live in Trysil.Validation.Attributes.

Using validation attributes

Here is a TTEmployee entity with comprehensive validation:

unit Employee.Model;

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

interface

uses
  Trysil.Types,
  Trysil.Attributes,
  Trysil.Validation.Attributes;

type
  [TTable('Employees')]
  [TSequence('EmployeesID')]
  TTEmployee = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TRequired]
    [TMinLength(2)]
    [TMaxLength(50)]
    [TColumn('Firstname')]
    FFirstname: String;

    [TRequired]
    [TMinLength(2)]
    [TMaxLength(50)]
    [TColumn('Lastname')]
    FLastname: String;

    [TMaxLength(255)]
    [TEmail]
    [TColumn('Email')]
    FEmail: String;

    [TRange(18, 70)]
    [TColumn('Age')]
    FAge: Integer;

    [TGreater(0.00)]
    [TColumn('Salary')]
    FSalary: Double;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;
  public
    property ID: TTPrimaryKey read FID;
    property Firstname: String read FFirstname write FFirstname;
    property Lastname: String read FLastname write FLastname;
    property Email: String read FEmail write FEmail;
    property Age: Integer read FAge write FAge;
    property Salary: Double read FSalary write FSalary;
  end;

With this definition:

Firstname and Lastname must be non-empty and between 2 and 50 characters
Email must be a valid email address (if provided — TEmail skips empty strings)
Age must be between 18 and 70
Salary must be greater than zero

Display names for error messages

By default, validation errors use the column name. You can override this with TDisplayName:

[TRequired]
[TDisplayName('First name')]
[TColumn('Firstname')]
FFirstname: String;

Now the error message will say "First name is required" instead of "Firstname is required".

Custom error messages

Every validation attribute accepts an optional error message:

[TRequired('Please enter the employee name')]
[TColumn('Firstname')]
FFirstname: String;

[TRange(18, 70, 'Age must be between 18 and 70')]
[TColumn('Age')]
FAge: Integer;

Handling validation errors

Validation runs automatically on Insert and Update. If any rule fails, Trysil raises an ETValidationException:

uses
  Trysil.Exceptions;

var
  LEmployee: TTEmployee;
begin
  LEmployee := LContext.CreateEntity<TTEmployee>();
  LEmployee.Firstname := '';   // will fail TRequired
  LEmployee.Salary := -1000;   // will fail TGreater(0.00)

  try
    LContext.Insert<TTEmployee>(LEmployee);
  except
    on E: ETValidationException do
      ShowMessage(E.Message);
  end;
end;

You can also validate explicitly without inserting:

try
  LContext.Validate<TTEmployee>(LEmployee);
except
  on E: ETValidationException do
    // handle errors
end;

This is useful when you want to validate in a UI before the user clicks "Save".

Custom validators

For business rules that go beyond simple field checks, you can write a custom validator method and mark it with [TValidator]:

[TTable('Invoices')]
[TSequence('InvoicesID')]
TTInvoice = class
strict private
  [TPrimaryKey]
  [TColumn('ID')]
  FID: TTPrimaryKey;

  [TRequired]
  [TColumn('IssueDate')]
  FIssueDate: TDateTime;

  [TRequired]
  [TColumn('DueDate')]
  FDueDate: TDateTime;

  [TColumn('VersionID')]
  [TVersionColumn]
  FVersionID: TTVersion;
public
  [TValidator]
  procedure ValidateDates(const AErrors: TTValidationErrors);

  property ID: TTPrimaryKey read FID;
  property IssueDate: TDateTime read FIssueDate write FIssueDate;
  property DueDate: TDateTime read FDueDate write FDueDate;
end;

procedure TTInvoice.ValidateDates(const AErrors: TTValidationErrors);
begin
  if FDueDate < FIssueDate then
    AErrors.Add('DueDate', 'Due date cannot be before issue date');
end;

The method is called automatically alongside the attribute-based validation. You can add multiple errors to AErrors — they are all collected before the exception is raised.

Validation and the ORM pipeline

Here is what happens when you call LContext.Insert<T>(LEntity):

Validation — all [TRequired], [TMaxLength], [TRange], etc. attributes are checked, plus any [TValidator] methods
Events — [BeforeInsert] methods are called (more on events in a future article)
SQL execution — the INSERT statement is generated and executed
Post-events — [AfterInsert] methods are called

If step 1 fails, steps 2-4 never happen. Your database stays clean.

What is next

We now have entities that are both correctly mapped and validated before they reach the database. In the next article we will explore filtering and querying — how to search, sort, and paginate data using Trysil's fluent filter builder.

Trysil is open-source and available on GitHub. If you find it useful, a star goes a long way!

Entity mapping in depth: attributes, types, and nullable fields

David Lastrucci — Sun, 12 Apr 2026 12:27:02 +0000

In the previous article we created a simple entity and performed CRUD operations. Now let's look at how entity mapping actually works under the hood, and how to handle more realistic scenarios like nullable fields and optimistic locking.

The mapping attributes

Every Trysil entity needs at least two class-level attributes:

[TTable('Contacts')]      // which table
[TSequence('ContactsID')]   // how the PK is generated
TTContact = class

And every mapped field needs a [TColumn] attribute:

[TColumn('Firstname')]
FFirstname: String;

Here is the full set of mapping attributes:

Attribute	Level	Purpose
`TTable`	Class	Maps the class to a database table
`TSequence`	Class	Names the sequence for the PK
`TPrimaryKey`	Field	Marks the primary key field
`TColumn`	Field	Maps a field to a column
`TVersionColumn`	Field	Enables optimistic locking
`TRelation`	Class	Declares a parent-child relationship (covered in a later article)

Core types

Trysil defines three foundational types in Trysil.Types:

TTPrimaryKey

[TPrimaryKey]
[TColumn('ID')]
FID: TTPrimaryKey;

TTPrimaryKey is an alias for Int32. Every entity must have exactly one primary key field of this type. The value is auto-generated by the database.

The ID property is read-only — you never set it manually:

property ID: TTPrimaryKey read FID;

TTVersion

[TColumn('VersionID')]
[TVersionColumn]
FVersionID: TTVersion;

TTVersion is also an Int32. When you mark a field with [TVersionColumn], Trysil includes it in the WHERE clause of UPDATE and DELETE statements:

UPDATE Contacts SET ... WHERE ID = :ID AND VersionID = :VersionID

If the version in the database does not match the version in your object, the update affects zero rows and Trysil raises an exception. This is optimistic locking — it prevents two users from silently overwriting each other's changes.

After a successful update, Trysil increments the version automatically.

TTNullable<T>

Database columns are often nullable, but Delphi value types (Integer, TDateTime, Double) cannot be null. Trysil solves this with a generic record:

[TColumn('BirthDate')]
FBirthDate: TTNullable<TDateTime>;

[TColumn('Score')]
FScore: TTNullable<Integer>;

TTNullable<T> has no default constructor. When you declare a field of this type and do not assign a value, it is null:

var
  LDate: TTNullable<TDateTime>;
begin
  // LDate is null here

  if LDate.IsNull then
    WriteLn('No date set');

  // Assign a value
  LDate := TTNullable<TDateTime>.Create(Now);
  WriteLn(DateTimeToStr(LDate.Value));
end;

In the database, a null TTNullable<T> field is stored as SQL NULL. When reading, SQL NULL maps back to the null state.

A more realistic entity

Let's build a TTProduct entity that uses all of these types:

unit Product.Model;

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

interface

uses
  System.SysUtils,
  Trysil.Types,
  Trysil.Attributes,
  Trysil.Validation.Attributes;

type
  [TTable('Products')]
  [TSequence('ProductsID')]
  TTProduct = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TRequired]
    [TMaxLength(100)]
    [TColumn('Description')]
    FDescription: String;

    [TGreater(0.00)]
    [TColumn('Price')]
    FPrice: Double;

    [TColumn('DiscountedPrice')]
    FDiscountedPrice: TTNullable<Double>;

    [TColumn('AvailableFrom')]
    FAvailableFrom: TTNullable<TDateTime>;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;
  public
    property ID: TTPrimaryKey read FID;
    property Description: String read FDescription write FDescription;
    property Price: Double read FPrice write FPrice;
    property DiscountedPrice: TTNullable<Double>
      read FDiscountedPrice write FDiscountedPrice;
    property AvailableFrom: TTNullable<TDateTime>
      read FAvailableFrom write FAvailableFrom;
    property VersionID: TTVersion read FVersionID;
  end;

The matching SQL table:

CREATE TABLE Products (
  ID INTEGER PRIMARY KEY,
  Description TEXT NOT NULL,
  Price REAL NOT NULL,
  DiscountedPrice REAL,
  AvailableFrom TEXT,
  VersionID INTEGER NOT NULL DEFAULT 1
);

Note how DiscountedPrice and AvailableFrom are nullable in both the Delphi entity and the SQL schema.

Working with nullable fields

var
  LProduct: TTProduct;
begin
  LProduct := LContext.CreateEntity<TTProduct>();
  LProduct.Description := 'Mechanical keyboard';
  LProduct.Price := 149.99;
  // DiscountedPrice and AvailableFrom are null — we simply don't set them

  LContext.Insert<TTProduct>(LProduct);

  // Later, set a discounted price
  LProduct.DiscountedPrice := TTNullable<Double>.Create(119.99);
  LContext.Update<TTProduct>(LProduct);
end;

How the mapping cache works

The first time you use an entity type with TTContext, Trysil reads its attributes via RTTI and builds a TTTableMap — an internal representation of the table name, columns, primary key, version column, and relationships.

This mapping is cached globally in TTMapper.Instance (a singleton). Subsequent operations on the same entity type skip the RTTI scan entirely. This means the reflection cost is paid only once per entity type for the lifetime of your application.

Compile-time safety

Always add this directive at the top of your model units:

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

Without it, a typo like [TColum('Name')] compiles silently and the field is never mapped. With the directive, the compiler flags it as an error immediately.

What is next

We now know how to map classes to tables, handle nullable fields, and rely on optimistic locking for safe concurrent updates. In the next article we will explore validation — how Trysil checks your data before it hits the database.

Trysil is open-source and available on GitHub. Feedback and contributions are welcome!

Meet Trysil: a lightweight ORM for Delphi

David Lastrucci — Thu, 09 Apr 2026 20:21:17 +0000

If you have ever written a Delphi application that talks to a database, you know the routine: write SQL by hand, manage parameters, loop through datasets, and copy values into objects field by field. It works, but it is tedious and error-prone.

Trysil is an open-source ORM for Delphi that eliminates that boilerplate. You decorate your classes with attributes, and the framework handles the rest — mapping, querying, inserting, updating, deleting. It is lightweight, attribute-driven, and built on top of FireDAC, so it works with SQLite, PostgreSQL, SQL Server, and Firebird out of the box.

In this first article we will go from zero to a working CRUD application with SQLite.

Installation

You can install Trysil in three ways:

GetIt — search for "Trysil" in the Embarcadero GetIt Package Manager
Boss — boss install davidlastrucci/Trysil
Manual — clone the GitHub repo, open Packages/<ver>/Trysil.groupproj, and build all

After installation, point your project's Search Path to the compiled output directory.

Your first entity

An entity in Trysil is a plain Delphi class with attributes. No base class to inherit, no interface to implement.

unit Contact.Model;

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

interface

uses
  Trysil.Types,
  Trysil.Attributes,
  Trysil.Validation.Attributes;

type
  [TTable('Contacts')]
  [TSequence('ContactsID')]
  TTContact = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TRequired]
    [TMaxLength(50)]
    [TColumn('Firstname')]
    FFirstname: String;

    [TRequired]
    [TMaxLength(50)]
    [TColumn('Lastname')]
    FLastname: String;

    [TMaxLength(255)]
    [TEmail]
    [TColumn('Email')]
    FEmail: String;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;
  public
    property ID: TTPrimaryKey read FID;
    property Firstname: String read FFirstname write FFirstname;
    property Lastname: String read FLastname write FLastname;
    property Email: String read FEmail write FEmail;
    property VersionID: TTVersion read FVersionID;
  end;

A few things to note:

[TTable('Contacts')] maps the class to the Contacts table.
[TSequence('ContactsID')] tells Trysil how the primary key is generated.
[TPrimaryKey] and [TColumn('...')] mark the primary key and map each field to a column.
[TVersionColumn] enables optimistic locking — Trysil will check this value on every update to prevent lost writes.
[TRequired], [TMaxLength], [TEmail] are validation attributes — we will cover them in depth in a later article.
{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR} turns typos in attribute names into compile-time errors. Always add this.

Fields are strict private with public properties. The ORM accesses them through RTTI.

Connecting to SQLite

uses
  Trysil.Data,
  Trysil.Data.FireDAC.SQLite,
  Trysil.Data.FireDAC.ConnectionPool,
  Trysil.Context;

var
  LConnection: TTConnection;
  LContext: TTContext;
begin
  // Disable connection pooling (not needed for desktop apps)
  TTFireDACConnectionPool.Instance.Config.Enabled := False;

  // Register and create the connection
  TTSQLiteConnection.RegisterConnection('MyApp', 'contacts.db');
  LConnection := TTSQLiteConnection.Create('MyApp');
  try
    LContext := TTContext.Create(LConnection);
    try
      // ... use the context
    finally
      LContext.Free;
    end;
  finally
    LConnection.Free;
  end;
end;

TTContext is the single entry point for all ORM operations. You create it with a connection, and it gives you methods for reading, writing, and querying entities.

CRUD operations

Create

var
  LContact: TTContact;
begin
  LContact := LContext.CreateEntity<TTContact>();
  LContact.Firstname := 'Ada';
  LContact.Lastname := 'Lovelace';
  LContact.Email := 'ada@example.com';
  LContext.Insert<TTContact>(LContact);
end;

Always use CreateEntity<T> to create new entities — it initializes internal state that the context needs to track the object.

Read

var
  LContacts: TTList<TTContact>;
begin
  LContacts := TTList<TTContact>.Create;
  try
    // Load all contacts
    LContext.SelectAll<TTContact>(LContacts);

    for LContact in LContacts do
      WriteLn(Format('%s %s — %s', [
        LContact.Firstname,
        LContact.Lastname,
        LContact.Email]));
  finally
    LContacts.Free;
  end;
end;

To fetch a single entity by ID:

var
  LContact: TTContact;
begin
  LContact := LContext.Get<TTContact>(42);
  // raises an exception if not found
end;

Update

LContact.Email := 'ada.lovelace@example.com';
LContext.Update<TTContact>(LContact);

Behind the scenes, Trysil generates an UPDATE statement that includes the VersionID in the WHERE clause. If another user has modified the same record in the meantime, the update fails with a concurrency error rather than silently overwriting their changes.

Delete

LContext.Delete<TTContact>(LContact);

The SQL table

For completeness, here is the SQLite schema that matches our entity:

CREATE TABLE Contacts (
  ID INTEGER PRIMARY KEY,
  Firstname TEXT NOT NULL,
  Lastname TEXT NOT NULL,
  Email TEXT,
  VersionID INTEGER NOT NULL DEFAULT 1
);

What is next

In this article we defined an entity, connected to SQLite, and performed full CRUD — all without writing a single line of SQL.

In the next article we will look deeper into entity mapping: nullable fields, custom types, and how Trysil handles the relationship between your Delphi classes and your database schema.

The full source code is available on GitHub.

Trysil is an open-source project. If you find it useful, consider giving it a star on GitHub!

Automatic audit trails and soft delete in Delphi with Trysil

David Lastrucci — Wed, 08 Apr 2026 15:11:14 +0000

Most ORMs handle INSERT, UPDATE, and DELETE well. But when you need to track who changed a record and when, or keep deleted records around instead of erasing them, you're usually on your own — writing triggers, adding boilerplate to every repository method, or bolting on an external audit library.

In Trysil (an open-source ORM for Delphi), I wanted these features to be declarative: add an attribute, and the framework handles the rest. No base class to inherit, no interface to implement, no code to write beyond the attribute itself.

This article shows how it works.

The problem

Consider a typical entity:

[TTable('Invoices')]
[TSequence('InvoicesID')]
TInvoice = class
strict private
  [TPrimaryKey]
  [TColumn('ID')]
  FID: TTPrimaryKey;

  [TColumn('InvoiceNumber')]
  FInvoiceNumber: String;

  [TColumn('Amount')]
  FAmount: Double;

  [TVersionColumn]
  [TColumn('VersionID')]
  FVersionID: TTVersion;
public
  property ID: TTPrimaryKey read FID;
  property InvoiceNumber: String read FInvoiceNumber write FInvoiceNumber;
  property Amount: Double read FAmount write FAmount;
end;

Now you get a new requirement: "We need to know who created each invoice and when. And when invoices are deleted, don't actually remove them — mark them as deleted so we can audit them later."

In most ORMs this means: add columns to the table, add fields to the entity, then manually set CreatedAt := Now in every insert method, UpdatedAt := Now in every update method, and replace every DELETE FROM with an UPDATE ... SET DeletedAt = Now. And don't forget to add WHERE DeletedAt IS NULL to every single query.

The solution: six attributes

Trysil solves this with six attributes that you place on entity fields:

Attribute	Populated on	Field type
`[TCreatedAt]`	INSERT	`TTNullable<TDateTime>`
`[TCreatedBy]`	INSERT	`String`
`[TUpdatedAt]`	UPDATE	`TTNullable<TDateTime>`
`[TUpdatedBy]`	UPDATE	`String`
`[TDeletedAt]`	DELETE (soft)	`TTNullable<TDateTime>`
`[TDeletedBy]`	DELETE (soft)	`String`

Here's the same invoice entity with full change tracking and soft delete:

[TTable('Invoices')]
[TSequence('InvoicesID')]
TInvoice = class
strict private
  [TPrimaryKey]
  [TColumn('ID')]
  FID: TTPrimaryKey;

  [TColumn('InvoiceNumber')]
  FInvoiceNumber: String;

  [TColumn('Amount')]
  FAmount: Double;

  [TCreatedAt]
  [TColumn('CreatedAt')]
  FCreatedAt: TTNullable<TDateTime>;

  [TCreatedBy]
  [TColumn('CreatedBy')]
  FCreatedBy: String;

  [TUpdatedAt]
  [TColumn('UpdatedAt')]
  FUpdatedAt: TTNullable<TDateTime>;

  [TUpdatedBy]
  [TColumn('UpdatedBy')]
  FUpdatedBy: String;

  [TDeletedAt]
  [TColumn('DeletedAt')]
  FDeletedAt: TTNullable<TDateTime>;

  [TDeletedBy]
  [TColumn('DeletedBy')]
  FDeletedBy: String;

  [TVersionColumn]
  [TColumn('VersionID')]
  FVersionID: TTVersion;
public
  property ID: TTPrimaryKey read FID;
  property InvoiceNumber: String read FInvoiceNumber write FInvoiceNumber;
  property Amount: Double read FAmount write FAmount;
  property CreatedAt: TTNullable<TDateTime> read FCreatedAt;
  property CreatedBy: String read FCreatedBy;
  property UpdatedAt: TTNullable<TDateTime> read FUpdatedAt;
  property UpdatedBy: String read FUpdatedBy;
  property DeletedAt: TTNullable<TDateTime> read FDeletedAt;
  property DeletedBy: String read FDeletedBy;
end;

That's it. No other code changes. The ORM does the rest.

What happens at runtime

Insert

LInvoice := LContext.CreateEntity<TInvoice>();
LInvoice.InvoiceNumber := 'INV-2026-001';
LInvoice.Amount := 1500.00;
LContext.Insert<TInvoice>(LInvoice);

Before executing the INSERT, the resolver automatically sets CreatedAt to the current timestamp and CreatedBy to the current user. You never touch these fields yourself.

The generated SQL looks like:

INSERT INTO Invoices (InvoiceNumber, Amount, CreatedAt, CreatedBy, VersionID)
VALUES (:p1, :p2, :p3, :p4, 0)

Update

LInvoice.Amount := 1750.00;
LContext.Update<TInvoice>(LInvoice);

The resolver sets UpdatedAt and UpdatedBy automatically:

UPDATE Invoices
SET InvoiceNumber = :p1, Amount = :p2, UpdatedAt = :p3, UpdatedBy = :p4,
    VersionID = VersionID + 1
WHERE ID = :pk AND VersionID = :ver

Note the optimistic locking in the WHERE clause — [TVersionColumn] works alongside change tracking.

Delete (soft)

LContext.Delete<TInvoice>(LInvoice);

Because the entity has a [TDeletedAt] field, this does not execute a DELETE FROM. Instead it runs:

UPDATE Invoices
SET DeletedAt = :p1, DeletedBy = :p2, VersionID = VersionID + 1
WHERE ID = :pk AND VersionID = :ver

The record stays in the database. It's just marked as deleted.

Queries automatically exclude soft-deleted records

Every SELECT generated by Trysil checks for the presence of [TDeletedAt] on the entity. If found, it appends DeletedAt IS NULL to the WHERE clause:

LContext.SelectAll<TInvoice>(LInvoices);
// Generated: SELECT ... FROM Invoices WHERE DeletedAt IS NULL

This happens transparently. You don't add any filter — the ORM knows that if you declared [TDeletedAt], you want soft-deleted records hidden by default.

When you need to see deleted records

Use IncludeDeleted on the filter builder:

LFilter := LContext.CreateFilterBuilder<TInvoice>()
  .IncludeDeleted
  .Build;
LContext.Select<TInvoice>(LInvoices, LFilter);
// Generated: SELECT ... FROM Invoices (no DeletedAt filter)

This is useful for admin panels, audit views, or data recovery.

Providing the current user

The *By attributes need to know who the current user is. Trysil doesn't impose any authentication mechanism — instead, you provide a callback:

LContext.OnGetCurrentUser :=
  function: String
  begin
    result := GetCurrentSessionUser(); // your logic here
  end;

This callback is invoked by the resolver right before executing INSERT, UPDATE, or soft DELETE. If not assigned, an empty string is written — so the *By fields are optional. You can use only the *At attributes if you just need timestamps.

Mix and match

You don't have to use all six attributes. Common combinations:

Timestamps only (no user tracking):

[TCreatedAt]
[TColumn('CreatedAt')]
FCreatedAt: TTNullable<TDateTime>;

[TUpdatedAt]
[TColumn('UpdatedAt')]
FUpdatedAt: TTNullable<TDateTime>;

Soft delete only (no creation/update tracking):

[TDeletedAt]
[TColumn('DeletedAt')]
FDeletedAt: TTNullable<TDateTime>;

Full audit trail — all six, as shown in the invoice example above.

Each entity can have its own combination. There's no global setting to toggle.

How it works internally

The implementation touches three layers:

1. Mapping — When Trysil first encounters an entity class, it scans its RTTI for the six attributes and builds a TTChangeTrackingMap for each phase (created, updated, deleted). Each map holds references to the ChangedAt and ChangedBy column mappings. This scan happens once and is cached.

2. Resolver — Before executing any command, the resolver calls ApplyChangeTracking with the appropriate map. This sets the timestamp via TTNullable<TDateTime>.Create(Now) and the user string via the OnGetCurrentUser callback.

3. SQL generation — When [TDeletedAt] is present, the resolver switches from CreateDeleteCommand (which generates DELETE FROM) to CreateSoftDeleteCommand (which generates an UPDATE targeting only the deleted tracking columns and the version column). On the SELECT side, AddSoftDeleteWhere injects the DeletedAt IS NULL condition unless IncludeDeleted is set.

The soft delete command also skips relation checks (CheckRelations), since the record isn't actually being removed — foreign key constraints don't apply.

Database table

The corresponding table just needs the extra columns. For example, with PostgreSQL:

CREATE TABLE Invoices (
  ID SERIAL PRIMARY KEY,
  InvoiceNumber VARCHAR(50) NOT NULL,
  Amount DOUBLE PRECISION NOT NULL,
  CreatedAt TIMESTAMP,
  CreatedBy VARCHAR(100),
  UpdatedAt TIMESTAMP,
  UpdatedBy VARCHAR(100),
  DeletedAt TIMESTAMP,
  DeletedBy VARCHAR(100),
  VersionID INTEGER NOT NULL DEFAULT 0
);

No triggers, no stored procedures. The ORM handles everything at the application level.

Wrapping up

Adding audit trails and soft delete to a Delphi application usually means writing repetitive code in every data access method. With Trysil's attribute-based approach, you declare the behavior once on the entity and the framework enforces it consistently across all operations.

The feature works with all four supported databases (SQLite, PostgreSQL, SQL Server, Firebird) and composes naturally with the rest of the framework — optimistic locking, identity map, fluent query builder, JSON serialization.

Links:

GitHub: github.com/davidlastrucci/Trysil
Documentation: davidlastrucci.github.io/trysil
Install: available on GetIt and via boss install davidlastrucci/Trysil

If you have questions or feedback, feel free to open an issue on GitHub.

Trysil - API REST made simple

David Lastrucci — Thu, 20 Nov 2025 21:21:00 +0000

If you've ever built REST APIs, you know the drill: define your data model, write your database queries, map results to objects, handle serialization, write controllers... and that's before you even get to the business logic!

What if I told you that with Trysil, a Delphi ORM, you can skip most of that boilerplate and go from your data model to a working API in just a few steps?

Let me show you how.

The Problem with Traditional Approaches

When building REST APIs the traditional way, you often end up writing:

SQL queries (or stored procedures)
Data mapping code
Validation logic
CRUD operations for each entity
Serialization/deserialization code

It works, but it's repetitive and time-consuming. For each new entity, you're basically copying and pasting the same patterns over and over.

Enter Trysil

Trysil is a Delphi ORM that helps you focus on what matters: your business logic. Once you've defined your entity model, Trysil handles the heavy lifting of persistence, queries, and data mapping.

Let's see how simple it can be with a real example.

Step 1: Define Your Entity Model

First, we define our TCustomer entity class. With Trysil, you simply create a plain Delphi class and decorate it with attributes to map it to your database:

[TTable('Customers')]
TCustomer = class
strict private
  [TPrimaryKey]
  [TColumn('ID')]
  FID: TTPrimaryKey;

  [TRequired]
  [TMaxLength(100)]
  [TColumn('Firstame')]
  FFirstname: String;

  [TRequired]
  [TMaxLength(100)]
  [TColumn('Lastname')]
  FLastname: String;

  [TMaxLength(255)]
  [TColumn('Email')]
  FEmail: String;

  [TVersionColumn]
  [TColumn('VersionID')]
  FVersionID: TTVersion;
public
  property ID: TTPrimaryKey read FID;
  property Firstname: String read FFirstname write FFirstname;
  property Lastname: String read FLastname write FLastname;
  property Email: String read FEmail write FEmail;
  property VersionID: TTVersion read FVersionID;
end;

That's it! Notice how clean and readable it is. The attributes tell Trysil everything it needs to know about the database mapping.

Step 2: Create Your API Controller

Now here's where the magic happens. To expose this entity through a REST API, we create a controller. Thanks to Trysil handling all the persistence logic, our controller can be incredibly simple:

[TUri('/customer')]
TCustomerController = class(TReadWriteController<TCustomer>)
end;

Look at that! With just a few lines of code, we have:

GET to retrieve customers
POST to create a new customer
PUT to update existing one
DELETE to remove a customer

Trysil takes care of:

Database connections
SQL generation
Object-relational mapping
Transactions
Error handling

The Result

What you end up with is a clean, maintainable codebase where:

Your entity model is the single source of truth
Controllers are thin and focused on HTTP concerns
No SQL strings scattered throughout your code
Easy to test and extend

This is the beauty of using an ORM like Trysil: you spend less time on plumbing and more time building features that matter to your users.

Trysil - Multi-tenat API REST

David Lastrucci — Tue, 26 Nov 2024 15:54:28 +0000

Run "Create new Trysil multi-tenant API REST" from the Trysil menu.

Choose the folder, the name of the project, and whether to download the project template from HTTP (the template on the HTTP server is usually more up-to-date).

Enter the settings for your API REST: the base uri, the port, whether the API requires authentication, and whether it should write the HTTP traffic log to the database.

If you have decided to write the HTTP traffic log to a database, you will be asked for the type of database (you can choose between Firebird, Microsoft SQL Server, PostgreSQL or SQLite) and all the other parameters for the connection.

Enter the settings for the Windows service: the service name, name, and description that will appear in the list of services.

Now you need to enter the settings for the root tenant database (localhost) of your API REST.

You're done, your API REST is ready.

Enjoy!

Use Trysil to draw the Entity Model, create the scripts for the database, create the model and controller classes of your new API REST.

To learn more about "Trysil - Delphi ORM" visit the link to the Github project:

https://github.com/davidlastrucci/trysil

Trysil - TWhereClause

David Lastrucci — Mon, 19 Feb 2024 09:26:49 +0000

Introduzione
Uno degli attributi disponibili in "Trysil - Delphi ORM" è [TWhereClauseAttribute].

Cerco, in questo articolo, di spiegarne l'utilità.

Database
Prendiamo ad esempio la seguente tabella del database:

CREATE TABLE Users(
  ID int NOT NULL,
  Firstname nvarchar(255) NOT NULL,
  Lastname nvarchar(255) NOT NULL,
  UserType int NOT NULL,
  VersionID int NOT NULL,
  PRIMARY KEY (ID)
);

Stabiliamo che la colonna UserType potrà contenere i valori 0 (Insegnante) e 1 (Studente).

Aggiungiamo un indice alla tabella per ottimizzare i filtri:

CREATE INDEX Users_Index1 ON Users (UserType);

TUser
Definiamo il modello astratto per l'entità TUser:

{ TUser }

  [TTable('Users')]
  [TSequence('UsersID')]
  TUser = class abstract
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TColumn('Firstname')]
    FFirstname: String;

    [TColumn('Lastname')]
    FLastname: String;

    [TVersionColumn]
    [TColumn('VersionID')]
    FVersionID: TTVersion;
  strict protected
    [TColumn('UserType')]
    FUserType: Integer;
  public
    property ID: TTPrimaryKey read FID;
    property Firstname: String read FFirstname write FFirstname;
    property Lastname: String read FLastname write FLastname;
    property VersionID: TTVersion read FVersionID;
  end;

TTeacher
Specializziamo l'entità TTeacher:

{ TTeacher }

  [TWhereClause('UserType = 0')]
  TTeacher = class(TUser)
  public
    procedure AfterConstruction; override;
  end;

// ...

procedure TTeacher.AfterConstruction;
begin
  inherited AfterConstruction;
  FUserType := 0;
end;

TStudent
Specializziamo poi l'entità TStudent:

{ TStudent }

  [TWhereClause('UserType = 1')]
  TStudent = class(TUser)
    procedure AfterConstruction; override;
  end;

// ...

procedure TStudent.AfterConstruction;
begin
  inherited AfterConstruction;
  FUserType := 1;
end;

Trysil
Adesso siamo in grado di utilizzare TTeacher e TStudent con Trysil come se le due entità fossero persistite su due tabelle separate, in realtà entrambe andranno ad eseguire tutte le operazioni di CRUD sulla tabelle Users del database.

Per saperne di più su Trysil - Delphi ORM visita il link al progetto:

https://github.com/davidlastrucci/Trysil

Trysil - Delphi ORM

David Lastrucci — Mon, 19 Feb 2024 09:19:34 +0000

Perché continuare a scrivere codice così:

Query.SQL.Text :=
  'SELECT I.ID AS InvoiceID, I.Number, ' +
  'C.ID AS CustomerID, C.Name AS CustomerName, ' +
  'U.ID AS CountryID, U.Name AS CountryName ' +
  'FROM Invoices AS I ' +
  'INNER JOIN Customers AS C ON C.ID = I.CustomerID ' +
  'INNER JOIN Countries AS U ON U.ID = C.CountryID ' +
  'WHERE I.ID = :InvoiceID';
Query.ParamByName('InvoiceID').AsInteger := 1;
Query.Open;

ShowMessage(
  Format('Invoice No: %d, Customer: %s, Country: %s', [
    Query.FieldByName('Number').AsInteger,
    Query.FieldByName('CustomerName').AsString,
    Query.FieldByName('CountryName').AsString]));

Quando c'è la possibilità di scriverlo così?

LInvoice := FContext.Get<TInvoice>(1);
ShowMessage(
  Format('Invoice No: %d, Customer: %s, Country: %s', [
    LInvoice.Number,
    LInvoice.Customer.Name,
    LInvoice.Customer.Country.Name]));

Per maggiori informazioni visitail link al progetto Trysil:

https://github.com/davidlastrucci/Trysil