SERGE: Today we are the topic of Databases inside RAD Studio & Delphi ecosystem. If you remember, in the first part we provided a couple of data access patterns that we had 10-15 years ago and that we have today, we explained pitfalls of migration of legacy versions of databases like Interbase and Firebird to up-to-date versions. Then together with Wagner we discussed pros and cons of migration to ORM instead of using regular practices, and we showed you exact frameworks and technologies like TMS Aurelius – it’s ORM system from TMS Software. So, in the second part we are going to show what else do we have for data access using Delphi, tell about REST API Server and other technologies and frameworks that we can use for buildings 3-tier data access applications.
Ok, before we discuss data access patterns using ORM and their differences between what did we have in the past and today, I think one of the major branches of data access patterns is REST API. And here I’m just comparing REST API with “classic” DB access patterns. I think it may include ORM or direct database access using our popular components like ADO or TQuery and other components based on this TQuery. Personally, I like REST API architecture and in our company we’re using it a lot for different multi-tier solutions. Even for desktop software, even if we have database storage inside local network, inside Ethernet, we are also using it for mobile solutions, for web application, we even have web applications like websites, web portals which are using REST API like data storage and data access to another server or to another database which is hosted on different machines or even different clouds. I like this solution and this pattern because it’s very flexible: flexible for adding new clients and new systems, subsystems, especially if you want to build any platform solution or SaaS solution. And what are the pros of a REST API? The first one is versioning. You can add different REST API versions for your solutions and different clients will have support for different versions. Of course, somebody can say “okay, I can write new clients using new SQL queries”, but all the time you have to think about backward compatibility when you’re developing your database tables and so on. Here you are controlling it on the top level, you’re controlling this data access level just providing guides and manuals how people have to use your REST API and data, and you even can hide your data. It’s a very good solution if you provide some data access for third party systems, or vendors, or your subcontractors. You are not providing direct access to a database, you just provide what you want, you can develop some permission features, you even can control speed, how fast and how many requests to your data 3rd-party side can perform. Again, I have already mentioned the permissions and roles but for example if you are using SQL server of course you can set up roles, permissions, provide login and password for your 3rd-party subcontractor but believe me for huge databases with hundreds of tables and storage procedures it is not easy to set up all these permissions and keep them in head or on some paper or even if something happens for example with your database.
And REST API provide for us data layer abstraction and what I have already mentioned – you have the same data specification for any client. It can be Delphi application, C++ Builder application, even some mobile application which was built using Android Studio or XCode for iOS, even Objective C, and you are not thinking about how your clients (I’m talking about clients’ applications, clients’ software) will implement access to your data because REST API using XML or JSON is unifying the data access and that’s why I like it.
And here I just want to provide a couple of architectures which you can use for building your REST API server. Of course, it is not all solutions which you can find in the Internet because we have solutions for Java, we have for Python, for other programming languages, but here are the most popular from my point of view, especially if we’re talking about data-related solutions and Delphi ecosystem, RAD Studio ecosystem. The first one is RAD Server. RAD Server is like solution and product from Embarcadero, it can be a part of a RAD Studio if you have specific license, if I remember architectural license. RAD Server is ready-to-use REST API server which you can just install on your Windows or Linux machine it has its server’s database and you will have REST API server from the scratch, this REST API server is like a wrapper for your database. It also has support for different databases: Interbase, SQL Server and so on. And it will really save your time. On the board it already has roles and permissions management for different login systems. A little bit later I will provide some information about RAD Server. The second solution – you can take ready-to-use and free solutions. One of such solutions is DreamFactory. We also used it a lot of times, and right now we are using. It’s free for free database systems. For SQL Server it is not free. But for databases like MySQL, MongoDB it’s free and again it is ready-to-use REST API Server which you have to install on your virtual machine and connect to your database. It has its own console, it has different logs, and it’s also a beautiful solution but it’s based on PHP, it’s a LAMP solution if we are talking about customization. Of course, you can build your REST API Server using TMS XData solution from TMS software. Again, you can build your application, deploy it to virtual machine, it will be connected to database, and it will be like a database wrapper like REST API server for your clients and for your software. You can host it on cloud or on-premises server, later I will ask Wagner about some details. And the fourth solution, last year it’s very popular: you can build your REST API server you use different serverless solutions from different cloud platforms. Here I show you Microsoft Azure. To be honest I don’t know if we have the same for Amazon Web Services but for Azure you can just write some code and develop your REST API server using Azure console without any compilation.
WAGNER: for Amazon you have Amazon Lambda.
SERGE: oh, yeah, thank you. Ok, let’s move on. Here are couple of slides will be about RAD Server, and we also use it. As I already said, it is part of RAD Studio and it’s integrated to some features and for Delphi solutions it’s good. And you can also check its features and make a decision if you need it or not. It’s solution from the box it implements a lot of routines. If you want to start developing yours REST API Server, you have to think about different things like users management, permissions, log in, rules how to perform and how to build your packets for your data. I know two different admin consoles, if you want to manage your REST API Server it’s all about the time. And interesting that you can develop extensions for your RAD Server, you can extend its functionality just using Delphi code. You can just create some kind of Delphi project, it’s called RAD Server extension, using Delphi you can write and add any customization that you need for developing any extension for your RAD Server. Because from the box RAD Server is just a wrapper from your database for insert, update, delete, select operators and functions.
And if we’re talking about cons, for me one of the most not acceptable things is that we cannot deploy it to shared hosting. When I’m talking about shared hosting I’m talking about web applications from Azure, from Amazon, or maybe even GoDaddy website. You cannot just compile something and upload it. You will need a virtual machine usually with access to operation system features. And you have to think in this case about this virtual machine, it’s much more expensive, you have to think about security, updates, and so on. And of course, if you are using ready solution, you have to think about backward compatibility: what you will have in the next versions of RAD Server, if you will update Delphi and so on and so on. Sometimes if you’re developing your own solution, you won’t have these problems, but you will need more time for development. And, of course, you will have more bugs which you also have to fix and control. And again, if you want to change core functionality, it can be challenging like for any other ready software and solution.
As we already mentioned, we can we can develop our REST API Server using XData solution for TMS Software and right now I want to ask Wagner to explain how we can use this solution to save our time for development.
WAGNER: Ok, Serge. To be short, the main features, the main pros of TMS XData, are two points that I mention in the slide. The first one – it is very easy to learn and use because of its architecture. We usually receive feedback from the users that they are enjoying – that’s the word – enjoying using XData because you really feel home, and you feel that the code is smooth, and in a sharp, it’s easy and fun to use it.
And the second point is that it’s just like Aurelius, it just works. I have for example several servers in XData, and clients are running some servers for months, some servers for years, and it doesn’t have memory leaks, it doesn’t take a lot of memory, it doesn’t crash, so it simply keeps working and working, and it also uses low memory, so in the end you can have cheaper architecture in the cloud to run it. And there are other features that we don’t have time to describe here in details but the fact that you reuse code for both servers and clients and you have swagger UI that it works out of the box because you don’t have to add a lot of documentation, it integrates with Aurelius that’s one point, people think that you have to use TMS Aurelius to use XData and that’s not true, it’s optional. And, finally, comes with full source code, so everything of the framework is with source code provided, and it’s royalty free distribution meaning that you can compile your server and distribute it as many as you want for as many customers or as many cloud Computers you want, without the need to have a license for each of those servers. That’s it, Serge.
SERGE: yeah, and what we have already mentioned, building REST API Server using TMS is like a subject for a separate webinar because even for me it’s very interesting: what will I have in the end, what kind of Delphi project I have to create, how can I deploy it… For me it’s very interesting but, unfortunately, we don’t have enough time today and right now. But you can check TMS documentation, it’s very detailed. And what about cons of XData. Wagner, give me cons! Because I’d like to be objective. What can you say, Wagner, about this ideal solution?
WAGNER: Yeah, you have to learn, your application will be a very different one, because if you have an application accessing database with query component and you have to move to XData or RAD Server, whatever REST API you are using it’s a whole new paradigm for you, for developers that are not used to it, you have to rewrite your client applications at some level. So that’s one problem, you wrote a con for RAD Server that XData has the same problem, the deployment is similar, yeah, but the main problem, the way I see it, is the thing that people have problems with understanding the architecture itself and how they should rewrite their client applications to work with the new one.
SERGE: yeah, because some people are thinking about REST API Server in terms of classic web application which was built for example using PHP and Apache. And they think that REST Server is just PHP application, or Python application, or asp.net application which I just put hosting, and it is just regular web application. In terms of RAD Server and XData it’s a little bit different, it’s like the next level, it’s not such a low-level solution. Am I correct, Wagner?
WAGNER: Yes.
SERGE: Yeah, and this is what maybe in the future we will have, because what I see is that people don’t want to think about all this machines management. They are even expensive. People just want to buy some slot on some cloud platform, put a couple of files to this slot and don’t think about its availability, failover, and so on. They don’t want to manage this virtual machine, and this is maybe one of the challenges for us for the future how to create such servers using Delphi, that’s my opinion. Okay I think that we can move on. I asked Wagner to provide some example of solutions using TMS XData and he provided some software product which right now is working for many years. Maybe, Wagner, you have something to add about this application?
WAGNER: yes, it’s a tool for health system in Brazil validortiss.com.br, and it use XData as a back-end, it’s a public XData, so it’s not an internal network, and it handles thousands of users in the whole Brazil, there’s a lot of transactions per day, it’s a many requests per second, and it’s working fine. And just for curiosity, they are now building a web interface, application is desktop, and it’s now in a web interface using TMS WEB Core. It’s a great use case it working running and it’s a big app.
SERGE: Very impressing. Here we provided other patterns and solutions, even some of the commercial products, how you can access data using Delphi. You can check all the solutions. I don’t think that we have listed all the solutions available for Delphi, but I think these are the most popular. Maybe some of them are not up-to-date, but anyway. One of these solutions is the product from TMS which is very interesting for me, it is TMS RemoteDB. Wagner, can you briefly provide some words about it?
WAGNER: Ok, TMS RemoteDB is another way of moving your application to the cloud. People get confused about the difference with XData, but besides moving the application to the cloud it’s very different solution. TMS RemoteDB is a super rapid way to move your application to the cloud different from a REST API move, it requires minimum rewrite of your existing application, you still be using your query components, you replace it with the RemoteDB, but the pattern will be the same, and the idea is that you keep your application running as if it was client-server, but they are accessing the database in the cloud and not directly the database in the cloud but through the RemoteDB server.
In the cloud you have your database server which is what you want to reach, and in front of it you have the RemoteDB Server. And inside your client application you have your TRemoteDB database and TXDataset that you are going to use to connect to the database in the cloud through HTTP not through TCP for example. So that’s the mechanism. And you have the option, of course, to still connect directly to your database using the generic database. So you have your application moving to the cloud, connecting to a cloud database, or connecting to your local database in the same application. There are some limitations. It’s not a RAD Server so you don’t have server-side logic there, you just execute SQL statements. That’s a lower security level because any SQL statement can be executed, of course with proper credentials, and since it’s a proprietary specific protocol you can only use Delphi-clients, you cannot use clients for other platforms or from the web etc. So it’s a very specific solution.
SERGE: Here are just some use case of migration to RemoteDB. Wagner, what do you want to add?
WAGNER: We had a customer who had a traditional VCL client-server ERP, which is huge, it is still being developed. So, hundreds of tables, lots of dataset components in that traditional way, you know, and they had more and more pressure to move the application to the cloud to allow the customers to use their software from anywhere: from home, from notebook, using the cellphone network. And in nearly two weeks after RemoteDB was available and ready for production, they were able to migrate their whole ERP to the cloud. Of course, two or three weeks of coding, I mean. Of course they were testing, and they had to do a lot of tricks here and there, which is normal to make it ready for production, but it was really fast, the core of the coding was done in two weeks and then only testing remained. And then they were able to move the application to the cloud. Now they are using XData, slowly moving a lot of code to the cloud using the REST API approach, but since then their application was accessible from anywhere without needing to install client, or open parts, or having poor performance, and those connection losses.
SERGE: Yeah, very cool. Okay, we are almost done, if you have any questions you can ask Bruno, Wagner, you can ask me, you can reach us in social networks, you can visit our websites.
WAGNER: I’d just like to thank you, Serge, for the invitation, we’ll be in contact, at TMS Software we are available for any questions you might have. As Sergey said, you can reach me via my email.
BRUNO: Yes, Serge, thank you very much in the first place for organizing this talk, it’s very much appreciated.
