Choosing a database, and keeping it safe

A decision guide

Most apps start with one relational database, such as PostgreSQL or MySQL, and that is the right default: it handles transactions, joins, reports and a lot of traffic. Add another kind of database only when a need clearly calls for it.

Using several databases, each for what it does best, is called polyglot persistence. It is powerful but every extra database is something to run, monitor, back up and keep in sync. A common pattern is: the relational database is the source of truth, and the others (cache, search index) are copies fed from it.

Least privilege, GRANT and REVOKE

Least privilege means each user or app gets only the rights it needs. The reporting tool reads; it never deletes. The web app writes orders; it never drops tables. If one password leaks, the damage stays small. In SQL you give rights with GRANT and take them away with REVOKE (DCL, from module 1).

-- PostgreSQL (MySQL and SQL Server are similar)
CREATE ROLE report_reader LOGIN;          -- password set separately by the admin
GRANT CONNECT ON DATABASE shop TO report_reader;
GRANT USAGE ON SCHEMA public TO report_reader;
GRANT SELECT ON orders, order_items, products TO report_reader;

REVOKE INSERT, UPDATE, DELETE ON products FROM web_app;

SQL injection

SQL injection happens when an app builds a query by gluing user input into the SQL text. The input can then change the meaning of the query. Suppose a login form builds:

-- The app glues the typed name into the SQL text:
-- "SELECT id, username, role FROM users WHERE username = '" + name + "'"
-- An attacker types:   ' OR '1'='1
SELECT id, username, role FROM users WHERE username = '' OR '1'='1';
-- returns EVERY user, including admin

The fix is not to "clean" the input by hand. Use parameterised queries (also called prepared statements): the SQL text has placeholders, and the values are sent separately, so they can never become SQL.

import sqlite3
con = sqlite3.connect('shop.db')
name = "' OR '1'='1"                      # whatever the user typed

# WRONG: never build SQL with + or f-strings
# con.execute("SELECT id FROM users WHERE username = '" + name + "'")

# RIGHT: a ? placeholder, value passed separately
rows = con.execute("SELECT id, username FROM users WHERE username = ?", (name,)).fetchall()
print(rows)
# Output: []   (no user is literally called ' OR '1'='1)

// Node.js with the pg library (PostgreSQL uses $1, $2...)
const { rows } = await client.query(
  'SELECT id, username FROM users WHERE username = $1',
  [name]
);

Backups and restore drills

A backup you have never restored is a hope, not a backup. Take regular backups (pg_dump, mysqldump, SQLite's .backup command or VACUUM INTO, or the managed service's snapshots), keep a copy in another place, and practise restoring to a test server on a schedule. Two numbers guide the plan: RPO, how much data you can afford to lose (for example 5 minutes), and RTO, how long you can be down (for example 1 hour).

Managed databases

A managed database is one a cloud provider runs for you: it handles installs, patches, backups, replicas and failover, and you pay for it. Examples: Amazon RDS and Aurora, Google Cloud SQL, Azure SQL Database, and newer serverless PostgreSQL services such as Neon and Supabase (which adds auth and APIs on top). You still own the schema, the queries, the users and the security settings.

Common mistakes

• Adding MongoDB, Redis and Elasticsearch on day one. Start with one relational database and add others when a need is proven.
• Letting the app connect as the database owner or root. Create a separate user with only the rights the app needs.
• Gluing input into SQL with + or f-strings, even "just for an admin page". Use placeholders everywhere.
• Never testing restores. Schedule a restore drill and time it.
• Leaving a database open to the whole internet. Allow only your app servers' network.