Building a REST API with PostgreSQL and PostgREST (No ORM Needed)

May 20, 2025

—

In the world of API development, many approaches require extensive coding in languages like JavaScript, Python, or Java, usually coupled with ORMs (Object-Relational Mappers) to interact with databases. However, there’s a remarkably efficient alternative that leverages PostgreSQL’s powerful features: PostgREST.

PostgREST automatically creates a RESTful API directly from your PostgreSQL database schema, eliminating the need for an ORM or boilerplate API code. This approach is particularly powerful for data-centric applications where you want to focus on database design rather than API coding.

In this guide, we’ll walk through building a complete REST API using PostgreSQL and PostgREST, showing how you can create sophisticated APIs with minimal coding effort.

Why PostgreSQL + PostgREST?

Before diving into implementation, let’s consider why this approach is worth considering:

Advantages:

Zero API Boilerplate: PostgREST creates endpoints automatically
Declarative Security: Security rules are defined in the database
Performance: Direct SQL execution without ORM overhead
PostgreSQL Features: Leverage powerful database features like JSON functions, row-level security, and stored procedures
Automatic Documentation: OpenAPI (Swagger) documentation generated automatically
Horizontal Scaling: Stateless architecture makes scaling easy

Potential Challenges:

Different paradigm from traditional API development
Requires solid understanding of PostgreSQL
Complex business logic might be less intuitive to implement

Prerequisites

To follow along, you’ll need:

PostgreSQL 10 or later installed
PostgREST installed (download from GitHub)
Basic knowledge of SQL and REST concepts

Project Overview

We’ll build an API for a simple task management system with:

User authentication
Projects containing tasks
Comments on tasks
Role-based access control

Step 1: Setting Up the Database Schema

Let’s create our database schema with proper relationships and constraints:

-- Create a new database
CREATE DATABASE task_manager;

-- Connect to the database
\c task_manager

-- Create schema for our API
CREATE SCHEMA api;

-- Create schema for authentication
CREATE SCHEMA auth;

-- Install required extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create users table
CREATE TABLE api.users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL CHECK (email ~* '^.+@.+\..+$'),
  password TEXT NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create projects table
CREATE TABLE api.projects (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  owner_id INTEGER NOT NULL REFERENCES api.users(id) ON DELETE CASCADE
);

-- Create project members junction table
CREATE TABLE api.project_members (
  project_id INTEGER REFERENCES api.projects(id) ON DELETE CASCADE,
  user_id INTEGER REFERENCES api.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('viewer', 'contributor', 'admin')),
  PRIMARY KEY (project_id, user_id)
);

-- Create tasks table
CREATE TABLE api.tasks (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'pending' 
    CHECK (status IN ('pending', 'in_progress', 'completed', 'cancelled')),
  due_date TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  project_id INTEGER NOT NULL REFERENCES api.projects(id) ON DELETE CASCADE,
  assigned_to INTEGER REFERENCES api.users(id) ON DELETE SET NULL,
  created_by INTEGER NOT NULL REFERENCES api.users(id)
);

