Database Views for Self-Hosted Supabase: Design Clean APIs with PostgreSQL

When you deploy self-hosted Supabase, your PostgreSQL tables automatically become REST API endpoints via PostgREST. That's powerful—but it also means your database schema directly shapes your API surface. Every column in every table becomes part of your public interface.

For production applications, this tight coupling creates problems. Rename a column? Your frontend breaks. Add an internal field? It's now visible to clients. Join data across tables? Your frontend makes multiple requests. PostgreSQL views solve all of these problems by creating an abstraction layer between your tables and your API.

This guide shows you how to use views effectively in self-hosted Supabase: designing clean API interfaces, handling security correctly with security_invoker, and avoiding the common pitfalls that trip up developers.

Why Views Matter for API Design

Views are virtual tables defined by a query. Instead of storing data, they execute their underlying query each time you access them. For API design, this means you can:

Reshape your data: Join tables, rename columns, compute values—all transparently
Hide internal details: Keep implementation columns out of your API
Simplify complex queries: Move JOIN logic to the database instead of the client
Evolve your schema safely: Change tables without breaking client code

Here's a practical example. Suppose you have a users table with internal fields you don't want exposed:

-- Your actual table has internal columns
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,  -- Never expose this
  internal_notes TEXT,           -- Admin only
  stripe_customer_id TEXT,       -- Internal billing
  display_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create a clean API surface with a view
CREATE VIEW public_users AS
SELECT 
  id,
  email,
  display_name,
  avatar_url,
  created_at
FROM users;

Now your frontend can query public_users instead of users. The password_hash, internal_notes, and billing fields are invisible—not filtered by RLS, but simply not part of the view at all.

The Security Gotcha: Understanding security_invoker

Here's where many developers get caught. By default, PostgreSQL views execute with the permissions of the view owner, not the user querying them. If you created the view in Supabase's SQL Editor, the owner is typically postgres or supabase_admin—roles that bypass Row Level Security.

This means a view on an RLS-protected table will return all rows by default, completely bypassing your security policies.

-- This view BYPASSES RLS - dangerous!
CREATE VIEW user_profiles AS
SELECT id, display_name, avatar_url
FROM users;

-- When authenticated users query this, they see ALL users
-- Even if your RLS policy says "users can only see their own data"

PostgreSQL 15+ solves this with security_invoker:

-- This view RESPECTS RLS - correct!
CREATE VIEW user_profiles 
WITH (security_invoker = true) AS
SELECT id, display_name, avatar_url
FROM users;

With security_invoker = true, the view executes with the permissions of whoever queries it. Your RLS policies on the underlying tables apply correctly.

If you're running PostgreSQL 14 or earlier, you have two options:

Change the view owner to a non-superuser role that respects RLS
Put views in a non-exposed schema and access them only through functions

For self-hosted Supabase, the PostgreSQL version depends on your Docker image. Check with:

SELECT version();

Modern Supabase images ship with PostgreSQL 15+, so security_invoker should work. If you need to upgrade your deployment, consider doing so to access this feature.

Practical View Patterns for APIs

The most common use case—expose joined data as a single endpoint instead of forcing clients to make multiple requests:

-- Base tables
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID REFERENCES users(id),
  title TEXT NOT NULL,
  content TEXT,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE post_stats (
  post_id UUID PRIMARY KEY REFERENCES posts(id),
  view_count INTEGER DEFAULT 0,
  like_count INTEGER DEFAULT 0
);

-- API view: join post data with author and stats
CREATE VIEW posts_with_details
WITH (security_invoker = true) AS
SELECT 
  p.id,
  p.title,
  p.content,
  p.published_at,
  p.created_at,
  u.display_name AS author_name,
  u.avatar_url AS author_avatar,
  COALESCE(s.view_count, 0) AS views,
  COALESCE(s.like_count, 0) AS likes
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN post_stats s ON p.id = s.post_id
WHERE p.published_at IS NOT NULL;

