CRUD Web API with Delphi, DMVCFramework and PostgreSQL - Part 1

👉 This article is available also in italian.

👉 Questo articolo è disponibile anche in italiano.

Introduction

DMVCFramework is the most popular Delphi REST framework. It’s used to build web solution based on RESTful style or JSON-RPC protocol (or any kinds of “style” you prefer). It is a first-class framework; it reach the Richardson Maturity Model level 3 - a.k.a. “Glory of REST” - as M. Fowler call it in his famous paper about REST approaches. One of the main strength of DMVCFramework is the ability to build stand-alone applications without any implicit dependencies. That’s it, you can just build your API and deploy it without any framework, runtime etc. Another deployment strategy is to deploy the DMVCFramework APIs as Windows Service, as Apache module (for Windows and Linux), as Linux daemon or as IIS ISAPI (for Windows). All these kind of deployments allows a clean, fast and performant solution with minimal configuration.

Learning by practical examples is always great; therefore, this article will walk you through the creation of a simple but complete RESTful CRUD (create-read-update-delete) API using DMVCFramework. In this example I’ll use PostgreSQL as RDBMS. Yes, I love PostgreSQL, but DMVCFramework can be used with any kind of database engine of course or even no one. Though DMVCFramework can provide RESTful API and JSON-RPC APIs, the greater emphasis in this article will be on a RESTful API and database connectivity.

🔔 Please, note that code in this article works in dmvcframework-3.2.3-radium and newer versions. If you are forced to use an older version some small changes maybe required.

✅ dmvcframework-3.4.0-neon introduces many new features included the awaited functional actions. In a future version of this article I’m going to show how to implement the same API with functional actions. If you are interested in dmvcframework-3.4.0-neon feel free to check this article.

Designing the Web APIs

I strongly suggest to start you API project with API design; do not think about the storage, the deployment type or any others details - just start with the part of the system provided to your clients - the APIs.

In this case the API is quite simple. We need a simple CRUD on a single entity . Eventually the API will got some changes and we don’t want to break the clients, so we need to properly version it. Here’s the API we are going to implement.

GET /api/v1/customers (retrieve a list of customers)
GET /api/v1/customers/$id (retrieve the customer with id = $id)
POST /api/v1/customers (create a new customer)
PUT /api/v1/customers/$id (update the customer with id = $id)
DELETE /api/v1/customers/$id (delete the customer with id = $id)

As you can see, this API is really simple but we need to start somewhere. We’ll improve it in the next article parts. Being DMVCFramework RMM3 compliant (a.k.a. support HATEOAS) the “customer list” endpoint will provide the link to the other entities too.

Creating database structure

In this example our database is very simple: just a customers table. You can use any database you like but in this case we’ll use PostgreSQL. The script to create the customers table is the following.

Customers table DDL

CREATE TABLE customers (
 id int8 generated always as identity NOT NULL,
 code varchar(20) NULL,
 description varchar(200) NULL,
 city varchar(200) NULL,
 note text NULL,
 rating int2 NOT NULL DEFAULT 0,
 CONSTRAINT customers_pk PRIMARY KEY (id)
);

Quite simple, but effective for our purposes. I loaded in my table the following data.

Data manually loaded in the Customers table

Creating the application server

Installing and using DMVCFramework is quite simple, but if you need a detailed tutorial read the https://leanpub.com/delphimvcframework/read_sample[first free chapter] of my book https://leanpub.com/delphimvcframework[DMVCFramework - the official guide] (available also in Spanish and Portuguese). The full book is available at https://leanpub.com/delphimvcframework[Leanpub website].

Let’s create a new DMVCFramework project using the wizard provided using the settings shown below.

DMVCFramework Wizard

The wizard will generate all the needed files (dproj, dpr, pas and dfm) required to the project. Save all the files using the following names (these names are not mandatory but will be useful to easily refers to them):

File names to use when save project

At this point you should be able to run the project using Project->Run or just hitting F9. After the file saving, in some part of the code some units are still referenced using the old names, fix them using the names you choose.

Launching the project you should get something like the following.

DMVCFramework CRUDAPIVersion1.exe is running

Declare the Customer entity

While using DataSet and SQL can be handy for small project, in this example I’d like to use the MVCActiveRecord micro-framework included in DMVCFramework: it is fast, easy to use and full of handy features. Let’s add a new unit to the project, save it as Entities.Customer.pas and fill it with the following code.

TCustomer entity mapped on customers table

unit Entities.Customer;

interface

uses
  MVCFramework.Serializer.Commons,
  MVCFramework.ActiveRecord,
  MVCFramework.Nullables;