-- Create comments table
CREATE TABLE api.comments (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  task_id INTEGER NOT NULL REFERENCES api.tasks(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES api.users(id) ON DELETE CASCADE
);

-- Add update triggers for updated_at fields
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_tasks_updated_at
  BEFORE UPDATE ON api.tasks
  FOR EACH ROW EXECUTE PROCEDURE update_modified_column();

CREATE TRIGGER update_comments_updated_at
  BEFORE UPDATE ON api.comments
  FOR EACH ROW EXECUTE PROCEDURE update_modified_column();

Step 2: Setting Up Authentication

PostgREST uses JWT (JSON Web Token) for authentication. Let’s set up the necessary functions and roles:

-- Create JWT secret
CREATE TABLE auth.secrets (
  secret TEXT NOT NULL
);

-- Insert a strong secret (replace with your own secure secret in production!)
INSERT INTO auth.secrets (secret) VALUES ('your-super-secret-jwt-token-with-at-least-32-characters');

-- Create authentication function
CREATE TYPE auth.jwt_token AS (
  token TEXT
);

CREATE FUNCTION auth.login(email TEXT, password TEXT) RETURNS auth.jwt_token AS $$
DECLARE
  account api.users;
  result auth.jwt_token;
  secret TEXT;
BEGIN
  -- Get user with matching email
  SELECT * INTO account
  FROM api.users
  WHERE users.email = login.email;

  -- Check if user exists and password matches
  IF account.password = crypt(login.password, account.password) THEN
    SELECT auth.secrets.secret INTO secret FROM auth.secrets LIMIT 1;
    
    -- Generate JWT token
    SELECT sign(
      json_build_object(
        'role', 'api_user',
        'user_id', account.id,
        'email', account.email,
        'exp', extract(epoch from now())::integer + 60*60*24  -- Token expires in 24 hours
      ),
      secret
    ) AS token INTO result;
    
    RETURN result;
  ELSE
    RAISE EXCEPTION 'Invalid email or password';
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create function to register new users
CREATE FUNCTION api.register(email TEXT, password TEXT, name TEXT) RETURNS auth.jwt_token AS $$
DECLARE
  result auth.jwt_token;
  user_id INTEGER;
  secret TEXT;
BEGIN
  -- Insert new user and get ID
  INSERT INTO api.users (email, password, name)
  VALUES (
    email,
    crypt(password, gen_salt('bf')), -- Hash password with bcrypt
    name
  )
  RETURNING id INTO user_id;
  
  -- Generate JWT token
  SELECT auth.secrets.secret INTO secret FROM auth.secrets LIMIT 1;
  
  SELECT sign(
    json_build_object(
      'role', 'api_user',
      'user_id', user_id,
      'email', email,
      'exp', extract(epoch from now())::integer + 60*60*24  -- Token expires in 24 hours
    ),
    secret
  ) AS token INTO result;
  
  RETURN result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Step 3: Setting Up Row-Level Security

One of PostgreSQL’s powerful features is Row-Level Security (RLS), which we’ll use to control access to our API:

-- Enable row-level security on all tables
ALTER TABLE api.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE api.projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE api.project_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE api.tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE api.comments ENABLE ROW LEVEL SECURITY;

-- Create a special authentication role
CREATE ROLE api_anonymous;
CREATE ROLE api_user;

-- Grant usage on our schemas
GRANT USAGE ON SCHEMA api TO api_anonymous, api_user;
GRANT USAGE ON SCHEMA auth TO api_anonymous, api_user;

-- Grant ability to authenticate
GRANT EXECUTE ON FUNCTION auth.login(text, text) TO api_anonymous, api_user;
GRANT EXECUTE ON FUNCTION api.register(text, text, text) TO api_anonymous;

-- Grant access to sequences
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA api TO api_user;

-- Grant table permissions
GRANT SELECT ON api.users TO api_user;
GRANT INSERT, SELECT, UPDATE, DELETE ON api.projects TO api_user;
GRANT INSERT, SELECT, UPDATE, DELETE ON api.project_members TO api_user;
GRANT INSERT, SELECT, UPDATE, DELETE ON api.tasks TO api_user;
GRANT INSERT, SELECT, UPDATE, DELETE ON api.comments TO api_user;

-- Define row security policies

-- Users can only see themselves and members of their projects
CREATE POLICY users_policy ON api.users
  USING (
    id = current_setting('request.jwt.claim.user_id', true)::integer
    OR id IN (
      SELECT user_id FROM api.project_members pm
      JOIN api.project_members my_pm ON my_pm.project_id = pm.project_id
      WHERE my_pm.user_id = current_setting('request.jwt.claim.user_id', true)::integer
    )
  );

-- Projects policy - users can see projects they own or are members of
CREATE POLICY projects_policy ON api.projects
  USING (
    owner_id = current_setting('request.jwt.claim.user_id', true)::integer
    OR id IN (
      SELECT project_id FROM api.project_members
      WHERE user_id = current_setting('request.jwt.claim.user_id', true)::integer
    )
  );

-- Project members policy
CREATE POLICY project_members_policy ON api.project_members
  USING (
    user_id = current_setting('request.jwt.claim.user_id', true)::integer
    OR project_id IN (
      SELECT project_id FROM api.project_members
      WHERE user_id = current_setting('request.jwt.claim.user_id', true)::integer
      AND role = 'admin'
    )
  );

-- Tasks policy - users can see tasks from their projects
CREATE POLICY tasks_policy ON api.tasks
  USING (
    project_id IN (
      SELECT project_id FROM api.project_members
      WHERE user_id = current_setting('request.jwt.claim.user_id', true)::integer
    )
  );

-- Comments policy - users can see comments on tasks they can see
CREATE POLICY comments_policy ON api.comments
  USING (
    task_id IN (
      SELECT id FROM api.tasks
      WHERE project_id IN (
        SELECT project_id FROM api.project_members
        WHERE user_id = current_setting('request.jwt.claim.user_id', true)::integer
      )
    )
  );

Step 4: Creating Views for API Endpoints

Views allow us to customize how our data is presented through the API:

-- Create users view (with password excluded)
CREATE VIEW api.user_profiles AS
  SELECT id, email, name, created_at
  FROM api.users;

-- Create projects view with additional information
CREATE VIEW api.project_details AS
  SELECT 
    p.id,
    p.name,
    p.description,
    p.created_at,
    p.owner_id,
    u.name AS owner_name,
    (SELECT COUNT(*) FROM api.tasks t WHERE t.project_id = p.id) AS task_count,
    (SELECT COUNT(*) FROM api.tasks t WHERE t.project_id = p.id AND t.status = 'completed') AS completed_task_count
  FROM api.projects p
  JOIN api.users u ON p.owner_id = u.id;

-- Create tasks view with additional information
CREATE VIEW api.task_details AS
  SELECT 
    t.*,
    p.name AS project_name,
    creator.name AS creator_name,
    assignee.name AS assignee_name,
    (SELECT COUNT(*) FROM api.comments c WHERE c.task_id = t.id) AS comment_count
  FROM api.tasks t
  JOIN api.projects p ON t.project_id = p.id
  JOIN api.users creator ON t.created_by = creator.id
  LEFT JOIN api.users assignee ON t.assigned_to = assignee.id;

Step 5: Creating Stored Procedures for Business Logic

For complex operations, we can create stored procedures:

-- Create a function to assign a task to a user
CREATE FUNCTION api.assign_task(task_id INTEGER, assignee_id INTEGER) RETURNS api.tasks AS $$
DECLARE
  task api.tasks;
BEGIN
  -- Check if the current user has permission
  IF NOT EXISTS (
    SELECT 1 FROM api.tasks t
    JOIN api.project_members pm ON t.project_id = pm.project_id
    WHERE t.id = assign_task.task_id
    AND pm.user_id = current_setting('request.jwt.claim.user_id', true)::integer
    AND pm.role IN ('admin', 'contributor')
  ) THEN
    RAISE EXCEPTION 'Permission denied';
  END IF;
  
  -- Check if the assignee is a member of the project
  IF NOT EXISTS (
    SELECT 1 FROM api.tasks t
    JOIN api.project_members pm ON t.project_id = pm.project_id
    WHERE t.id = assign_task.task_id
    AND pm.user_id = assign_task.assignee_id
  ) THEN
    RAISE EXCEPTION 'Assignee must be a project member';
  END IF;
  
  -- Update the task
  UPDATE api.tasks
  SET 
    assigned_to = assign_task.assignee_id,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = assign_task.task_id
  RETURNING * INTO task;
  
  RETURN task;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to change task status
CREATE FUNCTION api.change_task_status(task_id INTEGER, new_status TEXT) RETURNS api.tasks AS $$
DECLARE
  task api.tasks;
BEGIN
  -- Check if the current user has permission
  IF NOT EXISTS (
    SELECT 1 FROM api.tasks t
    JOIN api.project_members pm ON t.project_id = pm.project_id
    WHERE t.id = change_task_status.task_id
    AND pm.user_id = current_setting('request.jwt.claim.user_id', true)::integer
    AND (pm.role IN ('admin', 'contributor') OR t.assigned_to = pm.user_id)
  ) THEN
    RAISE EXCEPTION 'Permission denied';
  END IF;
  
  -- Update the task
  UPDATE api.tasks
  SET 
    status = change_task_status.new_status,
    updated_at = CURRENT_TIMESTAMP
  WHERE id = change_task_status.task_id
  RETURNING * INTO task;
  
  RETURN task;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute permissions
GRANT EXECUTE ON FUNCTION api.assign_task(INTEGER, INTEGER) TO api_user;
GRANT EXECUTE ON FUNCTION api.change_task_status(INTEGER, TEXT) TO api_user;

Step 6: Configuring PostgREST

Create a configuration file named postgrest.conf:

# postgrest.conf

# The database connection string
db-uri = "postgres://authenticator:yourpassword@localhost:5432/task_manager"

# The database schema to expose
db-schema = "api"

# The database anonymous role
db-anon-role = "api_anonymous"

# JWT secret used to decode JWT tokens
jwt-secret = "your-super-secret-jwt-token-with-at-least-32-characters"

# Port on which the server listens
server-port = 3000

# Use a prepared statement cache to increase throughput
prepared-statements = false

Make sure to create the authenticator role in PostgreSQL:

CREATE ROLE authenticator NOINHERIT LOGIN PASSWORD 'yourpassword';
GRANT api_anonymous TO authenticator;
GRANT api_user TO authenticator;

Step 7: Start PostgREST

Run PostgREST using your configuration file:

postgrest postgrest.conf

Your API is now running at http://localhost:3000!

Step 8: Testing the API

Let’s test our API using curl commands:

1. Register a new user

curl -X POST http://localhost:3000/rpc/register \
  -H "Content-Type: application/json" \
  -d '{"email": "user@example.com", "password": "securepassword", "name": "Test User"}'

This will return a JWT token that we’ll use for authentication.

2. Create a project

curl -X POST http://localhost:3000/projects \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name": "My First Project", "description": "A test project", "owner_id": 1}'

3. Create a task

curl -X POST http://localhost:3000/tasks \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"title": "Important Task", "description": "This needs to be done", "project_id": 1, "created_by": 1}'

4. Add a comment

curl -X POST http://localhost:3000/comments \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"content": "This is a comment", "task_id": 1, "user_id": 1}'

5. Get task details with filtering

curl http://localhost:3000/task_details?project_id=eq.1&status=eq.pending \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

6. Update task status using our function

curl -X POST http://localhost:3000/rpc/change_task_status \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"task_id": 1, "new_status": "in_progress"}'

Advanced Features

1. Full-Text Search

PostgreSQL has excellent full-text search capabilities:

-- Add a full-text search column to tasks
ALTER TABLE api.tasks ADD COLUMN search_vector tsvector 
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || coalesce(description, ''))) STORED;

