Complex Data Models and config files

[BumpaRoy]

As I start to get my hands around some of this, I am trying to figure out the best practice for exposing tables and relationships in a database tied to an ERP System. Lots of tables.

I see the config file which exposes and defines the data access becoming pretty complicated. Traditionally, SQL had all of the tables and relationships defined already, and it looks like this will require me to duplicate what SQL already knows into a config file. Granted, the project I am looking at only needs 15 to 20 tables out of the DB, but also having to redefine those relationships.

My question is: Is this the design of this tool that the complete data access model needs to fit into the config file? Or is there a way to leverage some of what SQL already knows?

[Aniruddh25]

Hi @BumpaRoy,

Thanks for the question.
Currently, for security reasons, just like you are explicitly required to add every entity in the config to expose the underlying table, you are also required to specify between which entities you need the relationships exposed by updating the relationship section of the config.

dab update Orders --relationship Customer --cardinality one --target.entity Customers

This helps the tool understand you indeed want to expose customer under orders. The tool automatically infers foreign key information if they are already present between your tables. So, not the complete model needs to fit in the config file - but only what you actually desire to expose as a REST/GraphQL endpoint.
Granted, it could be enhanced by providing an option to expose all the tables and their relationships completely automatically.

[BumpaRoy]

I just see the potential of these config files becoming a nightmare to maintain. Lots of entities and lots of relationships.

[yorek]

Given that SQL Server and Azure SQL provide full support for JSON, it would be possible to generate the config file right from a T-SQL query. I'll play with it in the next days and I'll post the results here. In the meantime, it would be great if you can suggest an improvement in the Ideas section (https://github.com/Azure/data-api-builder/discussions/categories/ideas) about having a DAB CLI option to automatically generate the configuration file from an existing database.
In order to help us building it in future, what would be you expectation in terms of generate configuration? For example, a few points that comes to my mind

• what permission to each table should be given by default?
• should views and stored procedure included too by default?

[yorek]

I added a sample here: #1705. I tested the script on the AdventureWorksDW database, with 27 tables