Your frontend now calls:

const { data } = await supabase
  .from('posts_with_details')
  .select('*')
  .order('created_at', { ascending: false })
  .limit(10);

One request returns posts with author info and engagement stats. No client-side joins needed.

Pattern 2: Computed Columns

Views can include computed values that would be awkward to maintain as stored columns:

CREATE VIEW orders_summary
WITH (security_invoker = true) AS
SELECT 
  o.id,
  o.customer_id,
  o.status,
  o.created_at,
  o.subtotal,
  o.tax_rate,
  -- Computed fields
  ROUND(o.subtotal * o.tax_rate, 2) AS tax_amount,
  ROUND(o.subtotal * (1 + o.tax_rate), 2) AS total,
  -- Time-based computation
  CASE 
    WHEN o.status = 'pending' AND o.created_at < NOW() - INTERVAL '24 hours'
    THEN true 
    ELSE false 
  END AS is_stale,
  -- Relative time
  EXTRACT(EPOCH FROM (NOW() - o.created_at)) / 3600 AS hours_since_order
FROM orders o;

Computed columns execute on every query, so they're always current. For expensive computations on large datasets, consider materialized views instead.

Pattern 3: Schema Evolution

Views let you change your underlying tables without breaking API clients:

-- Original table
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name TEXT,
  price_cents INTEGER  -- Stored as cents
);

-- API view: expose as dollars
CREATE VIEW products_api
WITH (security_invoker = true) AS
SELECT 
  id,
  name,
  price_cents / 100.0 AS price  -- Clients see dollars
FROM products;

-- Later, you refactor to a decimal column
ALTER TABLE products ADD COLUMN price_decimal NUMERIC(10,2);
UPDATE products SET price_decimal = price_cents / 100.0;
ALTER TABLE products DROP COLUMN price_cents;

-- Update the view - API contract unchanged
CREATE OR REPLACE VIEW products_api
WITH (security_invoker = true) AS
SELECT 
  id,
  name,
  price_decimal AS price  -- Same API, different source
FROM products;

Clients continue using price without knowing the underlying column changed.

Updatable Views for Write Operations

By default, views are read-only. But PostgreSQL can make simple views automatically updatable when they meet certain criteria:

Based on a single table
No aggregates, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET
No set operations (UNION, INTERSECT, EXCEPT)
All columns from the underlying table included

For more complex views, you can create INSTEAD OF triggers:

-- View joining users and profiles
CREATE VIEW user_with_profile
WITH (security_invoker = true) AS
SELECT 
  u.id,
  u.email,
  p.bio,
  p.website
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id;

-- Make the view updatable for profile fields
CREATE OR REPLACE FUNCTION update_user_with_profile()
RETURNS TRIGGER AS $$
BEGIN
  -- Update profile fields
  INSERT INTO profiles (user_id, bio, website)
  VALUES (NEW.id, NEW.bio, NEW.website)
  ON CONFLICT (user_id) 
  DO UPDATE SET bio = EXCLUDED.bio, website = EXCLUDED.website;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER user_with_profile_update
INSTEAD OF UPDATE ON user_with_profile
FOR EACH ROW
EXECUTE FUNCTION update_user_with_profile();

Now your frontend can update through the view:

const { error } = await supabase
  .from('user_with_profile')
  .update({ bio: 'New bio', website: 'https://example.com' })
  .eq('id', userId);

The trigger handles splitting the update across the correct tables.

Performance Considerations

Views don't cache results—they execute their query every time. For most CRUD operations, this is fine. But complex views with multiple JOINs on large tables can become slow.

Monitor your view performance:

-- Enable timing
\timing on

-- Test the view query directly
EXPLAIN ANALYZE SELECT * FROM your_view WHERE id = 'some-uuid';

Strategies for slow views:

