Best practices for working with a DB accessed via REST API?

[David Schwartz 415]

I'm curious if anybody has done much interfacing with a DB via a REST API, and can share their insights and "best practices" that might differ from a locally-accessible DB server.

 

I'm building a web app and I want my back-end service to talk with a DB hosted on a remote machine. I see the back-end service scaling up at a different rate than the DB and I want to keep the two independent for a while. My only experience is with DB servers that are hosted locally, either on the same machine or on a machine in a LAN that's on the same IP subnet and behind a firewall.

 

Initially I need to save user-specific state data, as well as some canned audio and video files that are used from time to time. The links to the media files can be sent out directly since they're not protected, but the user-specific data need to be protected.

 

My biggest concern is how to handle versioning of the DB, although being a web app going through my back-end service, there's really only one "user" accessing this DB, which is my back-end service.

 

So what's the best way to handle adding new fields and especially new tables once it's in production?

 

Are there other concerns that would need to be addressed?

[mytbo 5]

If you want to use mORMot for the implementation, you can find some suggestions in these articles with sourcecode. For the creation of a MVC Web Application you can find an example here. If you use the ORM, the update is done by calling function CreateMissingTables.

 

With best regards

Thomas

[Lars Fosdal 1732]

My hard-earned advice

• Refrain from replicating the database structures in the REST API. Think about the tasks you want to execute, and what data that is needed for these tasks. It doesn't matter how they actually are stored in the database, as it is the REST server's job to sort out what goes where and how. It also enables you to swap out the underlying database without changing the API itself.  
• Use JSON data packages.  The good thing about these, is that you can have missing or extra fields in both directions, making the API less fragile. If a value is missing and not critical, simply use a default instead. If an unknown value arrives, just ignore it. This moves the versioning to the API levels, instead of purely on the database.
• Try https://api.swaggerhub.com for building APIs. Swagger files, now known as OpenAPI definitions, are very useful as there are numerous dev tools that can use them to create both server -and client-side code or configuration.
• Do not expose primary keys in the APIs - if you need an actual unique key, create a GUID field in the database and use that as the unique id. 
  It is way too easy to do serial breach attempts on queries where the PK is just an integer.
• Use secure connections - i.e. https.

• Make sure you use proper authentication. OAuth2 with limited time keys.

• Remember why it is called RESTful and obey the rules

 

 

[Attila Kovacs 617]

I believe he has a good understanding of the topic, he just needs some additional ideas to consider.

[David Schwartz 415]

12 hours ago, Attila Kovacs said:

I believe he has a good understanding of the topic, he just needs some additional ideas to consider.

 

TBH, I'm unclear how good of an understanding I have of this topic! I have what seems like a common use-case, and yet none of the vendors I've found with products that come close to what I need actually support it directly. Also, their documentation is either incomplete, disjointed, or lacks any sort of complete use-case description. If vendors in the market can't clearly document and explain their own models and don't support a straightforward use-case, then it makes me wonder what the heck I'm missing. It leaves me with more questions than answers, which is why I thought I'd ask about it.

 

[KenR 29]

I have also struggled with this concept. I am currently using TMS Web Core with the db server created by using TMS XData. For most visual components I am using DevExtreme. This seems to work very well. The main advice I can give is to retrieve the absolute minimum amont of data with each request.

[David Schwartz 415]

13 hours ago, Lars Fosdal said:

My hard-earned advice

• Refrain from replicating the database structures in the REST API. Think about the tasks you want to execute, and what data that is needed for these tasks. It doesn't matter how they actually are stored in the database, as it is the REST server's job to sort out what goes where and how. It also enables you to swap out the underlying database without changing the API itself.  
• Use JSON data packages.  The good thing about these, is that you can have missing or extra fields in both directions, making the API less fragile. If a value is missing and not critical, simply use a default instead. If an unknown value arrives, just ignore it. This moves the versioning to the API levels, instead of purely on the database.
• Try https://api.swaggerhub.com for building APIs. Swagger files, now known as OpenAPI definitions, are very useful as there are numerous dev tools that can use them to create both server -and client-side code or configuration.
• Do not expose primary keys in the APIs - if you need an actual unique key, create a GUID field in the database and use that as the unique id. 
  It is way too easy to do serial breach attempts on queries where the PK is just an integer.
• Use secure connections - i.e. https.

• Make sure you use proper authentication. OAuth2 with limited time keys.

• Remember why it is called RESTful and obey the rules

 

 

All great points. To me, I'm looking for what amounts to an embedded DB with a REST API to handle security issues better than you get by just using a raw IP:port# protocol, which is what pretty much every DB server in the world does. They all have UIs of various types, but none of them are tightly coupled the way I've found in my search for a solution. The vendors seem to start with a nice web-based UI drag-n-drop designer and then slap a REST API onto the side to make it accessible by code. But they ALL are tightly coupled to their doggone UI. WTF? MySQL has no UI -- but there are plenty of tools that let you connect to it and manage it via a nice UI, the most common and well-known of which is probably phpMyAdmin. But look how many fancy UI-based tools exist that let you connect to any number of DBs that don't rely or depend on any UI parts of those DBs that might exist. The market for them exploded in the 90's because of Y2K and it's still quite healthy -- only now this tech is referred to as "ETL" support. 

 

