Install packages from the database.dev registry


# dbdev

dbdev is the SQL client for database.new and is the primary way end users interact with the package registry.

dbdev can be used to load packages from the registry. For example:

```sql
-- Load the package from the package index
select dbdev.install('olirice@index_advisor');
```
Where `olirice` is the handle of the author and `index_advisor` is the name of the package.

Once installed, packages are visible in PostgreSQL as extensions. At that point they can be enabled with standard Postgres commands i.e. the `create extension`

To improve reproducibility, we recommend __always__ specifying the package version in your `create extension` statements.

For example:
```sql
-- Enable the extension
create extension "olirice@index_advisor"
    schema 'public'
    version '0.1.0';
```

Which creates all tables/indexes/functions/etc specified by the extension.

## How to Install

The in-database SQL client for the package registry is named `dbdev`. You can bootstrap the client with:

```sql
/*---------------------
---- install dbdev ----
----------------------
Requires:
  - pg_tle: https://github.com/aws/pg_tle
  - pgsql-http: https://github.com/pramsey/pgsql-http
*/
create extension if not exists http with schema extensions;
create extension if not exists pg_tle;
-- drop dbdev with older naming scheme if present
drop extension if exists "supabase-dbdev";
select pgtle.uninstall_extension_if_exists('supabase-dbdev');
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_alias=eq.supabase@dbdev'
        || '&order=version.desc'
        || '&limit=1',
        array[
            (
                'apiKey',
                'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJp'
                || 'c3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyY'
                || 'ndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzI'
                || 'sImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJ'
                || 'rzM0AQKsu_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";
```

With the client ready, search for packages on [database.dev](database.dev) and install them with

```sql
select dbdev.install('handle@package_name');
create extension "handle@package_name"
    schema 'public'
    version '1.2.3';
```


supabase

Packages