How to Set Up a Managed Database for Your Web App

Why Most Developers Get Their Database Setup Wrong

Setting up a managed database for your web app sounds straightforward. Pick a provider, create a table or two, connect your backend. Done.

But the choices you make at this stage — SQL or NoSQL, which provider, how you handle migrations, what your access model looks like — compound quickly. A database is the one part of your stack that’s genuinely hard to change later without real pain.

This guide covers how to set up a managed database the right way: what to look for in a provider, how to decide between SQL and NoSQL, how to run migrations without breaking production, and what security basics you shouldn’t skip. Whether you’re building a SaaS MVP or a production app with real users, the same principles apply.

SQL vs. NoSQL: Making the Right Call Before You Start

The SQL vs. NoSQL debate gets overcomplicated. For most web apps, the answer is SQL — and it has been for decades. But there are real cases where NoSQL makes more sense.

When to Choose SQL

SQL databases (PostgreSQL, MySQL, SQLite) are relational. Your data lives in tables with defined schemas, rows reference other rows via foreign keys, and you query across them with JOIN statements.

Use SQL when:

Your data has clear relationships (users have orders, orders have items)
You need transactional integrity — operations that need to succeed or fail atomically
You’re building anything with financial data, user accounts, or structured content
You want to run complex queries across multiple entities

PostgreSQL is the default choice for most production web apps. It’s mature, feature-rich, and handles everything from small personal projects to large-scale applications. For smaller apps or embedded use cases, SQLite is surprisingly capable — especially with WAL mode enabled, which adds concurrent read support.

When to Consider NoSQL

NoSQL databases (MongoDB, DynamoDB, Firestore) store data as documents or key-value pairs rather than rows and columns. They’re flexible — you don’t define a schema upfront — but that flexibility comes at a cost.

NoSQL makes sense when:

Your data has no consistent structure (e.g., user-defined custom fields)
You need horizontal scaling across many servers from day one
You’re storing large volumes of simple, non-relational data (logs, events, sensor data)
You’re building something that maps naturally to a document model (CMS content, user preferences)

For most web apps — especially early-stage ones — SQL is the better default. The schema forces you to think clearly about your data model, and the query power pays off as your app grows. Starting with NoSQL for flexibility often just delays data modeling problems instead of solving them.

A separate category worth knowing: vector databases. If your app involves semantic search, embeddings, or AI memory systems, you may need something like pgvector (a PostgreSQL extension) or a dedicated vector store. What Is a Vector Database and Why AI Agents Need Them covers this in more detail.

What “Managed” Actually Means — and Why It Matters

A managed database is one where the provider handles operational concerns: hardware provisioning, software updates, automated backups, failover, scaling, and security patching.

The alternative is self-hosting — running a database server yourself on a VPS or cloud instance. Self-hosting gives you maximum control and lower costs at scale, but it also means you’re responsible for keeping the database running, backed up, and secure.

For the vast majority of web apps, managed is the right call. Here’s what you’re buying:

Automated backups — Most managed providers snapshot your database daily or more frequently. This is the single most important thing you get.
High availability — Replication across availability zones means one server failure doesn’t take your app down.
Connection pooling — Managed databases often include built-in pooling (or easy integration with tools like PgBouncer), which prevents you from exhausting connection limits as you scale.
Monitoring and alerts — Disk usage, query performance, connection counts — you get observability without setting it up yourself.
Compliance and security — SOC 2, encryption at rest, audit logs. Important if you’re handling user data.

The tradeoff is cost and vendor lock-in. Managed databases cost more than a bare VPS. And some providers use proprietary features that make switching later harder. Both are manageable if you know about them going in.

Choosing a Managed Database Provider

There’s no single best provider. The right one depends on your database type, your hosting environment, your scale, and how much configuration overhead you want.

PostgreSQL-First Options

Supabase is the most popular choice for indie developers and small teams right now. It wraps PostgreSQL with a real-time API, built-in auth, edge functions, and a clean dashboard. If you want Postgres without wiring up everything manually, it’s a solid starting point. What Is Supabase? has a full breakdown.

Neon offers serverless PostgreSQL — databases that scale to zero when idle and spin up on demand. Great for apps with variable traffic or lots of preview/staging environments.

Railway and Render both offer managed Postgres as part of a broader PaaS platform. If you’re already deploying your app to one of them, adding a database is simple.

