Source control for PostgreSql Data base with git

Question

I have a PostgreSql Data base, and I want to keep the schema in source control. I prefer using git because all of my code is in git. Is there a way to accomplish that. Thanks

Answer 1

Generally the modern approach to doing this these days is to learn to use propel ORM or Doctrine ORM, which will version your database for you and keep the structure in sync as you commit the basic schema (in an xml format) and migrations.

PHP ORMs: Doctrine vs. Propel Has more about propel vs. doctrine.

Answer 2

You can use some tool that can generate text representation of db schema into files in some folder under vcs.

To see adequate working tree status you'll need to run schema generation before git status.

Before update from remote (git fetch && git rebase or git pull) you'll need to get db schema into worktree.
If that version differs from latest version in HEAD then you'll need to revert it in db or commit.
After update you'll need to get db schema into worktree again, check out diff and revert it in real db until db schema get will produce clean worktree.

P.S. I'm using this technique for MsSql db schemas in git. I even added db data to repo, this may be useful too.

Answer 3

Hi future readers of this Q, I am the maintainer of project #yuniql: a schema versioning and migration tool. yuniql can help you maintain and keep your postgresql schema evolution by organizing your DDL and DML scripts in ordinary directories, commit to your git repo and perform gitops to your postgresql database. You may refer to github and getting started guide below ;)

https://github.com/rdagumampan/yuniql
https://yuniql.io/docs/get-started-postgresql/

Br, Rodel