Appearance
Database Fundamentals
1. What is the difference between SQL and NoSQL databases?
The short answer
SQL databases, such as PostgreSQL, MySQL and SQL Server, store data in tables with a fixed schema, link tables with keys, are queried with SQL, and give strong guarantees through transactions. NoSQL databases is a loose name for everything else: document stores such as MongoDB, key-value stores such as Redis and DynamoDB, wide-column stores such as Cassandra, and graph databases such as Neo4j. They trade some of SQL's flexibility in querying, or its strict consistency, for flexible schemas, easy horizontal scaling, or speed for one access pattern. Choose by your data's shape and how you will read it.
| SQL, relational | NoSQL | |
|---|---|---|
| Data model | Tables, rows, columns | Documents, key-values, columns, graphs |
| Schema | Fixed, enforced by the database | Flexible, often enforced by the app |
| Relationships | Joins with foreign keys | Embedded data or app-side joins |
| Transactions | Multi-row and multi-table, ACID | Varies: often per document or per key |
| Scaling | Mostly vertical, plus read replicas; sharding is harder | Often built for horizontal scaling |
| Query flexibility | Any question, with SQL | Fast for the access patterns you designed for |
The four NoSQL families:
| Family | Example | Good for |
|---|---|---|
| Document | MongoDB, Firestore | Records read as a whole, such as a product with its variants |
| Key-value | Redis, DynamoDB | Lookup by key at huge scale: sessions, carts, counters |
| Wide-column | Cassandra, ScyllaDB | Huge write volumes, time series, event logs |
| Graph | Neo4j | Many-hop relationships: social graphs, fraud rings |
The answer that impresses. "I default to PostgreSQL, because most business data is relational and needs transactions, and Postgres also handles JSON documents well. I reach for a NoSQL store when one access pattern dominates and must scale beyond one machine, such as a session store in Redis or an event log in Cassandra." Many large systems use both, each for what it does best.
2. What is an ORM, and what is an ODM?
The short answer
An ORM, object-relational mapper, lets you work with relational database rows as objects in your programming language: you call orders.findMany() instead of writing SELECT by hand, and it maps tables to classes and rows to objects. Examples are Prisma, Drizzle, TypeORM and Sequelize in Node, SQLAlchemy in Python. An ODM, object-document mapper, does the same for document databases, such as Mongoose for MongoDB. They speed up everyday work and add type safety, but can hide slow queries, such as the N+1 problem, behind innocent-looking code.
ts
// Prisma, an ORM: typed queries without writing SQL
const recent = await prisma.order.findMany({
where: { customerId: 42, status: 'paid' },
include: { items: true }, // (1)
orderBy: { createdAt: 'desc' },
take: 10,
})
// Mongoose, an ODM: schema and model for a MongoDB collection
const Product = model('Product', new Schema({ name: String, priceCents: Number, tags: [String] })) // (2)
const shoes = await Product.find({ tags: 'running' }).limit(20)includeloads the related items in the same call. The ORM decides how, usually one extra query for all of them.- The ODM adds a schema to a schemaless database, validating documents in your app before they are saved.
| Good | Watch out |
|---|---|
| Less boilerplate, typed results | Hidden N+1 queries (the N+1 question) |
| Migrations and schema in code | Complex reports are awkward; drop to raw SQL |
| Safer: parameterised by default | Loading whole objects when you need two columns |
Query builders such as Knex or Kysely sit in between: you write SQL-shaped code with types, and you see exactly what runs.
3. What is database connection pooling, and why is it needed?
The short answer
Opening a database connection is slow, often several milliseconds for TCP, TLS and authentication, and each PostgreSQL connection uses a whole server process with several megabytes of memory. A pool opens a small number of connections once and lends them to requests: a request borrows a connection, runs its queries, and returns it. This makes queries faster, and protects the database from thousands of connections when traffic spikes. Size it small, typically 10 to 20 per app instance, and remember the total across all instances must stay under the database's limit.
ts
import { Pool } from 'pg'
export const pool = new Pool({
connectionString: config.DATABASE_URL,
max: 10, // (1)
idleTimeoutMillis: 30_000, // (2)
connectionTimeoutMillis: 2_000, // (3)
})
const { rows } = await pool.query('SELECT id, total FROM orders WHERE customer_id = $1', [customerId]) // (4)- At most 10 connections from this instance. With 8 instances, that is 80 against the database's limit, often 100 by default.
- Close connections idle for 30 seconds, so a quiet instance gives them back.
- Wait at most 2 seconds for a free connection, then fail fast with an error instead of hanging.
pool.queryborrows, runs and returns in one call.
Why bigger is not better. A database with 8 CPU cores can only run about 8 queries truly at once. A pool of 200 just makes queries fight each other for CPU and memory. When you have many instances or serverless functions, put an external pooler such as PgBouncer in front of PostgreSQL, so thousands of app connections share a few dozen real ones.
4. What is a database transaction?
The short answer
A transaction is a group of database operations that succeed or fail as one unit. You start it with BEGIN, run several statements, and finish with COMMIT to make all changes permanent, or ROLLBACK to undo all of them. The classic example is a transfer: subtract £100 from Asha and add £100 to Ben. If the server crashes in between, the transaction ensures neither change happens, so money is never lost or created. While a transaction runs, other sessions see its changes only according to the isolation level.
ts
export async function placeOrder(customerId: number, items: CartItem[]) {
const client = await pool.connect() // (1)
try {
await client.query('BEGIN')
const { rows: [order] } = await client.query(
'INSERT INTO orders (customer_id, status) VALUES ($1, $2) RETURNING id', [customerId, 'pending'])
for (const item of items) {
const res = await client.query(
'UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1', [item.qty, item.productId]) // (2)
if (res.rowCount === 0) throw new HttpError(409, `Out of stock: ${item.productId}`)
await client.query('INSERT INTO order_items (order_id, product_id, qty) VALUES ($1, $2, $3)', [order.id, item.productId, item.qty])
}
await client.query('COMMIT') // (3)
return order.id
} catch (err) {
await client.query('ROLLBACK') // (4)
throw err
} finally {
client.release() // (5)
}
}- One connection for the whole transaction.
BEGINandCOMMITmust run on the same connection, so you cannot usepool.queryfor each statement. - Check and change stock in one statement, so two buyers cannot both take the last item.
- Make everything permanent at once.
- Any error undoes every statement, including the order already inserted.
- Always return the connection, or the pool slowly runs dry.
5. What are the ACID properties?
The short answer
ACID is the set of guarantees a transaction gives. Atomicity: all of it happens or none of it does. Consistency: it moves the database from one valid state to another, never breaking rules such as foreign keys, unique constraints or checks. Isolation: transactions running at the same time do not see each other's half-finished work, to the degree the isolation level promises. Durability: once COMMIT returns, the change survives a crash, because it was written to the write-ahead log on disk first.
| Property | Means | How the database provides it |
|---|---|---|
| Atomicity | All or nothing | Undo information; rollback on error or crash |
| Consistency | Rules always hold | Constraints, foreign keys, checks, triggers |
| Isolation | Concurrent work does not interfere | Locks and MVCC snapshots (question 8) |
| Durability | Committed means saved | Write-ahead log flushed to disk before COMMIT returns |
Write-ahead logging in one sentence. Before changing the data files, the database appends a record of the change to a log and flushes the log to disk. After a crash, it replays the log, so every committed change is recovered.
"Consistency" here is not the C in CAP. ACID consistency means your data rules hold. CAP consistency means every reader sees the latest write (question 7). Interviewers like this distinction.
6. What is the difference between ACID and BASE?
The short answer
ACID describes strict transactional guarantees, typical of relational databases: every committed change is immediately visible and correct. BASE, Basically Available, Soft state, Eventually consistent, describes systems that stay available and scale across many machines by relaxing that: replicas may briefly disagree, and data converges to a consistent state shortly after writes stop. Banking balances want ACID. A social feed's like count can be BASE. Many modern databases let you choose per operation.
| ACID | BASE | |
|---|---|---|
| Priority | Correctness now | Availability and scale |
| After a write | Every reader sees it | Some readers may see old data briefly |
| Typical | PostgreSQL, MySQL, SQL Server | Cassandra, DynamoDB with eventual reads, DNS |
| Good for | Payments, inventory, bookings | Feeds, counters, analytics, caches |
Eventual consistency, concretely. You change your profile photo. For a second or two, a friend on another continent still sees the old one, because their nearest replica has not received the update yet. Nothing is wrong; it will catch up. That is acceptable for a photo, and not for a bank balance.
7. What is the CAP theorem, and what trade-offs does it describe?
The short answer
The CAP theorem says that when the network between parts of a distributed database fails, a partition, the system must choose between consistency, where every read returns the latest write or an error, and availability, where every request gets a non-error response, possibly with stale data. You cannot have both during a partition. Partitions will happen, so the real choice is "CP or AP". The PACELC extension adds that even without partitions, you trade latency against consistency.
| Choice | During a partition | Example systems | Good for |
|---|---|---|---|
| CP | Some requests fail | PostgreSQL with synchronous replicas, etcd, ZooKeeper, Spanner | Payments, inventory, leader election |
| AP | Answers may be stale; conflicts merged later | Cassandra, DynamoDB with eventual reads, DNS | Carts, feeds, presence, caches |
Common misreading. CAP does not say "pick any two of three" at all times. Without a partition, a system can be both consistent and available. And many databases are tunable: Cassandra can do quorum reads and writes for stronger consistency at the cost of availability.
PACELC in one line. If there is a Partition, choose A or C; Else, choose Latency or Consistency. A synchronous replica in another region makes every write slower, even on a perfect network.
8. What are database isolation levels?
The short answer
Isolation levels decide how much concurrent transactions can see of each other's work. The SQL standard defines four, from weakest to strongest. Read Uncommitted may see other transactions' uncommitted changes. Read Committed sees only committed data, but each statement may see newer data than the last. Repeatable Read sees one consistent snapshot for the whole transaction. Serializable guarantees the result is as if transactions ran one at a time. Stronger levels prevent more anomalies but cause more waiting or retries. PostgreSQL defaults to Read Committed; MySQL's InnoDB defaults to Repeatable Read.
| Level | Dirty read | Non-repeatable read | Phantom read | Notes |
|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | PostgreSQL treats it as Read Committed |
| Read Committed | No | Possible | Possible | PostgreSQL default |
| Repeatable Read | No | No | Possible by standard | PostgreSQL also prevents phantoms; MySQL default |
| Serializable | No | No | No | Transactions may fail and must be retried |
How PostgreSQL does it: MVCC. Multi-version concurrency control keeps several versions of a row. A writer creates a new version instead of overwriting, so readers keep reading the old version without waiting. Each transaction, or each statement in Read Committed, gets a snapshot, a view of which versions were committed when it started. Readers never block writers, and writers never block readers. The cost is old versions that must be cleaned up later by VACUUM.
ts
await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE')
// ... reads and writes ...
await client.query('COMMIT') // may throw: could not serialize access, error code 40001At Serializable, PostgreSQL aborts one of two conflicting transactions with error code 40001. Your code must catch it and run the whole transaction again (handling transaction failures).
9. What are dirty reads, non-repeatable reads, and phantom reads?
The short answer
They are the anomalies isolation levels prevent. A dirty read sees another transaction's change before it commits, which may then be rolled back, so you acted on data that never existed. A non-repeatable read reads the same row twice in one transaction and gets different values, because someone committed a change in between. A phantom read runs the same query twice and gets a different set of rows, because someone inserted or deleted matching rows in between. Two more to know are the lost update and write skew.
Two anomalies interviewers add:
| Anomaly | What happens | Fix |
|---|---|---|
| Lost update | Two transactions read stock 5, each writes 4; one sale is lost | Update in one statement, SET stock = stock - 1, or lock the row, or optimistic version check |
| Write skew | Two doctors each check "another doctor is on call", and both go off call | Serializable, or lock the rows being checked with SELECT … FOR UPDATE |
The lost update is the one you will meet in real code:
ts
// wrong: read, then write, with a gap in between
const { rows: [p] } = await client.query('SELECT stock FROM products WHERE id = $1', [id])
await client.query('UPDATE products SET stock = $1 WHERE id = $2', [p.stock - 1, id])
// right: let the database do the arithmetic atomically
await client.query('UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock > 0', [id])