auto-generated REST APIs?

[David Schwartz 426]

Does anybody here have any experience using tools that auto-generate a REST API from a DB schema?

 

Here are a couple I've found:

 

https://github.com/o1lab/xmysql

 

https://github.com/mevdschee/php-crud-api

 

I'm curious about using something like this vs. a hand-rolled REST server. 

 

Obviously this is going to publish pretty much everything, delivering 1:1 mappings to the DB structure. It strikes me as best for simple DBs.

 

But from what I've seen of examples of REST service designs, they often function a lot like views rather than just mirroring the DB's structure.

 

Any thoughts?

[Lars Fosdal 1792]

IMO, it is crucial for a REST API to not directly reflect the underlying system.

The goal should be to ensure that any underlying implementation detail is hidden from the REST API.
I would be wary of creating APIs that go 1:1 on the DB structure.

[Mohammed Nasman 11]

I think I saw that once before with TMS Xdata

 

But I agree with @Lars Fosdal, The REST API should be design in the API way, not the Database way, it should not have 1:1 relation with database.

Also for some performance issues, you should send all the required data using one API call, but this may require multiple relations with tables on DB.

 

[mvanrijnen 123]

Just written our own tool for this. We already have the DB layout in specific tables, with extra attributes, used for generating std forms, lists etc. 

Now busy with a experiment to acces tables with REST, also publish the table data & info with radserver (internal use only!).  

 

Generating now all the radserver resources for schema's (collection of tables for us), tables etc. Mostly just for getting the hang of radserver etc. 

(developing a a 1990 years environment here)

 

