Motherbase
Quick start
Browse documentation

Motherbase uses PostgreSQL 16 and exposes project tables through an auto-generated REST API. The normal workflow is SQL-first:

  1. Open the project from the Platform Console.
  2. Go to Project Studio → Database → SQL or use the Table editor.
  3. Create a table.
  4. Review the generated /rest/v1/:model routes.
  5. Add PostgreSQL RLS policies.
  6. Connect from your app with the SDK.
Note

Platform Console is the multi-project control plane. Project Studio is the admin UI for one project, mounted under /console, with tables, SQL, auth, policies, logs, storage, realtime, edge functions, triggers, cron jobs, and settings.

Create a table with SQL

Run SQL from Project Studio → Database → SQL:

CREATE TABLE todo (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  done boolean NOT NULL DEFAULT false,
  user_id text DEFAULT auth.uid(),
  created_at timestamptz NOT NULL DEFAULT now()
);

When CREATE TABLE succeeds, Motherbase discovers the table, enables RLS for it, and exposes REST routes automatically:

GET    /rest/v1/todo
POST   /rest/v1/todo
PATCH  /rest/v1/todo?id=eq.<id>
DELETE /rest/v1/todo?id=eq.<id>

The SQL editor shows results, errors, destructive-query warnings, and generated route information. Enable it with:

CONSOLE_SQL_ENABLED=true

It may write by default. To restrict it to reads, turn off Allow writes from the SQL editor under Database → Settings → SQL editor: statements then run in a PostgreSQL read-only transaction.

Table editor

You can also create tables from the Project Studio Table editor. The modal shows a SQL preview and sends standard SQL to the backend, so tables created there behave the same as tables created in the SQL editor.

Database automation

Project Studio also exposes database-side automation:

  • SQL Functions inspect, create, replace, and drop functions in the public schema.
  • Extensions enable or disable PostgreSQL extensions such as pg_cron.
  • Postgres Triggers create real CREATE TRIGGER ... EXECUTE FUNCTION ... definitions.
  • Cron Jobs schedule recurring SQL commands through pg_cron.

See Postgres Triggers and Cron Jobs for the dedicated workflows.

Row Level Security

Motherbase uses native PostgreSQL RLS, not an application-level policy DSL. Policies are real CREATE POLICY rules stored in pg_policies.

CREATE POLICY "todo owner read"
ON todo
FOR SELECT
TO authenticated
USING (auth.uid() IS NOT NULL AND auth.uid() = user_id);
 
CREATE POLICY "todo owner insert"
ON todo
FOR INSERT
TO authenticated
WITH CHECK (auth.uid() IS NOT NULL AND auth.uid() = user_id);

Open Project Studio → Policies for the visual editor, or run raw SQL from Project Studio → Database → SQL.

Note

With RLS enabled and no policy, anon and authenticated callers see no rows. With RLS disabled, the table is effectively open through REST because the data-plane roles have broad table grants.

See the RLS guide for full policy examples and SQL Editor workflow.

REST API access

Every exposed database table is available at /rest/v1/:model. Access is controlled by PostgreSQL RLS.

GET /rest/v1/todo?done=eq.false&order=created_at.desc&limit=20
import { createClient } from '@motherbase/client'
 
const client = createClient(process.env.NEXT_PUBLIC_MOTHERBASE_URL!)
 
const { data: todos } = await client
  .from('todo')
  .select('*')
  .eq('done', false)
  .order('created_at', { ascending: false })
  .limit(20)
 
const { data: created } = await client
  .from('todo')
  .insert({ title: 'Hello', done: false })
 
const todo = created?.[0]
 
if (todo) {
  await client.from('todo').update({ done: true }).eq('id', todo.id)
  await client.from('todo').delete().eq('id', todo.id)
}

Connecting directly

Not everything reaches a project through the REST API. An ORM, a migration tool, psql, a BI client or a dashboard wants the database itself.

Every project publishes its PostgreSQL port on the host, and on the loopback interface only, so it is not an internet service. Reach it over an SSH tunnel:

ssh -N -L 5432:localhost:<DB_PORT> user@your-vps

Then connect to localhost:5432 as if the database were local:

postgresql://postgres:<password>@localhost:5432/app

Project Studio → Connect → Database connection shows the port, the password and a ready-to-paste URI, in three modes: over a tunnel, direct, and from another container on the project network.

Each project gets a stable port allocated from a contiguous range (54320-54419) at creation, so a saved connection string keeps working across a recreate, and an operator can firewall the whole block in one rule.

Warning

Setting DB_BIND=0.0.0.0 in the project's .env.prod publishes the database on the public internet. PostgreSQL authentication is then the only thing between the world and every row in the project. Restrict the port to known addresses at the firewall before you do it.

The credentials shown on the Connect page are the owner role. It is not subject to row level security, so treat that URI exactly like a secret API key: server side only.

Prisma

Prisma is an internal implementation and maintenance tool for the backend itself. Contributors and self-host maintainers may still use Prisma migrations for core schema changes, but end users should create project tables from Project Studio with SQL or the Table editor.