Amazon RDS / Google Cloud SQL / Azure Database for PostgreSQL are the major cloud provider offerings. More configuration, more power, more cost. Worth it if you’re already deep in one cloud ecosystem or need enterprise compliance features.

MySQL Options

PlanetScale built its name on Vitess (the MySQL infrastructure that powers YouTube), offering horizontal scaling and a developer-friendly branching workflow for schema changes. For high-scale MySQL apps, it’s worth a look. Supabase vs PlanetScale compares the two directly.

Document / NoSQL Options

MongoDB Atlas is the standard managed offering for MongoDB. Solid tooling, global clusters, and good performance at scale.

Firebase Firestore is Google’s managed NoSQL option, tightly integrated with the rest of Firebase. It works well for mobile apps and rapid prototypes, especially if you’re already using Firebase Auth. The tradeoff is that it’s very much Firebase’s model — you’re buying into their ecosystem.

If you’re comparing Firebase to a more open alternative, Supabase vs Firebase is a useful read.

What to Actually Look For

When evaluating providers, these are the things worth comparing:

Backup frequency and retention — Daily snapshots are the minimum. Look for point-in-time recovery if your data is critical.
Connection limits — Free and entry-level plans often cap concurrent connections. Check this against your expected traffic.
Region availability — Your database should be in the same region as your backend. Latency adds up.
Pricing model — Some providers charge by storage, some by compute, some by queries. Run the numbers for your expected usage before committing.
Migration tooling — Does the provider support schema migrations cleanly? Some have built-in support; others assume you’ll bring your own tooling.
Egress fees — Data leaving the database costs money on most cloud providers. Check the rate before you assume that read-heavy workloads will be cheap.

For indie hackers and early-stage founders specifically, Best Backend Platforms for Indie Hackers covers this from a cost-and-simplicity angle.

Step-by-Step: Setting Up a Managed PostgreSQL Database

This walkthrough uses Supabase as the example, but the steps are similar across most managed PostgreSQL providers.

Step 1: Create Your Project

Database name — Use something that matches your app. Keep it lowercase with hyphens or underscores.
Region — Pick the region closest to your backend server or your primary user base.
PostgreSQL version — Use the latest stable release unless you have a specific reason not to.
Password — Generate a strong one and store it in a password manager immediately. You won’t get it back.

Most providers will give you a connection string after creation. It looks something like this:

postgresql://username:password@host:5432/dbname

Store this in your environment variables (.env file locally, secrets manager in production). Never commit it to git.

Step 2: Connect Your Backend

Install your database client library. For Node.js with PostgreSQL, common options are:

pg — the standard PostgreSQL driver
Prisma — ORM with migration tooling, schema-first development
Drizzle ORM — lightweight, TypeScript-native, closer to raw SQL
Knex.js — query builder, good middle ground between raw SQL and a full ORM

A basic connection in Node.js with pg:

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false } // required for most managed providers
});

Most managed databases require SSL connections. If your queries are failing on connect, this is often why.

Step 3: Define Your Schema

Write your initial schema as SQL or through your ORM’s schema definition format. A simple users table:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Keep a few principles in mind when designing your schema:

Use UUIDs for primary keys if you plan to merge data from multiple sources or expose IDs in URLs
Always include created_at and (often) updated_at timestamps
Add indexes on columns you’ll filter or sort by frequently (email, user_id foreign keys, status fields)
Define foreign key constraints — they enforce referential integrity at the database level

Step 4: Run Your Initial Migration

Apply the schema to your database. If you’re using Prisma:

npx prisma migrate dev --name init

If you’re writing raw SQL, you can run it directly via your provider’s SQL editor (Supabase has one built-in) or using psql:

psql $DATABASE_URL -f schema.sql

This creates the schema in your actual database. Confirm it by checking your provider’s table viewer or running \dt in psql.

Step 5: Set Up Environment Variables for Each Environment

You should have separate databases for development, staging, and production. Never point your local development environment at your production database.

Configure three separate connection strings:

DATABASE_URL — local development (can be a local Postgres instance or a dev branch)
STAGING_DATABASE_URL — staging environment
PRODUCTION_DATABASE_URL — production only

Most CI/CD platforms let you set environment-specific secrets. Use them.

Database Migrations: How to Change Your Schema Without Breaking Things

