Hi,
We’ve been using mORMot since version 1.18. We’ve successfully ported our codebase to mORMot 2 and have gradually become more proficient with the library.
We initially started using mORMot for its object dispatching capabilities across different web services and standards. The “35 - Practical DDD” demo was very useful in demonstrating the value of mORMot beyond the ORM itself.
However, we really wanted to use mORMot’s ORM for its own sake, because it makes implementing repositories incredibly fast.
The historical issue we faced is that our database is designed without integer ID columns, and instead relies on UUIDs, for specific reasons such as easier data replication.
As we became more familiar with mORMot’s internals, I discovered that we could actually use the ORM for our needs (mostly repositories and CreateJoined) with only a very minor tweak—literally a single line of code.
We had always assumed that we needed to redesign our foreign keys from UUIDs to integer IDs in order to use the ORM. This assumption was mostly due to our own lack of understanding of mORMot’s ORM design, which favors the principle of “one table, one repository” to avoid the usual speed and optimization problems that often occur when joining multiple tables at such a low level.
But since our database design is a couple of decades old, we have many aggregates that span across, for instance, two or three tables, so we really needed to use the CreateJoined functionality of the ORM to be able to build those aggregates easily. Using manual SQL (the solution proposed in the mORMot manual for UUID‑based databases) turns out to be long and error‑prone. With the ORM, it’s unbelievably easy to create a repository service that serves such an aggregate via the CreateJoined feature. The code is so short compared to the manual‑SQL approach.
The following is a good example, because years ago I decided to leave this aggregate out simply because coding the repository in manual SQL was so ridiculously complex. Recently I tried again using mORMot’s ORM, because I knew it required almost no code to achieve exactly the same result:
(I am aware that nowadays it’s a bit silly to create three tables for this kind of thing)
type
TOrmPaymentConditionExpiration = class({$IFDEF USE_MORMOT118}TSQLRecord{$ELSE}TOrm{$ENDIF})
private
FUUID: RawUTF8;
FPercentPart: Real;
FExpirationDays: Integer;
FBase: RawUTF8;
published
property UUID: RawUTF8 read FUUID write FUUID;
property PercentPart: Real read FPercentPart write FPercentPart;
property ExpirationDays: Integer read FExpirationDays write FExpirationDays;
property Base: RawUTF8 read FBase write FBase;
end;
TOrmPaymentConditionExpirationObjArray = array of TOrmPaymentConditionExpiration;
TOrmPaymentConditionBase = class({$IFDEF USE_MORMOT118}TSqlRecord{$ELSE}TOrm{$ENDIF})
private
FUUID: RawUTF8;
FDescription: RawUTF8;
FInstallments: SmallInt;
FExpirations: TOrmPaymentConditionExpirationObjArray;
FChangeTimeStamp: TModTime;
procedure GetAllExpirations(const aClient: {$IFDEF USE_MORMOT118}TSQLRest{$ELSE}IRestOrm{$ENDIF});
published
property UUID: RawUTF8 read FUUID write FUUID;
property Description: RawUTF8 read FDescription write FDescription;
property Installments: SmallInt read FInstallments write FInstallments;
property Expirations: TOrmPaymentConditionExpirationObjArray read FExpirations;
property ChangeTimeStamp: TModTime read FChangeTimeStamp write FChangeTimeStamp;
end;
TOrmPaymentCondition = class({$IFDEF USE_MORMOT118}TSqlRecord{$ELSE}TOrm{$ENDIF})
private
FUUID: RawUTF8;
FCode: RawUTF8;
FDescription: RawUTF8;
FChangeTimeStamp: TModTime;
FBase: TOrmPaymentConditionBase;
published
property UUID: RawUTF8 read FUUID write FUUID;
property Code: RawUTF8 read FCode write FCode;
property Description: RawUTF8 read FDescription write FDescription;
property ChangeTimeStamp: TModTime read FChangeTimeStamp write FChangeTimeStamp;
property Base: TOrmPaymentConditionBase read FBase write FBase;
end;
TPaymentConditionRepository = class(TNeoretailCQRSService, IPaymentConditionRepository)
private
FRestOrm: {$IFDEF USE_MORMOT118}TSQLRest{$ELSE}IRestOrm{$ENDIF};
protected
function GetPaymentCondition(const ACode: RawUTF8): TOrmPaymentCondition;
public
destructor Destroy; override;
procedure AfterConstruction; override;
function RetrievePaymentCondition(var APaymentCondition: TCondicionDePago): TPaymentConditionRepositoryError;
function SaveNewPaymentCondition(var APaymentCondition: TCondicionDePago): TPaymentConditionRepositoryError;
end;
implementation
function TPaymentConditionRepository.GetPaymentCondition(const ACode: RawUTF8): TOrmPaymentCondition;
var
RowID: Int64;
a: TOrmPaymentConditionExpiration;
begin
if FRestOrm.OneFieldValue(TOrmPaymentCondition, 'ID', 'Code=?', [ACode], [ACode], RowID) then
begin
result := TOrmPaymentCondition.CreateJoined(FRestOrm, RowID);
result.Base.GetAllExpirations(FRestOrm);
end
else
raise Exception.Create(Format('Record with code %s not found', [ACode]));
end;This simple and short code just works for quite a complex aggregate!
It turned out that the only required change was in the CreateJoined method, so that joins are performed using UUID fields instead of RowID.
This means that simply adding an ID column to the tables that mORMot works with is a very easy requirement to fulfill—much easier than changing all foreign keys. Keeping our UUID‑based foreign key system is very convenient and allows us to use mORMot’s ORM with a UUID‑based database design.
The only line of code we had to change was the following one in TOrmModel.SetTableProps:
// add LEFT JOIN clause
W.AddStrings([' FROM ', aTableName]);
for j := 1 to high(Props.Props.JoinedFieldsTable) do
begin
aFieldName := Props.Props.JoinedFields[j - 1].Name;
with Props.Props.JoinedFieldsTable[j].OrmProps do
// W.Add(' LEFT JOIN % AS % ON %.%=%.RowID',
W.Add(' LEFT JOIN % AS % ON %.%=%.UUID',
[SqlTableName, aFieldName, aTableName, aFieldName, aFieldName]);
end;It worked like a charm. The ORM is really impressive and useful.
I wanted to share this small change with you because you might find it feasible—or even desirable—to make this behavior configurable or available as a feature, so that more people with UUID‑based databases can adopt mORMot more easily. I know this change may seem trivial, but new users could certainly benefit from it being officially supported.
Thank you very much for creating such a great framework!
Hi,
We’ve been using mORMot since version 1.18. We’ve successfully ported our codebase to mORMot 2 and have gradually become more proficient with the library.
We initially started using mORMot for its object dispatching capabilities across different web services and standards. The “35 - Practical DDD” demo was very useful in demonstrating the value of mORMot beyond the ORM itself.
However, we really wanted to use mORMot’s ORM for its own sake, because it makes implementing repositories incredibly fast.
The historical issue we faced is that our database is designed without integer ID columns, and instead relies on UUIDs, for specific reasons such as easier data replication.
As we became more familiar with mORMot’s internals, I discovered that we could actually use the ORM for our needs (mostly repositories and
CreateJoined) with only a very minor tweak—literally a single line of code.We had always assumed that we needed to redesign our foreign keys from UUIDs to integer IDs in order to use the ORM. This assumption was mostly due to our own lack of understanding of mORMot’s ORM design, which favors the principle of “one table, one repository” to avoid the usual speed and optimization problems that often occur when joining multiple tables at such a low level.
But since our database design is a couple of decades old, we have many aggregates that span across, for instance, two or three tables, so we really needed to use the
CreateJoinedfunctionality of the ORM to be able to build those aggregates easily. Using manual SQL (the solution proposed in the mORMot manual for UUID‑based databases) turns out to be long and error‑prone. With the ORM, it’s unbelievably easy to create a repository service that serves such an aggregate via theCreateJoinedfeature. The code is so short compared to the manual‑SQL approach.The following is a good example, because years ago I decided to leave this aggregate out simply because coding the repository in manual SQL was so ridiculously complex. Recently I tried again using mORMot’s ORM, because I knew it required almost no code to achieve exactly the same result:
(I am aware that nowadays it’s a bit silly to create three tables for this kind of thing)
This simple and short code just works for quite a complex aggregate!
It turned out that the only required change was in the
CreateJoinedmethod, so that joins are performed using UUID fields instead ofRowID.This means that simply adding an ID column to the tables that mORMot works with is a very easy requirement to fulfill—much easier than changing all foreign keys. Keeping our UUID‑based foreign key system is very convenient and allows us to use mORMot’s ORM with a UUID‑based database design.
The only line of code we had to change was the following one in
TOrmModel.SetTableProps:It worked like a charm. The ORM is really impressive and useful.
I wanted to share this small change with you because you might find it feasible—or even desirable—to make this behavior configurable or available as a feature, so that more people with UUID‑based databases can adopt mORMot more easily. I know this change may seem trivial, but new users could certainly benefit from it being officially supported.
Thank you very much for creating such a great framework!