Create schema and tables on demand at the runtime

user_9122915 0
Viewed 819

So as the title suggests - I need to create an application (preferably Spring Boot), which will create schemas and tables based on user input. Basically, a rest endpoint will be offered to the clients where they would upload their data model in json format. I'll be parsing the json and constructing the db artifacts (schema and tables) in runtime. And once all the tables are created, provide a rest endpoint (with unique identifier), to the client, to perform CRUD operations on their schema.

The approach I am considering currently is -

  1. Create a super user in db , before deploying the app which will have priviliges to create new schemas and db

  2. Create prepared statements to invoke schema/table creation on demand. The prepared statements will have place holders to take the schema name and table definition.

  3. After proper authentication, allow users to upload their data model definition in json.

  4. Clean the json and invoke the schema/table creation prepared statements.

Few questions that I had in mind -

  1. Since all these DB operations will be invoked from a single super user's account, is it safe ?

  2. The schemas and tables will be realized using native SQL queries instead of Hibernate's ORM capabilities. Is it safe/efficient ?

  3. For the CRUD operations, is it possible to switch the db connection from super user to the client specific schema created in the earlier steps ? Or should I continue using the same super user for the CRUD operations? It would be nice if it is possible to switch schemas in runtime using Hibernate/Spring-Boot.

What I would like is a general approach to this problem. I do not need any code.

spring-boot hibernate spring database java
1 Answers
  1. A typical web application already has permissions to DELETE all the data for all the users.
  2. JPA makes your queries slower, not faster. JPA can help with caching, but it doesn’t seem you need this.
  3. Yes, you can have multiple datasources in Spring Boot. Look at this for example: https://www.baeldung.com/spring-abstract-routing-data-source

Be aware that your database might not like having millions of tables. Query planning, maintanance jobs, backups, etc all get performance penalties. Basically, databases are not designed for your use case.

user_40766060
Related