Which database should you use?

For most new apps, use Postgres. It is a SQL database, so your data lives in tables with clear links between them. It grows from a side project to a large company, and hosts, tools and coding agents all support it well. Choose SQLite for prototypes and small apps, and a document database such as MongoDB or Firestore when your data really is loose documents.

If you are still deciding which database to use, know that the brand matters less than two habits: lock down who can read which rows, and keep backups you have actually restored. This guide covers the choice, today’s free tiers and both habits.

01SQL or NoSQL, in plain words

A document database, often called NoSQL, stores each record as a flexible document, much like a JSON file. There is no fixed shape, so two records can have different fields. MongoDB and Firebase’s Firestore work this way.

Most business apps are full of relationships. Users have projects, projects have invoices, invoices have payments. That is what SQL does best, and Postgres can still store JSON for the parts of your data that really are loose.

02Which database should you use?

• Postgres for a web app or SaaS with users, accounts and payments. This is the default.

• SQLite for a prototype, an internal tool, a desktop or mobile app, or a site on one server. The whole database is one file, with no server to run.

• MySQL when your framework or host expects it, as WordPress and many PHP apps do. It is as proven as Postgres.

• MongoDB or Firestore when your data really is documents, or for a mobile app built on Firebase, where Firestore’s live updates and offline support shine.

03Hosted databases and their free tiers

You rarely run a database server yourself anymore. These services do it for you. Limits as of September 2026, from each provider’s pricing page:

Two limits bite first. A paused Supabase project takes your app down until you restore it. A Neon database that scaled to zero can make the first request after a quiet spell slower.

Neon has one feature worth knowing if you build with an agent: branches. A branch is a copy of your database for testing a change, and the free plan includes 10 per project. Where your database lives also shapes where your app should run, which our guide on where to deploy your app covers.

04Row-level security: the lock behind your login

Some setups let the browser talk to the database directly with a public key. Supabase works this way, and so does Firebase. Anyone can copy that key from your site, so the database itself must decide who sees which rows.

Supabase’s rule is simple: enable row-level security on every table its API can reach. Once it is on, the public key reads nothing until you add policies.

-- 1. Lock the table: with no policy, nobody can read it through the API
alter table notes enable row level security;

-- 2. Let each signed-in user read only their own rows
create policy "Owners can read their notes"
  on notes for select
  to authenticated
  using ((select auth.uid()) = owner_id);

This ran on PostgreSQL 16 with a stand-in for Supabase’s auth.uid() function, against a table with one note from Ada and one from Bob. Signed in as Ada:

The policy covers reading only. When Ada tried to add a note owned by Bob, Postgres refused: new row violates row-level security policy for table "notes". Add a policy for each action you allow, such as insert, update and delete, each checking owner_id the same way.

Two more rules. Supabase’s secret key bypasses these policies entirely, so it must never reach the browser. And Firebase has its own version of this lock, called Security Rules. Row-level security works alongside your login, not instead of it; see how to add login the safe way.

05How to work with a coding agent on your database

Agents write queries and schema changes quickly. The risk is not the SQL itself but where it runs.

This is not theoretical. In July 2025, Replit’s agent deleted the production database of SaaStr founder Jason Lemkin during a code freeze, despite explicit instructions not to change code without permission. The data was recovered with a rollback that the agent had wrongly said was impossible. The lesson applies to every agent: separate what it can touch from what your customers depend on.

• Give it the schema, not the keys. Let it read your migration files so it uses real table names instead of guessing.

• Change the schema through migration files in your repository, reviewed like code. Read twice anything that drops or renames a table or column.

• Give the agent a development database. Production changes go through reviewed migrations that you, or your deploy pipeline, apply.

• Test risky changes on a copy, such as a Neon branch or a second Supabase project.

I want to [describe the change]. Before touching anything:
1. Show me the current schema for the tables involved.
2. Write a migration for the change, plus one that undoes it.
3. List anything that could lose or corrupt existing data, and how the migration avoids it.
4. Explain how you will test it on the development database.
Do not run anything against production. I will review and apply it myself.

For everyday queries, our guide to writing SQL with AI shows how to get correct results.

06Backups you have actually restored

A backup you have never restored is a hope, not a plan. Check what your host keeps. As of September 2026, Supabase Pro keeps daily backups for 7 days, with point-in-time recovery as a $100 a month add-on. Neon’s free plan lets you restore to any moment in the last 6 hours, up to 1 GB of history.

Keep your own copy too, so a billing problem or a deleted project cannot take your data with it. For Postgres:

# 1. Take a portable copy of the whole database
pg_dump "$DATABASE_URL" --format=custom --file=backup.dump

# 2. Prove it works: restore it into an empty test database
pg_restore --no-owner --dbname="$TEST_DATABASE_URL" backup.dump

# 3. Check the data arrived
psql "$TEST_DATABASE_URL" -c "select count(*) from notes;"

On the two-note test database from above, the restored copy answers:

 count
-------
     2
(1 row)

The restored copy kept the row-level security policy too. This ran with PostgreSQL 16.13. Use a pg_dump at least as new as your database’s major version, because it refuses to dump from a newer server. For SQLite, one command writes a copy to a new file: sqlite3 app.db ".backup backup.db".

07FAQ

Next, see the whole path from idea to first paying user.