> But a big pain is to maintain your database code, by example the version control of your functions.
The solution I came with is to have a git repository in which each schema is represented by a directory and each table, view, function, etc… by a .sql file containing its DDL. Every time I make a change in the database I make the change in the repository. It doesn't automate anything, it doesn't save me time in the process of modifying the database, it's actually a lot of extra work, but I think it's worth it. If I want to know when, who, why and how a table has been modified over the last 2 years, I just check the logs, commits dates, authors and messages, and display the diffs If I want to see exactly what changed.
Liquibase supports plain sql files (just needs one comment for author at the start) and custom folder structures, so if you actually want to take it one step further, do check it out ;)
It could not only be great for documentation purposes but also actually help maintenance by making sure that all statements are executed in all environments
On one of my oldest postgreSQL project which I usually edit on live database I also added version control in the form of a one liner pg_dump call that backup --schema-only.
This way whenever I make a change I call this one liner, overwrite previous sql dump in git repo, then stage all and commit.
This way il also enjoy diff over time for my schemas.
The solution I came with is to have a git repository in which each schema is represented by a directory and each table, view, function, etc… by a .sql file containing its DDL. Every time I make a change in the database I make the change in the repository. It doesn't automate anything, it doesn't save me time in the process of modifying the database, it's actually a lot of extra work, but I think it's worth it. If I want to know when, who, why and how a table has been modified over the last 2 years, I just check the logs, commits dates, authors and messages, and display the diffs If I want to see exactly what changed.