Writing a CRUD Web API with Delphi, DMVCFramework and PostgreSQL — Part 1
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 Apache module (for Windows and Linux) 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 uses dmvcframework-3.2.2-nitrogen. If someone would like the use the same code with older version some small changes are required.
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 the 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.
Quite simple, but effective for our purposes. I loaded in my table the following data.
Creating the application server
Installing and using DMVCFramework is quite simple, but if you need a detailed tutorial read the first (free) chapter of my book “DMVCFramework — the official guide” (available also in Spanish and Portuguese). If you like the project, why don’t buy the whole book too? 😉
Let’s create a new DMVCFramework project using the wizard provided using the settings shown below.
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):
- Project Name = CRUDAPIVersion1.dproj
- WebModule Name = WebModuleU.pas
- Controller Name = Controllers.Customers.pas
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.
Now it’s time for a a beautiful but completely unrelated picture… 😁
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.
In this version of the APIs there isn’t any business logic, but we’ll add it in the next versions.
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.
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.
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.
Done! Let’s run the project and use our API.
Using the API
To use and test the CRUD API I’ll use the Insomnia REST client available from https://insomnia.rest/. If you don’t know Insomnia there are a lot of tutorials to start with it, check them out).
Let’s start with a simple list of customers.
GET http://localhost:8080/api/v1/customers
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)
Great! Let’s now create a new customer using the following POST request with the related body
POST http://localhost:8080/api/v1/customers
The API returns the newly created resource in the “location” header as shown below.
Using the returned URI and the PUT API we can modify the customer just created.
The default DMVCFramework behavior allows to do partial updates, as shown below.
Conclusion
In this small article we show how DMVCFramework makes really simple to create powerful and RESTful APIs. It allows to automatically produce JSON structure starting from Plain Delphi objects. The built in RQL support allows to easily implements resource filtering, ordering and limit.
If you liked this article, 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 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!