Add indexes on columns used in JOINs and WHERE clauses in the underlying tables
Limit columns to only what's needed—don't SELECT * from base tables if you only need a few fields
Filter early by using WHERE clauses that reduce rows before JOINs
Consider materialized views for analytics queries that don't need real-time data

For connection pooling, views behave identically to regular tables—the query is parsed and executed within the same connection.

Granting Access to Views

In self-hosted Supabase, you need to grant access to views just like tables. As of April 2026, new Supabase projects require explicit grants for any object exposed via the Data API.

-- Grant read access for authenticated users
GRANT SELECT ON user_profiles TO authenticated;

-- Grant full access for specific operations
GRANT SELECT, INSERT, UPDATE ON user_with_profile TO authenticated;

-- For anon access (public endpoints)
GRANT SELECT ON posts_with_details TO anon;

Without these grants, the view exists in your database but won't be accessible through the REST API.

Organizing Views in Schemas

For larger applications, consider organizing views by access level:

-- Public API views
CREATE SCHEMA api;
CREATE VIEW api.posts ...
CREATE VIEW api.users ...

-- Admin-only views
CREATE SCHEMA admin_api;
CREATE VIEW admin_api.all_users ...
CREATE VIEW admin_api.audit_logs ...

Then configure which schemas are exposed in your environment variables:

# In your docker-compose.yml or environment
PGRST_DB_SCHEMAS: api,admin_api

Grant different roles access to different schemas:

GRANT USAGE ON SCHEMA api TO anon, authenticated;
GRANT USAGE ON SCHEMA admin_api TO service_role;

This creates clear boundaries between public and internal APIs.

Debugging View Issues

Common problems and solutions:

View returns all rows (RLS bypassed)

Check: SELECT security_invoker FROM pg_views WHERE viewname = 'your_view';
Fix: Recreate with WITH (security_invoker = true)

View not visible in API

Check: SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = 'your_view';
Fix: GRANT SELECT ON your_view TO authenticated;

View query is slow

Check: EXPLAIN ANALYZE SELECT * FROM your_view WHERE ...;
Fix: Add indexes on underlying tables, simplify JOINs, or use materialized views

Cannot update through view

Check if view meets auto-updatable criteria
Fix: Create INSTEAD OF triggers for complex views

Wrapping Up

Database views give you a powerful abstraction layer between your tables and your API. For self-hosted Supabase, they're especially valuable because you have full control over your PostgreSQL configuration and can design your API surface intentionally rather than having it dictated by your schema.

Key takeaways:

Always use security_invoker = true on views that access RLS-protected tables
Views are excellent for joining data, computing values, and hiding internal columns
Grant explicit permissions for views to appear in the REST API
Monitor performance—views execute on every query

Combined with RLS policies, stored procedures, and materialized views, database views complete your toolkit for building clean, secure, and performant APIs directly from PostgreSQL.

Database Views for Self-Hosted Supabase: Design Clean APIs with PostgreSQL

Build cleaner REST APIs using PostgreSQL views in self-hosted Supabase. Learn security_invoker, RLS patterns, and API abstraction.

Why Views Matter for API Design

The Security Gotcha: Understanding security_invoker

Practical View Patterns for APIs

Pattern 2: Computed Columns

Pattern 3: Schema Evolution

Updatable Views for Write Operations

Performance Considerations

Granting Access to Views

Organizing Views in Schemas

Debugging View Issues

Wrapping Up

Further Reading

Database Views for Self-Hosted Supabase: Design Clean APIs with PostgreSQL

Build cleaner REST APIs using PostgreSQL views in self-hosted Supabase. Learn security_invoker, RLS patterns, and API abstraction.

Why Views Matter for API Design

The Security Gotcha: Understanding security_invoker

Practical View Patterns for APIs

Pattern 1: Joining Related Data

Pattern 2: Computed Columns

Pattern 3: Schema Evolution

Updatable Views for Write Operations

Performance Considerations

Granting Access to Views

Organizing Views in Schemas

Debugging View Issues

Wrapping Up

Further Reading