Database
113 bites tagged Database — interview questions with model answers, and 60-second explainers.
Testing a DB endpoint via dependency override
Use app.dependency_overrides to swap the real get_db for one yielding a test database session, run against a disposable SQLite or test Postgres, and assert through TestClient. Isolating tests from production data.
Defining many-to-many relationships in Sequelize
Use belongsToMany through a join table, the through table holds the foreign keys, eager-load courses with include. modeling many-to-many with a join table.
Purpose of an ORM like Sequelize
Maps rows to objects, gives a model-based API, handles associations, migrations, and parameterized queries across dialects. understanding ORM value and tradeoffs.
Preventing SQL injection with parameterized queries
The flaw is SQL injection; prevent it with parameterized queries/prepared statements (pg $1, mysql2 ?), never string concatenation, so input is data not code. Knowing SQL injection and parameterization.
Managing clean test state across API integration tests
Compare seed-and-truncate, per-test transaction rollback, and in-memory or containerized databases, weighing fidelity, speed, and isolation. How you keep integration tests isolated and fast.
Atomic order creation with Sequelize transactions
Wrap dependent writes in sequelize.transaction, pass the transaction to each query, let managed transactions auto-commit or roll back. atomicity and transaction handling.
Database migrations with the Sequelize CLI
Migrations are version-controlled scripts with up/down so teams apply identical schema changes; use sequelize-cli to generate, edit with addColumn, then db:migrate. versioned, repeatable schema changes.
Zero-downtime index migration on a hot table?
Build the index concurrently to avoid table locks, run off-peak with monitoring, and keep it reversible since dropping an index is cheap. Safe schema change at scale. A blocking CREATE INDEX that locks writes on a hot table.
Diagnose database CPU saturation under load
Find the expensive queries via the database's stats, check for missing indexes and full scans, then fix with indexing, query rewrites, caching, or read replicas. DB performance diagnosis.
How does a StatefulSet give stable identity and storage?
Ordinal Pod names plus a headless Service yield stable per-Pod DNS; volumeClaimTemplates give each ordinal its own persistent PVC that follows it on reschedule. the mechanics behind StatefulSet guarantees.
How Compose services reach each other by name
Services share a default network and the web app uses the database's service name as the hostname; Docker's embedded DNS resolves it to the container IP. Compose service discovery.
Diagnose 100% CPU on a managed database
Correlate the spike with deploys and traffic, find top queries via the engine's views, inspect plans for missing indexes, then tune before scaling. structured DB triage. scaling up without finding the offending query.
Serverless functions with a relational database
Concurrent function instances each open connections and exhaust the database's bounded pool; fix with a connection proxy or pooler, init-phase reuse, or capped concurrency. the connection-storm problem. a connection per call.
How do you diagnose and optimize a slow Room query?
Tests SQLite profiling and Room optimization. Isolate the query with Database Inspector, run EXPLAIN QUERY PLAN to spot scans, then add covering indexes, rewrite joins, or use Paging3. Red flag: blindly adding indexes or switching to NoSQL without measuring.
How do you manage database connections in serverless Next.js API routes?
This tests serverless concurrency and connection limits. A strong answer notes lambda scaling exhausts DB connections, advocates connection pooling or serverless drivers, and caches the client globally.
Use startup events to initialize a database pool and inject it
This tests FastAPI lifespan hooks and dependency injection for shared state. A strong answer creates the pool in an async startup handler, stores it on app.state, and accesses it via a dependency in routes. A red flag is creating a fresh pool per request.
How do you atomically create an order and update inventory?
Tests transaction boundaries and SQLAlchemy 2.0 session lifecycle in FastAPI. A strong answer wraps both writes in session.begin(), flushes to catch constraint errors early, and uses exceptions to trigger rollback.
Implement an async database session dependency using yield for setup and teardown
This tests async resource lifecycle management in FastAPI. A strong answer uses async def, yields a session inside try, closes in finally, and injects with Depends. A red flag is omitting finally or using sync def for async I/O, which leaks connections.
MongoDB Aggregation Pipeline: Server-Side Assembly Line
MongoDB's aggregation pipeline reshapes documents stage by stage on the server. Use it for reports, joins, or analytics without pulling whole collections into your app. Running $sort or $group before $match scans excess documents and kills performance.
Node.js Built-in SQLite Driver
Node.js bundles a SQLite driver in node:sqlite. Open a file with new DatabaseSync(path), then run SQL with exec() or prepared statements. Use it for local tools and caches. DatabaseSync is synchronous, so running it on a web server main thread blocks requests.
Compare sqflite and Drift: trade-offs and when to choose Drift
This tests Flutter persistence trade-offs. Contrast sqflite's raw maps and raw SQL with Drift's typed code, streams, and migrations; choose Drift for complex schemas or web targets. Red flag: calling Drift bloat or claiming raw sqflite is always faster.
Describe a robust strategy for GitOps database schema migrations
Tests imperative-to-declarative schema reconciliation. Strong answers version idempotent pre-sync jobs and colocate schema state in Git. They use dedicated operators, not infra tools, for live execution.
Explain integration tests and isolate Room DAO tests
Tests integration vs unit testing and Room isolation. Outline: verify real DAO-to-DB interaction with Room.inMemoryDatabaseBuilder; reset state by closing and recreating the DB in @After. Red flag: mocking the DAO or using an on-disk database.
Model a Room one-to-many Playlist-to-Song relationship
Tests Room relational modeling. Strong answer: Song foreign key, `@Embedded` Playlist with `@Relation` to `List<Song>`, DAO wrapped in `@Transaction`. Red flag: embedding songs in Playlist or skipping `@Transaction`, which causes N+1 queries.
Get Database bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.