PostgreSQL development workflow
database, development-environment, postgresql, workflow
Solution
Check out liquibase. We use it in the company I work at to setup our PostgreSQL database. It's open source, easy to use and the changelog file you end up with can be added to source control. Each changeset gets an id, so that each changeset is only run once. You end up with two extra tables for tracking the changes to the database when it's run.
While it's DB agnostic, you can use PostgreSQL SQL directly in each changeset and each changeset can have it's own comments.
The only caveat from having used it is that you have to caution yourself and others not to re-use a changeset once it's been applied to a database. Any changes to an already applied changeset result in a different checksum (even whitespace) which can cause liquibase to abort it's updates. This can end up in failed DB updates in the field, so each update to any of the changelogs should be tested locally first. Instead all changes, however minor should be inserted into a new changeset with a new id. They have a changeset sub-tag called "validCheckSum" to let you work around this, but I think it's better to try to enforce always making a new changeset.
Here are the doc links for creating a table and creating a view for example.
Problem
I am starting to build a new database for my project using PostgreSQL. (I am new to PostgreSQL and database by the way.) I think my development workflow is very bad, and here is a part of it: - create table/view/function with pgAdmin. - determine the name of the file before saving the code. The goal is to be able to recreate the database automatically by running all the saved scripts, I need to know the order to run these scripts for dependency reason. So I add a number for each file indicating the order. for example: 001_create_role_user.ddl, 002_create_database_project.ddl, 013_user_table.ddl - save the code. - commit the file to repository using GIT. Here are some bads I can think of: - I can easily forget what changes I made. For example, created a new type, or edited comment - It is hard to determine a name (order) for the file. - Change the code would be a pain in the ass, especially when the new code changes the order. So my workflow is bad. I was wondering what other Postgres developers' workflow looks like. Are there any good tools (free or cheap) for editing and saving scripts? good IDE maybe? It would be great if I can create automated unit tests for the database. Any tool for recreating the database? CI server tool? Basically I am looking for any advice, good practice, or good tool for database development. (Sorry, this question may not fit for the Q&A format, but I do not know where else to ask this question.)