type
  [MVCNameCase(ncLowerCase)]
  [MVCTable('customers')]
  TCustomer = class(TMVCActiveRecord)
  private
    [MVCTableField('id', [foPrimaryKey, foAutoGenerated])]
    fID: NullableInt64;
    [MVCTableField('code')]
    fCode: NullableString;
    [MVCTableField('description')]
    fCompanyName: NullableString;
    [MVCTableField('city')]
    fCity: string;
    [MVCTableField('rating')]
    fRating: NullableInt32;
    [MVCTableField('note')]
    fNote: string;
  public
    property ID: NullableInt64 read fID write fID;
    property Code: NullableString read fCode write fCode;
    property CompanyName: NullableString read fCompanyName write fCompanyName;
    property City: string read fCity write fCity;
    property Rating: NullableInt32 read fRating write fRating;
    property Note: string read fNote write fNote;
  end;

implementation

end.

🔔 In this version of the APIs there isn’t any business logic, but we’ll add it in the next versions.

✅ TMVCActiveRecord is very powerful and is able to handle complex data structures (inheritance, relations, aggregates and more). To have an idea about what TMVCActiveRecord can do, check this article.

This TCustomer class maps the interesting fields of the “customers” table in our database. The attributes used in the declaration allows to completely describe the entity in terms of functionality, datatypes and nullability while there isn’t any information about the string length. The unit MVCFramework.Nullables.pas, used in the interface section, allows to use the nullable types introduced in DMVCFramework to better mimic the table fields behavior.

Declaring the Controller and its Actions

Controllers are the most visible part of a DMVCFramework app. Any app has at least one controller. The controller public methods a called “actions” and are the actual code executed after the router URL parsing.

Open the file named Controllers.Customers.pas and write the following code.

Customers controller - where the customers endpoint is implemented

unit Controllers.Customers;

interface

uses
  MVCFramework,
  MVCFramework.ActiveRecord,
  MVCFramework.Commons,
  Entities.Customer;

const
  BASE_API_V1 = '/api/v1';

type

  [MVCPath(BASE_API_V1 + '/customers')]
  TCustomersController = class(TMVCController)
  public
    [MVCPath]
    [MVCHTTPMethods([httpGET])]
    procedure GetCustomers(const [MVCFromQueryString('rql','')] RQL: String);

    [MVCPath('/($ID)')]
    [MVCHTTPMethods([httpGET])]
    procedure GetCustomerByID(const ID: Integer);

    [MVCPath('/($ID)')]
    [MVCHTTPMethods([httpPUT])]
    procedure UpdateCustomerByID(const ID: Integer);

    [MVCPath('/($ID)')]
    [MVCHTTPMethods([httpDELETE])]
    procedure DeleteCustomer(const ID: Integer);

    [MVCPath]
    [MVCHTTPMethods([httpPOST])]
    procedure CreateCustomers(const [MVCFromBody] Customer: TCustomer);
  end;

implementation

uses
  System.SysUtils,
  FireDAC.Comp.Client,
  FireDAC.Stan.Param,
  MVCFramework.Logger,
  MVCFramework.Serializer.Commons,
  JsonDataObjects;

{ TCustomersController }

procedure TCustomersController.CreateCustomers(const Customer: TCustomer);
begin
  Customer.Insert;
  Render201Created(BASE_API_V1 + '/customers/' + Customer.ID.Value.ToString);
end;

procedure TCustomersController.DeleteCustomer(const ID: Integer);
begin
  var lCustomer := TMVCActiveRecord.GetByPK<TCustomer>(ID);
  try
    lCustomer.Delete;
    Render204NoContent();
  finally
    lCustomer.Free;
  end;
end;

procedure TCustomersController.GetCustomerByID(const ID: Integer);
begin
  Render(ObjectDict().Add('data', TMVCActiveRecord.GetByPK<TCustomer>(ID)));
end;

procedure TCustomersController.GetCustomers(const RQL: String);
begin
  Render(ObjectDict().Add('data', TMVCActiveRecord.SelectRQL<TCustomer>(RQL, 100),
    procedure (const Customer: TObject; const Links: IMVCLinks)
    begin
      Links
        .AddRefLink
        .Add(HATEOAS.HREF, BASE_API_V1 + '/customers/' + TCustomer(Customer).ID.Value.ToString)
        .Add(HATEOAS.REL, 'self')
        .Add(HATEOAS._TYPE, 'application/json');
    end));
end;

procedure TCustomersController.UpdateCustomerByID(const ID: Integer);
begin
  var lCustomer := TMVCActiveRecord.GetByPK<TCustomer>(ID);
  try
    Context.Request.BodyFor<TCustomer>(lCustomer);
    lCustomer.Update;
    Render204NoContent(BASE_API_V1 + '/customers/' + lCustomer.ID.Value.ToString);
  finally
    lCustomer.Free;
  end;
end;

end.

Now we need to connect to the database. While this can be done in a number of way, here I’m suggest you to follow this approach, if feasible for you.

• Build the project and identify where the CRUDAPIVersion1.exe file is (probably in Win32/Debug or Win64/Debug if you didn’t change default output path and build configuration)

• At the same executable level, create a new file named FDConnectionDefs.ini and write the following text filling the data between < and > with your actual values.

