Gem #131 : Type-Safe Database API - Part 3

Let's get started...

A recent tendency in a lot of web frameworks, which is extending into other areas, is to hide SQL in the persistent layer of applications. Users want to manipulate objects, indicate that they should be made persistent somehow, and forget that this is done through SQL.

GNATCOLL.SQL.ORM provides such an API. It is fully compatible with GNATCOLL.SQL, so one can decide to mix explicit SQL queries, like the ones we discussed before, with persistent ORM objects.

To use the ORM, it's necessary to generate some additional code from a schema description, as we saw in the first part. For this, you need to pass an extra parameter "-orm" in the call to gnatcoll_db2ada:

gnatcoll_db2ada -dbname <name> -dbhost <host> -dbuser <user> 
   -orm ORM -api Database

Access to the database in the context of the ORM is slightly different. The connections are encapsulated in objects called Sessions. A session is basically responsible for executing the SQL queries, caching the results when appropriate, and committing or rolling back the changes. In fact, GNATColl provides a pool of such sessions so that the whole process is lighter weight: when you need a new session, you request it from the pool. If one is available, it's returned to you and it will not have to connect to the database again (which can be slow); otherwise, a new session and database connection are created. When the session is no longer needed, it's automatically returned to the pool.

So we first need to initialize this pool of sessions:

declare
  Session : GNATCOLL.SQL.Sessions.Session_Type;
begin
  GNATCOLL.SQL.Sessions.Setup
     (Descr => DB_Descr,   -- See second part of this Gem series
      Max_Sessions => 2);  -- Maximum number of concurrent sessions

  --  Then, when you need a session:
  Session := Get_New_Session;
  ...
end;

Using the same schema we had in the first two parts, here is how we could return all elements of Table1 that have a specific value for Field2:

with ORM;  use ORM;   -- The generated package

...

declare
   L : Table1_List := All_Table1   -- Generate function
                       .Filter (Field2 => "some value")
                       .Get (Session);
begin
   while L.Has_Row loop
      Put_Line (Integer'Image (L.Element.Field1));
      L.Next;
   end loop;
end;

The ORM is based on managers. There is one type of manager per table for a given schema. A manager is responsible for building the queries. In fact, the generated ORM package has one global instance of these types per table.

Here, we are using the All_Table1 manager. By default, this will return all elements of the table. We then filter the results by specifying that they should have a specific value for Field2. We can call Filter multiple times; there are no queries executed until we call Get on the third line.

We then retrieve a list of rows from Table1. The list behaves exactly like the Forward_Cursor we saw in the second part of the Gem series. It provides additional subprograms, so that what we actually manipulate is not just a row with fields, but an actual tagged object that corresponds to the schema, and so that instead of retrieving the first field of the row, we can simply retrieve "Field1". This is of course safer and more readable.

It's possible to write the filter so that it also involves the second table of the schema. The GNATColl documentation provides more details on how to do this. (Note that this is still an evolving API, which currently requires writing the JOIN explicitly through GNATCOLL.SQL, although the intent is to also hide this completely.)

But the ORM can also be used to modify objects from the database. Here is an example that retrieves a specific row from Table1, modifies some information and writes it back to the database.

declare
   T : Table1 := Get_Table1 (Session, Field1 => 2);  -- Lookup by id
begin
   T.Set_Field2 ("New value");
   T.Set_Field4 (...);   -- If it was defined in the schema
   Session.Commit;
end;

This is much more readable than the equivalent SQL query, is type safe, and is about as efficient (since in fact only one query is executed when Commit is called, where none were executed before).

In summary, using an ORM often provides queries that are easier to read. More importantly, it provides more convenient code to retrieve the result of the query, so that the code manipulates objects instead of rows.