👉📝 Feedback Wanted: Share Your Use Case & Experience

[tianzhou]

I'd like to collect some customer testimonials to feature on our website and better understand your use cases. Please share your comments with the following information (all fields are optional):

• Are you using it in prod (Y/N):
• Use Case:
• What I Love:
• What Could Be Better:
• Other Tools I've Tried:
• Company (skip this if you're not comfortable to disclose):

[Dulanic]

Use Case: Playing! I like testing new things.
What I Love: Don't know yet
What Could Be Better: It feels like a default dump isn't a good way to export the data as the plan always fails. I was just looking for test with a existing data set for another project I've worked on Teslamate. However, I couldn't find a easy way to even try to test this. Maybe we did a bad job designing the schema in the first place! I posted the dump results, teslamate-schema.sql

Other Tools I've Tried: N/A

[abarreir]

Use Case: Lots of views & materialized views, and some of them are cascaded, so when we want to change a simple condition we have to drop / recreate all views, the migration to write is error prone and code review is counterproductive

What I Love: The promise, allowing devs to only review the change on the schema and not the generated migration

What Could Be Better: At the moment it does not work with my schema, crashes when generating a diff because of an incorrect syntax in the generated schema dump. Can't really share my schema. Also, I'm still on a Mac Intel, would be great to have an amd build on homebrew :)

Other Tools I've Tried: Atlas, but too expensive and comes with too many other features i don't need

[marcintustin]

Use Case: Capturing actual changes made in development and minimizing drift between development and production environments. Specifically we're using this in the context of modernising an ad hoc database schema incrementally into a more designed schema. I generally only use the dump functionality to see the current state of my databases, and manually prepare migrations. It also gives me and others visibility into the final state after a migration runs.

What I love: Filtering, ease of use out of the box, responsiveness of @tianzhou to bug reports, quality of documentation. Dumping to separate files makes viewing diffs much nicer.

What could be better: It still has bugs :) The migration generation functionality doesn't generate the most parsimonious changes (but for me this is not that important; it's also a problem with terraform, so I assume it's a largely intractable problem). Would be nice to have more debugging output available to see what's happening, which would probably help me submit more precise bug reports.

What would be really lovely would be if pgschema could use postgres' built in ability to get the text of objects ddl as much as possible to ensure consistent formatting and use some kind of parsing (e.g. a treesitter grammar) to identify discrepancies between what pgdump is seeing and what postgres is outputting.

Other tools: Pgdump for dumping postgres schemas, and tooling built on top of that. OK, but frankly much more fiddly than this.

[KrisBraun]

Are you using it in prod (Y/N): N (not yet -- experimenting to see if it could replace atlas)

Use Case: I have a complex, multi-schema database with many views, functions, trigggers, and have always defined it decoratively and generated migrations from it using tools like tusker. This requires spinning up a shadow database and often has false-positive changes or errors in dependency order that need to be corrected manually. I simply want a reliable way to bring the development and then production database in line with the declared schema.

What I Love: The promise of terraform-like migrations

What Could Be Better: I'm experimenting and haven't yet successfully generated a plan from my schema. But I keep reporting issues and they're fixed incredibly fast!

Other Tools I've Tried: tusker, supabase (with all the diff engines it supports -- migra, pg-delta, etc.); currently paying for Atlas which feels like overkill since all I need is the migration-generation feature (which is the only one I've found that works properly)

