ORM mapping class fields to external table columns

When working with an ORM, you have mainly two possibilites:

  1. Start from scratch, i.e. write your classes and let the ORM creates all the database structure - it is also named "code-first";
  2. From an existing database, you define in your model how your classes map the existing database structure - this is "database-first".

We have just finalized ORM external table field mapping in mORMot, using e.g.
See this last commit.

So you can write e.g.

fProperties := TSQLDBSQLite3ConnectionProperties.Create(

Then you use your TSQLRecordPeopleExt table as usual from Delphi code, with ID and YearOfDeath fields:

  • The "internal" TSQLRecord class will be stored within the PeopleExternal external table;
  • The "internal" TSQLRecord.ID field will be an external "Key: INTEGER" column;
  • The "internal" TSQLRecord.YearOfDeath field will be an external "YOD: BIGINT" column;
  • Other internal published properties will be mapped by default with the same name to external column.

Continue reading


Support of MySQL, DB2 and PostgreSQL

We just tested, benchmarked and validated Oracle MySQL, IBM DB2 and PostgreSQL support for our SynDB database classes and the mORMot's ORM core.
This article will also show all updated results, including our newly introduced multi-value INSERT statement generations, which speed up a lot BATCH insertion.

Stay tuned!

Purpose here is not to say that one library or database is better or faster than another, but publish a snapshot of mORMot persistence layer abilities, depending on each access library.

In this timing, we do not benchmark only the "pure" SQL/DB layer access (SynDB units), but the whole Client-Server ORM of our framework.

Process below includes all aspects of our ORM:

  • Access via high level CRUD methods (Add/Update/Delete/Retrieve, either per-object or in BATCH mode);
  • Read and write access of TSQLRecord instances, via optimized RTTI;
  • JSON marshaling of all values (ready to be transmitted over a network);
  • REST routing, with security, logging and statistic;
  • Virtual cross-database layer using its SQLite3 kernel;
  • SQL on-the-fly generation and translation (in virtual mode);
  • Access to the database engines via several libraries or providers.

In those tests, we just bypassed the communication layer, since TSQLRestClient and TSQLRestServer are run in-process, in the same thread - as a TSQLRestServerDB instance. So you have here some raw performance testimony of our framework's ORM and RESTful core, and may expect good scaling abilities when running on high-end hardware, over a network.

On a recent notebook computer (Core i7 and SSD drive), depending on the back-end database interfaced, mORMot excels in speed, as will show the following benchmark:

  • You can persist up to 570,000 objects per second, or retrieve 870,000 objects per second (for our pure Delphi in-memory engine);
  • When data is retrieved from server or client 38, you can read more than 900,000 objects per second, whatever the database back-end is;
  • With a high-performance database like Oracle, and our direct access classes, you can write 70,000 (via array binding) and read 160,000 objects per second, over a 100 MB network;
  • When using alternate database access libraries (e.g. Zeos, or DB.pas based classes), speed is lower (even if comparable for DB2, MS SQL, PostgreSQL, MySQL) but still enough for most work, due to some optimizations in the mORMot code (e.g. caching of prepared statements, SQL multi-values insertion, direct export to/from JSON, SQlite3 virtual mode design, avoid most temporary memory allocation...).

Difficult to find a faster ORM, I suspect.

Continue reading


RESTful mORMot

Our Synopse mORMot Framework was designed in accordance with Fielding's REST architectural style without using HTTP and without interacting with the World Wide Web.
Such Systems which follow REST principles are often referred to as "RESTful".

Optionally, the Framework is able to serve standard HTTP/1.1 pages over the Internet (by using the mORMotHttpClient / mORMotHttpServer units and the TSQLHttpServer and TSQLHttpClient classes), in an embedded low resource and fast HTTP server.

Continue reading

REpresentational State Transfer (REST)

Representational state transfer (REST) is a style of software architecture for distributed hypermedia systems such as the World Wide Web.
As such, it is not just a method for building "web services". The terms "representational state transfer" and "REST" were introduced in 2000 in the doctoral dissertation of Roy Fielding, one of the principal authors of the Hypertext Transfer Protocol (HTTP) specification, on which the whole Internet rely.


There are 5 basic fundamentals of web which are leveraged to create REST services:

  1. Everything is a Resource;
  2. Every Resource is Identified by a Unique Identifier;
  3. Use Simple and Uniform Interfaces;
  4. Communication is Done by Representation;
  5. Every Request is Stateless.

Continue reading


Some enhancements to REST routing of interface-based services

We have just committed some enhancements to interface-based service process.

TSQLRestRoutingREST will now recognize several URI schemes, like new  root/Calculator/Add?n1=1&n2=2 alternative could be pretty convenient to be consumed from some REST clients.

Please find here a documentation update.

Continue reading


AES encryption over HTTP

In addition to regular HTTPS flow encryption, which is not easy to setup due to the needed certificates, mORMot proposes a proprietary encryption scheme. It is based on SHA-256 and AES-256/CTR algorithms, so is known to be secure.

You do not need to setup anything on the server or the client configuration, just run the TSQLHttpClient and TSQLHttpServer classes with the corresponding parameters.

Continue reading


JSON record serialization

In Delphi, the record has some nice advantages:

  • record are value objects, i.e. accessed by value, not by reference - this can be very convenient, e.g. when defining a Domain-Driven Design
  • record can contain any other record or dynamic array, so are very convenient to work with (no need to define sub-classes or lists); 
  • record variables can be allocated on stack, so won't solicit the global heap; 
  • record instances automatically freed by the compiler when they come out of scope, so you won't need to write any try..finally Free; end block.

Serialization of record values are therefore a must-have for a framework like mORMot.

In recent commits, this JSON serialization of record has been enhanced.
In particular, we introduced JSON serialization via a new text-based record definition.

Continue reading


Updated mORMot database benchmark - including MS SQL and PostgreSQL

On an recent notebook computer (Core i7 and SSD drive), depending on the back-end database interfaced, mORMot excels in speed:

  • You can persist up to 570,000 objects per second, or retrieve more than 900,000 objects per second (for our pure Delphi in-memory engine);
  • When data is retrieved from server or client cache, you can read more than 900,000 objects per second, whatever the database back-end is;
  • With a high-performance database like Oracle and our direct access classes, you can write 65,000 (via array binding) and read 160,000 objects per second, over a 100 MB network;
  • When using alternate database access libraries (e.g. Zeos, or DB.pas based classes), speed is lower, but still enough for most work.

Difficult to find a faster ORM, I suspect.

The following tables try to sum up all available possibilities, and give some benchmark (average objects/second for writing or read).

In these tables:

  • 'SQLite3 (file full/off/exc)' indicates use of the internal SQLite3 engine, with or without Synchronous := smOff and/or DB.LockingMode := lmExclusive;
  • 'SQLite3 (mem)' stands for the internal SQLite3 engine running in memory;
  • 'SQLite3 (ext ...)' is about access to a SQLite3 engine as external database - either as file or memory;
  • 'TObjectList' indicates a TSQLRestServerStaticInMemory instance, either static (with no SQL support) or virtual (i.e. SQL featured via SQLite3 virtual table mechanism) which may persist the data on disk as JSON or compressed binary;
  • 'Oracle' shows the results of our direct OCI access layer (SynDBOracle.pas);
  • 'NexusDB' is the free embedded edition, available from official site;
  • 'Zeos *' indicates that the database was accessed directly via the ZDBC layer;
  • 'FireDAC *' stands for FireDAC library;
  • 'UniDAC *' stands for UniDAC library;
  • 'BDE *' when using a BDE connection;
  • 'ODBC *' for a direct access to ODBC;
  • 'Jet' stands for a MSAccess database engine, accessed via OleDB;
  • 'MSSQL local' for a local connection to a MS SQL Express 2008 R2 running instance (this was the version installed with Visual Studio 2010), accessed via OleDB.

This list of database providers is to be extended in the future. Any feedback is welcome!

Numbers are expressed in rows/second (or objects/second). This benchmark was compiled with Delphi 7, so newer compilers may give even better results, with in-lining and advanced optimizations.

Note that these tests are not about the relative speed of each database engine, but reflect the current status of the integration of several DB libraries within the mORMot database access.

Purpose here is not to say that one library or database is better or faster than another, but publish a snapshot of current mORMot persistence layer abilities.

In this timing, we do not benchmark only the "pure" SQL/DB layer access (SynDB units), but the whole Client-Server ORM of our framework: process below includes read and write RTTI access of a TSQLRecord, JSON marshaling, CRUD/REST routing, virtual cross-database layer, SQL on-the-fly translation. We just bypass the communication layer, since TSQLRestClient and TSQLRestServer are run in-process, in the same thread - as a TSQLRestServerDB instance. So you have here some raw performance testimony of our framework's ORM and RESTful core.

You can compile the "15 - External DB performance" supplied sample code, and run the very same benchmark on your own configuration.

Continue reading


Thread-safety of mORMot

We tried to make mORMot at the same time fast and safe, and able to scale with the best possible performance on the hardware it runs on.
Multi-threading is the key to better usage of modern multi-core CPUs, and also client responsiveness.

As a result, on the Server side, our framework was designed to be thread-safe.

On typical production use, the mORMot HTTP server will run on its own optimized thread pool, then call the TSQLRestServer.URI method. This method is therefore expected to be thread-safe, e.g. from the TSQLHttpServer. Request method. Thanks to the RESTful approach of our framework, this method is the only one which is expected to be thread-safe, since it is the single entry point of the whole server. This KISS design ensure better test coverage.

Let us see now how this works, and publish some benchmarks to test how efficient it has been implemented.

Continue reading


Summer videos of mORMot

During this summer, warleyalex did meet some mORMots in the mountains of REST, Java, AJAX and JSON. (picture may differ from actual user:) ) He did some videos of his experiment with our little rodent. At this time, there are 11 videos available! Latest one is a Java client application,  […]

