Supabase•17h ago

storage.buckets table schema changed from v15 to v17 and now my migration will not apply

I was working on a slightly older project that was still on postgres 15 which I want to ugprade to v17. The hosted upgrade ran fine and everything seems to work as expected. When I linked the DB to my local development environment I naturally had to do a full reset since there is no in-place upgrade. This is where I ran into a problem with my migrations. Specifically when I create my buckets:

INSERT INTO storage.buckets (id, name, allowed_mime_types, public) VALUES ('photos-standard', 'vehicle-photos-standard', ARRAY['image/*'], true);

INSERT INTO storage.buckets (id, name, allowed_mime_types, public) VALUES ('photos-standard', 'vehicle-photos-standard', ARRAY['image/*'], true);

The error is that the allowed_mime_types column no longer exists. And apparently it does not in my local copy. The hosted version and local version have a discrepancy of the structure of this table. Did I miss an announcement of the table changing? The insert above was copied from the official documentation which is also now changed. There is no documentation how to add the allowed types and make a bucket public using just SQL. I also now have to go back and alter a migration file which is just incredibly bad practice. Has anyone else run into this and how did you work around it?

7 Replies

vickOP•17h ago

Differences between the environments. Both are running docker image 17.6.1.011. Hosted:

postgres=> \d storage.buckets;
                                        Table "storage.buckets"
       Column       |           Type           | Collation | Nullable |            Default
--------------------+--------------------------+-----------+----------+--------------------------------
 id                 | text                     |           | not null |
 name               | text                     |           | not null |
 owner              | uuid                     |           |          |
 created_at         | timestamp with time zone |           |          | now()
 updated_at         | timestamp with time zone |           |          | now()
 public             | boolean                  |           |          | false
 avif_autodetection | boolean                  |           |          | false
 file_size_limit    | bigint                   |           |          |
 allowed_mime_types | text[]                   |           |          |
 owner_id           | text                     |           |          |
 type               | storage.buckettype       |           | not null | 'STANDARD'::storage.buckettype

postgres=> \d storage.buckets;
                                        Table "storage.buckets"
       Column       |           Type           | Collation | Nullable |            Default
--------------------+--------------------------+-----------+----------+--------------------------------
 id                 | text                     |           | not null |
 name               | text                     |           | not null |
 owner              | uuid                     |           |          |
 created_at         | timestamp with time zone |           |          | now()
 updated_at         | timestamp with time zone |           |          | now()
 public             | boolean                  |           |          | false
 avif_autodetection | boolean                  |           |          | false
 file_size_limit    | bigint                   |           |          |
 allowed_mime_types | text[]                   |           |          |
 owner_id           | text                     |           |          |
 type               | storage.buckettype       |           | not null | 'STANDARD'::storage.buckettype

Local dev:

postgres=> \d storage.objects
                                 Table "storage.objects"
      Column      |           Type           | Collation | Nullable |      Default
------------------+--------------------------+-----------+----------+--------------------
 id               | uuid                     |           | not null | uuid_generate_v4()
 bucket_id        | text                     |           |          |
 name             | text                     |           |          |
 owner            | uuid                     |           |          |
 created_at       | timestamp with time zone |           |          | now()
 updated_at       | timestamp with time zone |           |          | now()
 last_accessed_at | timestamp with time zone |           |          | now()
 metadata         | jsonb                    |           |          |

postgres=> \d storage.objects
                                 Table "storage.objects"
      Column      |           Type           | Collation | Nullable |      Default
------------------+--------------------------+-----------+----------+--------------------
 id               | uuid                     |           | not null | uuid_generate_v4()
 bucket_id        | text                     |           |          |
 name             | text                     |           |          |
 owner            | uuid                     |           |          |
 created_at       | timestamp with time zone |           |          | now()
 updated_at       | timestamp with time zone |           |          | now()
 last_accessed_at | timestamp with time zone |           |          | now()
 metadata         | jsonb                    |           |          |

Fieryduck82579•16h ago

I am on 17.6 (local), and it is still there.

Fieryduck82579•16h ago

Also there on hosted, similarly 17.6.

vickOP•16h ago

Apparently I just needed to upgrade the CLI. Go figure. Version v2.40.7 update to v2.48.3 and then somehow it now gets those columns added, all without loading another docker image. I found a clue in another question: https://discord.com/channels/839993398554656828/1420846171483803658

Fieryduck82579•16h ago

I didn't know that about the CLI, but good to know. Thanks for sharing your solution!

vickOP•16h ago

Not sure why the internal migrations to the internal schemas were missed with the older CLI.

Fieryduck82579•16h ago

Indeed, I wonder about that too.

Gaming

Programming

storage.buckets table schema changed from v15 to v17 and now my migration will not apply

Did you find this page helpful?