Move an app from SQLite to Postgres

SQLite apps usually outgrow a single file when they need several servers, real-time updates or user accounts. Moving to a SupaHost project gives you Postgres plus Auth, Storage and an HTTP API. The data move is quick with pgloader; most of the work is tightening data that SQLite accepted loosely and swapping the driver in your code.

Plan

• Look at the data first. SQLite lets any column hold any type and doesn't enforce foreign keys unless you turned on PRAGMA foreign_keys. Postgres will reject rows that break your declared types or references, so check before you convert:

  -- in sqlite3: orphaned rows that will fail a foreign key in Postgres
  select count(*) from tasks where project_id not in (select id from projects);
  -- values stored with the wrong type
  select typeof(due_date), count(*) from tasks group by 1;
  

• Decide on types. SQLite booleans are 0/1 integers and dates are usually text or Unix seconds. In Postgres you'll want boolean and timestamptz.

Convert with pgloader

pgloader reads a SQLite file and writes to Postgres. On Business and Enterprise it can write straight into your project through the SSH tunnel; on other plans, write into a local Postgres 17 first.

LOAD DATABASE
  FROM sqlite:///home/me/todo-app/data.db
  INTO postgresql://postgres:<password>@127.0.0.1:6543/p_app_one
WITH include drop, create tables, create indexes, reset sequences

CAST column tasks.done to boolean using tinyint-to-boolean,
     column tasks.due_date to timestamptz drop typemod;

# Business: open the tunnel, then load
ssh -N -L 6543:127.0.0.1:5432 [email protected] &
pgloader todo.load

The due_date cast assumes ISO text dates. If yours are Unix seconds, load them as bigint and convert afterwards with to_timestamp(due_date). The connection password comes from Dashboard → your project → Connect → Database → Show connection string.

Load on Builder and Pro

Small SQLite databases are often only a few MB, so the simplest route is:

1. Run pgloader into a local Postgres (docker run -p 6543:5432 -e POSTGRES_PASSWORD=dev postgres:17).
2. pg_dump --schema-only --no-owner --no-privileges -n public and run the output in Studio's SQL editor, or send it through MCP apply_migration from your AI tool.
3. Export each table as CSV (\copy tasks to 'tasks.csv' csv header) and import it in Studio's table editor.

For larger files, dump the local database and restore it into a temporary Supabase Cloud project, then use the one-click import.

Add users and security

A single-user SQLite app often has no notion of an owner. Add one and lock tables down, since public is exposed through the Data API:

alter table public.tasks add column user_id uuid references auth.users (id) default auth.uid();
alter table public.tasks enable row level security;
create policy "own tasks" on public.tasks
  for all to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

Existing rows get null in user_id, so assign them to an account (update public.tasks set user_id = '<uuid>') once you've created your user.

Switch the code

// before: better-sqlite3
const rows = db.prepare('select * from tasks where done = 0 order by due_date').all()

// after: supabase-js
const { data: rows, error } = await supabase
  .from('tasks')
  .select('*')
  .eq('done', false)
  .order('due_date')

Raw SQL that used ? placeholders becomes either query-builder calls or a database function called with supabase.rpc().

Verify

Compare select count(*) per table with SQLite, check a few dates came through in the right time zone, and run your test suite against the new project.

Related

• Importing data (CSV, SQL dumps)
• Row Level Security
• Timezones and wrong local time in Postgres
• Querying and filtering data