Continue reading


Let mORmot's applications be even more responsive

In mORmot applications, all the client communication is executed by default in the current thread, i.e. the main thread for a typical GUI application.
This may become an issue in some reported environments.

Since all communication is performed in blocking mode, if the remote request takes long to process (due to a bad/slow network, or a long server-side action), the application may become unresponsive, from the end-user experience.
Even Windows may be complaining about a "non responsive application", and may propose to kill the process, which is far away from an expected behavior.

In order to properly interacts with the user, a OnIdle property has been defined in TSQLRestClientURI, and will change the way communication is handled.
If a callback event is defined, all client communication will be processed in a background thread, and the current thread (probably the main UI thread) will wait for the request to be performed in the background, running the OnIdle callback in loop in the while.

Continue reading


SQLite3 performance in Exclusive file locking mode

As stated in previous blog articles, the default SQlite3 write speed is quite slow, when running on a normal hard drive. By default, the engine will pause after issuing a OS-level write command. This guarantees that the data is written to the disk, and features the ACID properties of the database engine.

ACID is an acronym for "Atomicity Consistency Isolation Durability" properties, which guarantee that database transactions are processed reliably: for instance, in case of a power loss or hardware failure, the data will be saved on disk in a consistent way, with no potential loss of data.

In SQLite3, ACID is implemented by two means at file level:
- Synchronous writing: it means that the engine will wait for any written content to be flushed to disk before processing the next request;
- File locking: it means that the database file is locked for exclusive use during writing, allowing several processes to access the same database file concurrently.

