Celesta: SQL Database APIs, Schema Migration, and Testing in a Single Java Library

It is not a coincidence that we first mentioned the business logic code independence from RDBMS type — Celesta code does not know what RDBMS it is executed on. Why would it?

First of all, RDMBS choice is often a matter of preference. Our clients often have their preferred RDMBS they invested in, and they want to have solutions developed for the existing infrastructure. Technological landscape shifts: today, PostgreSQL is becoming more frequent, while just a few years ago we saw the dominance of MS SQL Server. Celesta supports the most widely used RDBMS, and these changes do not concern us.

Secondly, we wanted to reuse the code from project to project and create a reusable library. Things like hierarchical directories and email notification delivery modules are essentially standard, so why support several versions for clients with different RDMBS?

And last but not the least, it allows users to launch unit tests without tools like DbUnit and containers using an H2 embedded database. An H2 database does not require installation and starts up instantly in an in-memory mode. Celesta quickly creates a data scheme in it to run the tests and “forget” the database afterward.

With database type independence, it really does not matter on what database the business logic is executed, so if no errors occur in H2, the same will be true for PostgreSQL as well. Of course, the Celesta development team needs to run multiple tests on a real RDBMS to make sure the platform executes its API uniformly on different RDMBS. And we do this, so the business logic developers are spared the task.

How is this cross-database functionality achieved? Naturally, the price for that is a need for a special API to isolate the business logic from any database-related aspects to work with the data. Celesta generates the code of data access Java classes, and for the database, it generates SQL code and certain auxiliary objects.

Celesta does not allow for object-relational mapping as is, because the data model design is based on the database structure and not on classes. I.e. first, an ER model for tables is designed, and then Celesta generates data access classes (cursors) in accordance with it.

It is possible to achieve the same behavior of the code running with all the supported RDBMS only for the features implemented more or less similarly in each of them. If we use the Euler diagram to represent the sets of features of supported databases, we get the following result:

If we ensure full independence from the database type, the functional capabilities for Celesta users must lie in the intersection of all the feature sets. And at first glance, this seems to be a major limitation. Certainly, some specific features of, say, MS SQL Server, are inaccessible for us. But, all relational databases support tables, foreign keys, views, sequences, and SQL queries with JOIN and GROUP BY statements. These are the tools we can provide to developers. We offer a "generalized SQL" that is transpiled to SQL queries for respective database dialects and call it CelestaSQL.

CelestaSQL language includes DDL to define database objects and SELECT queries for views and filters, but it does not contain DML commands — modification is done by cursors, and we will cover those later.

Every database has its own data typeset. And CelestaSQL language also has its own. At the time, this article is being written, there are nine of them, and in this table, they are compared to types from different databases and Java data types.

Nine types might seem to be few (compared to the number PostgreSQL supports, for example), but in reality, they are enough to store financial, trading, and logistic information: strings, integers and floating-point values, dates, Boolean values, and BLOBs are always enough to represent that kind of data.

The CelestaSQL language is described in the documentation featuring an abundance of syntax diagrams.

Another key functional capability of Celesta is the approach to production database structure migration as the project develops. This feature is implemented with a built-in approach utilizing an idempotent DDL.

In short, when we write the following text in CelestaSQL:

Celesta interprets it not as, “create a table, and if it already exists, return an error”, but as “modify the table to match the required structure”. In other words: “If the table does not exist, create it, and if it does, look at its fields, their types, indices, foreign keys, default values, etc. and alter them in case there is a need to modify anything to match the required structure.”

This approach allows refactoring and version control of a database schema with database structure definition scripts:

Certainly, this approach has its limitations. Celesta makes great efforts to perform automatic migration painlessly and unnoticeably, but sometimes it is impossible. We have explained the rationale, capabilities, and limitations of this approach in this article.

Celesta saves DDL scripts’ checksums to speed up database structure verification/update (database structure verification and update start only when the checksum changes). Problems related to the order in which the interdependent objects change are eliminated due to the topological sorting of dependencies between schemas and foreign keys. Automatic migration is described in detail in the documentation.

