How to Move an App From SQLite to Managed Postgres (Migrate SQLite to Postgres Hosting)

By /

How to Move an App From SQLite to Managed Postgres (Migrate SQLite to Postgres Hosting)

Migrating SQLite to Postgres hosting is the most common scaling step developers take when a side project turns into a real product. SQLite is fine for local development and single-process apps, but it breaks under concurrent writes, remote connections, and multi-instance deployments. This guide walks you through every step — dump, schema conversion, data import, connection string swap — so your app is live on managed Postgres with zero data loss.

Why SQLite Breaks in Production

SQLite stores your entire database in a single file on disk. That works perfectly when one process reads and writes locally. The moment you deploy to a hosting environment, you hit three hard limits:

  • No concurrent writes — SQLite serialises all writes; under load, requests queue or time out.
  • No remote connections — the file must live on the same server as the app, which blocks horizontal scaling and external tooling.
  • No replication or backups via standard Postgres tooling — you cannot connect TablePlus, pgAdmin, or Metabase to an SQLite file over a network.

Managed Postgres eliminates all three problems with a single connection string.

Before You Start: What You Need

  • Your existing SQLite database file (usually db.sqlite3, database.db, or app.db)
  • A managed Postgres instance (see pricing below)
  • pgloader or pg_dump/psql installed locally
  • Your app’s ORM or database driver config file

Step 1 — Provision a Managed Postgres Instance

Sign up for CM Cloud Managed PostgreSQL at cmcloudhosting.com/pricing. The Starter plan covers most apps moving off SQLite. You get a TLS connection string, a dedicated host, and automatic daily backups from day one.

Note your connection details:

Host: your-db-host.cmcloud.io
Port: 5432
Database: your_db_name
User: your_db_user
Password: ••••••••

Keep this window open — you will paste these into your app config in Step 4.

Step 2 — Export Your SQLite Database

The fastest single-command migration uses pgloader, an open-source tool that reads SQLite and writes directly to Postgres.

Install pgloader:

# Ubuntu/Debian
sudo apt-get install pgloader

# macOS
brew install pgloader

Run the migration:

pgloader sqlite:///path/to/your/app.db \
  postgresql://your_db_user:[email protected]:5432/your_db_name

pgloader handles type coercion automatically — SQLite’s INTEGER PRIMARY KEY becomes Postgres SERIAL, TEXT stays TEXT, and BLOB becomes BYTEA. Watch the output for any ERROR lines; they are rare but worth fixing before you go live.

Step 3 — Verify the Data

Connect to your new Postgres instance with a GUI client. If you have already set that up, your existing TablePlus or DBeaver connection works perfectly here.

Run row-count checks for every critical table:

SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM products;

Compare each count against the same query on your SQLite file using the sqlite3 CLI:

sqlite3 app.db "SELECT COUNT(*) FROM users;"

If counts match, your data is intact. If they differ, re-run pgloader with the --verbose flag to trace the discrepancy.

Step 4 — Update Your App’s Database Connection

Node.js / Express / Next.js

Replace your SQLite driver (better-sqlite3, sqlite3) with pg:

npm install pg
npm uninstall better-sqlite3

Update your connection:

const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false }
});

Set DATABASE_URL as an environment variable in your hosting portal — never hard-code credentials. If you deploy on CM Cloud App Hosting, add it under Environment Variables in the portal (see the App Hosting env vars guide).

Django (Python)

Install psycopg2:

pip install psycopg2-binary

Update settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'your_db_name',
        'USER': 'your_db_user',
        'PASSWORD': os.environ.get('DB_PASSWORD'),
        'HOST': 'your-db-host.cmcloud.io',
        'PORT': '5432',
    }
}

Run python manage.py migrate — Django will apply any pending migrations against Postgres.

Laravel (PHP)

Set the following in your .env (or as server environment variables):

DB_CONNECTION=pgsql
DB_HOST=your-db-host.cmcloud.io
DB_PORT=5432
DB_DATABASE=your_db_name
DB_USERNAME=your_db_user
DB_PASSWORD=your_password

Run php artisan migrate to confirm Laravel connects and all migrations are current.

FastAPI (Python / SQLAlchemy)

Update your SQLAlchemy engine:

from sqlalchemy import create_engine
DATABASE_URL = os.environ["DATABASE_URL"]  # postgresql://user:pass@host:5432/db
engine = create_engine(DATABASE_URL)

Step 5 — Deploy and Smoke-Test

Push your updated code. If you use CM Cloud App Hosting with the CI/CD deploy hook, a git push to your main branch triggers an automatic redeploy — no SSH required.

After deploy:
1. Register a test user or create a test record.
2. Check it appears in Postgres via your GUI client.
3. Run your critical API endpoints and check response times.
4. Monitor your Postgres connection count in the CM Cloud dashboard — if it spikes near your plan’s limit, increase the pool size in your driver config.

Common Migration Gotchas

  • Boolean columns — SQLite stores booleans as 0/1 integers. pgloader converts these, but verify your ORM maps them correctly after migration.
  • Auto-increment sequences — Postgres SERIAL sequences may not match your SQLite ROWID values if rows were deleted. Run SELECT setval('table_id_seq', MAX(id)) FROM table; for each table to reset sequences.
  • Case sensitivity — Postgres is case-sensitive for identifiers by default; SQLite is not. Quoted table names in migrations can cause issues.
  • Date formats — SQLite stores dates as text; Postgres enforces DATE/TIMESTAMP types. pgloader handles this, but test any date-filtering queries.

Pricing: Managed Postgres vs. DIY Postgres on a VPS

Provider Starter Price (USD) XAF EUR Key Feature
CM Cloud Managed Postgres $9.99/mo XAF 6,200 €9.25 Automated backups, TLS, Africa-ready
DigitalOcean Managed Postgres $15.00/mo ~XAF 9,300 ~€13.90 Managed, US/EU regions
Railway Postgres ~$10.00/mo (usage) ~XAF 6,200 ~€9.25 Pay-per-use, can spike
Self-managed on CM Cloud VPS $7.99/mo + ops time XAF 5,000 €7.40 Full control, no backups included

CM Cloud is the only provider showing prices in USD, XAF, and EUR simultaneously — no surprise conversion rates for Cameroonian or West African businesses. See the full plan comparison at cmcloudhosting.com/pricing.

When to Stay on SQLite

Not every app needs this migration. SQLite is the right choice when:
– You are in local development or prototyping.
– Your app is a single-user CLI tool or desktop application.
– You use PocketBase on CM Cloud App Hosting — PocketBase manages its own SQLite internally and you never touch it directly.

If you are deploying a multi-user web app, an API with concurrent traffic, or anything that needs external database connections, Postgres is the correct production database.

Ready to Migrate?

CM Cloud Managed PostgreSQL starts at $9.99/mo (XAF 6,200 / €9.25) with TLS connections, daily backups, and a dedicated host — no shared noisy neighbours. Pair it with App Hosting at $7.99/mo for a complete backend stack that auto-deploys on every git push.

👉 Start your managed Postgres plan at cmcloudhosting.com/pricing — provisioned in minutes, connect in seconds.

Scroll to Top