Changing these default settings can ensure much better writing performance.

We just added direct File locking tuning.
It appears that defining exclusive access mode is able to increase the performance a lot, in both reading and writing speed.

Here are some new benchmarks and data, extracted from the updated SAD documentation.

Continue reading


Authentication and Authorization

Our mORMot framework tries to implement security via:
- Process safety;
- Authentication;
- Authorization.

Process safety is implemented at every n-Tier level:
- Atomicity of the SQLite3 database core;
- RESTful architecture to avoid most synchronization issues;
- ORM associated to the Object pascal strong type syntax;
- Extended test coverage of the framework core.

Authentication allows user identification:
- Build-in optional authentication mechanism, implementing both per-user sessions and individual REST Query Authentication;
- Authentication groups are used for proper authorization;
- Several authentication schemes, from very secure SHA-256 based challenging to weak but simple authentication;
- Class-based architecture, allowing custom extension.

Authorization of a given process is based on the group policy, after proper authentication:
- Per-table access right functionalities built-in at lowest level of the framework;
- Per-method execution policy for interface-based services;
- General high-level security attributes, for SQL or Service remote execution.

We will now give general information about both authentication and authorization in the framework.

In particular, authentication is now implemented via a set of classes.

Continue reading


mORMots know how to swim like fishes

Another great video by warleyalex. This time, a full FishFacts demo in AJAX, using mORMot and its SQLite3 ORM as server. See it on YouTube! Feedback is welcome on our forum. Update: I've just uploaded the corresponding source code to our repository. See sample 19 - AJAX ExtJS FishFacts. You need to  […]

Continue reading


Two videos about EXTjs client of mORMot server

