This guide is a work in progress as I learn more about creating database.dev extensions, but should be useful as-is. If you have feedback or find any issues, beat me up on Twitter.
Supabase recently announced dbdev and database.dev, an exciting new package manager that allows you to install trusted extensions to Postgres without requiring access to the filesystem. It does this by leveraging pg_tle, an extension from AWS that provides the installation functionality for extensions written using an approved subset of typical Postgres extension functionality.
This was an awesome fit for my Supabase test helpers library, so I decided to take some time to convert the helper into a dbdev extension. This guide is a quick overview of what I've learned and how you can create your own extensions as well.
pg_tle allows external Postgres extensions to be installed without filesystem access. dbdev makes it way easier to do so.
pg_tle is the underlying technology that allows external extensions to be installed in Postgres without requiring access to the file system. If you've worked with managed Postgres instances often, you'll know this is a massive deal. As long as the database has pg_tle installed, you'll have the ability to install "most" extensions on your own. I say "most" because the format is still fairly new, and while I expect it to get popular quite fast, it will take some time for all our favorites to get ported over.
dbdev and database.dev are a layer on top of pg_tle that provide a trusted extension repository and remote installation. For an example on how this works, you can check out my supabase_test_helpers pg_tle function. You'll notice the pgtle.install_extension
call at the top containing all the extension functionality. That's awesome, but cumbersome. Once approved on database.dev, that installation would become `dbdev.install_extension('basejump-supabase_test_helpers').
At launch time, pg_tle supports SQL, PL/pgSQL, pl/v8 (javascript) and pl/perl. Support for pl/rust is coming soon.
I suspect more languages get added over time.
Extensions are effectively a collection of functions. Since the details here will vary based on what you're building, I won't go into specific functionality too much. Instead, I'll give a quick example of some functions and mention a few random things I've learned while building supabase_test_functions.
-- Create a schema for your extension
CREATE SCHEMA IF NOT EXISTS my_extension;
-- Create a generic function for my extension
CREATE OR REPLACE FUNCTION my_extension.my_function(value text)
RETURNS text
AS $$
SELECT value;
$$ LANGUAGE SQL;
CREATE OR REPLACE
so that it's easy to update them without having to drop them firstCREATE SCHEMA IF NOT EXISTS tests;
-- anon and authenticated should have access to tests schema
GRANT USAGE ON SCHEMA tests TO anon, authenticated;
-- Don't allow public to execute any functions in the tests schema
ALTER DEFAULT PRIVILEGES IN SCHEMA tests REVOKE EXECUTE ON FUNCTIONS FROM public;
-- Grant execute to anon and authenticated for testing purposes
ALTER DEFAULT PRIVILEGES IN SCHEMA tests GRANT EXECUTE ON FUNCTIONS TO anon, authenticated;
CREATE OR REPLACE FUNCTION my_function(value text default 'hello world') RETURNS text AS $$
SELECT value;
$$ LANGUAGE SQL;
SELECT my_function(); -- returns 'hello world'
SELECT my_function('hello'); -- returns 'hello'
authenticated
user or anon
for anonymous requests. You can get around that by defining your function as SECURITY DEFINER
, which gets run with elevated permissions. You almost always want to avoid SECURITY DEFINER
. If you need to use it, make sure you set a search_path
as well, typically to public
. Supabase talks about this more here.Now that your functions are created, converting them into a pg_tle extension is super straight forward. You'll just wrap your function in the pgtle.install_extension function.
SELECT pgtle.install_extension
(
'my_extension',
'0.1',
'This is a quick description of your extension',
$_pg_tle_$
-- YOUR EXTENSION FUNCTIONS GO HERE
$_pg_tle_$
);
For example:
SELECT pgtle.install_extension
(
'my_extension',
'0.1',
'This is a quick description of your extension',
$_pg_tle_$
-- Create a schema for your extension
CREATE SCHEMA IF NOT EXISTS my_extension;
-- Create a generic function for my extension
CREATE OR REPLACE FUNCTION my_extension.my_function(value text)
RETURNS text
AS $$
SELECT value;
$$ LANGUAGE SQL;
$_pg_tle_$
);
Extensions were meant to be shared. Out of the box, your users will need to enable pg_tle on their database and then run your above pgtle.install_extension
function in its entirety to leverage your code. It's a bit cumbersome, and that's where dbdev
and database.dev come in. But we'll tackle that in a bit, to start, here's how your users can install unapproved extensions.
CREATE EXTENSION IF NOT EXISTS pgtle;
SELECT pgtle.install_extension
(
'my_extension',
'0.1',
'This is a quick description of your extension',
$_pg_tle_$
-- Create a schema for your extension
CREATE SCHEMA IF NOT EXISTS my_extension;
-- Create a generic function for my extension
CREATE OR REPLACE FUNCTION my_extension.my_function(value text)
RETURNS text
AS $$
SELECT value;
$$ LANGUAGE SQL;
$_pg_tle_$
);
CREATE EXTENSION my_extension;
SELECT my_extension.my_function('hello world'); -- returns 'hello world'
If you want to get your extension listed on database.dev, you'll need to get it approved by the Supabase team.
They're working on an API for extension submission, but for now it requires opening up a PR on the database.dev repo.
First, create an account at database.dev
Extensions are maintained as a list of migration files into the database.dev database. The format is:
-- First we upsert the extension into the app.packages table
insert into app.packages(
handle,
partial_name,
control_description,
control_relocatable,
control_requires
)
values (
'your_database_dev_handle',
'your_extension_name',
'Your extension description',
false, -- set to true if your extension is relocatable
'{}' -- set to a json array of extension names that your extension requires
) on conflict (handle, partial_name) do update set
control_description = excluded.control_description,
control_relocatable = excluded.control_relocatable,
control_requires = excluded.control_requires;
insert into app.package_versions(package_id, version_struct, sql, description_md)
values (
(select id from app.packages where package_name = 'your_database_dev_handle-your_extension_name'), -- handle-partial_name
(1,0,0), -- your semver version number
$pkg$ -- start of your extension
-- YOUR EXTENSION FUNCTIONS GO HERE
$pkg$, -- end of your extension
$description$ -- start of your description
# Markdown text content goes here
$description$ -- end of your description
);
It's a good idea to test your extension using pgTAP before you publish it. I have a full guide on creating tests for Supabase here. You can also view some example tests from my supabase-test-helpers project.
Supabase comes with pgTAP already configured and ready to go, making it easy to generate your tests. I like to create a sample supabase project outside of my extension directory.
mkdir supabase-test-project && cd supabase-test-project && supabase init
supabase start
./supabase/tests/your-test.sql
PGASSWORD=postgres psql -v ON_ERROR_STOP=1 -U postgres -d postgres -h localhost -p 54322 -c "CREATE EXTENSION pg_tle;"
PGASSWORD=postgres psql -v ON_ERROR_STOP=1 -U postgres -d postgres -h localhost -p 54322 -f ./my-extension-pglet.sql
supabase test db
If you're not using Supabase, you can still use pgTAP to test your extension. You'll just need to install it yourself using the instructions here. Once installed on your local database, you can perform the same steps as above. When it comes to running your tests, just make sure you run them using pg_prov
instead of supabase test db
.
I'm working on some automation to make it easier to generate pg_tle and database.dev extensions. I'll update this section once I have something to share. Just waiting to get through the process a bit more before I make them public.
Assuming you've located your tests in a project root tests
directory, here's a quick Github Action that will run them for you using the Supabase CLI.
name: PGTap Tests
on:
pull_request:
branches: [ main ]
jobs:
build:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: supabase/setup-cli@v1
with:
version: 1.50.4
- name: Supabase Start
run: supabase init && supabase start
- name: Enable pgtle
run: psql -v ON_ERROR_STOP=1 -U postgres -d postgres -h localhost -p 54322 -c "CREATE EXTENSION pg_tle;"
env:
PGPASSWORD: postgres
- name: Install supabase_test_helpers extension
run: psql -v ON_ERROR_STOP=1 -U postgres -d postgres -h localhost -p 54322 -f ./your-pg_tle-extension.sql
env:
PGPASSWORD: postgres
- name: Move files into supabase tests directory
run: mkdir ./supabase/tests && mv ./tests/* ./supabase/tests/
- name: Run Tests
run: supabase test db