So why is REST support for DBs always viewed as an "add-on"? And why is it mostly just half-assed in terms of what it supports?

 

That 3rd one in particular about swagger is an especially great point, but I can't figure out why so few vendors actively embrace it. It's not unlike SOAP, and SOAP requires a special compiler to take the WSDL specs and generate an interface unit for it in your language du jour. Swagger 3.0 is complete enough that there should be tools that read the swagger file and generate an interface unit for it in whatever language you want. They even have a list on their site with tools for a few dozen different languages, but ... Delphi ain't one of them. Hello EMBT? Don't you think Delphi is a useful enough platform for building REST interfaces to warrant vendor-sponsored support for a swagger tool?  (TMS' Wagner Landgraf built something last year that's posted on github as open source, but they're just a 3rd-party tool vendor.

 

I'm still pretty new to REST stuff, but from where I stand, I've come to two conclusions:

• TObject and/or RTTI should support toJSON and fromJSON in the same way that they support the abliity to read and write DFM files to serialize and deserialize objects, and it should not be done in a way that's VCL-specific
   
• You should be able to point to a swagger spec using something that's shipped "in the box" and have it create a robust class definition from it that can ingest data from that API and return PODOs, even if it's all just a bunch of Variant fields to handle ambiguous field definitions

There are at least a half dozen tools in the C# / .NET world that do the latter. I only know of one for Delphi (Wagner's). 

 

I see this as a necessary part of the support needed to work with embedded REST-based DBs, but not sufficient. The authentication and security part is also necessary, but completely unrelated -- how to you read a swagger spec and generate a fully-compliant auth layer? Is it even possible? At least SOAP -- as ugly and inefficient as it may be -- has a standardized security protocol you can implement (AFAIK).

[David Schwartz 415]

5 minutes ago, KenR said:

I have also struggled with this concept. I am currently using TMS Web Core with the db server created by using TMS XData. For most visual components I am using DevExtreme. This seems to work very well. The main advice I can give is to retrieve the absolute minimum amont of data with each request.

I'm also working with Web Core. My needs are actually quite simple right now -- I need to be able to store stuff that might otherwise go into an INI file for saving user state data. A web app requires that data to be saved in a single, remotely-accessible place, because the user can run the web app from any device anywhere, and that data needs to follow them around. The ideal place would be wherever the rest of the user profile data is being stored. But I can't find anybody that provides user / member management that lets you add your own fields or even a single blob to save stuff that's invisible to their UI.

 

I've been looking at TMS Sphinx, but it's like having to completely reinvent the wheel. Right now I'm building an MVP and I want something as turn-key as possible. The most common suggestion I've been getting is to use Wordpress. Ugh. No thanks! Even the Memberful folks suggested that. 

 

So far, there are solutions I've found on one end that offer member management that have REST APIs but cannot be used to save a few additional user-specific data items that aren't visible to users; and on the other end there are platforms that let me save whatever I want (they're a general DB) but I'd need to build all of the member management, auth, payment processing, and security stuff into it.

 

There's even AirTable, which is quite comprehensive, but it started out life as an easy-to-use drag-n-drop GUI-based web DB tool, and the REST API was added as an afterthought. Everything in the DB has to be added from the UI side and it doesn't look well-suited for saving user-specific state data from a middle-tier service.

[mytbo 5]

5 hours ago, David Schwartz said:

So far, there are solutions I've found on one end that offer member management that have REST APIs but cannot be used to save a few additional user-specific data items that aren't visible to users; and on the other end there are platforms that let me save whatever I want (they're a general DB) but I'd need to build all of the member management, auth, payment processing, and security stuff into it.

You wrote a long text but probably didn't spend a second to follow my links. If you did, what are you missing? I'm happy to take suggestions for further articles.

 

With best regards

Thomas

[omnibrain 14]

13 hours ago, David Schwartz said:

So far, there are solutions I've found on one end that offer member management that have REST APIs but cannot be used to save a few additional user-specific data items that aren't visible to users; and on the other end there are platforms that let me save whatever I want (they're a general DB) but I'd need to build all of the member management, auth, payment processing, and security stuff into it.

Supabase

Try Supabase. It is postgreSQL+postgREST+Auth.

You can manage your access rights via row level security directly in the database.

If the automatic API via postgREST does not fit you can always build something yourself later, because in the end it's postgreSQL and you can use it as such.

[David Schwartz 415]

20 hours ago, mytbo said:

You wrote a long text but probably didn't spend a second to follow my links. If you did, what are you missing? I'm happy to take suggestions for further articles.

 

With best regards

Thomas

You said:

 

On 3/14/2023 at 10:20 AM, mytbo said:

If you want to use mORMot for the implementation ...

I do not. I'm looking for basic principles that rise above implementation issues. I didn't find anything there that was relevant to what I'm looking for, but I appreciate that they seem to ring a bell for you.

 

[David Schwartz 415]

12 hours ago, omnibrain said:

Supabase

Try Supabase. It is postgreSQL+postgREST+Auth.

You can manage your access rights via row level security directly in the database.

If the automatic API via postgREST does not fit you can always build something yourself later, because in the end it's postgreSQL and you can use it as such.

thanks. I'm working with TMS XData and a bit of Aurelius, and Sphinx can handle all of the auth stuff if I want to build it myself. The back-end DB is irrelevant, as Aurelius can connect to pretty much anything.