Two nice videos, posted by a framework user. The first one presents a remote RESTful access of a SQLite3 database, hosted by a mORMot server: After one post in the forum, warleyalex was able to easily add remote filtering of the request: In addition to the previous video about security (by which the  […]

Continue reading


64 bit compatibility of mORMot units

I'm happy to announce that mORMot units are now compiling and working great in 64 bit mode, under Windows.
Need a Delphi XE2/XE3 compiler, of course!

ORM and services are now available in Win64, on both client and server sides.
Low-level x64 assembler stubs have been created, tested and optimized.
UI part is also available... that is grid display, reporting (with pdf export and display anti-aliasing), ribbon auto-generation, SynTaskDialog, i18n... the main SynFile demo just works great!

Overall impression is very positive, and speed is comparable to 32 bit version (only 10-15% slower).

Speed decrease seems to be mostly due to doubled pointer size, and some less optimized part of the official Delphi RTL.
But since mORMot core uses its own set of functions (e.g. for JSON serialization, RTTI support or interface calls or stubbing), we were able to release the whole 64 bit power of your hardware.

Delphi 64 bit compiler sounds stable and efficient. Even when working at low level, with assembler stubs.
Generated code sounds more optimized than the one emitted by FreePascalCompiler - and RTL is very close to 32 bit mode.
Overall, VCL conversion worked as easily than a simple re-build.
Embarcadero's people did a great job for VCL Win64 support, here!

Continue reading


Introducing ZEOS, UniDAC, NexusDB, BDE, any TDataset to SynDB and mORMot's ORM

Up to now, our SynDB database classes were handling ODBC, OleDB providers and direct Oracle or SQLite3 connection.

We have added a DB.pas based layer, ready to be used with UniDAC, NexusDB, or the BDE.
Any other TDataset based component is ready to be interfaced, including UIB, AnyDAC or DBExpress.

The ZEOS library (in its latest 7.0.3 stable version, which works from Delphi 7 up to XE3) has also been interfaced, but without the TDataset/DB.pas layer: our SynDBZEOS.pas unit calls the ZDBC layer, which is not tied to DB.pas nor its RAD components, and is therefore faster. By the way, it will work also with the Starter edition of Delphi (which does not include the DB components) - just like the other "regular" SynDB classes.

This is a work in progress, any testing and feedback is welcome!
We had to circumvent some particularities of the libraries, but I guess we have something interesting.

A dedicated "SynDBDataset" sub-folder has been created in the repository, to contain all SynDBDataset.pas-based database providers.
SynDBNexusDB.pas unit has been moved within this sub-folder, as SynDBUniDAC.pas + SynDBBDE.pas units have been added.
SynDBZeos.pas has a direct access to the ZDBC layer, so is not part of the "SynDBDataset" sub-folder.

Here is some benchmark, mainly about Oracle and SQlite3 database access.
Of course, our direct SynDBOracle / SynDBSQLite3 layers are the fastest around, and we can see that ZDBC layer is sometimes more efficient than the TDataset components.

Continue reading


Enhance existing projects with mORMot

Even if mORMot will be more easily used in a project designed from scratch, it fits very well the purpose of evolving any existing Delphi project, or even creating the server side part of an AJAX application. 

One benefit of such a framework is to facilitate the transition from a Client-Server architecture to a N-Tier layered pattern.

Continue reading


Transmission between mORMots

Little mORMots have a very efficient transmission protocol, in their mountains...

Most mORMots are highly social and use loud whistles to communicate with one another, especially when alarmed.
As stated by Wikipedia

In a comment of the already quoted blog article about DataSnap performance issues, Tom asked this interesting question about mORMot, after I presented how we like to use http.sys kernel-mode server to achieve best performance possible over HTTP:

What about TCP communication, http is not the only Internet protocol. Protobuf, Thrift, MsgPack, BSON etc, is binary protocol and http i big overhead in some systems.
Did http.sys enable use of tcp/ip socket protocol?

Continue reading


WinRT support for XE3

Apart the sad and concerning license change issue (which has been confirmed by David I. himself), XE3 has some features, in order to support Windows 8 new 'tile-based' interface (formerly known as "Metro").

Windows Runtime, or WinRT (not to be confused with Windows RT, which is a tablet manufacturer only version of Windows 8) is a cross-platform application architecture on the Windows 8 operating system.
WinRT supports development in C++/CX (Component Extensions, a language based on C++) and the managed languages C# and VB.NET, as well as JavaScript.
WinRT applications natively support both the x86 and ARM architectures, and also run inside a sandboxed environment to allow for greater security and stability.
WinRT will also be part of the upcoming Windows Phone 8 operating systems.
(source: Wikipedia)

It has been clearly stated that only Microsoft compilers and runtime libraries (RTL) will be able to have full access to the low-level API needed to create a decent RTL.
This has been done for security reasons, but it won't allow third-party JIT or compilers to work as expected. Only Microsoft's C++ and C# compilers / virtual machines have access to the needed API. Even if you do not have a JIT in your language (Delphi is compiled and do not have any virtual machine), you would need to access to some low-level API calls e.g. to mark some memory block as executable (e.g. for virtual methods stubbing).

So Delphi is not able to have native support of WinRT, due to this limitation.

This is a known fact, but let us tell about "Windows 8 sideloading" feature, available with XE3.
In short, even if you do not have 100% WinRT application, XE3 "Metropolis" (sic) styled Desktop applications have some potential to behave like native UI applications, even if not being native.

Continue reading

- page 2 of 4 -