Hi all, in the past nearly all of my apps have been VCL-based, acessing data on a network-available SQL Server database. I have an idea for a mobile-based app that will entail access to a shared cloud-based database (preferably SQL Server). Is it possible/advisable to have a mobile app connect directly with a DB in the Cloud? If not, what is the recommended approach: Azure, AWS, website hosted DB, etc?
Any advice appreciated.
Thx,
Kim.
It isn’t recommended to expose a database to the internet.
Here is my suggestion for doing this (google each line for instructions)
Setup an AWS Amazon Linux 2 or 2023 system - EC2 server
Install apache (httpd), with http2 enabled
Install certbot using the python3 method to get valid and free SSL certificates. You’ll need to also install the certbot-apache module.
Choose PostgreSQL for your database, its better than SQL Server and Free - installing Postgres 15 on Amazon linux
Get PAServer from Delphi onto the Linux box
Setup Apache as a WebProxy to route to your Delphi service/app
Write a Delphi web server using horse, mormot, indy etc and run it inside the linux box let’s say on port 7100. Use the apache web proxy to route traffic to the service.
Send data to your app in JSON format.
Receive data from the your app as JSON.
You should be able to find examples in the web server tech you choose to query a database and return the data in JSON. With Postgres you can ask the database to turn the result set into JSON, so you can just do this:
query.sql.add(‘select array_to_json(array_agg(row_to_json(t))) from (’);
query.sql.add(‘select * from customers’); //<<<Normal SQL here
query.sql.add(‘) t’);
query.open;
response.contentText := query.fields[0].asString;
This is high level, if you have specific questions let me know.
1 Like
I’ll mirror what Andrew said (don’t expose db to internet!), except my reverse proxy of choice is nginx.
Postgresql on linux is really, really good, and pretty simple to install and configure (lots of guides on the net).
Thx very much… I thought it might be simpler. Going away for 6 weeks on hols so will get back into it when I return. Cheers, Kim