Can the database migration mechanism use an idempotent DDL be utilized separately from Celesta? Yes, 2bass, a tool that uses Celesta internally, makes it possible.

The demo project we are going to talk about is available on GitHub. Let us talk about how to use Celesta when writing a Spring Boot application. We will need the following Maven dependencies:

Now, let us create a data model and generate data access classes.

Assume this is a project for an e-commerce company recently merged with another company. And each of them has their own databases. They receive purchase orders, but until they join their databases, they need a single entry point to receive orders from outside.

It is possible to implement the entry point in a traditional fashion: an HTTP service with CRUD operations saving data to a relational database.

A database-first approach is implemented in Celesta, so first, we need to create a structure of the tables. Order, as we all know, is a composite entity: it consists of a single header — containing information about the client, order date, and other attributes — and multiple lines (items).

So let us get started, create:

We have defined two tables connected with a foreign key and a view to return the total quantity of products in all orders. As you may see, this is not dissimilar to standard SQL at all. Still, there are some specific aspects. For example, all table and field names we use must be convertible into Java class and variable names. So we cannot use spaces and hyphens. You could have also noted that some of the comments we added above the names of some tables and fields start not with usual /* , but with /** , as JavaDoc comments, and there is a reason for that! The comment for an object starting with /** is available in the run time in its .getCelestaDoc() property. It can help when we want to add extra meta-information for database elements: i.e. human-readable field names, information about field representation in the user interface and so on.

The CelestaSQL script serves two equally important purposes: to deploy/modify the relational database structure and to generate data access class code.

Now we can generate data access classes by simply executing a mvn generate-sources command, and if you are working in IDEA, by pressing the ‘Generate sources and update folders’ button on the Maven control panel. In any case, IDEA will recognize the folder created in target/generated-sources/celesta as a generated source folder. The generated code will look as follows, one class for each object in the database:

Connection to the database is set in the application settings, in our case, in the src/main/resources/application.yml file. If using spring-boot-starter-celesta, the IDEA code completion will suggest available options.

If we do not want to fiddle with running a “real” RDBMS just for a demo, we can make Celesta work with a built-in H2 database in an in-memory mode by setting the following configuration:

To connect a “real” database, change it to something like this:

(in this case, you also need to add a PostgreSQL JDBC driver to your application via a Maven dependency).

When launching Celesta application connected to the database server, you can see that the necessary tables, views, indices, etc. are created for an empty database and upgraded for a non-empty one to match the definition given in Celesta SQL DDL scripts.

After getting your head around the database structure, you can start writing the business logic.

To implement permission management and data change logging, every operation with data in Celesta is performed on behalf of some user – anonymous operations are impossible. So all Celesta code is executed in a call context described in the CallContext class.

If we use spring-boot-starter-celesta, these actions are performed automatically for all methods annotated by @CelestaTransaction.

For example, we want to write a request handler saving a document to the database. At the controller level, its code may look as follows:

Usually, at the controller method level (i.e. after authentication), we know the user’s ID and can use it when creating CallContext. Binding the user to the context determines access rights to tables and allows to log changes made on their behalf. If you do not want to use the Celesta permission management system and give the call context full rights to all tables, you can create a SystemCallContext object instead.

The order saving method at the service level may look as follows:

Note the @CelestaTransaction annotation. It allows the DocumentService proxy object to perform all service actions with the CallContext ctx parameter described above. So, at the start of the method execution, it is already bound to the database connection, and the transaction is ready to start. Meanwhile, we can focus on writing business logic (in this case, reading the OrderDto object and saving it to the database).

To do that, we use the so-called cursors, which are classes generated, using celesta-maven-plugin. We have already seen what they look like. For each schema object — in this case, two tables and one view — a class is generated. And now, we can use these classes to access database objects in our business logic.

You need the following code to create a cursor for an order table and select the first record:

After creating a header object, we can access the table record fields using getters and setters:

When creating an active cursor, we have to use an active call context — this is the only way to create one. Call context stores information on the current user and their access rights.

We can do a lot with a cursor object: filter, navigate, insert, delete, and modify records. See the documentation for the detailed cursor API description.

For example, we can develop our demo code this way:

Here, we set a filter for the manager_id field and then find the first record with a tryFirst method.

The moment tryFirst executes, cursor fields are filled with data of a single record and we can read and assign them. And when the data in the cursor is fully prepared, execute update() to save the cursor content to the database.

What problems can this code encounter? Certainly, it can encounter a race condition/lost update! This may happen because somebody else can access, modify, or update data in the database in the gap between the moment we get the data using tryFirst and the moment we try to update the data at the update stage.

After the data is read, the cursor does not prevent other users from using it in any way! To prevent lost updates, Celesta uses an optimistic lock. By default, it creates a recversion field in each table, increments the version number in an ON UPDATE trigger, verifies that the updated data has the same version as that in the table, and throws an exception if the mismatch occurs. For more detail, see the Lost Updates Prevention section in the documentation.

Again, the transaction is bound to a CallContext object. If the Celesta procedure ends successfully, it commits the changes, and it rolls back if the method ends with an uncaught exception. So, if an error occurs in a complex procedure, the whole transaction associated with the call context rolls back — everything stays just as it was before the start, and the data is not corrupted. If you need to commit in the middle for some reason — for some long time executing procedure, for example — you can do so by committing explicitly by calling context.commit().

Cursors allow you to write code that works with the database efficiently. See the Best Practices section in the Celesta documentation to learn how to do it properly and avoid unnecessary load on the database.

Let us create a unit test to check the correct operation of the service method used to save OrderDto to the database.

It is easy to do by using JUnit5 and an extension for it in the celesta-unit module. The test structure is the following:

The @CelestaTest annotation allows us to declare the CallContext context parameter in testing methods. This context is already activated and is bound to the database (in-memory H2), so we do not need to use a proxy for the service class. We create it with new here and not using Spring. At the same time, there are no obstacles for using Spring testing facilities instead.

We create unit tests, assuming that the database at the time of their execution is completely empty but has a structure we need. After their execution, we do not need to worry about cleaning up the database. These tests are very fast.

Let us create a second procedure to return JSON with aggregated values, showing the quantity of ordered products.

The test adds two orders to the database and then checks the aggregated value returned with a new getAggregateReport method:

To implement the getAggregateReport method, we use the OrderedQty view, which reads as follows in a CelestaSQL file:

The query is standard: we add the order lines by quantity and group by product code. To access its data, we can use the generated OrderedQtyCursor class. We declare the cursor, iterate it and collect the Map<String, Integer>, as follows:

What is wrong with using views to get aggregated data? This method is actionable, but it is a disaster waiting to happen because a SQL query is executed more and more slowly as the system accumulates data. It has to add up and group increasing amounts of table rows. How do you avoid it?

MS SQL Server has a concept of materialized (indexed) views stored as tables and quickly updated as the data changes in the source tables. If we were working in ‘vanilla’ MS SQL Server, replacing views with indexed views would be just the thing we need: extracting an aggregated report would not slow down as the data accumulates, and the report would be updated the moment the order items enter the table, which also would stay at about the same speed as the number of rows grows.

But, what are our options with PostgreSQL in Celesta? Redefine the view by adding the word "materialized":

Launch the system and see what has happened to the database.

Note that the OrderedQty view has vanished and has been replaced with an OrderedQty table. And as the OrderLine tables is filled with data, and the information in the OrderedQty table "magically" updates the same way views update.

There is no magic here: look at the triggers defined for the OrderLine table. Having received a task to create a materialized view, Celesta analyzed the query and created triggers in the OrderLine table to update OrderedQty. By adding a single keyword — materialized — to the CelestaSQL file, we have solved the problem of declining performance without making a single change to the business logic!

Naturally, this approach has rather strict limitations. In Celesta, you can only materialize the views built on a single table, with no JOIN s or aggregation by GROUP BY. But, this is enough to prepare account and inventory statements and similar common reports.

We have briefly explained the key Celesta capabilities. You’re welcome to check out GitHub and the documentation if you are interested in the technology.