ApostropheCMS

Appearance

Edit this page on GitHub or open an issue

Using SQLite or PostgreSQL Instead of MongoDB ​

ApostropheCMS has historically required MongoDB, and MongoDB remains the default. In addition to MongoDB, projects can now run on SQLite or PostgreSQL through the @apostrophecms/db-connect adapter layer. The adapter is transparent to application code: the same models, queries, cursors and aggregations work identically across all three backends.

This guide is intended for developers who already know why they want to use SQLite or PostgreSQL — for example, to avoid running a database server entirely in local development by using SQLite, or to consolidate on an existing PostgreSQL infrastructure. It does not try to convince you to switch. If you are happy with MongoDB, there is no reason to change.

INFO

The Apostrophe CLI will prompt for your preferred database when creating a new project. For existing projects, you can try non-MongoDB backends by setting one environment variable.

Prerequisites ​

  • Node.js and a working Apostrophe development environment (see Development Setup).
  • For PostgreSQL: a running PostgreSQL 14+ server you can connect to (local, Docker, or managed).
  • For SQLite: no server. A file on disk is the entire database.

No changes to your project's package.json or module code are required. Apostrophe loads the right adapter based on the protocol in your connection URI.

Starting from the public demo ​

The quickest way to try this out is the public demo project:

bash
git clone https://github.com/apostrophecms/public-demo.git
cd public-demo
npm install

Apostrophe reads the database connection string from the APOS_DB_URI environment variable. By setting it to a non-mongodb:// URI, you switch backends without editing a single line of project code.

Using SQLite ​

SQLite is the easiest option for local development and small sites: the database is a single file, there is no server to run, and backups are a file copy.

Point APOS_DB_URI at a file path using the sqlite:// protocol:

bash
export APOS_DB_URI=sqlite:///absolute/path/to/apos-demo.db
npm run dev

The triple slash is intentional — sqlite:// is the protocol, and the path that follows is absolute. Use a relative path with a double slash if you prefer:

bash
export APOS_DB_URI=sqlite://./data/apos-demo.db

The file is created on first run, along with all tables and indexes. To start over, stop the process and delete the file.

WARNING

