supabase / postgres-meta

A RESTful API for managing your Postgres. Fetch tables, add roles, and run queries

postgres-meta

A RESTful API for managing your Postgres. Fetch tables, add roles, and run queries (and more).

Documentation

https://supabase.github.io/postgres-meta/

Progress

Schema:

• POST /query (Execute SQL query)
  • POST /format (Format SQL query)
  • POST /parse (Parse SQL query into AST)
  • POST /explain (Explain SQL query)
• /columns
  • GET (List)
  • POST (alter table add column)
  • PATCH (alter table alter/rename column)
  • DELETE (alter table drop column)
• /extensions
  • GET (List)
  • POST (create extension)
  • PATCH (alter extension)
  • DELETE (drop extension)
• /functions
  • GET (List)
  • POST (create function)
  • PATCH (alter function)
  • DELETE (drop function)
• /publications
  • GET (List)
  • POST (create publication)
  • PATCH (alter publication)
  • DELETE (drop publication)
• /roles
  • GET (List)
  • POST (create role)
  • PATCH (alter role)
  • DELETE (drop role)
• /schemas
  • GET (List)
  • POST (create schema)
  • PATCH (alter schema)
  • DELETE (drop schema)
• /tables
  • GET (List)
  • POST (create table)
  • PATCH (alter table)
  • DELETE (drop table)
• /triggers
  • GET (List)
  • POST (create trigger)
  • PATCH (alter trigger)
  • DELETE (drop trigger)
• /types
  • GET (List)
  • POST (create type)
  • PATCH (alter type)
  • DELETE (drop type)

Helpers:

• /config
  • GET /version: Postgres version
• /generators
  • GET /openapi: Generate Open API
  • GET /typescript: Generate Typescript types

Quickstart

Set the following ENV VARS:

PG_META_HOST="0.0.0.0"
PG_META_PORT=8080
PG_META_DB_HOST="postgres"
PG_META_DB_NAME="postgres"
PG_META_DB_USER="postgres"
PG_META_DB_PORT=5432
PG_META_DB_PASSWORD="postgres"

Then run any of the binaries in the releases.

FAQs

Why?

This serves as a light-weight connection pooler. It also normalises the Postgres system catalog into a more readable format. While there is a lot of re-inventing right now, this server will eventually provide helpers (such as type generators). The server is multi-tenant, so it can support multiple Postgres databases from a single server.

What security does this use?

None. Please don't use this as a standalone server. This should be used behind a proxy in a trusted environment, on your local machine, or using this internally with no access to the outside world.

Developers

To start developing, run npm run dev. It will set up the database with Docker for you. The server will restart on file change.

If you are fixing a bug, you should create a new test case. To test your changes, add the -u flag to vitest on the test:run script, run npm run test, and then review the git diff of the snapshots. Depending on your change, you may see id fields being changed - this is expected and you are free to commit it, as long as it passes the CI. Don't forget to remove the -u flag when committing.

To make changes to the type generation, run npm run gen:types:<lang> while you have npm run dev running, where <lang> is one of:

• typescript
• go

To use your own database connection string instead of the provided test database, run: PG_META_DB_URL=postgresql://postgres:postgres@localhost:5432/postgres npm run gen:types:<lang>

Licence

Apache 2.0

Sponsors

We are building the features of Firebase using enterprise-grade, open source products. We support existing communities wherever possible, and if the products don’t exist we build them and open source them ourselves.

postgres-meta's Issues

Enable/disable RLS on tables

Required for the applications of RLS policies

Ability to create a new role

A user should be able to post to /roles which should create a new role in the database

PATCH Column change type throws 500

Not sure if this is intended but trying to update field type from text -> int4 throws a 500, but text -> varchar is okay
Similarly, trying to update int4 -> float8 is fine too, but int4 -> text throws 500

Are we limiting column type updates to only types that are similar to what it was originally?

Actions: build

There is a script npm run dist which packs this completely into a binary.

Improve logging

Goals

• Our pg-api middleware handles requests for multiple users and it is hard to filter logs per project. We should be able to easily filter logs per project
• Filter based on log levels like critical, error, info, etc
• printing logs in JSON makes it easier for searching in Cloudwatch. Right now each new line is treated as a separate log message which makes it harder to group and search.
• log information about the incoming query, IP address of client, etc - useful for debugging issues and replaying the exact request which caused the error
  This is how the logs appear in Cloudwatch currently for context
  

