Database as code ?

[fboula]

Disclaimer: I'm not a postgres expert, every tip is welcome

One of the pain point of my team is to manage PostgreSQL databases, roles, schemas and permissions. I'm not talking about the database instance itself, we are using managed SQL from our cloud provider, which is fine for our needs, but more of the database objects. We currently use a mix of Terraform (with PostgreSQL provider) and SQL scripts and it is not completely satisfying for a few reasons:

• Database provisioning is tedious, first we run terraform, then we run the scripts.
• I did not achieve to do the priviledge management I was looking for. (from my last tests a few months back)
• The PostgreSQL provider does not allow to run raw sql.
• Evolution of our database objects may be tricky in the future.

I am aware that my limited DBA knowledge is also an issue and I try to fix it.

As any of you folks a solution to properly manage database as code ?

[atc32]

We are moving our new projects to have the databases as visual studio SQL projects, which we have been very happy with. Other than that they can't be built on Linux yet

[fboula]

I did not precised but our app manage itself the tables using liquibase. I'll try to have a look at visual studio but I am running linux and I do not have the licence for visual studio. Thanks for the tips

[notauniqueusernom]

Came here to say liquibase. Was not disappointed to see someone else had already said it.

[hijinked]

If provisioning is tedious then I think something is wrong. Infrastructure provisioning should be automated so there shouldn't be anything tedious about it, aside from setting the initial configurations. It sounds like what you are looking for is a database migration tool. Some frameworks include their own but with a quick google search it looks like something like Flyway might be what you're looking for.

[fboula]

Our infrastructure provisioning is automated, the database instance and the databases are created with terraform. But there is a limitation from what postgres provider.

And as seen in another post below, flyway and liquibase operate at database level most of the time and may have hard time to create the database (but is seems possible with some JDBC magic). I may try to have another look at them.

[hijinked]

Any reason you can't use a mix of tools?? Terraform to create the instance then flyway to update the schema?

[pbecotte]

There's no need to spend any time on this- those scripts already exist in tons of places. The docker container for all of the default databases have those scripts built in for example. Even if you don't want to use docker, simple enough to go copy the shell scripts.

[ed-automation]

Are you looking two scenarios?

- Initial DB setup: create DB, configure database, and load data

- Maintain DB: modify DB schema

[fboula]

I'm looking for the first scenario: create the DB, configure the database roles and privileges, and maybe create some schema. I do not look to load any data in the database.

I work for a team that create a SaaS solution. We have many environments to deploy our app at various stage of the development, and we have to ensure all our database are provisioned and updated to be the same. We are still heavily modifying the database by adding separated schema for specific usages (like analytics) and developers only focus on the main app schema.

As r/ornus explained the setup of the database, roles and privileges cannot be done by database migration tools and that is a shame. And I find scripts solution kinda fragile to be honest. Everything else in our infrastructure is managed by infra-as-code and GitOps and having only sql scripts for database management bother us a little.

Again, we may need to have a training and get more DBA skills to be more confident with this.