What is the best practice to manage sql files in heavily postgreSQL files based projects?

user_6914106 0
Viewed 213

I'm currently working on project which is using postgreSQL. In this project I should develop a lot of SQL functions, Triggers/Events, Views, and etc. This project is using docker to manage its services.

I want to separate thees files into multiple files to have a better and more flexible version control on this part of the project. My question is how to manage and organize sql files? Is there any standard for that or not?

The first thing came into my mind was creating multiple folders named functions, triggers, and etc. But I don't know deploying this structure would be a best practice or not. Also, because there are multiple tables and each of them, have their own functions and triggers, simple architecture like what I said might cause problems while the project grows.

Also, I know how should I import sql codes into postrgreSQL with docker on docker startup but by using the simple architecture I said, I should write a code in Dockerfile to search for all sql files and import them to database which seems the opposite of best practice for me.

To clarify more, I'm looking for a standard to manage my files. For example, this is how MVC files are manged:

index
|
|-Models|
|       |- user_model
|       | ...
|
|-Views |
|       |- login
|       | ...
|...

Update1: The MVC showed above is an example, I'm looking for a satisfying architecture for multiple sql files.

postgresql sql
2 Answers

Keep the files in a source versioning system (you are probably doing that already).

Use psql to execute your scripts.

Have a main script main.sql that includes others:

\i models/models.sql
\i views/views.sql
...

Each of those scripts could again include the individual scripts.

That way you can have small files, and the overall execution order is still maintained.

user_64643080

I think it would be much better to use database version control tools like Flyway, It has a community version and also a docker image. You can use it for your migrations, views, triggers, etc.

user_129005620
Related