MongoDB + ORM = ODM

The mORMotMongoDB.pas unit is able to let any TSQLRecord class be persisted on a remote MongoDB server.

As a result, our ORM is able to be used as a NoSQL and Object-Document Mapping (ODM) framework, with almost no code change.
Any MongoDB database can be accessed via RESTful commands, using JSON over HTTP.

This integration benefits from the other parts of the framework (e.g. our UTF-8 dedicated process, which is also the native encoding for BSON), so you can easily mix SQL and NoSQL databases with the exact same code, and are still able to tune any SQL or MongoDB request in your code, if necessary.

Register the TSQLRecord class

In the database model, we define a TSQLRecord class, as usual:

  TSQLORM = class(TSQLRecord)
    fAge: integer;
    fName: RawUTF8;
    fDate: TDateTime;
    fValue: variant;
    fInts: TIntegerDynArray;
    fCreateTime: TCreateTime;
    fData: TSQLRawBlob;
    property Name: RawUTF8 read fName write fName stored AS_UNIQUE;
    property Age: integer read fAge write fAge;
    property Date: TDateTime read fDate write fDate;
    property Value: variant read fValue write fValue;
    property Ints: TIntegerDynArray index 1 read fInts write fInts;
    property Data: TSQLRawBlob read fData write fData;
    property CreateTime: TCreateTime read fCreateTime write fCreateTime;

Note that we did not define any index ... values for the RawUTF8 property, as we need for external SQL databases, since MongoDB does not expect any restriction about text fields length.

The property values will be stored in the native MongoDB layout, i.e. with a better coverage than the SQL types:

Delphi MongoDB Remarks
byte int32
word int32
integer int32
cardinal N/A You should use Int64 instead
Int64 int64
boolean boolean 0 is false, anything else is true
enumeration int32 store the ordinal value of the enumerated item(i.e. starting at 0 for the first element)
set int32 each bit corresponding to an enumerated item (therefore a set of up to 64 elements can be stored in such a field)
single double
double double
extended double stored as double (precision lost)
currency double safely converted to/from currency type with fixed decimals, without rounding error
RawUTF8 UTF-8 this is the preferred field type for storing some textual content in the ORM
WinAnsiString UTF-8 WinAnsi char-set (code page 1252) in Delphi
RawUnicode UTF-8 UCS2 char-set in Delphi, as AnsiString
WideString UTF-8 UCS2 char-set, as COM BSTR type (Unicode in all version of Delphi)
SynUnicode UTF-8 Will be either WideString before Delphi 2009, or UnicodeString later
string UTF-8 Not to be used before Delphi 2009 (unless you may loose some data during conversion) - RawUTF8 is preferred in all cases
TDateTime datetime ISO 8601 encoded date time
TTimeLog int64 as proprietary fast Int64 date time
TModTime int64 the server date time will be stored when a record is modified (as proprietary fast Int64)
TCreateTime int64 the server date time will be stored when a record is created (as proprietary fast Int64)
TSQLRecord int32 RowID pointing to another record (warning: the field value contains pointer(RowID), not a valid object instance - the record content must be retrieved with late-binding via its ID using a PtrInt(Field) typecast or the Field.ID method), or by using e.g. CreateJoined()
TSQLRecordMany nothing data is stored in a separate pivot table; for MongoDB, you should better use data sharding, and an embedded sub-document
TRecordReference int32 store both ID and TSQLRecord type in a RecordRef-like value (use e.g. TSQLRest. Retrieve(Reference) to get a record content)
TPersistent object BSON object (from ObjectToJSON)
TCollection array BSON array of objects (from ObjectToJSON)
TObjectList array BSON array of objects (from ObjectToJSON) - see TJSONSerializer. RegisterClassForJSON TObjectList serialization
TStrings array BSON array of strings (from ObjectToJSON)
TRawUTF8List array BSON array of string (from ObjectToJSON)
any TObject object See TJSONSerializer. RegisterCustomSerializer TObject serialization
TSQLRawBlob binary BSON binary for blob storage - note that you need to retrieve explicitly such fields, which are not part of the default Retrieve() method; this type is an alias to RawByteString
dynamic arrays array
if the dynamic array can be saved as true JSON, will be stored as BSON array - otherwise, will be stored in the TDynArray.SaveTo binary format - note that TByteDynArray will always be stored as BSON binary, so that TSQLRawBlob may be reserved to store content on GridFS in the future
variant array
BSON number, text, object or array, depending on TDocVariant custom variant type or TBSONVariant stored value
record binary
BSON as defined in code by overriding TSQLRecord.InternalRegisterCustomProperties

