Your First API

Build a blog API with users, posts, and comments. You’ll write SQL tables and views, define a Python schema, and serve GraphQL + REST from a single binary.

Tip

Just want to see it work first? The 5-Minute Quickstart gets you a working API with one docker compose up — no installation needed.

Prerequisites

• FraiseQL installed (Installation guide)
• PostgreSQL running (Docker is easiest):

docker run --name blog-postgres \
  -e POSTGRES_USER=user \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=db_blog \
  -p 5432:5432 \
  -d postgres:16

Note

This guide uses PostgreSQL. FraiseQL also supports MySQL 8.0+, SQLite, and SQL Server 2019+ — see the database guides for dialect-specific SQL.

Step 1: Initialize the project

mkdir blog-api && cd blog-api
fraiseql init .

Step 2: Write the tables

Create tables in db/schema/01_write/ using the Trinity Pattern: a surrogate key (pk_*), a UUID (id), and a text identifier.

db/schema/01_write/tb_user.sql

CREATE TABLE tb_user (
    pk_user    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier TEXT UNIQUE NOT NULL,

    username   VARCHAR(255) NOT NULL UNIQUE,
    email      VARCHAR(255) NOT NULL UNIQUE,
    bio        TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

db/schema/01_write/tb_post.sql

CREATE TABLE tb_post (
    pk_post    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier TEXT UNIQUE NOT NULL,
    fk_user    BIGINT NOT NULL REFERENCES tb_user(pk_user),

    title      TEXT NOT NULL,
    slug       TEXT UNIQUE NOT NULL,
    content    TEXT NOT NULL,
    is_published BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX ix_tb_post_fk_user ON tb_post(fk_user);

db/schema/01_write/tb_comment.sql

CREATE TABLE tb_comment (
    pk_comment BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier TEXT UNIQUE NOT NULL,
    fk_user    BIGINT NOT NULL REFERENCES tb_user(pk_user),
    fk_post    BIGINT NOT NULL REFERENCES tb_post(pk_post),

    content    TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX ix_tb_comment_fk_user ON tb_comment(fk_user);
CREATE INDEX ix_tb_comment_fk_post ON tb_comment(fk_post);

Step 3: Write the views

Views go in db/schema/02_read/ and return (id UUID, data JSONB) — the standard v_* pattern. Views compose other views: v_post embeds v_user.data for the author.

db/schema/02_read/v_user.sql

CREATE VIEW v_user AS
SELECT
    u.id,
    jsonb_build_object(
        'id', u.id::text,
        'username', u.username,
        'email', u.email,
        'bio', u.bio,
        'created_at', u.created_at
    ) AS data
FROM tb_user u;

db/schema/02_read/v_comment.sql

CREATE VIEW v_comment AS
SELECT
    c.id,
    c.fk_post,
    jsonb_build_object(
        'id', c.id::text,
        'content', c.content,
        'created_at', c.created_at,
        'author', vu.data
    ) AS data
FROM tb_comment c
JOIN tb_user u ON u.pk_user = c.fk_user
JOIN v_user vu ON vu.id = u.id;

db/schema/02_read/v_post.sql

CREATE VIEW v_post AS
SELECT
    p.id,
    jsonb_build_object(
        'id', p.id::text,
        'title', p.title,
        'slug', p.slug,
        'content', p.content,
        'is_published', p.is_published,
        'author', vu.data,
        'comments', COALESCE(
            jsonb_agg(vc.data) FILTER (WHERE vc.id IS NOT NULL),
            '[]'::jsonb
        )
    ) AS data
FROM tb_post p
JOIN tb_user u ON u.pk_user = p.fk_user
JOIN v_user vu ON vu.id = u.id
LEFT JOIN v_comment vc ON vc.fk_post = p.pk_post
GROUP BY p.pk_post, vu.data;

Add a field to v_user once, and it appears everywhere v_user.data is embedded — no duplication.

Step 4: Define the schema

schema.py

import fraiseql
from fraiseql.scalars import ID, Email, Slug, DateTime

@fraiseql.type
class User:
    """A blog user."""
    id: ID
    username: str
    email: Email
    bio: str | None
    created_at: DateTime

@fraiseql.type
class Post:
    """A blog post."""
    id: ID
    title: str
    slug: Slug
    content: str
    published: bool
    created_at: DateTime
    updated_at: DateTime
    author: User
    comments: list['Comment']

@fraiseql.type
class Comment:
    """A comment on a post."""
    id: ID
    content: str
    created_at: DateTime
    author: User

@fraiseql.query
def posts(limit: int = 10) -> list[Post]:
    return fraiseql.config(sql_source="v_post")

@fraiseql.query
def post(id: ID) -> Post | None:
    return fraiseql.config(sql_source="v_post")

@fraiseql.query
def users(limit: int = 10) -> list[User]:
    return fraiseql.config(sql_source="v_user")

@fraiseql.query
def user(id: ID) -> User | None:
    return fraiseql.config(sql_source="v_user")

Note

See the TypeScript, Go, and other SDK pages for the equivalent schema in your language.

Step 5: Configure

fraiseql.toml

[project]
name = "blog-api"

[database]
url = "postgresql://user:password@localhost:5432/db_blog"

[server]
port = 8080
host = "127.0.0.1"

[server.cors]
origins = ["http://localhost:3000"]

Step 6: Build, compile, serve

# Build the database from SQL files
confiture build --env local

# Compile the GraphQL mapping
fraiseql compile

# Start the server
fraiseql run

Your API is running at http://localhost:8080/graphql.

Step 7: Test it

Query posts with nested authors and comments:

query {
  posts {
    id
    title
    author {
      username
    }
    comments {
      content
      author {
        username
      }
    }
  }
}

The nested response comes from JSON composition in your views — v_post embeds v_user.data and aggregates v_comment.data. One SQL query per GraphQL request, no N+1.

Adding REST

Transport annotations extend a query to REST without changing GraphQL behavior:

@fraiseql.query(rest_path="/posts", rest_method="GET")
def posts(limit: int = 10) -> list[Post]:
    return fraiseql.config(sql_source="v_post")

curl http://localhost:8080/rest/v1/posts?limit=5

The GraphQL endpoint continues to work unchanged. See REST Transport for the full reference.

Adding custom views

v_user exposes scalar fields only. To return a user with all their posts (a reverse relationship), create a dedicated view:

db/schema/02_read/v_author_profile.sql

CREATE VIEW v_author_profile AS
SELECT
    u.id,
    jsonb_build_object(
        'id', u.id::text,
        'username', u.username,
        'email', u.email,
        'bio', u.bio,
        'created_at', u.created_at,
        'posts', COALESCE(
            jsonb_agg(vp.data) FILTER (WHERE vp.id IS NOT NULL),
            '[]'::jsonb
        )
    ) AS data
FROM tb_user u
LEFT JOIN tb_post tp ON tp.fk_user = u.pk_user
LEFT JOIN v_post vp ON vp.id = tp.id
GROUP BY u.pk_user;

@fraiseql.type
class AuthorProfile:
    id: ID
    username: str
    email: Email
    bio: str | None
    created_at: DateTime
    posts: list[Post]

@fraiseql.query
def author_profile(id: ID) -> AuthorProfile | None:
    return fraiseql.config(sql_source="v_author_profile")

Shape data for the UI at the SQL level. v_author_profile composes v_post the same way v_post composes v_user — no resolvers, no N+1.

Next steps

• Adding Mutations — Add create, update, and delete operations
• Developer-Owned SQL — Why SQL ownership is a strength
• Confiture — Database lifecycle management
• Schema Definition — Advanced type patterns
• Deployment — Production checklist