Once your app is running with real users, your schema will need to change. New features require new tables. Business logic shifts require new columns. Migrations are how you apply those changes safely.

The Migration Mindset

A migration is a versioned script that describes a change to your schema. Every schema change — adding a column, dropping an index, renaming a table — should go through a migration. Never make ad-hoc changes directly to a production database.

Good migrations are:

Reversible — Write an “up” migration (apply the change) and a “down” migration (revert it)
Non-destructive by default — Add columns before dropping old ones; populate data before adding NOT NULL constraints
Small and focused — One migration per logical change. Don’t bundle unrelated schema changes together.

Running Migrations Safely in Production

The common failure mode is running a migration that locks a table while production traffic is still hitting it. A full table lock while a migration runs can take your app down.

Some rules that prevent this:

Add columns as nullable first. ALTER TABLE users ADD COLUMN phone TEXT; is safe. Adding a NOT NULL column without a default locks the table.
Backfill data in batches. If you need to populate a new column for existing rows, do it in chunks (1,000 rows at a time, with a delay between batches) rather than a single UPDATE on the whole table.
Create indexes concurrently. In PostgreSQL: CREATE INDEX CONCURRENTLY idx_users_email ON users(email);. This builds the index without locking writes.
Test migrations on a production-sized dataset. A migration that runs in 100ms on your dev database with 50 rows might take 20 minutes on production with 10 million rows.

Most ORMs with migration support (Prisma, Drizzle, Flyway) handle basic safety automatically. But understanding why these rules exist helps you catch problems the tooling misses.

Migration Tooling Options

Prisma Migrate — Schema-first, generates migration files automatically from schema changes, integrates tightly with the TypeScript ecosystem
Flyway — Language-agnostic, works with raw SQL migration files, widely used in enterprise environments
Liquibase — Similar to Flyway, supports XML/YAML/JSON in addition to SQL
Drizzle Kit — Lightweight migration tooling for Drizzle ORM users
golang-migrate — If you’re working in Go

The best migration tool is the one that matches your stack and that your team will actually use consistently.

Security Basics You Shouldn’t Skip

Databases are frequent targets. The most common breaches aren’t sophisticated attacks — they’re credentials in a public repo, databases exposed to the public internet, or overly permissive access controls.

Access Control

Create separate database roles for different parts of your application:

An application role with SELECT, INSERT, UPDATE, DELETE on the tables it needs — nothing more
An admin role for running migrations — used only during deployment, not in your live app
A read-only role for analytics queries, BI tools, or reporting

Never connect your application to the database as a superuser or the root admin account.

Network Security

Your database should not be publicly accessible. Set your firewall or security group rules to allow connections only from:

Your application server’s IP address (or VPC)
Your CI/CD pipeline’s IP range (for running migrations)
Your own IP when you need direct access for debugging

Most managed providers offer VPC peering or private networking. Use it for production.

Credentials Management

Never hardcode connection strings in your source code
Rotate database passwords regularly, especially when team members leave
Use a secrets manager (AWS Secrets Manager, HashiCorp Vault, 1Password Secrets Automation) for production credentials
Enable audit logging so you have a record of who queried what and when

If you’re building an app with user accounts, how to add authentication to your web app covers the auth side of the security picture — which connects closely to how your users table and session management are structured.

Backups and Disaster Recovery

Even with a managed database, verify your backups actually work. Restore a backup to a test database at least once before you need it for real. This case study of a 1.9 million row database wipe is a good reminder of what happens when you assume backups are working without checking.

Document your recovery procedure. If your database goes down at 2am, you want a written runbook, not a memory exercise.

How Remy Handles Database Setup

If you’re building a full-stack web app and want the database layer handled as part of the overall application — not as a separate infrastructure problem — this is where Remy fits.

Remy compiles a spec (an annotated markdown document describing your application) into a full-stack app. That includes the backend, the frontend, auth, deployment, and the database. You don’t configure a managed database separately and then wire it to your backend — the database schema is derived from your spec and created as part of the compiled output.

Under the hood, Remy uses SQLite with WAL/journal mode enabled, which handles concurrent reads correctly and keeps things fast. Schema migrations run automatically on each deploy. If your spec changes — say, you add a new field to your users entity — the schema updates when you push.

This matters for the migration problem specifically. When your data model lives in a spec that’s the source of truth for the entire app, schema changes aren’t a separate task you coordinate between your backend code, your database, and your migration scripts. They’re derived from the same document. Change the spec, redeploy, and the schema follows.