In-memory SQLite (sqlite://:memory:) is not supported. Apostrophe opens multiple connections to the database, and an in-memory database is only visible to the connection that created it. Use a real file on disk — even a file in /tmp works fine for throwaway experiments.

Using PostgreSQL ​

PostgreSQL is a good choice when you want to consolidate on infrastructure that already runs PostgreSQL, or to use the operational tooling (backups, monitoring, replication) your team is already familiar with.

Create an empty database and point APOS_DB_URI at it using the postgres:// protocol:

bash
# Create the database (once)
createdb apos_demo

# Run Apostrophe against it
export APOS_DB_URI=postgres://user:password@localhost:5432/apos_demo
npm run dev

On first run, Apostrophe creates the tables and indexes it needs inside that database. It does not touch other databases or schemas on the same server.

If you prefer not to embed credentials in the URI, PostgreSQL's standard environment variables (PGUSER, PGPASSWORD, PGHOST, PGPORT) are honored:

bash
export PGUSER=apos
export PGPASSWORD=...
export APOS_DB_URI=postgres://localhost:5432/apos_demo

Changing your default adapter ​

By default, if you do not set APOS_DB_URI or APOS_MONGODB_URI, apostrophecms defaults to mongodb://localhost:27017/your-shortName-here.

For local development, if you always use sqlite or postgres, you can optionally set the APOS_DEFAULT_ADAPTER environment variable to sqlite or postgres to get the same effect with your preferred database.

If you set APOS_DEFAULT_ADAPTER to sqlite, then a shortName setting of my-project will point to the sqlite://data/my-project.sqlite, which refers to the data/my-project.sqlite file in your project. Make sure you add the file to .gitignore and be aware that if your project folder is removed, the database is gone too.

If you set APOS_DEFAULT_ADAPTER to postgres, then a shortName setting of my-project will point to postgres://localhost:5432/my-project.

If you choose this approach it is best to be consistent about it, e.g. set it in your .bashrc or .zshrc file.

For multisite projects, this technique is not currently supported. Use DB_URI as described above.

Switching a project between backends ​

Because the three URI formats are fully interchangeable, you can experiment with different backends on the same codebase by changing APOS_DB_URI alone. Each URI targets an independent database — there is no automatic migration between them.

To migrate content between backends, use the apos-db-dump and apos-db-restore tools shipped with @apostrophecms/db-connect. They produce and consume a portable JSONL format that works across all three adapters.

Because @apostrophecms/db-connect is already a transitive dependency of every Apostrophe project, the simplest way to run these tools is from inside your project directory with npx — no global install required:

bash
cd /path/to/your/apostrophe/project
npx apos-db-dump mongodb://localhost:27017/mydb --output=backup.jsonl
npx apos-db-restore postgres://localhost:5432/mydb --input=backup.jsonl

If you prefer them on your PATH for use across many projects, install globally instead:

bash
npm install -g @apostrophecms/db-connect
apos-db-dump mongodb://localhost:27017/mydb --output=backup.jsonl

See the db-connect dump/restore documentation for the full set of options, including piping dump output straight into restore for cross-backend migration.

Multi-tenant deployments with the multisite module ​

If you are using the multisite module to host many Apostrophe sites from a single codebase, MongoDB, SQLite, and PostgreSQL are all supported. Two things differ from a single-site project:

  • Multisite reads its connection URI from the DB_URI environment variable (or the dbUri option), not APOS_DB_URI.
  • Database, file, and schema names are derived from multisite's shortNamePrefix option (default multisite-). Each hosted site uses <shortNamePrefix><siteId>, and the dashboard uses <shortNamePrefix>dashboard. Because the dashboard is the one tenant guaranteed to exist, the convention is to point DB_URI at it:
bash
# MongoDB (default)
export DB_URI=mongodb://localhost:27017/multisite-dashboard

# SQLite — the file's basename must equal <shortNamePrefix>dashboard
export DB_URI=sqlite:///var/lib/apostrophe/multisite-dashboard.db

# PostgreSQL — use multipostgres://, not postgres://, see below
export DB_URI=multipostgres://user:password@localhost:5432/multisite-dashboard

If you change shortNamePrefix from the default, change every URI to match (e.g. myapp- → myapp-dashboard, myapp-dashboard.db).

Why multipostgres:// and not postgres://? ​

multipostgres:// maps each site to its own schema inside a single shared PostgreSQL database, rather than provisioning a separate database per site. This matters because most managed PostgreSQL services (RDS, Cloud SQL, Neon, Supabase) bill, provision, and limit you per database, so a database-per-tenant model is rarely practical.

A multipostgres:// URI must include both a database name and a schema name, separated by the last hyphen in the path. In multipostgres://.../multisite-dashboard, multisite is the real PostgreSQL database name and dashboard is the schema for the dashboard site. The portion before the last hyphen must equal shortNamePrefix with the trailing hyphen removed; multisite substitutes each tenant's short name for dashboard when accessing per-site schemas at runtime.

Plain postgres:// is supported for single-tenant Apostrophe projects but is not appropriate for multisite.

Refer to the multisite extension page for installation and tenant-management documentation.

Compatibility notes ​

All three adapters are provided by the @apostrophecms/db-connect module. db-connect implements a large subset of the MongoDB API — enough to support ApostropheCMS core and the extensions and patterns it relies on — but it does not attempt to cover 100% of the MongoDB API surface. Application code that sticks to the queries, cursors, aggregations, and index definitions Apostrophe itself uses will work across all three backends; code that reaches for less-common MongoDB features (obscure aggregation stages, server-side JavaScript, change streams, geospatial operators, and so on) may not.

If you are writing modules intended to run across MongoDB, PostgreSQL, and SQLite, treat the db-connect API as authoritative rather than the full MongoDB driver API. See the db-connect README for the complete list of supported query operators, cursor methods, aggregation stages, and connection-URL formats, and the db-connect docs folder for deeper material on the dump/restore format and adapter internals.