Lets move to using structured logging with something like Winston. We can add DB_HOST as labels, add logging levels, timestamps.

Don't run name updates on `/tables` and `/columns` if the name is not changed

This line:

https://github.com/supabase/pg-api/blob/f7d66c2395e682d4ebb082fe881fe1662052b5e2/src/api/tables.ts#L87

Should only run if the name is different from what the table was previously named.

At the moment it is running even if you pass it the same name. This results in an error relation already exists.

Actions: testing

Create an action to automatically run the tests. The action needs to spin up the database (docker-compose), and then then call npm test

Publications api doesn't support table name with uppercase char

Bug report

Describe the bug

postgres-meta doesnt double quote table name on SQL query. so the query will fails when table name includes uppercase

tableSql = `ALTER PUBLICATION ${ident(old!.name)} SET TABLE ${tables.join(',')};`

It's a PostgreSQL convention. The main drawback of using camel case is that you'll need to double quote your tables when using SQL. supabase/supabase#619 (comment)

To Reproduce

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

1. Go to dashboard app
2. Create a new table with uppercase char in the name like 'Menu'
3. Go to ./project/[project-ref]/database/replication and try to enable replication for the name table.
4. The payload is sent properly: {tables: ["public.Menu"], id: 16387}
5. See error message {code: 400, message: "relation "public.menu" does not exist"}

Expected behavior

postgres-meta should double quote table name as default to support uppercase char.

Additional context

https://github.com/supabase/infrastructure/issues/963

perf: optimise /tables route

It's proving extremely slow in prod. I assume the with joins are running sequentially and will need to be converted to regular joins

Ability to create a table

A user should be able to post to /tables, which should create a new table in the database

fix: POST and PATCH inconsistency for /columns

• missing column foreign_key on POST and PATCH
• POST returns table_id as an int, PATCH returns table_id as a string (seems to be a difference for getColumnSqlize and getColumnByPosSqlize ?)

The automated release is failing 🚨

🚨 The automated release from the master branch failed. 🚨

I recommend you give this issue a high priority, so other packages depending on you could benefit from your bug fixes and new features.

You can find below the list of errors reported by semantic-release. Each one of them has to be resolved in order to automatically publish your package. I’m sure you can resolve this 💪.

Errors are usually caused by a misconfiguration or an authentication problem. With each error reported below you will find explanation and guidance to help you to resolve it.

Once all the errors are resolved, semantic-release will release your package the next time you push a commit to the master branch. You can also manually restart the failed CI job that runs semantic-release.

If you are not sure how to resolve this, here is some links that can help you:

• Usage documentation
• Frequently Asked Questions
• Support channels

If those don’t help, or if this issue is reporting something you think isn’t right, you can always ask the humans behind semantic-release.

---

No npm token specified.

An npm token must be created and set in the NPM_TOKEN environment variable on your CI environment.

Please make sure to create an npm token and to set it in the NPM_TOKEN environment variable on your CI environment. The token must allow to publish to the registry https://registry.npmjs.org/.

---

Good luck with your project ✨

Your semantic-release bot 📦🚀

feat: GET a single table

RFC: Renaming this repo

We would like to rename this repo to make it clearer on first glance exactly what this is doing. pg-api seems like it could be for interacting with data (like PostgREST), but the intent of the repo is to manage the database itself.

Our current proposal is postgres-meta.

Ability to fetch schema

A user should have the ability to GET the /schemas endpoint and receive back all the schemas in the Postgres database

POST pg/columns does not return the same structure as GET columns

Bug report

Describe the bug

Context: Creating a new column through the table editor (POST columns) returns a slightly different structure than GET columns, specifically tableId is an integer from POST but string from GET, and POST columns does not return foreignKey property. Screenshots below.

To Reproduce

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

1. Trigger a POST request for pg/columns and check the response body

2. Trigger a GET request for pg/columns and check the response body

3. Compare both response bodies (have attached screenshots as below for easier reference)

Expected behavior

POST columns response body should be the same as each column from GET request. (tableId as string, foreignKey property)

Screenshots

GET:

POST:

System information

• OS: macOS
• Browser (if applies): Chrome
• Version of supabase-js: 0.35.1
• Version of Node.js: 11.9.0

