Migrating from PostgREST

PostgREST automatically exposes PostgreSQL tables and views as REST endpoints. FraiseQL requires explicit annotations — you decide what is exposed, what parameters it accepts, and what it returns.

Your existing PostgreSQL views work unchanged as sql_source values in FraiseQL. The schema authoring step is new.

What Changes

PostgREST	FraiseQL
Tables and views auto-exposed	Only annotated operations are exposed
API surface defined in the database	API surface defined in schema code
PostgreSQL RLS as auth mechanism	[security] config with JWT claim injection
REST only	REST + GraphQL + gRPC
PostgreSQL only	PostgreSQL, MySQL, SQLite, SQL Server

What You Gain

• GraphQL and gRPC alongside REST — same compiled binary, same schema
• Multi-database support — if you later need MySQL or SQL Server
• Field-level auth via Python/TypeScript/Go SDK decorators
• OpenAPI 3.0.3 auto-generated from schema at compile time
• Explicit API surface — the REST surface is version-controlled in your schema code
• JSON envelope responses — consistent data/meta/links structure with pagination
• Bracket filter operators — ?name[icontains]=Ali&age[gte]=18
• ETag / conditional requests — If-None-Match → 304 Not Modified
• Prefer header — count=exact, return=representation/minimal

Migration Steps

1. Install FraiseQL

   curl -fsSL https://fraiseql.com/install.sh | sh

   Verify:

   fraiseql --version

2. Keep your existing PostgreSQL views

   PostgREST exposes tables and views directly. FraiseQL uses views as sql_source values. Your existing views do not need to change.

   -- Existing view (unchanged from your PostgREST setup)
   CREATE OR REPLACE VIEW v_user AS
   SELECT id, email, name, created_at FROM users;

   For tables that PostgREST exposed directly (without a view), create views that select the columns you want to expose:

   -- New view wrapping a table PostgREST exposed directly
   CREATE OR REPLACE VIEW v_post AS
   SELECT id, title, body, author_id, created_at FROM posts;

3. Write SDK annotations for each endpoint

   For each PostgREST endpoint you want to keep, create a corresponding annotated query in your schema file.

   schema.py

   import fraiseql
   from uuid import UUID
   
   @fraiseql.type
   class User:
       id: UUID
       email: str
       name: str
       created_at: str
   
   @fraiseql.type
   class Post:
       id: UUID
       title: str
       body: str
       author_id: UUID
       created_at: str
   
   # PostgREST: GET /users  →  FraiseQL: GET /rest/v1/users
   @fraiseql.query(
       sql_source="v_user",
       rest_path="/users",
       rest_method="GET",
   )
   def users(limit: int = 100, offset: int = 0) -> list[User]: ...
   
   # PostgREST: GET /users?id=eq.123  →  FraiseQL: GET /rest/v1/users/{id}
   @fraiseql.query(
       sql_source="v_user",
       rest_path="/users/{id}",
       rest_method="GET",
   )
   def user(id: UUID) -> User: ...
   
   # PostgREST: POST /posts  →  FraiseQL: POST /rest/v1/posts
   @fraiseql.mutation(
       sql_source="fn_create_post",
       rest_path="/posts",
       rest_method="POST",
   )
   def create_post(title: str, body: str, author_id: UUID) -> Post: ...
   
   # PostgREST: PATCH /posts?id=eq.123  →  FraiseQL: PATCH /rest/v1/posts/{id}
   @fraiseql.mutation(
       sql_source="fn_update_post",
       rest_path="/posts/{id}",
       rest_method="PATCH",
   )
   def update_post(id: UUID, title: str | None = None, body: str | None = None) -> Post: ...
   
   # PostgREST: DELETE /posts?id=eq.123  →  FraiseQL: DELETE /rest/v1/posts/{id}
   @fraiseql.mutation(
       sql_source="fn_delete_post",
       rest_path="/posts/{id}",
       rest_method="DELETE",
   )
   def delete_post(id: UUID) -> bool: ...

4. Create fraiseql.toml

   [project]
   name = "my-api"
   
   [database]
   url = "${DATABASE_URL}"
   
   [server]
   port = 8080

   Add [rest] to your schema TOML to configure the REST transport:

   [rest]
   path = "/rest/v1"
   openapi_enabled = true
   default_page_size = 20
   max_page_size = 100

   Note

   The [rest] configuration section is available since v2.1. See TOML Configuration Reference for all fields.

