Postgres extension
This extension allows you to integrate Postgres databases into your federated graph declaratively with GraphQL directives.
Use the grafbase-postgres
introspection tool to generate a subgraph schema from your Postgres database. You can then compose this subgraph into your federated schema. Commit the generated schema to your repository and re-introspect the database periodically to keep the schema up-to-date.
The introspection process generates a complete set of types representing the data in your Postgres database, including entities, relationships, and scalar fields. Use these types to query and mutate data in your Postgres database via the Grafbase Gateway.
Generally, every operation executed through the extension generates exactly one SQL statement. This includes relationships, which the extension represents as lateral joins. The database handles JSON rendering; the extension manages query generation and data retrieval.
The extension provides a command-line tool for introspecting your Postgres database and generating a subgraph schema. Find the installation and usage instructions from the repository.
The introspection tool is highly recommended for automating schema generation. It simplifies the process of creating a subgraph schema from your Postgres database.
Configure your Grafbase Gateway to use the extension in your grafbase.toml
configuration file.
Using a Published Version:
Specify the desired version of the Postgres extension:
# grafbase.toml
[extensions.postgres]
version = "0.1"
Using a Local Build:
- Build the extension:
grafbase extension build
- This command creates a
build
directory containing the Wasm module and its manifest:build/ ├── extension.wasm └── manifest.json
Point the gateway to the build directory:
# grafbase.toml
[extensions.postgres]
path = "/path/to/your/build" # Update this path
To run the tests:
- Start the test database using Docker Compose:
docker compose up -d
- Execute the test suite using Cargo:
cargo test
To speed up a full test run, you can pre-compile the extension:
grafbase extension build
Then, run the tests with the PREBUILT_EXTENSION
environment variable set:
PREBUILT_EXTENSION=1 cargo nextest run
Configure the Postgres extension within your grafbase.toml
under the [extensions.postgres.config]
section. See the Grafbase Gateway configuration documentation for general extension configuration details.
# Example configuration within grafbase.toml
[extensions.postgres]
# version = "0.1" # Or path = "..."
[extensions.postgres.config]
[[extensions.postgres.config.databases]]
# Optional: Specify a name if connecting multiple databases.
# This name links the database connection to a specific subgraph.
name = "default"
# Use environment variables for sensitive parts like passwords
url = "postgres://user:password@host:port/database"
[extensions.postgres.config.databases.pool]
# Maximum number of connections (default: 10)
max_connections = 10
# Minimum number of idle connections maintained (default: 0)
min_connections = 0
# Maximum idle time before closing a connection (ms, default: 600000 / 10 min)
idle_timeout_ms = 600000
# Maximum time to wait for a connection from the pool (ms, default: 30000 / 30 sec)
acquire_timeout_ms = 30000
# Maximum lifetime of a connection (ms, default: 1800000 / 30 min)
max_lifetime_ms = 1800000
Provide all required TLS parameters directly in the connection string. For TLS connections, add parameters like:
sslmode=verify-full
sslrootcert=/path/to/ca.crt
sslcert=/path/to/client.crt
sslkey=/path/to/client.key
Find example certificate files in our test setup repository.
📚 Find complete documentation in the grafbase-postgres
repository.
The introspection process reads tables, views (TODO: Add view support), and enums from the specified database schema to generate a GraphQL SDL.
Consider this example PostgreSQL schema:
CREATE TABLE "users" (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
metadata JSONB DEFAULT '{}'
);
CREATE TABLE profiles (
id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
user_id BIGINT NOT NULL REFERENCES "users"(id) ON DELETE CASCADE,
first_name VARCHAR(100),
last_name VARCHAR(100)
);
The introspection tool generates the following GraphQL types based on the tables above:
type Profile
@pgTable(name: "profiles")
@pgKey(fields: ["id"], type: PRIMARY)
{
id: BigInt! @pgColumn(name: "id", type: BIGINT)
userId: BigInt! @pgColumn(name: "user_id", type: BIGINT)
firstName: String @pgColumn(name: "first_name", type: VARCHAR)
lastName: String @pgColumn(name: "last_name", type: VARCHAR)
user: User! @pgRelation(name: "profiles_user_id_fkey", fields: ["userId"], references: ["id"])
}
type User
@pgTable(name: "users")
@pgKey(fields: ["id"], type: PRIMARY)
{
id: BigInt! @pgColumn(name: "id", type: BIGINT)
username: String! @pgColumn(name: "username", type: VARCHAR)
email: String! @pgColumn(name: "email", type: VARCHAR)
metadata: JSON @pgColumn(name: "metadata", type: JSONB)
profiles(
filter: ProfileFilterInput,
first: Int,
last: Int,
before: String,
after: String,
orderBy: [ProfileOrderByInput!],
): ProfileConnection! @pgRelation(name: "profiles_user_id_fkey")
}
Key Generation Principles:
- Naming: Field names default to camelCase, and type names default to PascalCase. The original database names are preserved in the
@pgTable
and@pgColumn
directives. - Schemas: If your database uses multiple PostgreSQL schemas, the directives (e.g.,
@pgTable(name: "users", schema: "public")
) will include the schema name. - Relationships: The tool generates fields for foreign key relationships using the
@pgRelation
directive. The side defining the foreign key constraint includesfields
andreferences
arguments; the other side represents the inverse relationship. - JSON Types: Columns with
JSON
orJSONB
types map to theJSON
scalar type in the SDL.- If your JSON data has a consistent structure, you can replace the
JSON
scalar with a custom GraphQL object type after introspection. Note: Queries will fail if the database returns JSON that doesn't match your custom type definition.
- If your JSON data has a consistent structure, you can replace the
- Customization: You can rename generated types and fields after introspection. However, you must keep the original database object names within the
@pgTable
,@pgColumn
, and@pgRelation
directives. Ensure you also update any corresponding input types if you rename elements. - Pruning: You can safely remove unused queries, mutations, and their associated input/output types from the generated schema if they are not needed in your API.
The introspection generates queries for fetching single records and collections.
# Example generated queries
type Query {
# Fetch a single user by primary/unique key
user(
lookup: UserLookupInput!,
): User @pgSelectOne
# Fetch a collection of users with filtering, ordering, and pagination
users(
filter: UserFilterInput,
first: Int,
last: Int,
before: String,
after: String,
orderBy: [UserOrderByInput!],
): UserConnection! @pgSelectMany
}
- Single Record (
@pgSelectOne
): Fetches a unique row (e.g.,user
). Itslookup
argument accepts fields corresponding to the table's primary key or unique constraints. For composite keys, the tool generates specific input types. - Collections (
@pgSelectMany
): Fetches multiple rows (e.g.,users
). It supports filtering (filter
), ordering (orderBy
), and cursor-based pagination (first
,last
,before
,after
). - Performance: When you query fields representing relationships, the extension generates efficient SQL joins (specifically lateral joins). The extension guarantees execution of exactly one SQL query per incoming GraphQL request, preventing the N+1 query problem.
- Pagination: Queries returning multiple items (including nested one-to-many relations) expose standard GraphQL connection types with pagination arguments (
first
,last
,before
,after
) andpageInfo
. (Note: Cursors andpageInfo
currently return dummy values, see Missing Features).
The introspection also generates standard CRUD mutations.
# Example generated mutations
type Mutation {
# Create a single user
userCreate(
input: UserCreateInput!,
): UserCreatePayload! @pgInsertOne
# Create multiple users
userCreateMany(
input: [UserCreateInput!]!,
): UserCreateManyPayload! @pgInsertMany
# Update a single user (identified by lookup)
userUpdate(
lookup: UserLookupInput!,
input: UserUpdateInput!,
): UserUpdatePayload! @pgUpdateOne
# Update multiple users (identified by filter)
userUpdateMany(
filter: UserFilterInput,
input: UserUpdateInput!,
): UserUpdateManyPayload! @pgUpdateMany
# Delete a single user (identified by lookup)
userDelete(
lookup: UserLookupInput!,
): UserDeletePayload! @pgDeleteOne
# Delete multiple users (identified by filter)
userDeleteMany(
filter: UserFilterInput,
): UserDeleteManyPayload! @pgDeleteMany
}
- Operations: The tool generates mutations for single-row (
@pgInsertOne
,@pgUpdateOne
,@pgDeleteOne
) and multi-row (@pgInsertMany
,@pgUpdateMany
,@pgDeleteMany
) operations. - Returning Data: All mutations support a
returning
selection set, allowing you to fetch data about the affected rows within the same database transaction. - Performance: Each mutation executes as a single SQL statement.
The extension logs parameterized queries at the debug level without revealing any user data.
Enable query logging by setting the environment variable:
GRAFBASE_LOG=info,postgres=debug
We primarily test against the latest stable Postgres version. The extension relies on SQL features, particularly JSON/JSONB functions, available in Postgres. Therefore, the minimum supported version is Postgres 9.4.