# Move an app from SQLite to Postgres

> Move a SQLite database and the app around it to Postgres on SupaHost: convert with pgloader, fix loose SQLite types and foreign keys, load the data and switch to supabase-js.

Source: https://supahost.app/kb/migrate-from-sqlite · Updated 2026-11-11

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:

  ```sql
  -- 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;
```

```bash
# Business: open the tunnel, then load
ssh -N -L 6543:127.0.0.1:5432 supahost@acme.supahost.space &
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](/kb/migrate-from-supabase-cloud).

## 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:

```sql
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

```ts
// 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)](/docs/database/import-data)
- [Row Level Security](/docs/database/row-level-security)
- [Timezones and wrong local time in Postgres](/kb/timezones)
- [Querying and filtering data](/docs/api/querying)