-- Create a GIN index for fast text search
CREATE INDEX tasks_search_idx ON api.tasks USING GIN(search_vector);

-- Create a search function
CREATE FUNCTION api.search_tasks(search_query TEXT) RETURNS SETOF api.task_details AS $$
  SELECT * FROM api.task_details
  WHERE 
    project_id IN (
      SELECT project_id FROM api.project_members
      WHERE user_id = current_setting('request.jwt.claim.user_id', true)::integer
    )
    AND search_vector @@ plainto_tsquery('english', search_query)
  ORDER BY ts_rank(search_vector, plainto_tsquery('english', search_query)) DESC;
$$ LANGUAGE sql SECURITY DEFINER;

-- Grant execution permissions
GRANT EXECUTE ON FUNCTION api.search_tasks(TEXT) TO api_user;

2. Real-time Notifications

PostgreSQL’s LISTEN/NOTIFY can be used with PostgREST for real-time updates:

-- Create notification functions
CREATE FUNCTION notify_task_change() RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify(
    'task_changes',
    json_build_object(
      'operation', TG_OP,
      'record', row_to_json(NEW)
    )::text
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create triggers for notifications
CREATE TRIGGER tasks_notify_trigger
  AFTER INSERT OR UPDATE OR DELETE ON api.tasks
  FOR EACH ROW EXECUTE PROCEDURE notify_task_change();

You would then need a separate service to listen to these notifications and forward them to clients via WebSockets.

3. JSON Operations

PostgreSQL has powerful JSON capabilities for handling complex data:

-- Add a JSON metadata field to tasks
ALTER TABLE api.tasks ADD COLUMN metadata JSONB DEFAULT '{}'::jsonb;

-- Create an index for JSON operations
CREATE INDEX tasks_metadata_idx ON api.tasks USING GIN(metadata);

-- Function to add a tag to a task
CREATE FUNCTION api.add_task_tag(task_id INTEGER, tag TEXT) RETURNS api.tasks AS $$
DECLARE
  task api.tasks;
BEGIN
  UPDATE api.tasks
  SET 
    metadata = jsonb_set(
      CASE WHEN metadata ? 'tags' THEN metadata
      ELSE jsonb_set(metadata, '{tags}', '[]'::jsonb)
      END,
      '{tags}',
      (metadata -> 'tags') || to_jsonb(tag)
    )
  WHERE id = add_task_tag.task_id
  RETURNING * INTO task;
  
  RETURN task;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execution permission
GRANT EXECUTE ON FUNCTION api.add_task_tag(INTEGER, TEXT) TO api_user;

Performance Optimization

To ensure your PostgREST API performs well:

Proper Indexing: -- Add indexes to commonly queried columns CREATE INDEX tasks_project_id_idx ON api.tasks(project_id); CREATE INDEX tasks_assigned_to_idx ON api.tasks(assigned_to); CREATE INDEX tasks_status_idx ON api.tasks(status);
Materialized Views for complex queries: -- Create a materialized view for project statistics CREATE MATERIALIZED VIEW api.project_stats AS SELECT p.id AS project_id, p.name, COUNT(DISTINCT t.id) AS total_tasks, COUNT(DISTINCT t.id) FILTER (WHERE t.status = 'completed') AS completed_tasks, COUNT(DISTINCT pm.user_id) AS member_count FROM api.projects p LEFT JOIN api.tasks t ON p.id = t.project_id LEFT JOIN api.project_members pm ON p.id = pm.project_id GROUP BY p.id, p.name; -- Create a refresh function CREATE FUNCTION api.refresh_project_stats() RETURNS VOID AS $$ BEGIN REFRESH MATERIALIZED VIEW api.project_stats; END; $$ LANGUAGE plpgsql; -- Refresh automatically with a trigger CREATE TRIGGER refresh_project_stats AFTER INSERT OR UPDATE OR DELETE ON api.tasks FOR EACH STATEMENT EXECUTE PROCEDURE api.refresh_project_stats();
Request Limiting: Configure your web server or use a service like nginx in front of PostgREST to implement rate limiting.

Security Considerations

Use HTTPS: Always use HTTPS in production
Rate Limiting: Implement rate limiting to prevent abuse
JWT Expiration: Use short expiration times for JWTs
Regular Audits: Periodically review your security policies and RLS rules
Input Validation: Perform thorough input validation, especially when accepting JSON

Extending Your API

Integrating with Other Services

PostgREST can be integrated with other services in a microservices architecture:

Authentication Services: Integrate with Auth0 or similar services for more robust authentication
File Storage: Use PostgreSQL’s Foreign Data Wrappers (FDW) to integrate with file storage
Analytics: Use PostgreSQL’s pub/sub for analytics services

Adding API Documentation

PostgREST automatically generates an OpenAPI (Swagger) description. You can access it at:

http://localhost:3000/

You can use tools like Swagger UI to create beautiful API documentation from this output.

Conclusion

Building a REST API with PostgreSQL and PostgREST offers a refreshingly direct approach to API development. By eliminating the ORM layer and traditional API boilerplate, you can focus on what matters most: your data model and business logic.

This approach leverages PostgreSQL’s powerful features:

Row-level security for fine-grained access control
Functions and stored procedures for business logic
JSON support for flexible data structures
Full-text search capabilities
Real-time notifications

For many applications, especially data-driven ones, this “database as an API” approach can significantly reduce development time and complexity while providing excellent performance and security.

Remember that PostgREST is just one tool in your toolkit. For some applications, especially those with complex business logic or specific performance requirements, traditional API frameworks might still be the better choice. However, for many scenarios, the PostgreSQL + PostgREST combination offers a compelling alternative that’s well worth considering.