Metarest: create database objects from Supabase client libs

README.md

Metarest(work in progress)

The main idea is to allow users create database objects through Supabase client libraries. To get a similar feel to firestore.

This can already be done by using meta/meta_triggers + PostgREST.

Some examples with postgrest-js:

Creating a table and columns

const supabase = require('@supabase/postgrest-js')

const meta = new supabase.PostgrestClient('http://localhost:3000', {schema : 'meta'})

const pub = new supabase.PostgrestClient('http://localhost:3000', {schema : 'public'})

let { status } = await meta
  .from('table')
  .insert([{ schema_name: 'public', name : 'people' }])

let { status } = await meta
  .from('column')
  .insert([
    { schema_name: 'public', relation_name : 'people', name : 'id', type_name : 'integer' }
  , { schema_name: 'public', relation_name : 'people', name : 'age', type_name : 'integer' }
  ])

let { body, error } = await pub
  .from('people')
  .insert([ { id: 1, age: 40 } , { id: 2, age: 50 } , { id: 3, age: 60} , { id: 4, age: 13} , { id: 5, age: 19} ])

let { body, error } = await pub
  .from('people')
  .select('*')

console.log(body);
[
  { id: 1, age: 40 },
  { id: 2, age: 50 },
  { id: 3, age: 60 },
  { id: 4, age: 13 },
  { id: 5, age: 19 }
]

Creating a function

let { status } = await meta
  .from('function')
  .insert({
    schema_name: 'public', name : 'subtract',
    parameters: ['a integer', 'b integer'],
    return_type: 'integer', language: 'sql',
    definition: 'select a - b'
  })

let { body, error } = await pub
  .rpc('subtract', {a: 63, b: 13})

console.log(body);
50

Creating a view

let { status } = await meta
  .from('view')
  .insert([
    { schema_name: 'public', name : 'teens', query: 'select * from people where age between 13 and 19'}
  ])

let { body, error } = await pub
  .from('teens')
  .select('*')

console.log(body);
[ { id: 4, age: 13 }, { id: 5, age: 19 } ]

Comments

• Doing this for Supabase clients would require:

  • installing the meta extension
  • adding meta to db-schema
  • using the service_role for meta calls
  • adding an event trigger for automatic postgrest schema cache update

• Some operations could be wrapped by supabase-js to give better DX.

• The meta extension is pure SQL. So it can be installed on any provider.

TODO

• Creating a POLICY
• Creating indexes
• Creating fk constraints
• Creating table privileges
• Create a ROLE

Dockerfile

FROM postgres

RUN metaDependencies="git \
    ca-certificates \
    build-essential" \
    && apt-get update \
    && apt-get install -y --no-install-recommends ${metaDependencies} \
    && apt-get install -y build-essential \
    && apt-get install make \
    && cd /tmp \
    && git clone https://github.com/aquametalabs/meta.git \
    && cd meta \
    && make \
    && make install
    && cd /tmp \
    && git clone https://github.com/aquametalabs/meta_triggers.git \
    && cd meta_triggers \
    && make \
    && make install

#CREATE EXTENSION hstore SCHEMA public;
#CREATE EXTENSION meta;
#CREATE EXTENSION meta_triggers;