But i agree that you should not expose your tables like this for the "public" or for what application whatever, my colleague likes to use tables for client apps (not through general api's though).

I think api's should expose usable information blocks or usable application blocks, not simple table CRUD actions etc. 

 

Edited November 23, 2020 by mvanrijnen

[Arnaud Bouchez 407]

It is clearly a wrong good idea.

I don't see the benefit in respect to executing the SQL and return some JSON - which is a bad idea per se.

 

I just answered on SO why direct exposition of the database to a REST/HTTP/JSON client is not a good idea.

https://stackoverflow.com/a/64936643/458259

 

It is still a 2-Tier architecture, from the logical point of view.

Of course, it is a 3-Tier architecture from the physical point of view, but what matters is the logical point of view, and adding a physical layer is not a benefit here.

 

[Angus Robertson 574]

If the database uses stored procedures to isolate the business functions from underlying data and verify input, data, then having the REST API match the SPs would save a lot of coding,  But since most real databases are relationship based with multiple tables,  you don't really want that functionality in browser Javascript. 

 

Angus

[mvanrijnen 123]

16 hours ago, Arnaud Bouchez said:

It is clearly a wrong good idea.

I don't see the benefit in respect to executing the SQL and return some JSON - which is a bad idea per se.

 

I just answered on SO why direct exposition of the database to a REST/HTTP/JSON client is not a good idea.

https://stackoverflow.com/a/64936643/458259

 

It is still a 2-Tier architecture, from the logical point of view.

Of course, it is a 3-Tier architecture from the physical point of view, but what matters is the logical point of view, and adding a physical layer is not a benefit here.

 

"Anyone on the client side with the credentials could run any kind of SQL statements, which may be pretty unsafe, like DROP TABLE or DROP DATABASE in the middle of a SELECT statement. Or it could access data from other customers."

100% agree 🙂

 

But for us it;s for "inner" use, and not directly sql statements, there are different parameters from which sql statements are generated, filter, (and|or etc), sortby, fields. Thereby for us it's select only, not update/insert/delete. 

it's more like an excercise for playing with rest

 

[Wagner Landgraf 43]

TMS XData does that. These three videos show it in details:

 

https://www.youtube.com/watch?v=AZs9e2DNXdI

https://www.youtube.com/watch?v=e31Co9Fv3YY

https://www.youtube.com/watch?v=QvgS8pjpfkk

 

This has nothing to do with executing arbitrary SQL statements. It's regular REST endpoints.

 

Regarding being 1:1 with underlying db system, in my opinion it depends. As someone mentioned here, you might need an "inner" use in which this is not a problem. Or, you might use it for simple entities like a list of countries or something like that. So for example use it for simple or non-critical entities, and then implement DTOs and specific business logic for more complex or critical business operations.

 

Like anything, it's just a nice tool which is available. Use if it's helpful for you.

Edited November 24, 2020 by Wagner Landgraf

[David Schwartz 426]

On 11/24/2020 at 1:37 AM, mvanrijnen said:

But for us it;s for "inner" use, and not directly sql statements, there are different parameters from which sql statements are generated, filter, (and|or etc), sortby, fields. Thereby for us it's select only, not update/insert/delete. 

it's more like an excercise for playing with rest

I guess I should have been more specific in my question. What I was thinking was using this myself in situations when the DB needs are simple enough that going to the trouble of building a server-side app may not be warranted.

 

I cannot imagine publishing this for outside use -- only for internal use.

 

A good compromise would be if the generator could work with stored procs and/or views defined in the DB to extract specific filtered or aggregated data.

 

Also, it makes sense to me as a means of switching from classic Client/Server to REST-based interfaces without having to change anything (unless stored procs and/or views are being used).

[Arnaud Bouchez 407]

On 11/24/2020 at 12:10 PM, Wagner Landgraf said:

This has nothing to do with executing arbitrary SQL statements. It's regular REST endpoints.

This is not TMS DB Remote for sure.

But a request like GET /tms/xdata/Customer?$filter=Country/Name eq 'USA'&$orderby=Name&$top=10 seems very close to a SQL SELECT.

 

Security seems pretty basic: is there something more than the CRUD permissions of https://download.tmssoftware.com/business/xdata/doc/web/entity_sets_permissions.html ?
I guess the https://download.tmssoftware.com/business/xdata/doc/web/onentityget_event.html callback seems a bit manual to handle.

Even if the client is authenticated, how to you go deeper into the authorization? For instance, if you have a DB with all customers for all salers, how to prevent one saler to get the customers of other salers from the same table?

So not any arbitrary SQL statement could be executed, for sure. But the client side is still deciding what it queries, unless the server is bloated with authorization code.

 

What I would like to emphase is that it may be a 3-tier physical architecture, but it is still a 2-tier logical Architecture. The logic is still in the client.
A much cleaner n-Tier architecture would be to create a real business layer (logical 3-tier) - or even better an application layer (logicial 4-tier as in DDD), and expose only safe and efficient REST endpoints to the clients.
Then the server will query the data, and expose it as DTOs to the clients, depending on each bounded context (business layer) or each use-case (app layer).
The client side is still much too tied to the underlying database. In a clean SOA architecture, you don't start from the database, but from the client use-cases.

Edited November 26, 2020 by Arnaud Bouchez

[Wagner Landgraf 43]

1 minute ago, Arnaud Bouchez said:

This is not TMS DB Remote for sure.

But a request like GET /tms/xdata/Customer?$filter=Country/Name eq 'USA'&$orderby=Name&$top=10 seems very close to a SQL SELECT.

 

Security seems pretty basic: is there something more than the CRUD permissions of https://download.tmssoftware.com/business/xdata/doc/web/entity_sets_permissions.html ?

Even if the client is authenticated, how to you go deeper into the authorization? For instance, if you have a DB with all customers for all salers, how to prevent one saler to get the customers of other salers from the same table?

 

What I would like to emphase is that it may be a 3-tier physical architecture, but it is still a 2-tier logical Architecture. The logic is still in the client.
A much cleaner n-Tier architecture would be to create a real business layer (logical 3-tier) - or even better an application layer (logicial 4-tier as in DDD), and expose only safe and efficient REST endpoints to the clients.
The client side is still much too tied to the underlying database. In a clean SOA architecture, you don't start from the database, but from the client use-cases.

I will not get into an architectural discussion or in a feature discussion, it's out of the scope of the topic.

It's a tool that anyone can use if they want. It's not SQL SELECT, there is no injection, it's more secure. Yes, you can do complex authorization mechanisms.

Finally, that is not "XData", but one of XData tools. If user wants, he can simply choose not to use it, and do a 15-tiered application without any client-side logic. 

I'm just answering the OP question.