Company (skip this if you're not comfortable to disclose): Plot (https://plot.day)

Rooting for this project to succeed!

[czlowiek488]

Are you using it in prod (Y/N):
Not yet, but I'm going to.
Not really sure what you consider a production, THIS IS a developer tool from my perspective.
I have separate migrator that actually will run migrations on production database, I will use THIS tool to simplify how I generate migrations and how I manage schema.
Use Case:
Node.JS multitenant boilerplate with row level security and row level defined encryption at rest.
This is boilerplate because I have my game that will use it as backend for inventory management in my private game and I also consider it using in different project I cant disclouse any info about.
What I Love:
I hate every single ORM and query builder tool that node.js community can give, I love to write raw sql queries in my code.
I was looking for prisma-like schema management tool but I wanted to use raw postgres queries - this is exactly what I was looking for.
What Could Be Better:
As I previously mentioned im doing pretty advanced encryption at rest.
I'm doing it with pgsodium extension that uses labels to define how table is encrypted.
There is no mention about it in the documentation, but it works really well with pgsodium though it requires to manually replace what was generated via dump.

CREATE OR REPLACE FUNCTION user_invitation_encrypt_secret_email()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
AS $$
		BEGIN

            IF new.email = '' THEN RAISE EXCEPTION 'Cannot encrypt empty string.'; END IF;
            new.email = CASE WHEN new.email IS NULL THEN NULL ELSE
			CASE WHEN new.encryption_key_id IS NULL THEN NULL ELSE pg_catalog.encode(
			  pgsodium.crypto_aead_det_encrypt(
				pg_catalog.convert_to(new.email, 'utf8'),
				pg_catalog.convert_to(('')::text, 'utf8'),
				new.encryption_key_id::uuid,
				new.nonce
			  ),
				'base64') END END;
		RETURN new;
		END;

$$;

instead of just this on table creation.

SECURITY LABEL FOR pgsodium ON COLUMN user_invitation.email IS 'ENCRYPT WITH KEY COLUMN encryption_key_id NONCE nonce';

Also because of nature of pgschema I cannot at schema level generate encryption key to be used for column encryption. Because encryption_key_id will always be different plan will always request changes in the same place but just with different v4 uuid value in encryption definition. Below example (may not execute, but logic is correct).
It would be nice to have workaround for this scenario.

DO $$
DECLARE
    key_uuid uuid;
BEGIN
    SELECT id
    INTO key_uuid
    FROM pgsodium.create_key();

    EXECUTE format(
        'SECURITY LABEL FOR pgsodium ON COLUMN "user".email IS %L',
        'ENCRYPT WITH KEY ID ' || key_uuid
    );
END
$$;

[bme-cgm]

Are you using it in prod (Y/N): N
Use Case: Am considering the usecase to allow projects to only state final SQL and have pgschema apply the diff to a production db.
What I Love: That this project goes a long way to make this possible.
What Could Be Better: I would like to filter the produced plan to leave out destructive commands/changes like remove column, remove table, drop database. I don't know how robust the comparison is (out of order schema, case sensitive, etc).
Other Tools I've Tried: flyway (free), liquibase (free), and more. None support state-based migration. Ideally I would like to end up with a similar solution to what mssql has: create dacpac files and use sqlpackage /publish for deployment.

[cysp]

Are you using it in prod (Y/N):
No

Use Case:
In my current project I am using Drizzle for schema definition but exporting the schema to a DDL document for application via pgschema. I am use neon's serverless offering with ephemeral databases for pull request integration testing and am using pgschema to apply the proposed schema to those ephemeral databases as well as to the long-lived database upon merging to the mainline branch.

What I Love:
The declarative approach to schema management, with this tool generating the plan to move the current state to the desired state.

What Could Be Better:
Plans take a while 🙂

Other Tools I've Tried:
TypeORM, Prisma, Drizzle migrations.

[fjapm]

Love this project. I actually tried building something similar a few years ago, but never ended up continuing with it. Looking forward to seeing how the project evolves over time.

I had a chance to test it against our current workflow, and unless I'm missing something, there are currently two major limitations that would completely prevent us from adopting pgschema.

1. Multi-schema support

Multi-schema workflows don't appear to be first-class citizens, and I'm not sure whether they're fully supported at all. For us, the lack of robust multi-schema support is a dealbreaker. From my testing, scenarios like the one below don't seem to work correctly.

create table shared.users (
  id varchar not null
);

create table lms.students (
  id varchar not null,
  user_id varchar references shared.users(id) not null
);

2. Declarative data management

Another huge missing feature is the lack of support for what Atlas calls declarative data management. Beyond lookup tables, we manage database objects such as pg_cron jobs. Being able to define and manage those resources declaratively is important for us. Ideally, cron jobs and similar database-level objects should be explicitly declared and versioned alongside the schema itself.

For now, we'll need to stick with Atlas, but I'd be more than happy to revisit pgschema in the future if support for these capabilities is added.
Wishing you guys good luck!

[tianzhou]

@codextremist

Multi-schema should be supported (and especially for your use case). If you encounter an issue, please open a github issue with the steps.

Data management is out of the scope

[fjapm]

@tianzhou could you point me the documentation for that? I’m receiving an error whenever I run pgschema apply without the —schema parameter.

[tianzhou]

@codextremist it should work with the standard plan/apply workflow https://www.pgschema.com/workflow/plan-review-apply. If not, please file a github issue with your schema, steps and error

[irisjae]

@codextremist

Multi-schema should be supported (and especially for your use case). If you encounter an issue, please open a github issue with the steps.

Data management is out of the scope

I don't understand how multi-schema is supported. E.g., I have an index in schema public using a function from schema admin. It seems pgschema expects me to dump public and admin separately, and plan separately, but that fails since understandably; planning for schema public complains that the function from schema admin is not defined. Trying to use a complete pg_dump, with all my schemas, to plan does not seem to work well either with various errors such like Error: failed to apply desired state: failed to apply schema SQL to temporary schema pgschema_tmp_20260808_154626_a456dbef: ERROR: no schema has been selected to create in (SQLSTATE 3F000).

Is the recommended flow for multi-schemas documented?