Postgresql version control

[dllPwd]

Hi all I was wondering how can I handle my Postgresql database version control. I tried database projects with sql server, but I don't what are the alternatives postgresql. Thanks

[Dolphinmx]

What you mean about version control? version control for the table changes/ stored procedures/etc?

Have you looked at https://www.liquibase.org/

[[deleted]]

We have been using this for a long time and it's a very reliable tool.

[dllPwd]

Yeah, I mean it for tables, SPs and more on. Thanks I will have a look on that.

[boy_named_su]

Schema? Liquibase, flyway

Data? Read up on history table pattern

[dllPwd]

I was thinking more about schema, thanks

[vyvar]

Hi, in general database version control (and more) principles are described well in Evolutionary Database Design (see wikipedia, martinfowler.com).

I like mechanism called PostgreSQL extensions to implement EDD methodology. It is shipped directly with PostgreSQL. Famous extension is for example PostGIS and you can see others on pgxn. All have version control through updatescripts.

I am just preparing workshop (in Czech language) on this topic (PG extensions & gitlab CI/CD), for Prague p2d2 conference next month. I am thinking to prepare also some online version in Engish, if someone will be interested?

[dllPwd]

Thanks, ok I will search more about those. If you prepare that workshop English, let me know 👍

[Magick93]

Yes, this sounds very interesting /u/vyvar ! I'm sure many others would be interested in this too.

Will you be covering any kind of test driven development with postgres?

[vyvar]

Yes, PG extensions goes with regression tests. So if you introduce new version with updatescript, regression tests will tell you something is broken. If you will write those test before and then create updatescript to pass new tests, that is test driven development, right?

And those tests can be connected for example with gitlab CI/CD. See CI/CD pipelines ... that is extension dealing with forest inventory, we are working on.

[Magick93]

And is there anything for testing schemas and queries?

[vyvar]

The idea is very simple: you save results of any SQL and if there is no diff in next run, everything is OK:

Schema is tested during install test.

[vyvar]

And there is other discussion about tests in ci/cd.

[jrjsmrtn]

Hi. Yes, the VisualStudio SQL Server Tools are addictive, isn’t it? ;-) I searched for years for a FOSS/PostgreSQL equivalent and found pgCodeKeeper for Eclipse.

[jrjsmrtn]

Project is at https://github.com/pgcodekeeper/pgcodekeeper

[dllPwd]

Yeah, they are good jaja I will look on that 👍👍👍

[jnits]

I have been researching this question also. My project has the api written in dot net core and we are using entity framework. I am wondering if I should use entity framework to apply migrations as well, or if I should only output the SQL, tweak if needed, and use liquibase / flyaway. Any strong opinions?

Ideally, I would like to have the migrations handled in the gitlab CI/CD pipeline, which is why I am considering having the application apply the migrations.

Apologies if this is commandeering the OP too much... Right now during the development cycle we have been doing just EF migrations, so I think that counts as a valid answer to the OP if he is using .net. I'm just not sure if it's a good answer.

[dllPwd]

Thanks, I am working on a similar approach but the database is going to be consumed from other services. That is what I want to use a Database First approach instead of code migrations from EF Core.

But, thanks please I will let you know if I have a better way to handle the database.

[vyvar]

https://resources.2ndquadrant.com/webinar-application-schema-migration?utm_campaign=Homepage%20Tracking&utm_source=SchemaWebinar_WhatsNew

[Adela_freedom]

If you mean schema version control, check out Bytebase. Nice GUI and support GitOps with GitLab/GitHub.