cli: function extensions.moddatetime() does not exist for Migrations

Bug report

Describe the bug

I am getting an error that function extensions.moddatetime() does not exist

To Reproduce

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

  1. Create an updated function with the following:
     create extension if not exists moddatetime schema extensions;
     create trigger handle_updated_at before update on bom
     for each row execute procedure moddatetime (updated_at);
    
  2. run: supabase db commit updated_at
  3. Run: supabase stop or remove containers or start a new local dev setup
  4. See error: Error: Error resetting database: ERROR: function extensions.moddatetime() does not exist

Expected behavior

Exepted migrations to be run successfully and updated_at migration to work

System information

  • OS: macOS 12.1 M1
  • Version of supabase-cli: 0.16.1

P.S

Should update the issue templete since the Nodejs CLI is Deprecated

- Version of Node.js: [e.g. 10.10.0]

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Reactions: 3
  • Comments: 17 (4 by maintainers)

Most upvoted comments

I think I have fixed this locally for me:

CREATE EXTENSION IF NOT EXISTS moddatetime
	SCHEMA "extensions";

I had to manually add this to the top of migration file. The migration file did not include this when auto-generated.

This bug is still happening. fwiw

I think this is similar to https://github.com/supabase/cli/issues/61.

The problem with including CREATE EXTENSION in a migration is you won’t be able to push the migration to the remote db, since the postgres role used for accessing the remote db can’t do CREATE EXTENSION.

This needs to be fixed from the Supabase side, which we’re currently working on (to quote myself here, what you can do from the dashboard you should be able to do with the postgres role).

For now though, you can write it in supabase/extensions.sql instead, which is only run on the local database. You do need to manually keep it in sync with the remote db’s extensions though.

@sweatybridge Thanks that have fixed the issue. However, I found other issues with that patch, like not having anymore autocomplete of table names in the SQL editor, but this is for another issue to open. Thanks for your help