Does the TLE layer still add value now that dbdev generates migration files?

[point-source]

Is dbdev still solving a problem? If so, what problem is it solving?

Context

I maintain https://github.com/point-source/supabase-tenant-rbac, a package distributed via database.dev. I've been working through several user-reported issues that all trace back to the same root cause: objects created via CREATE EXTENSION are registered as extension members in pg_depend, which causes standard PostgreSQL tooling to skip them.

The problems I and my users encounter

• pg_dump excludes extension member objects (assumes CREATE EXTENSION will recreate them on restore)
• supabase db diff and supabase db pull don't capture changes to extension-owned objects
• Logical backup/restore can fail entirely (as warned on https://database.dev/docs)
• Migration squashing drops RBAC state because the objects aren't visible to the diff tooling

These aren't edge cases, they're core local development and operational workflows. See point-source/supabase-tenant-rbac#23 and point-source/supabase-tenant-rbac#41 on my repo for real user impact.

My question

With the deprecated in-database client, there was a clear reason for pg_tle: users ran dbdev.install(...) directly in their database, so TLE was the delivery mechanism. But now that the recommended workflow is dbdev add, which generates a migration file, the TLE layer sits between the user and what they actually need, which is plain SQL objects in their database. Why are we using a cli tool to generate migration files that pretty much just take normal migration files and wrap them in a pg_tle extension?

The current flow:

1. dbdev add → generates a migration file
2. Migration file calls pgtle.install_extension(...) + CREATE EXTENSION
3. Objects are created as extension members
4. Standard tooling can't see them

The alternative flow:

1. dbdev add → generates a migration file
2. Migration file contains plain CREATE TABLE, CREATE FUNCTION, etc.
3. Objects are normal schema objects
4. Everything works: dump, restore, diff, pull, migration squashing

Both flows end with a migration file. The only difference is whether the SQL inside is wrapped in TLE or not. The TLE wrapping is what causes the tooling breakage.

What TLE provides vs. what it costs

Benefits of the TLE layer:

• ALTER EXTENSION UPDATE TO 'x.y.z' as an upgrade mechanism — but this still requires the package author to publish upgrade scripts, which is the same work as providing a versioned migration file. And dbdev add could
  generate an upgrade migration just as easily.
• @extschema@ substitution — convenient but trivially replaced by the CLI doing a string substitution when generating the migration.
• Version tracking via pg_available_extensions — useful but achievable with a version function or metadata table.
• pg_tle requires pg_tle to be installed, which is available on Supabase but adds friction for other PostgreSQL hosts.

Costs:

• Breaks pg_dump / logical restore
• Breaks supabase db diff and supabase db pull
• Breaks migration squashing workflows
• Requires pg_tle as a dependency
• Package authors need workarounds to support local development

Proposal

Could dbdev add have an option (or even a new default) to generate plain SQL migrations instead of TLE-wrapped ones? The CLI already fetches the package SQL from the registry — it would just need to resolve @extschema@ to the target schema and emit the raw DDL.

The registry, CLI, and versioning model all remain valuable. It's specifically the TLE install mechanism in the generated migration that causes problems.

For upgrades, dbdev upgrade could generate a migration containing only the diff (ALTER/CREATE OR REPLACE statements), which is what package authors already write as upgrade path scripts.

Am I missing something?

[imor]

Thanks for starting this discussion @point-source. You raise some valid points. We are re-evaluating whether pg_tle is really required and having internal discussion about this.