Learn Backstage - Persistence & Databases

#Introduction

In episode 11, you deepened the advanced catalog: entity relations, ownership, catalog filters, and keeping entity quality with CI validators. Episode 12 shifts from the shape of data to where it's stored. Almost every Backstage plugin — catalog, scaffolder, auth, search — writes to a database, and once an instance is used by many people, the database layer becomes the most common source of problems. Understanding this layer will save you from unnecessary downtime.

#Backstage's Storage Layer

Backstage doesn't lock you into one single type of database. Each backend plugin chooses a store that fits its data:

• Relational database — for data needing structured queries and integrity: catalog entities, scaffolder tasks, auth sessions, and the like.
• Cache store — for short-lived data that must be read quickly and repeatedly.
• Search index store — for documents already parsed and ready to query.

These three layers are configured via app-config.yaml, each under the keys database, cache, and search.

#SQLite: The Development Default

When you first bootstrap via npx @backstage/create-app@latest, you'll find the instance runs without any extra database setup. That's because Backstage uses SQLite as its default store — zero-configuration, stored as a file, and perfect for experiments on your laptop.

backend:
  database:
    client: better-sqlite3
    connection: ':memory:'

SQLite's advantages: no separate server needed, portable, and enough for single-user development. Its weaknesses are clear: not designed for concurrent load (concurrent writes), single-file, and doesn't support connections from many instances at once. That's why SQLite is only suitable for development and testing, not production.

#PostgreSQL for Production

When you launch Backstage for a real team, PostgreSQL becomes the standard choice. Backstage supports Postgres as a shared database that can be accessed by many backend instances at once — a hard requirement for horizontal scaling.

backend:
  database:
    client: pg
    connection:
      host: postgres.internal
      port: 5432
      user: backstage
      password: ${POSTGRES_PASSWORD}
      database: backstage

Notice client: pg — that tells Backstage the connection uses the PostgreSQL driver. Behind the scenes, this connection is created through Knex, the query builder used by all of Backstage's database plugins.

#SQLite vs PostgreSQL

The rule of thumb is simple: start with SQLite for fast experimentation, then move to PostgreSQL as soon as a production or preview environment is wanted. Many teams actually use Postgres from the very beginning of development to avoid "different between laptop and production" surprises.

#Knex and Database Migrations

Knex is the database abstraction Backstage uses in almost every plugin. With Knex, one query codebase runs on both SQLite and PostgreSQL without changes. More importantly, Knex provides migrations — the database schema is managed as migration files whose versions are recorded in a special table. This database layer is wrapped into a service you can import from @backstage/backend-defaults.

When you change a plugin's schema or add a new plugin, migrations need to run so the database schema stays in sync:

yarn backstage-cli package schema migrate --package @internal/plugin-catalog

This command ensures the tables a package needs exist and are at the right version. In production deployments, migrations are usually run as a step before the application starts — never put migrations inside runtime logic that only runs once.

#Connection Pooling with PgBouncer

PostgreSQL limits the number of active connections per instance. The problem: Backstage opens several connections per backend plugin, and if you run many instances, the connection count can explode. That's where connection pooling comes in: a pooler lends out connections from a small pool and returns them when done.

PgBouncer is the most commonly used pooler with Backstage:

[databases]
backstage = host=postgres.internal port=5432 dbname=backstage
 
[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 1000

With pool_mode = transaction, a connection is held only for the duration of one transaction, then released back to the pool — a pattern that fits Backstage's short-lived workload. The application is then pointed at port 6432 instead of straight to Postgres on 5432.

#Cache Store: Redis

Some Backstage data doesn't need a relational database — just a fast cache shareable between instances. The default is in-memory, which works for a single instance but isn't consistent once there's more than one. For large scale, Backstage supports Redis as the cache store:

cache:
  backend:
    store: redis
    connection:
      url: redis://redis.internal:6379

With Redis, the cache can be shared between instances so a result cached by instance A is also useful to instance B. Make sure Redis runs in a separate environment from Postgres so the two loads don't interfere.

#Search Index Store for Large Scale

The Search feature (which you'll dive into in episode 16) needs an index store. By default, the search index is stored in the main database — fine for small teams. As the number of entities and documents grows, you can move the search index to a dedicated engine like Elasticsearch so queries stay fast without burdening the main database.

Search engine configuration is done through the search key in app-config.yaml. The big decision: start with the index in the database, then move to a dedicated engine once document volume and query needs exceed the main database's capabilities.

#Conclusion

Episode 12 organized Backstage's data foundation: SQLite for development and PostgreSQL for production, the role of Knex and migrations in keeping schemas consistent, PgBouncer for mitigating connection explosions, Redis for cache shareable between instances, and a direction for separating the search index at scale.

The key takeaways:

• SQLite is only for development — production uses PostgreSQL as a shared database.
• Migrations are a deployment step — run them before the backend starts, not inside the runtime.
• Pooling saves connections — PgBouncer separates connection load from Postgres itself.
• Separate cache and search index — Redis and a dedicated search engine keep the main database light.

In the next episode, episode 13, you enter authorization: the permission framework and RBAC — how to control who can see entities, run templates, and perform certain actions. The data neatly stored in this episode will be guarded by strict access rules.