[FDConnectionDefs.ini]
Encoding=UTF8

[crudapiversion1]
Database=<dbname>
User_Name=<dbusername>
Password=<dbpassword>
Server=<serverip>
Port=5432
DriverID=PG

This file is a standard FireDAC configuration file and is an handy way to create an external database configuration file because it is automatically loaded by FireDAC without any manual coding. In some environments you cannot use this clear-text file to store password and other database related information (for security reason), but can be useful in many server-side scenarios.

Now we have done all the pieces, but they still don’t talk with each other. Open the webmodule and change the code to looks like the following.

WebModule changes to register controller and middlewares

Done! Let’s run the project and use our API.

Testing the API

To use and test the CRUD API I’ll use the Insomnia REST client available from https://insomnia.rest[https://insomnia.rest]. If you don’t know Insomnia there are a https://www.google.com/search?q=insomnia+rest+tutorial[lot of tutorials] to start with it, check them out. If you are used to Postman or Embarcadero REST Debugger, it’s OK too.

Let’s start with a simple list of customers.

GET http://localhost:8080/api/v1/customers

{
  "data": [
    {
      "id": 4500,
      "code": "00008",
      "companyname": "Burger Inc.",
      "city": "Melbourne",
      "rating": 3,
      "note": "GAS",
      "links": [
        {
          "type": "application/json",
          "href": "/api/v1/customers/4500",
          "rel": "self"
        }
      ]
    },
    {
      "id": 4501,
      "code": "00086",
      "companyname": "Motors Corp.",
      "city": "New York",
      "rating": 0,
      "note": "House",
      "links": [
        {
          "type": "application/json",
          "href": "/api/v1/customers/4501",
          "rel": "self"
        }
      ]
    },
    {
      "id": 4503,
      "code": "00033",
      "companyname": "Motors Srl",
      "city": "New York",
      "rating": 2,
      "note": "Burger",
      "links": [
        {
          "type": "application/json",
          "href": "/api/v1/customers/4503",
          "rel": "self"
        }
      ]
    },
    {
      "id": 4504,
      "code": "00043",
      "companyname": "Burger Inc.",
      "city": "London",
      "rating": 2,
      "note": "Motors",
      "links": [
        {
          "type": "application/json",
          "href": "/api/v1/customers/4504",
          "rel": "self"
        }
      ]
    }
  ]
}

The result is automatically wrapped intro a json object with a data property array. This approach is a best practice and is quite useful and efficient compared to returning JSON array directly. Each customer object contains an additional “links” property with information related to the HATEOAS (that’s it, links to discover other related resources).

DMVCFramework has built in RQL support. Our little API already support all kind of filters using RQL. Let’s say we need all customers with id ≥ 4503 and id ≤ 4504 ordered by rating descending and city ascending.

GET http://localhost:8080/api/v1/customers?rql=and(ge(id,4503),le(id,4504));sort(-rating,+city)

{
  "data": [
    {
      "id": 4503,
      "code": "00033",
      "companyname": "Motors Srl",
      "city": "New York",
      "rating": 4,
      "note": "Burger",
      "links": [
        {
          "type": "application/json",
          "href": "/api/v1/customers/4503",
          "rel": "self"
        }
      ]
    },
    {
      "id": 4504,
      "code": "00043",
      "companyname": "Burger Inc.",
      "city": "London",
      "rating": 2,
      "note": "Motors",
      "links": [
        {
          "type": "application/json",
          "href": "/api/v1/customers/4504",
          "rel": "self"
        }
      ]
    }
  ]
}

Great! Let’s now create a new customer using the following POST request with the related body.

POST http://localhost:8080/api/v1/customers

Request body of the POST request

The API returns the newly created resource in the “location” header as shown below.

Response of the previous POST request

Using the returned URI and the PUT API we can modify the customer just created.

Request body of the PUT request

The default DMVCFramework behavior allows to do partial updates. Partial updates, by default, works using the following criteria:

• If the body request don’t contain the attribute, leave the value unchanged on the serialized object;
• If the body request contains the attribute update the mapped attribute on the object with the new value;
• If the body request contains the attribute with null value, set the attribute as null in the object mapped attribute;

So, shortly, if the attribute is not contained in the body request such attribute remains unchanged in the deserialized object. Here’s an example.

Request body of the PUT request to do partial updates

Conclusion

In this article we saw how DMVCFramework makes really simple to create powerful and RESTful APIs. It allows to automatically produce JSON structure starting from Plain Delphi objects. The builtin RQL support allows to easily implements resource filtering, ordering and limit. If you liked this article and the productivity of DMVCFrameowork, cannot miss the “DMVCFramework - the official guide book”. Leverage the power of REST and JSON-RPC using the most popular framework for Delphi.

What’s Next

In the next article of this series, available only for patreon users, we’ll start to improve the API with business logic, Swagger/OpenAPI, HATEOAS, JSON Web Token and nested resources. All these things are very simple to implement in DMVCFramework…

Stay tuned!