On the server side (there won't be any difference for the client), you define a TMongoDBClient, and assign it to a given TSQLRecord class:

  MongoClient := TMongoClient.Create('localhost',27017);
  DB := MongoClient.Database['dbname'];
  Model := TSQLModel.Create([TSQLORM]);
  Client := TSQLRestClientDB.Create(Model,nil,':memory:',TSQLRestServerDB);
  if StaticMongoDBRegister(TSQLORM,fClient.Server,fDB,'collectionname')=nil then
    raise Exception.Create('Error');

And... that's all!

You can then use any ORM command, as usual:


As with external databases, you can specify the field names mapping between the objects and the MongoDB collection.
By default, the TSQLRecord.ID property is mapped to the MongoDB's _id field, and the ORM will populate this _id field with a sequence of integer values, just like any TSQLRecord table.
You can specify your own mapping, using for instance:


Since the field names are stored within the document itself, it may be a good idea to use shorter naming for the MongoDB collection. It may save some storage space, when working with a huge number of documents.

Once the TSQLRecord is mapped to a MongoDB collection, you can always have direct access to the TMongoCollection instance later on, by calling:

 (aServer.StaticDataServer[aClass] as TSQLRestServerStaticMongoDB).Collection

This may allow any specific task, including any tuned query or process.

ORM/ODM CRUD methods

You can add documents with the standard CRUD methods of the ORM, as usual:

  R := TSQLORM.Create;
    for i := 1 to COLL_COUNT do begin
      R.Name := 'Name '+Int32ToUTF8(i);
      R.Age := i;
      R.Date := 1.0*(30000+i);
      R.Value := _ObjFast(['num',i]);
      R.Ints := nil;

As we already saw, the framework is able to handle any kind of properties, including complex types like dynamic arrays or variant.
In the above code, a TDocVariant document has been stored in R.Value, and a dynamic array of integer values is accessed via its index 1 shortcut and the TSQLRecord.DynArray() method.

The usual Retrieve / Delete / Update methods are available:

  R := TSQLORM.Create;
    for i := 1 to COLL_COUNT do begin
      // here R instance contains all values of one document, excluding BLOBs

You can define a WHERE clause, as if the back-end where a regular SQL database:

    R := TSQLORM.CreateAndFillPrepare(Client,'ID=?',[i]);

Current implementation understand one condition over one single field, with = > >= < <= IN clauses. More advanced queries are possible, but won't be handled as SQL, but via direct access to the TMongoDBCollection.

To perform a query and retrieve the content of several documents, you can use regular CreateAndFillPrepare or FillPrepare methods:

  R := TSQLORM.CreateAndFillPrepare(Client,'');
    n := 0;
    while R.FillOne do begin
      // here R instance contains all values of one document, excluding BLOBs

A WHERE clause can also be defined for CreateAndFillPrepare or FillPrepare methods.

BATCH mode

In addition to individual CRUD operations, our MongoDB is able to use BATCH mode for adding or deleting documents.

You can write the exact same code as with any SQL back-end:

  R := TSQLORM.Create;
    for i := 1 to COLL_COUNT do begin
      R.Name := 'Name '+Int32ToUTF8(i);
      R.Age := i;
      R.Date := 1.0*(30000+i);
      R.Value := _ObjFast(['num',i]);
      R.Ints := nil;

Or for deletion:

  for i := 5 to COLL_COUNT do
    if i mod 5=0 then

Speed benefit may be huge in regard to individual Add/Delete operations, even on a local MongoDB server. We will see some benchmark numbers now.

ORM/ODM performance

You can take a look at Data access benchmark to compare MongoDB as back-end for our ORM classes.

In respect to external SQL engines, it features very high speed, low CPU use, and almost no difference in use. We interfaced the BatchAdd() and BatchDelete() methods to benefit of MongoDB BULK process, and avoided most memory allocation during the process.

Here are some numbers, extracted from the MongoDBTests.dpr sample, which reflects the performance of our ORM/ODM, depending on the Write Concern mode used:

2. ORM

2.1. ORM with acknowledge: - Connect to local server: 6 assertions passed 18.65ms - Insert: 5,002 assertions passed 521.25ms 5000 rows inserted in 520.65ms i.e. 9603/s, aver. 104us, 2.9 MB/s - Insert in batch mode: 5,004 assertions passed 65.37ms 5000 rows inserted in 65.07ms i.e. 76836/s, aver. 13us, 8.4 MB/s - Retrieve: 45,001 assertions passed 640.95ms 5000 rows retrieved in 640.75ms i.e. 7803/s, aver. 128us, 2.1 MB/s - Retrieve all: 40,001 assertions passed 20.79ms 5000 rows retrieved in 20.33ms i.e. 245941/s, aver. 4us, 27.1 MB/s - Retrieve one with where clause: 45,410 assertions passed 673.01ms 5000 rows retrieved in 667.17ms i.e. 7494/s, aver. 133us, 2.0 MB/s - Update: 40,002 assertions passed 681.31ms 5000 rows updated in 660.85ms i.e. 7565/s, aver. 132us, 2.4 MB/s - Blobs: 125,003 assertions passed 2.16s 5000 rows updated in 525.97ms i.e. 9506/s, aver. 105us, 2.4 MB/s - Delete: 38,003 assertions passed 175.86ms 1000 rows deleted in 91.37ms i.e. 10944/s, aver. 91us, 2.3 MB/s - Delete in batch mode: 33,003 assertions passed 34.71ms 1000 rows deleted in 14.90ms i.e. 67078/s, aver. 14us, 597 KB/s Total failed: 0 / 376,435 - ORM with acknowledge PASSED 5.00s
2.2. ORM without acknowledge: - Connect to local server: 6 assertions passed 16.83ms - Insert: 5,002 assertions passed 179.79ms 5000 rows inserted in 179.15ms i.e. 27908/s, aver. 35us, 3.9 MB/s - Insert in batch mode: 5,004 assertions passed 66.30ms 5000 rows inserted in 31.46ms i.e. 158891/s, aver. 6us, 17.5 MB/s - Retrieve: 45,001 assertions passed 642.05ms 5000 rows retrieved in 641.85ms i.e. 7789/s, aver. 128us, 2.1 MB/s - Retrieve all: 40,001 assertions passed 20.68ms 5000 rows retrieved in 20.26ms i.e. 246718/s, aver. 4us, 27.2 MB/s - Retrieve one with where clause: 45,410 assertions passed 680.99ms 5000 rows retrieved in 675.24ms i.e. 7404/s, aver. 135us, 2.0 MB/s - Update: 40,002 assertions passed 231.75ms 5000 rows updated in 193.74ms i.e. 25807/s, aver. 38us, 3.6 MB/s - Blobs: 125,003 assertions passed 1.44s 5000 rows updated in 150.58ms i.e. 33202/s, aver. 30us, 2.6 MB/s - Delete: 38,003 assertions passed 103.57ms 1000 rows deleted in 19.73ms i.e. 50668/s, aver. 19us, 2.4 MB/s - Delete in batch mode: 33,003 assertions passed 47.50ms 1000 rows deleted in 364us i.e. 2747252/s, aver. 0us, 23.4 MB/s Total failed: 0 / 376,435 - ORM without acknowledge PASSED 3.44s

As for direct MongoDB access, the wcUnacknowledged is not to be used on production, but may be very useful in some particular scenarios.
As expected, the reading process is not impacted by the Write Concern mode set.

You can take a look at the previous blog article, about low-level MongoDB direct access.
Or check out more complete benchmark results, to compare MongoDB with other SQL back-end handled by our ORM.
Feedback is welcome on our forum, as usual!