Trouble using CLI for local development, type generation, and remote sync when schema depends on certain extensions

[emmbm]

Bug report

Describe the bug

Reading around, I've come to understand that for the time being (or forever?), migrations generated using supabase diff do not include extensions. While it is an easy manual fix to add some lines like create extension moddatetime; to the resulting migration file(s), the problem seems a bit more tricky with postgis and the schema it creates. I'm also encountering some errors when trying to generate types that I strongly think are related.

I've been struggling quite a bit to get the Supabase CLI & local developement workflow working. At first, I couldn't generate types from a project I've had at supabase, always facing a Error: error running container message. After some time trying different things (changing db password, updating docker, trying different connection strings) I decided to just try supabase gen types typescript --local with a quick local test. It worked all fine, so I went ahead and created a new project on remote (here I also realised the new project came with a pgsodium and a pgsodium_masks schema, both missing on my previous troublesome project). Using the CLI, type generation from this new remote project worked fine, up until I added the postgis extension (through the dashboard since I couldn't get it to work by pushing a migration). After that, any supabase gen types typescript --db-url ... attempts lead to the initially encountered error.

I also enabled the postgis extension in the local project, and now that I'm trying to re-run supabase start on the instance to see if supabase gen types typescript --local works locally, I'm getting this error:

supabase start
Error: Error starting database: ERROR:  schema "postgis" does not exist
LINE 16:     "location_geometry" postgis.geometry
                                 ^
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
...

Simply using a schemaless geometry type leads to an error about the type not being defined. The only way around I've found is to remove any postgis-related field types from the migration file, and add them through the UI or sql commands once the instance is running, which is not really ideal.

Even then, I can generate the types locally, but the resulting output's section related to postgis types is filled with hundreds of duplicate identifiers:

Duplicate identifier 'geometry'.
Duplicate identifier 'st_expand'.
...

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Start a new project locally or remotely (problem occurs on both)
2. Add the postgis extension (which also prompts to create a postgis schema)
3. Attempt to run supabase gen types typescript [--local or --db-url ...]
4. Get this error:

   
   node:internal/process/promises:279
           triggerUncaughtException(err, true /* fromPromise */);
           ^

[UnhandledPromiseRejection: This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). The promise rejected with the reason "null".] { code: 'ERR_UNHANDLED_REJECTION' } Error: error running container

or end up with a malformatted `.ts` file.

## Expected behavior

Extension or use of extension-related type in migration file should not break type generation or prevent local instance from running.

## Screenshots

If applicable, add screenshots to help explain your problem.

## System information

- OS: macOS
- Docker engine: 20.10.17
- CLI: 1.0.6

## Additional context

Add any other context about the problem here.

[pixtron]

I can reproduce the issue by following the steps @iolyd posted.

1. create new project on app.supabase.com
2. run Countries Quick Start SQL snippet
3. Run typegen -> Works
4. Add Extension "Postgis" (choose "Create new Schema postgis")
5. Run typegen -> Error

instead of using the cli i ran the typegen directly in @supabase/postgres-meta.

PG_META_DB_URL='CONNECTION_STRING' node bin/src/server/app.js gen types typescript --include-schemas public

When i log schemasError i get this error:

{ message: 'permission denied for schema postgis' }

After granting the user postgres usage on postgis, typgen works again:

GRANT USAGE ON SCHEMA postgis TO postgres;

[emmbm]

Based on the error and the workaround identified by @pixtron, this issue seems to stem from the same limitation discussed here: #61

FWIW, here's the snippet we can prepend to the migration command the CLI outputs in order to make things work as expected locally and during pushes/commits with remote:

create schema if not exists "postgis";

grant usage on schema "postgis" to "postgres";

create extension if not exists "postgis" with schema "postgis";

[sweatybridge]

I believe the typegen issue is now fixed as I cannot reproduce following @pixtron's steps. We have also added support for a few more flags and no longer require docker for generating types on hosted projects.

Examples:
  supabase gen types typescript --local
  supabase gen types typescript --linked
  supabase gen types typescript --project-id abc-def-123 --schema public --schema private
  supabase gen types typescript --db-url 'postgresql://...' --schema public --schema auth

Flags:
      --db-url string        Generate types from a database url.
      --linked               Generate types from the linked project.
      --local                Generate types from the local dev database.
      --project-id string    Generate types from a project ID.
      --schema stringArray   Schemas to generate types for.

Feel free to open this issue again if anyone is still running into problems.

[magick93]

I'm using version 1.11.3.

supabase gen types typescript --db-url "postgres://postgres:postgres@localhost:54322/postgres" --debug results in:

2022/11/01 10:42:49 Sent Header: Host [docker]
2022/11/01 10:42:49 Sent Header: User-Agent [Go-http-client/1.1]
2022/11/01 10:42:49 Send Done
2022/11/01 10:42:49 Recv First Byte
2022/11/01 10:42:49 Sent Header: Host [docker]
2022/11/01 10:42:49 Sent Header: User-Agent [Go-http-client/1.1]
2022/11/01 10:42:49 Send Done
2022/11/01 10:42:49 Recv First Byte
2022/11/01 10:42:49 Sent Header: Host [docker]
2022/11/01 10:42:49 Sent Header: User-Agent [Go-http-client/1.1]
2022/11/01 10:42:49 Sent Header: Content-Length [1761]
2022/11/01 10:42:49 Sent Header: Content-Type [application/json]
2022/11/01 10:42:49 Send Done
2022/11/01 10:42:49 Recv First Byte
2022/11/01 10:42:49 Sent Header: Host [docker]
2022/11/01 10:42:49 Sent Header: User-Agent [Go-http-client/1.1]
2022/11/01 10:42:49 Sent Header: Content-Length [0]
2022/11/01 10:42:49 Sent Header: Content-Type [text/plain]
2022/11/01 10:42:49 Send Done
2022/11/01 10:42:50 Recv First Byte
2022/11/01 10:42:50 Sent Header: Host [docker]
2022/11/01 10:42:50 Sent Header: User-Agent [Go-http-client/1.1]
2022/11/01 10:42:50 Send Done
2022/11/01 10:42:50 Recv First Byte
/usr/src/app/bin/src/server/app.js:74
            throw new Error(schemasError.message);
                  ^

Error: connect ECONNREFUSED 127.0.0.1:54322
    at /usr/src/app/bin/src/server/app.js:74:19
    at Generator.next (<anonymous>)
    at fulfilled (/usr/src/app/bin/src/server/app.js:5:58)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
2022/11/01 10:42:50 Sent Header: Host [docker]
2022/11/01 10:42:50 Sent Header: User-Agent [Go-http-client/1.1]
2022/11/01 10:42:50 Send Done
2022/11/01 10:42:51 Recv First Byte
Error: error running container

Supabase postgres is definitely running on port 54322.

[soedirgo]

Hey @magick93, the typegen is running in a container, so localhost probably won't resolve properly. For the local db specifically, you can use the --local flag instead of --db-url.