Additional context

Nil

apostrophes not being escaped?

@joshenlim mentioned that an apostrophe in sql comment caused pg-api to fail - need to verify that these are being escaped correctly else could be potential for sql injection attacks

Support for triggers

Feature request

Is your feature request related to a problem? Please describe.

We should implement trigger support. This is something we will need to build into our Dashboard

Describe the solution you'd like

A clean wrapper around select * from information_schema.triggers

Switch babel to typescript builder

Incrementally move this to typescript - the types will be important for the table/column creation

Automatic release notes

Can we somehow automate the release notes? I tried gren in the past but didn't really nail it

Ability to add/update COMMENTS on tables and columns

In Supabase, we would like to add descriptions to tables and columns. These descriptions can be stored in PG, using COMMENTS:

comment on table table_name is 'Some comment';
comment on column table_name.column_name is 'Some comment';

Update grants and add an ID field of some sort

https://github.com/supabase/pg-api/blob/c53dfaf2948d04e3baa267400f793aa3233f84ea/src/lib/interfaces/roles.ts#L18

Is there an internal PG ID that we can use for grants? We need this to update the permissions when we start doing RLS

Update IDs of each of the types

In src/lib/interfaces we have all the different types of data we are returning. All of them are using "named" IDs. For example, tables has a field like table_id == public.users. This is prone to breakages if the user updates the name of the table or schema.

We should add an id field to each of these which refers to PG's internal ID

/tables yield syntax error at or near "MATERIALIZED"

I gave a try to pg-api and requested /tables with the right headers, got a 500, looked at the log and found this:

throwing error error: syntax error at or near "MATERIALIZED"
    at Connection.parseE (/snapshot/pg-api/node_modules/pg/lib/connection.js:614:13)
    at Connection.parseMessage (/snapshot/pg-api/node_modules/pg/lib/connection.js:413:19)
    at Socket.<anonymous> (/snapshot/pg-api/node_modules/pg/lib/connection.js:129:22)
    at Socket.emit (events.js:311:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
  name: 'error',
  length: 101,
  severity: 'ERROR',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '16',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1133',
  routine: 'scanner_yyerror'
}

It seems to be related with this part
https://github.com/supabase/pg-api/blob/ed5fa837504b66e18b3278905063e0891cd29e58/src/api/tables.ts#L128-L133

Note: the database does not have any tables (both in public and another user-created schema)

Proposal/RFC: split up the API and the server

At the moment all of the interactions work through an express server. However, when we start work on the CLI, we will need to use this functionality without express. My proposal is to split the API and Server functioanality into 2 areas

src
- api      # contains all the TypeScript classes
- server   # contains the express routes, which import from `../api`

Then, we should be able to do something this:

npm install @supabasee/pg-api

API (usage with our CLI)

import { createClient } from 'pg-api'

const api: PostgresApiClient = createClient(process.env.connection_string)

api.tables.add({
  schema: 'public',
  name: 'test'
})

Server

import { createServer } from  'pg-api'

const server: PostgresApiServer = createServer(process.env.connection_string, {})

server.start() // starts express server on port 3000

Then the route POST /tables calls api.tables.add()

Allow Column CRUD to specify {ALWAYS | BY DEFAULT} for identity columns

Feature request

When creating or updating columns, we can specify a column to be an identity column by passing isIdentity: false/true to the POST payload body.

We'll need an added level of detail to isIdentity by allowing users to specify if it should be GENERATED ALWAYS or GENERATED BY DEFAULT as such, as they both have different implications on whether the user can specify a value for the column.

GENERATED ALWAYS prevents user from specifying a value for that column, but GENERATED BY DEFAULT allows so. In both cases, if no value is specified, the value will be generated automatically in a sequence.

Currently, the FE table editor does not allow user to specify a value if the column is marked as isIdentity, but this behavior will need to be changed accordingly to above.

Describe the solution you'd like

Add an additional parameter for the POST payload body to allow the specification of identity format as above.
And also return the parameter when getting columns.

Manage subscriptions

Feature request

Is your feature request related to a problem? Please describe.

The goal is to be able to manage subscriptions for

Describe the solution you'd like

