Databases

// SQL (via a query builder)
const users = await db('users').where({ active: true }).select('id', 'name');

// NoSQL (MongoDB)
const users = await db.collection('users').find({ active: true }).toArray();

Common follow-ups: In what scenario would a team choose to use both a relational and a NoSQL database within the same application (polyglot persistence)?;How does the lack of a rigid schema in NoSQL shift schema-validation responsibility onto the application code?

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 });

const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Common follow-ups: How do you determine an appropriate maximum pool size given both the database's connection limit and the number of running application instances?;What happens to a request if it needs a connection but the pool is already fully checked out?

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = $1', [fromId]);
  await client.query('UPDATE accounts SET balance = balance + 100 WHERE id = $1', [toId]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release();
}

Common follow-ups: What are the ACID properties, and which one specifically does a transaction most directly guarantee?;How do transactions behave differently in a NoSQL database like MongoDB compared to a traditional relational database?

// Vulnerable: user input concatenated directly into the query
const result = await pool.query(`SELECT * FROM users WHERE email = '${userInput}'`);

// Safe: parameterized query, value passed separately
const result = await pool.query('SELECT * FROM users WHERE email = $1', [userInput]);

Common follow-ups: Why doesn't simply escaping quote characters manually provide the same guarantee as a properly parameterized query?;Do NoSQL databases have an equivalent injection risk, and if so, what does it look like?

-- Without an index, this query scans the entire orders table
SELECT * FROM orders WHERE customer_id = 12345;

-- Creating an index dramatically speeds up that exact query pattern
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Common follow-ups: How do you identify which columns actually need an index versus adding indexes speculatively everywhere?;What's the tradeoff of having too many indexes on a frequently-written-to table?

-- Normalized: customer info stored once, referenced by ID
CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), total DECIMAL);

-- vs. denormalized: customer name duplicated on every order row for fast reads

Common follow-ups: How do you decide which specific data is worth denormalizing for performance versus keeping fully normalized?;What's the risk of denormalized data becoming inconsistent with its normalized source of truth?

const primaryPool = new Pool({ connectionString: PRIMARY_DB_URL });
const replicaPool = new Pool({ connectionString: REPLICA_DB_URL });

async function getUser(id) { return replicaPool.query('SELECT * FROM users WHERE id = $1', [id]); }
async function updateUser(id, data) { return primaryPool.query('UPDATE users SET ... WHERE id = $1', [id]); }

Common follow-ups: What problems can arise from replication lag if an application immediately reads data right after writing it?;How do you handle read-your-own-writes consistency when a user needs to see their own recent write immediately?

// Example migration file (using a tool like Knex or Sequelize)
exports.up = async (knex) => {
  await knex.schema.table('users', (table) => {
    table.string('phone_number').nullable();
  });
};
exports.down = async (knex) => {
  await knex.schema.table('users', (table) => table.dropColumn('phone_number'));
};

Common follow-ups: How do you handle a migration that needs to run against a production database with live traffic and zero downtime?;What's the risk of a migration that isn't properly reversible via a working 'down' script?

async function updateDocument(id, expectedVersion, newContent) {
  const result = await db.documents.updateOne(
    { _id: id, version: expectedVersion },
    { $set: { content: newContent }, $inc: { version: 1 } }
  );
  if (result.modifiedCount === 0) throw new Error('Conflict: document was modified by someone else');
}

Common follow-ups: How does optimistic concurrency control differ from pessimistic locking, and when is each more appropriate?;What should the user experience be when a conflict is detected -- how do you help them resolve it?

// N+1 problem: one query for users, then one additional query per user for their orders
const users = await User.findAll();
for (const user of users) {
  const orders = await user.getOrders(); // triggers a separate query, N times
}

// Fixed: a single query with eager loading
const users = await User.findAll({ include: [Order] });

Common follow-ups: How would you detect an N+1 query problem in an existing codebase before it becomes a production performance issue?;Does the N+1 problem apply to raw SQL queries as well, or is it specifically an ORM concern?