5. Compile and run

   fraiseql compile
   fraiseql run --database "$DATABASE_URL"

6. Verify your endpoints

   # REST endpoint (with /rest/v1 prefix instead of PostgREST's /)
   curl http://localhost:8080/rest/v1/users
   
   # Now also available via GraphQL — zero additional work
   curl -X POST http://localhost:8080/graphql \
     -H "Content-Type: application/json" \
     -d '{"query": "{ users { id email name } }"}'
   
   # OpenAPI spec auto-generated from your schema
   curl http://localhost:8080/rest/v1/openapi.json

PostgREST to FraiseQL Endpoint Mapping

PostgREST	FraiseQL
GET /users (auto from table/view)	GET /rest/v1/users (CQRS-derived or rest_path="/users")
GET /users?id=eq.123	GET /rest/v1/users/{id} (path parameter)
POST /users	POST /rest/v1/users
PATCH /users?id=eq.123	PATCH /rest/v1/users/{id}
DELETE /users?id=eq.123	DELETE /rest/v1/users/{id}
GET /users?select=*,orders(*)	GET /rest/v1/users?select=id,email,orders(id,total) (explicit field lists required)

For the complete list of REST transport options, filters, and response formats, see the REST API Reference.

Query Parameter Mapping

PostgREST	FraiseQL	Notes
?name=eq.Alice	?name=Alice or ?name[eq]=Alice	Simple equality is the default
?age=gt.18	?age[gt]=18	Bracket operator syntax
?name=ilike.*Ali*	?name[icontains]=Ali	Case-insensitive contains
?status=in.(active,pending)	?status[in]=active,pending	Comma-separated values
?deleted_at=is.null	?deleted_at[is_null]=true	Null check
?select=id,name	?select=id,name	Identical syntax
?order=name.asc,age.desc	?sort=name,-age	Prefix with - for descending
?limit=10&offset=20	?limit=10&offset=20	Identical syntax
Prefer: count=exact	Prefer: count=exact	Identical header
Prefer: return=representation	Prefer: return=representation	Identical header
Prefer: return=minimal	Prefer: return=minimal	Identical header

Response Format Differences

PostgREST returns unwrapped JSON arrays for collections. FraiseQL wraps all responses in an envelope:

// PostgREST
[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]

// FraiseQL
{
  "data": [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}],
  "meta": {"limit": 20, "offset": 0},
  "links": {"self": "/rest/v1/users?limit=20&offset=0", "next": null, "prev": null, "first": "/rest/v1/users?limit=20&offset=0"}
}

Error responses also differ:

// PostgREST
{"message": "...", "code": "PGRST..."}

// FraiseQL
{"error": {"code": "NOT_FOUND", "message": "...", "details": {}}}

Known Differences and Gotchas

Embedding syntax is similar but requires explicit field lists. PostgREST’s ?select=*,orders(*) uses * to include all fields and embeds using foreign key detection. FraiseQL requires explicit field lists: ?select=id,email,orders(id,total). Relationships are inferred from foreign key naming conventions (fk_user → pk_user on tb_user). Rename syntax (author:fk_user(id,name)) and count syntax (orders.count) are also available. Maximum nesting depth is configurable via max_embedding_depth. For complex joins that don’t follow naming conventions, create a SQL view:

CREATE OR REPLACE VIEW v_user_with_orders AS
SELECT
    u.id,
    u.email,
    jsonb_agg(
        jsonb_build_object('id', o.id, 'total', o.total)
        ORDER BY o.created_at DESC
    ) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Query operator syntax differs. PostgREST uses dot-prefix operators (?name=eq.foo&age=gt.18). FraiseQL uses bracket notation (?name[eq]=foo&age[gt]=18) or simple equality (?name=foo). See the query parameter mapping table above for full translations.

Authentication model changes. PostgREST uses PostgreSQL RLS directly, injecting JWT claims as GUC settings that RLS policies read. FraiseQL uses the [security] configuration with JWT claim injection — claims are available in your SQL views and functions via the same GUC mechanism, but configured via fraiseql.toml rather than PostgREST config.

Tip

If you rely heavily on PostgREST’s RLS integration, review the Authentication guide before migrating. The JWT claim injection approach is similar but configured differently.

The endpoint prefix changes. PostgREST serves at / by default (or a configured prefix). FraiseQL serves REST at /rest/v1 by default. Update your clients accordingly, or configure a custom path via [rest] path in your schema TOML (available since v2.1).