SERGE: Today we will tell you about databases inside RAD Studio and the Delphi ecosystem. Our special guests are Bruno Fierens, CEO and Founder of TMS Software, and Wagner Landgraf, who is responsible for databases development at TMS Software.
In the beginning, just a couple of words about Softacom and then Bruno will say a few words about TMS. At Softacom we are working on migration of legacy software to up-to-date versions using up-to-date technologies and frameworks, especially Delphi. We are Delphi Evangelist and we like Delphi. We are working on enhancement of Delphi software, re-engineering, migration from legacy Delphi versions like Delphi seven to up-to-date Delphi versions. Delphi is one of our main technologies and domains. Right now I’ll give some words to Bruno, so that he can introduce TMS software for you, but I think most of you know this company. Bruno, you’re welcome.
BRUNO: Thank you, Serge, for inviting us here and welcome to everyone. So, introducing TMS Software I think most of you will already know about our company, as we have been developing components for Delphi since 1995, started, of course, with VCL, and over the years we have extended these offerings of components with the main goal of making your development faster and making your development easier and enable you to develop better software. So, originally, focusing on VCL over time cross-platform components, UI controls were added to that, and also top of today frameworks for accessing REST APIs, creating REST APIs, offering multi-tier solutions to hook up and connect to databases and back, we will cover more of that later.
SERGE: It was just like an introduction, right now we are starting, and let’s start from the question why did we choose this subject. Because before we had different topics about migration, about components and other technologies, but it is our first talk about databases, and it’s really important, we had a lot of requests. Just let’s discuss what we have now that we did not have for example 15 years ago. Do we have any trendy database technologies, like other development tools? I’m not talking about Delphi or C++ Builder. And we can say yes, we have a lot of new frameworks for Delphi, all of them are up-to-date, are cutting-edge and there is the same functionality and performance like in other competitive tools, and we can say that we have everything and even more and we want to present you these technologies, because sometimes some people from the Delphi community are used to technologies which they used 10 years ago, and they are still using the same, and we want to present new ones. It will not be too deeply because each technology can be a separate subject. We will just explain it for you and show what we have right now in Delphi world.
And if we are talking about today’s agenda. There will be 2 parts of the article. In the first part Wagner will provide a couple of data access patterns that we had 10-15 years ago and that we have today. I will explain the pitfalls of migration of legacy versions of databases like Interbase and Firebird to up-to-date versions. Then together with Wagner we will discuss pros and cons of migration to ORM instead of using regular practices. And then we will show you these exact frameworks and technologies like TMS Aurelius – it’s ORM system from TMS Software. And this is why we have to show what else we have for data access using Delphi. So, in the second part we talk about REST API Server and other technologies and frameworks that we can use for buildings 3-tier data access applications. Right now we start from the basics of what we have 10-15 years ago and which trendy technologies we have today. Wagner, please.
WAGNER: Thank you, Sergey. Well, I believe many Delphi developers, most, if not all, of us have worked with database applications by using TQuery and TDatabase, or similar components like T-SQL query or TADOQuery etc. And that’s the data pattern of using Delphi that most developers are used to. And the usual way is to drop a query component in the form and put some SQL statements in the component and execute the SQL statement and bind it to the data controls using data source. And that’s something that has been used until today. But there are some issues with that approach like UI and business logic are mixed, so you have a lot of code, you double-click your button, and you execute SQL statements in your form and that brings a lot of issues, and it’s hard to maintain. You have your business logic mixed with your forms and everything is mixed up. That also makes it hard to build cross database applications and perform testing because it is like spaghetti code, everything is mixed in one place and it’s hard to isolate. So, currently, not only in Delphi of course, but in many development platforms, first we have in newer Delphi versions more modern database access components like FireDAC which is a huge improvement over the previous DB components like DB Express. FireDAC is great, fast and has a lot of features. But people are using FireDAC in the old way, dropping query in the form and mixing business logic. There are other ways, and one of those ways allows you to abstract the database access from your code. And even if you use FireDAC directly, people are using like data repositories where you create a layer over your FireDAC components for example, exactly to isolate the form, the view from your model or controller which is the code that handles database data. So that helps either if you use ORM framework, or if you have your own ORM, or if you just created a small ORM, so to speak, that abstracts your data layer, that allows you to separate your business code, that allows you to automate tests and so on. So, talking about direct database access, I think that’s one change of pattern today. Serge?
SERGE: Yeah, before, as I remember it, the internet was not so popular and that is why we did not have such technologies like REST API. Of course, it could be possible to develop them but they just did not make sense. But right now everything has changed and this is why we are talking about all these multi-tier solutions.
WAGNER: For example, we can ask the audience if you still have spaghetti code in your form with SQL statements. Just say “I do”. Because we can have an idea if that pattern is still used today, or a legacy code that you still have to maintain.
SERGE: Right now we will discuss three slides about migration and pitfalls of migration of legacy databases and database management systems which I guess you know. These are the most popular. I just selected those three ones: Firebird, Microsoft SQL Server, and Interbase. And I will briefly show a couple of pitfalls which we got in the past when we tried to migrate old Firebird version to up-to-date, and just like a use case which problems you can possibly have when if you for example migrate your 1.x version to 2.x or 3.x and why do you have to migrate at all. I provided a couple of improvements for each version. For Firebird I have like a use case: we had a database with maybe 400 tables, 400 stored procedures, the size was something like a hundred gigabytes, and migration took something like 1-3 weeks. And here you can see the differences in SQL syntax and in some of the data types. Also Firebird introduced a couple of new keywords, and if you had the same words in your stored procedures code, you have to change it. Also in the middle column you can see improvements because the original question was why I have to migrate if everything is working on my Firebird 1.x version.
The next one is about SQL Server. With SQL Server it’s a little bit harder because it has much more versions and all these versions do not have huge differences, and you have to check new improvements and make a decision do you need this improvements or you don’t because I know that your software can even work perfectly even on 2012 versions with modern transact-SQL, and you don’t even need to migrate to the latest version like 2019, but anyway here you can find the differences because SQL Server has a lot of improvements like CLR, it’s like user-defined functions, but they have to be written in .NET programming language like C#, this is not about Delphi, but anyway, a lot of Delphi software even right now uses SQL Server. I think it’s maybe even 30%, it’s just my feelings.
And the third slide is about Interbase, it also has some improvements. I remember when we used Delphi 7 we used Interbase version 4, 4.5, something like this, and of course it is a little bit obsolete, and when we are migrating Delphi 7 software we also think about migration of Interbase to up-to-date versions and here is just a list of nuances and pitfalls which you can get during this migration. I can say that the hardest migration is from old versions to XE3 and XE7. On the latest versions it’s much easier to migrate. But again, it can take a lot of time and you have to keep it in mind when you’re planning your migration.
That was about the pitfalls of migration. And right now let’s start talking about frameworks and technologies and access patterns to our data. Believe me, I’m hearing a lot of opinions from different people on should people use ORM or they shouldn’t, do we need it or we don’t. Sometimes I think that this is like a religion: some people say that we have to use it, some people – that we don’t need it. Right now together with Wagner we will try to provide pros and cons from our side and from our experience and you will make a decision on your side. You can see here a list of pros of using ORM. Wagner, what can you say from your point of view? Maybe you can add some examples from your previous experience?
WAGNER: Sure. First you have said very well when you said that sometimes it looks like a religion, this conversation between ORM supporters and ORM opponents. I’ve seen strong defenders for both sides. And actually, they are both right in their arguments, so it’s really a matter of your use case, your needs, and, as you said in a previous slide, your personal taste. There are pros and cons. Pros for ORM, for example, is that you work with more Pascal class codes or object code, so it’s easier to test. That’s something that is hard to see for people that are starting with ORM but in the end it makes your code easier to test. Sometimes when you work with spaghetti code or SQL statement, you don’t even consider it. If you do testing, you do interface testing, but when you use ORM, you can in a more easy way implement integration tests and test your quotes. It’s easier to maintain because of that, it’s faster prototyping, at least with TMS Aurelius it’s very easy to do, and again it is easier to separate concerns that also refers to maintenance and testing. The code gets cleaner, it’s also a personal taste of course. I know people that still prefer to use procedural codes instead object-oriented code, so that person will never say that using ORM brings you more clean and beautiful code. But it’s easier to transition, to make cross database applications, and, as I mentioned, it’s better security. For my taste, I prefer the clean code, the fact that I work with objects, and it’s faster to develop for me for example, cause I’m used to TMS Aurelius. And it’s also an advantage that it’s NOT all or nothing: you don’t have to migrate your whole application to ORM, or if you adopt it you don’t have to use it everywhere. It’s like a tool, you use it when you think you should use it, and you go back to SQL statements when you need to go back to SQL statements.
SERGE: Yeah, this maybe will be on our next slide. Why are people from my experience scared to use ORM? Because they just lose low level control from how all these queries are performed. Because we have to divide all queries on little bricks and not perform some very complicated constructions using ORM. It’s again my opinion, maybe I’m wrong. I have such experience with SQL Server, when I could analyze my queries only using database analytic tools , when I just dumped SQL queries and then tried to perform some experiments with ORM operators, like I change this one, and then I dump my query and check the result. This is one of the most famous arguments against using ORM. The second one that I’m hearing is something like when I change something in the database structure, some ORM is very sensitive to these changes. If I change something in the database structure and did not change it inside my client, it can generate errors because ORMs cannot on the flight change and work with changed database structures. This is again the counterargument from some people why you don’t need to use ORM. What about this situation, Wagner, with Aurelius? If I change something on the database side, for example just adding some field to some table, should I recompile all clients or this field will be ignored if it is not unique, not a mandatory field in the database.
WAGNER: In that specific case Aurelius will ignore it. It’s interesting that you made that point because I’m not aware of that limitation in other ORMs, but as you are saying probably there is. That’s indeed a point against ORM if that happens. That’s why, at least when using Aurelius, it’s not all-or-nothing at all, because you can have an application with hundreds of tables and a lot of code, and you simply create a new form for example inside your application, and say ok I’m using Aurelius in this new form that I’m creating and that’s ok regardless of you don’t have to map all your tables, all your classes and things like that. And, actually, that’s one advantage of using ORM. When you don’t use ORM and change your database, you might end up with errors like “field (name) doesn’t exist in database”. You only get those error in a runtime when you use SQL statements, because you change your database and you deploy your application the way it is, but you renamed a field or you don’t have a table in your code, and with ORM since you deal with objects of course you will not get all errors, but you get more errors at compile time, because you are dealing with objects, so it has small advantage as well. Of course, ORM is not a silver bullet. I don’t use it in all of my applications. If you really need super performance and you really need to save and retrieve millions of records in a very short time, and you have object overhead, sometimes of course it’s better to use direct SQL statements. It all depends on your application. Talking about SQL performance, for example, in the old days of gaming when we had those Atari games, people had to develop the gaming for Kbytes, because that was all the memory you had, and these days you don’t have to worry about memory in most applications. So performance is still important for some applications, but it’s becoming less important because hardware is evolving, so in most applications it doesn’t matter anymore.
SERGE: Yeah, but if we need just performance, we can put all these queries to stored procedures. They also will be performed very fast but inside Delphi we will have very clean code, if we use ORM without all this very long SQL queries. But also sometimes people like using SQL because they just can put SQL queries not inside compiled code, put them into some settings files, some encrypted files, and in this case they just will be able to replace them without recompilation of the application, for example. For some updates that’s also a good solution if you need such updating process of SQL queries and some features, of course in this case ORM won’t work. Correct?
WAGNER: Yeah.
SERGE: Okay, let’s move on. /strong>Now let’s talk exactly about TMS Aurelius because as for me it’s one of the most well-known tools and we want to show you how to use TMS Aurelius because Wagner is expert in this system and, Wagner, please, explain us why we have to use TMS Aurelius.
WAGNER: Well, about specific features of the framework itself: one thing I like most about TMS Aurelius is the flexibility. It works if you don’t map something, it works on virtually any database you have, so if you have composite keys, if you have a strange structure, it has flexibility enough to handle most of existing databases that you have there. And the code is clean because you don’t have to use specific data types, your classes will be just pure classes. You can even code your business logic without Aurelius, just Delphi, and then later you map and persist your classes in the database in an easy way. And it also has full query syntax, and that it just works is something that we are very proud of too, because the problem is in the details when you start using it, framework in other paper, it’s wonderful, but when you start using it you get stuck at some point, or you have a bug here, and strange behavior, and I can say that Aurelius has most features that are needed for most applications, and it’s very well-tested with all databases it supported, practically all features are tested, and virtually no known bugs for TMS Aurelius so far, if we know the receiver part right away, so that’s also one strong point about it.
SERGE: I asked Wagner a lot of times just because I want to be objective. I asked him: please, provide me some cons of Aurelius, you must know them. But I read a lot of documentation, all my team is also using TMS Aurelius, and believe me, I didn’t find any cons that don’t belong to any ORM, like things which we have already discussed. “I don’t want to use ORM because of this and this”. But it’s not about TMS Aurelius exactly. And we even did not find any cons and I think it should be objective opinion and objective point of view from me and from my team.
WAGNER: One complaint that people have about Aurelius is that it doesn’t work on old Delphi versions. Well, it’s not a con for many people. But sometimes you have a software in Delphi 7 or Delphi 2010, and then, unfortunately, you cannot use TMS Aurelius.
SERGE: What’s the minimal Delphi version for TMS Aurelius?
WAGNER: Delphi XE2.
SERGE: Okay, I think that people who are still using Delphi 5, Delphi 7 right now are thinking about migration, and I think it will be not only about just licenses for new versions because of new Windows interface and a lot of improvements and that’s why I think after the migration it will be available and it will be interesting to try this Aurelius for the software. I also asked Wagner to provide some comparison of ORM and Entity Framework. I’m not blind and I know that everybody is comparing Delphi and technologies from Windows, and here are just a couple of arguments from Wagner. And I can add from myself that Aurelius and some other Delphi ORMs have the same functionality and even better. Wagner, do you have what to add?
WAGNER: Yeah, Entity Framework is not something I’m very fluent with, I played with it a little bit but I don’t have experience in using it for production of big applications. But if you go to websites that list major Entity Framework features, you see those features for example that you can use simple classes, that it changes tracking of objects, you have eager/lazy loading feature to improve performance when you need it, you have queries, you have strong object-oriented programming support, inheritance, associations, you can use code first or DB first, and all of those features are also in TMS Aurelius. So it’s like you can compare TMS Aurelius with a major ORM framework in a major platform like .NET which is Entity Framework. And in the right side of the slide there is a small comment that got my attention which is an announcement for Entity Framework 7: they are adding Unique Constraints and that you can make map a model, an object, and map it to a foreign key that doesn’t map to the primary key of the references table. Anyway, it’s a very specific feature that has been in Aurelius since version one. And it’s not a big deal about EF just to point out that when you start working with, you have a lot of small tiny features here and there and that you need that one for example that entity framework was missing and not Aurelius.
SERGE: And you can see that TMS Software and their product can compete with Microsoft.
WAGNER: That’s it.
