supabase/postgres-meta

postgres-meta

postgres-meta is a RESTful API designed for managing PostgreSQL databases. It provides a way to fetch tables, manage roles, and execute SQL queries through HTTP.

Key Features:

• Normalization: It normalizes the Postgres system catalog into a more readable format.
• Connection Pooling: It serves as a lightweight connection pooler.
• Multi-tenancy: The server supports multiple Postgres databases from a single instance.
• Type Generation: It provides helpers to generate types for various languages (TypeScript, Go, Swift, Python).

WARNING

Security Warning: This service implements no security. It is not intended to be used as a standalone server. It should be deployed behind a proxy in a trusted environment, used locally, or used internally without external access.

To run postgres-meta, you must first configure the following environment variables to define the server binding and the target database connection details. After setting these, run any of the binaries provided in the releases.

Required Environment Variables:

• PG_META_HOST: The host the API server will bind to.
• PG_META_PORT: The port the API server will listen on.
• PG_META_DB_HOST: The hostname of the target Postgres database.
• PG_META_DB_NAME: The name of the target Postgres database.
• PG_META_DB_USER: The username for the Postgres database.
• PG_META_DB_PORT: The port of the target Postgres database.
• PG_META_DB_PASSWORD: The password for the Postgres database.

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"

You can generate type definitions for your database schema using the /generators endpoints. If you are developing locally, you can use the provided CLI commands to trigger type generation.

To use a custom database connection string instead of the default test database, set the PG_META_DB_URL environment variable.

Supported Languages:

• typescript
• go
• swift (beta)
• python (beta)

Most API responses in postgres-meta follow a standardized result pattern using the PostgresMetaResult<T> type. This ensures consistent error handling across different metadata queries.

• Success (PostgresMetaOk<T>): Contains the requested data of type T and a null error.
• Failure (PostgresMetaErr): Contains null data and an error object containing a message and an optional formattedError.

Use the Format function to beautify a SQL string using Prettier with the sql-formatter plugin. By default, it is configured for postgresql. You can provide custom FormatterOptions to override default settings.

Returns an object containing the formatted SQL data or an error.

When interacting with the SQL execution layer, you can use the following types to define query properties like pagination and filtering.

• SQLQueryProps: Basic pagination using limit and offset.
• SQLQueryPropsWithSchemaFilter: Adds a schemaFilter string to control which schema the query applies to.
• SQLQueryPropsWithIdsFilter: Adds an idsFilter string to filter by specific identifiers.
• SQLQueryPropsWithSchemaFilterAndIdsFilter: Combines schemaFilter and idsFilter with pagination.

export type SQLQueryProps = {
  limit?: number
  offset?: number
}

export type SQLQueryPropsWithSchemaFilter = SQLQueryProps & {
  schemaFilter?: string
}

export type SQLQueryPropsWithIdsFilter = SQLQueryProps & {
  idsFilter?: string
}

export type SQLQueryPropsWithSchemaFilterAndIdsFilter = SQLQueryProps & {
  schemaFilter?: string
  idsFilter?: string
}