What is the best way to include dbdev and installed TLE's in migrations?

[parkernilson]

I am developing with Supabase and I am using dbdev to install https://github.com/usebasejump/supabase-test-helpers for my tests.

However, I am not sure what the best practice is for including dbdev and dependencies installed through it in my database in a persistent way.

Currently I have included the pgtle install script to install dbdev (see here) in a migration file, and in that same file I have included select dbdev.install('basejump-supabase_test_helpers'); to install the test helpers extension.

This works, but it seems redundant to have to install dbdev and test_helpers over the internet every time I want to create a shadow database (for db diff / running tests, etc.)

Is there a better way to include dbdev and installed extensions in a persistent way?

[parkernilson]

-- install_dbdev_migration_1234567890.sql

create extension if not exists http with schema extensions;
create extension if not exists pg_tle;
drop extension if exists "supabase-dbdev";
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
select
    pgtle.install_extension(
        'supabase-dbdev',
        resp.contents ->> 'version',
        'PostgreSQL package manager',
        resp.contents ->> 'sql'
    )
from http(
    (
        'GET',
        'https://api.database.dev/rest/v1/'
        || 'package_versions?select=sql,version'
        || '&package_name=eq.supabase-dbdev'
        || '&order=version.desc'
        || '&limit=1',
        array[
            ('apiKey', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyYndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzIsImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJrzM0AQKsu_5k134s')::http_header
        ],
        null,
        null
    )
) x,
lateral (
    select
        ((row_to_json(x) -> 'content') #>> '{}')::json -> 0
) resp(contents);
create extension "supabase-dbdev";
select dbdev.install('supabase-dbdev');
drop extension if exists "supabase-dbdev";
create extension "supabase-dbdev";

select dbdev.install('basejump-supabase_test_helpers');

[imor]

@parkernilson currently there's no great way of including the raw contents of a TLE in your migration files, but this exact use has has come up in internal discussions as well. I've created an issue to track this.