It’s a different model from managing a standalone managed database. But if you’re building something new and don’t want database setup to be its own project, it’s worth seeing what this looks like in practice. You can try Remy at mindstudio.ai/remy.

For comparison, if you’re evaluating full-stack building approaches more broadly, Full-Stack App Builders Compared covers how different tools handle the backend and data layer. Most don’t — Remy is one of the few that does it properly.

Common Mistakes and How to Avoid Them

A few patterns that show up repeatedly when developers set up databases for the first time:

Not separating development and production databases. Running tests or local development against your production database is a disaster waiting to happen. Always use separate instances.

Skipping connection pooling. PostgreSQL has a connection limit. If each server process opens its own connection and you’re running under high load, you’ll hit the limit and start getting connection errors. PgBouncer or the built-in pooling from your provider handles this.

Ignoring query performance until it’s a problem. Add EXPLAIN ANALYZE to slow queries. Add indexes on foreign keys and columns used in WHERE clauses. A missing index on a table with 1 million rows turns a fast query into a slow one overnight.

Not testing backups. This one shows up on the security list too, but it’s worth repeating here. The moment you need a backup is the wrong time to find out it wasn’t working.

Storing secrets in code. Environment variables, secrets managers, .env files that are .gitignored. Never in source control.

Using one huge schema file for everything. As your app grows, modularize. Organize tables by domain (users, billing, content) rather than putting everything in one flat namespace.

Frequently Asked Questions

What’s the difference between a managed database and a hosted database?

These terms are often used interchangeably, but there’s a meaningful distinction. A hosted database just means it runs on someone else’s servers. A managed database means the provider actively handles operational tasks — backups, patching, failover, monitoring. Most modern database-as-a-service offerings are managed. Self-managed cloud databases (running Postgres on an EC2 instance you control) are hosted but not managed.

Do I need a separate database for development, staging, and production?

Yes. These environments should be isolated from each other. Running tests against production data is a data integrity risk. Running production traffic against a development database is a performance and availability risk. Most managed providers make it cheap or free to create additional instances for non-production environments.

How do I choose between PostgreSQL, MySQL, and SQLite for a web app?

For most production web apps, PostgreSQL is the default. It’s more feature-rich than MySQL, handles complex queries better, and has a stronger ecosystem. MySQL is a reasonable choice if you’re using PlanetScale or have existing MySQL infrastructure. SQLite is excellent for embedded use cases, edge deployments, or apps with low concurrency requirements — it doesn’t support multiple concurrent writes the way PostgreSQL does.

When should I start worrying about database scaling?

Most apps don’t need to worry about database scaling until they have meaningful traffic. PostgreSQL can handle thousands of queries per second on a single instance with proper indexing and connection pooling. The things that actually hurt performance first are missing indexes, N+1 query patterns (fetching data in a loop rather than a join), and holding long-running transactions open. Fix those before thinking about sharding or read replicas.

What are the most important indexes to add?

Always index foreign key columns (e.g., user_id on an orders table). Index columns you frequently filter or sort on in WHERE, ORDER BY, and GROUP BY clauses. Index columns used in JOIN conditions. Don’t over-index — each index slows down writes and takes up storage. Use EXPLAIN ANALYZE to identify what’s actually slow before adding indexes speculatively.

How do I handle database migrations in a CI/CD pipeline?

Run migrations as a separate step before your application deployment. Make sure your migration runner uses a distributed lock (or a migration table, which most tools create automatically) so multiple deploy replicas don’t run the same migration simultaneously. Run migrations with a role that has schema modification privileges, separate from the application role that your live app uses. Always test migrations on a staging environment first.

Key Takeaways

For most web apps, SQL (specifically PostgreSQL) is the right default database type. NoSQL has its place, but start with SQL unless you have a specific reason not to.
A managed database saves you from operational overhead — backups, patching, failover, monitoring — at a reasonable cost increase over self-hosting.
Choose your provider based on region availability, connection limits, backup policy, pricing model, and how well it fits your existing stack.
Treat every schema change as a migration: versioned, tested, and applied without locking production.
Separate your development, staging, and production databases. Restrict access. Never commit credentials to source control. Verify your backups.
If you want the database, backend, and schema to all derive from the same source of truth, Remy handles this as part of its full-stack compilation model.