• List subscriptions
• Enable replication for all tables
• Disable replication for all tables
• Enable for individual table (only if it's not "for all tables")
• Disable for individual table (only if it's not "for all tables")
• Enable replication type "FULL" for each table

Possible:

• Add replication status for each table to the /tables route

Additional context

• Related (internal task) https://github.com/supabase/infrastructure/issues/193

Support CRUD for Foreign Key Relations

Feature request

Describe the solution you'd like

The table editor on the FE will start to introduce relations between tables in V14, we'll need a set of endpoints to allow CRUD (not sure for read though) for foreign keys between tables.

No hurry on this - for V14, I'll be doing this feature manually via the POST /query endpoint

PATCH Tables to return errors as objects instead of array

Only ascertained for PATCH, not sure about POST or DELETE

Column endpoints return errors as objects

perf: inline all SQL on information_schema joins

Based on @soedirgo's research here: #41

we are JOINing information_schema views with system tables, e.g.:

-- tables.sql
...
FROM
  information_schema.tables
  JOIN pg_class c ON quote_ident(table_schema)::regnamespace = c.relnamespace
  AND c.relname = table_name
  LEFT JOIN pg_stat_user_tables ON pg_stat_user_tables.schemaname = tables.table_schema
  AND pg_stat_user_tables.relname = tables.table_name
...

information_schema views already use system tables under the hood (like below), so we could inline it, though it'd make the SQL templates pretty ugly.

-- information_schema.tables
...
FROM pg_namespace nc
  JOIN pg_class c ON nc.oid = c.relnamespace
  LEFT JOIN (pg_type t
  JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON c.reloftype = t.oid
...

The performance difference is big though (10x):

# Not inlined
❯ hyperfine 'curl -s http://localhost:1337/tables' --warmup 5
Benchmark #1: curl -s http://localhost:1337/tables
  Time (mean ± σ):      1.004 s ±  0.027 s    [User: 4.3 ms, System: 5.5 ms]
  Range (min … max):    0.983 s …  1.063 s    10 runs

# Inlined
❯ hyperfine 'curl -s http://localhost:1337/tables' --warmup 5
Benchmark #1: curl -s http://localhost:1337/tables
  Time (mean ± σ):      97.6 ms ±   5.3 ms    [User: 4.2 ms, System: 5.1 ms]
  Range (min … max):    91.9 ms … 110.0 ms    27 runs

The CI testing action is failing

https://github.com/supabase/pg-api/actions?query=workflow%3ACI

CRUD for policies

Required for Row Level Security

Return meaningful error messages from API

Context: Whenever the editor faces an error from calls to pg-api, I'll just show the error given by the API as a toast message.

At the moment, i noticed that most of the messages just throw "Request failed with status code 500", along with a 500 status code - will be better if the API returns more specific messages for greater clarity in UX and debugging

GET tables/id does not return the same structure as from GET tables

Mainly missing columns key that i'll need on the FE

Return body from tables/id:

Return body from tables:

Naming convention: snake_case vs. camelCase

This is a bit of a pedantic (breaking) issue that I found while writing up the OpenAPI spec: due to the naming convention of Postgres (snake_case), most of the fields in the API are also snake_case. It'd be more idiomatic to use camelCase.

There are also other minor issues that I found (mostly to do with types, e.g. string should be number), so we can do all that plus the OpenAPI stuff in one go. Probably not gonna eliminate all of such issues, but we can at least kaizen our way there.

Unable to set defaultValue for column text type

Bug report

Describe the bug

Attempting to set a defaultValue for a column of text type returns a 500

Doing it so for int type columns seem to be working fine

To Reproduce

Via the table editor:

1. Create a new column
2. Set column type to text and fill in any default value
3. Select save
4. A toast error will show 500 that's returning from pg-api

Expected behavior

Column should save with provided defaultValue

Screenshots

System information

• OS: macOS
• Browser (if applies): Chrome
• Version of supabase-js: 0.35.11
• Version of Node.js: 11.9.0

Generate Typescript definitions

Feature request

Is your feature request related to a problem? Please describe.

Currently we recommend creating Types from the OpenAPI spec (https://supabase.io/docs/reference/javascript/generating-types)

This will cause a few problems because the types in Open API spec are somewhat limited.

Describe the solution you'd like

It would be great to generate these definitions directly from the database. If we build it into this library then we can use it on the Dashboard and our CLI.

Additional context

• Issue: Array Columns Type Generation: supabase/supabase#1215
• We need different "types" for selects (which allow relationship) and insert/update (which don't allow relationships)
• We should configure this so that we can generate types for other languages too

Regression: enums is no longer appearing in columns

This is a breaking issue

Previously there was an enums field on every column. This must have been removed in the refactor

Run as CLI

At some stage this should be able to be run as a CLI as well.

eg:

pg-api types > types.json

Actions: continuous deployment

Is set this up on an example API using Fly.io

We should deploy each time we bump the version: https://fly.io/docs/app-guides/continuous-deployment-with-github-actions/

File was not included into executable at compilation stage

Error when upgrading to 0.15.3

Updating of rows from tables with no primary key

Currently the editor breaks if a user tries to update a row from a table with no primary key - wondering if there's an endpoint that I could use for this scenario?

Row updates on the editor is currently done via the supabase client

Add FK details to the /tables route

Add enums to columns

Selecting Publications with multiple schemas returns duplicate table names

Bug report

Describe the bug

To Reproduce

• Create a publication
• create a table "public.users"
• create a table "auth.user"
• Add both tables to the publication
• select the publication

The result will return an array of tables ["users", "users"]

Expected behavior

Some way to distinguish which schema the table belong to. Perhaps:

[
  { id: X, name: "users", schema: "public" },
  { id: Y, name: "users", schema: "auth" },
]

Feature: Add constraints

Feature request

Is your feature request related to a problem? Please describe.

It would be nice to be able to specify constraints within bot a table and a column

For example:

create table profiles (
  username text,
  
  unique(username)
)

alter:

alter table profiles  
   add constraint check_username 
   check (username ~* '^[a-zA-Z0-9_]*$' );

Describe the solution you'd like

It may need a similar treatment as the default values:

postgres-meta/test/integration/index.spec.js

Line 352 in f35c38f

it('/columns default_value with expressions', async () => {

Additional context

• supabase/supabase#770

Support primary key option for POST/PATCH columns

For context, ideally we'd want users to be able to create new tables with an opt-out option for a column with a primary key
-> i.e new tables will be created with one column by default

1. Currently pg-api supports creation of columns with name, type, and comment in the payload, but we'll need a way for users to create columns with primary key, and also select if they want it to be auto_increment (?).

2. Additionally not too sure if its pg-api related but I'll need the swagger definitions to be updated as well to reflect if a column is set to auto-increment or not. On the FE, I'll prevent users from editing fields which are auto-incremented to prevent it messing up psql's autoincrement logic from manually set values.

Outside of this issue:
I'm not exactly familiar with psql, but I'm assuming primary key columns are limited to certain data types too - could i know what types are supported?

live_count isn't always accurate

Bug report

Describe the bug

The live_count on tables is only indicative, but we are using it on the frontend to determine the number of rows. We should see if there is a figure that is accurate always

To Reproduce

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

1. insert thousands of rows into a database
2. query /tables
3. check live_count

Screenshots

Don't worry about the error he mentions, but this is the Dashboard after he has inserted 20m rows:

Fix failing fly deploy

Looks like the sql files aren't getting copied

POST pg/columns throws 500

This is the code that i'm using to poke the POST endpoint for columns

curl --location --request POST 'localhost:8000/pg/columns' \
--header 'x-connection-encrypted: U2FsdGVkX19kzsnMCPnxnFX52fPxwv6VFR3s3H4gvkDpDlJgf6wut+ylfvicB902wOcbLET39WTrvh4oUmip6xshgm3x39Rq3x1uUhGNPMLdediIPwKlScqA7aqjufNxmYttetfC6h8V93E5BpaOWtmOH06ty4JKdkeUYGpPoR0=' \
--header 'apikey: eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJzdXBhYmFzZSIsImlhdCI6MTU5NTQ3Njg3MiwiZXhwIjoyNTQyMTYxNjcyLCJhdWQiOiIiLCJzdWIiOiIiLCJyb2xlIjoicG9zdGdyZXMifQ.7wwVEyU4OFEWyoezDt6ud3cDQ-dbG3PlghGTCu8czz8' \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--data-raw '{
    "tableId": 16625,
    "name": "col_1",
    "type": "int4",
    "comment": "asd"
}'