For my current project, we're auto-generating a relatively full-featured CRUD interface with granular access controls and the works. [one detail to add there is that while it's primarily used as an admin interface now, it's not meant to be restricted to that - it has access control and workflow support built in that we're building product flows on, where the UI is still generated the same way]
It's not just from the SQL, but we use Sequel (Ruby ORM) to introspect the DB as a starting point, coupled with a plugin to Sequel that lets us annotate the models to provide more precise information that our Sinatra based API introspects to return JSON data to our React frontend that drives how the UI is presented. This extends to e.g. declaring which fields are most useful for users to search for relations that should be linked via foreign keys, for example (e.g. users are linked to by id, but when linking a user from another record, they are searchable by name and e-mail). As much as practical we auto-generation validations etc. from the database schema as well.
I think doing this by SQL alone would be quite painful unless you sacrifice usability, because a typical database schema has too little information on how users think about the data.
E.g. we dialled back on using Postgres enum types because they're annoying to update and deal with if you frequently update the allowed values - but you need relatively little extra config to be able to generate very full-featured interfaces, and you can make most of that extra information entirely declarative. Foreign keys is another issue, as described above - a normal database schema just tells you how things are linked together, not how users would like to see it. E.g. knowing a comment was written by user 42 is much less useful than knowing it was written by bob@example.com; to allow user-friendly linking etc. you'll want more information. We refer to our additional layer of information about the structure of our data as our meta schema.
You certainly could put the parts that don't fit perfectly in your database schema in your database anyway as regular data, and to some extent we do - e.g. the meta information that we augment our Sequel model classes with can be overridden by rows in a table.
But the main reason we've not fully embraced that vs. annotating the model classes in code is that it makes version control painful, and if you update it with migrations it's really not saving that much vs. just putting it in the model classes (this might be different if you have other applications accessing the database and the database in effect needs to be a versioned API in itself accessible by multiple consumers, but in this case all the database access happens via the API exported from the model classes).
It's not just from the SQL, but we use Sequel (Ruby ORM) to introspect the DB as a starting point, coupled with a plugin to Sequel that lets us annotate the models to provide more precise information that our Sinatra based API introspects to return JSON data to our React frontend that drives how the UI is presented. This extends to e.g. declaring which fields are most useful for users to search for relations that should be linked via foreign keys, for example (e.g. users are linked to by id, but when linking a user from another record, they are searchable by name and e-mail). As much as practical we auto-generation validations etc. from the database schema as well.
I think doing this by SQL alone would be quite painful unless you sacrifice usability, because a typical database schema has too little information on how users think about the data.
E.g. we dialled back on using Postgres enum types because they're annoying to update and deal with if you frequently update the allowed values - but you need relatively little extra config to be able to generate very full-featured interfaces, and you can make most of that extra information entirely declarative. Foreign keys is another issue, as described above - a normal database schema just tells you how things are linked together, not how users would like to see it. E.g. knowing a comment was written by user 42 is much less useful than knowing it was written by bob@example.com; to allow user-friendly linking etc. you'll want more information. We refer to our additional layer of information about the structure of our data as our meta schema.
You certainly could put the parts that don't fit perfectly in your database schema in your database anyway as regular data, and to some extent we do - e.g. the meta information that we augment our Sequel model classes with can be overridden by rows in a table.
But the main reason we've not fully embraced that vs. annotating the model classes in code is that it makes version control painful, and if you update it with migrations it's really not saving that much vs. just putting it in the model classes (this might be different if you have other applications accessing the database and the database in effect needs to be a versioned API in itself accessible by multiple consumers, but in this case all the database access happens via the API exported from the model classes).