How to Use Drift with an Existing Postgres Database and Introspect Tables?

[AndryHTC]

Question

Hi everyone,

I'm setting up a Dart server and want to use Drift to manage database interactions. My database is already set up in Postgres, and I want to avoid rewriting all the table definitions manually in Drift. Ideally, I'd like to introspect my Postgres database to pull the table definitions directly and generate Drift code based on that.

My main questions are:

1. Is it possible to use Drift with a pre-existing Postgres database by introspecting the database structure?
2. If so, how can I generate the Drift code for my existing tables?
3. Are there any specific tools or configurations in Drift to handle this, or would I need to use an external tool?

Any guidance or examples would be greatly appreciated! Thanks in advance for your help!

---

Additional Context

I’m running my server with Dart and connecting to a Postgres instance. My goal is to leverage Drift’s type-safe querying capabilities but without redefining all my existing schema manually. Ideally, I’m looking for a solution that’s both efficient and minimizes the risk of introducing inconsistencies between my Postgres schema and Drift’s generated code.

Thanks!

[simolus3]

Is it possible to use Drift with a pre-existing Postgres database by introspecting the database structure?

Yes! Drift's schema support for Postgres is a bit limited compared to how well it supports SQLite, but since you're using other tools to manage the schema, that should mostly be fine.
The first step is setting enableMigrations: false on PgDatabase so that drift will never try to mess with your schema by e.g. trying to recreate tables.

For questions 2 and 3, unfortunately the steps are a little harder than with SQLite and I'm not aware of a standalone tool that automates everything (but I think @davidmartos96 has worked on something similar generating drift table classes for the electric SQL client, that is probably a really good starting point).
If you have CREATE TABLE statements, what might work is just pasting them into a drift file, which allows defining drift schemas from SQL. These files take SQLite as a dialect, but since we only care about getting the types right to run queries, you can remove most column constraints.
For postgres-specific types that SQLite doesn't support (like UUID or datetimes), you can import and use PgTypes in drift files too.

[davidmartos96]

Yes, I worked on something similar in the past for the electricsql client in Dart. It used the Prisma CLI to introspect into a . schema file and then it generated a drift class based on that, handling all pg constraints using custom drift types (int4, int2...)
Ideally that part of the project could be extracted, as it's not really coupled with electric. Here you have a few links with the core implementation if you want to dig into it.

Dart tests to check the generated code. Expectations are in the fixtures folder.
https://github.com/SkillDevs/electric_dart/blob/develop/packages%2Felectricsql_cli%2Ftest%2Fsrc%2Fgenerate_drift_schema_test.dart

Main introspect + generate using the Prisma CLI
https://github.com/SkillDevs/electric_dart/blob/5884da4366647acb6f0c570ff4497ae133db0ec0/packages/electricsql_cli/lib/src/commands/generate/command.dart#L424