Database
113 bites tagged Database — interview questions with model answers, and 60-second explainers.
Drift Schema Migrations: Evolving Your Database Safely
Think of schema migrations as a recipe for upgrading your app's database from an old version to a new one. You'll use them whenever you add a table or column in Drift. The footgun is forgetting to bump `schemaVersion`; your migration won't run.
Hive: A Fast, Local Key-Value Store for Dart
Hive is a lightweight key-value database for Dart, acting like a persistent, super-fast Map. Use it to store simple data like user settings or cache API responses locally.
sqflite: Local SQL Databases in Flutter
sqflite gives your Flutter app a private, on-device SQL database by wrapping the native SQLite engine. Use it for storing structured local data, like to-do lists or settings. The footgun: all database operations are async, so you must `await` every call.
Hash-Based Aggregation: Grouping Data Without Sorting
Hash-based aggregation uses a hash table to group data for functions like COUNT or SUM, avoiding a costly sort. It's used in database query engines for GROUP BY operations, especially when distinct groups fit in memory.
Buffer Manager: The Database's Memory Gatekeeper
The buffer manager acts as a database's private RAM cache, deciding which data pages to keep in memory versus fetching from slow disk. It's central to query performance, as it tries to serve all data requests from this fast cache.
Optimizer Hints: Backseat Driving Your Database
An optimizer hint lets you override the database's query plan, like telling a GPS which street to take. Use it as a last resort when you know more than the optimizer, but beware: hints can become performance traps when data or schemas change.
Continuous Queries: Automating Time-Series Aggregation
A continuous query automatically aggregates real-time data on a schedule. Use it to create downsampled rollups, like hourly averages from raw sensor data, storing results in a new series.
Amazon DynamoDB: Scalable NoSQL as a Service
Think of DynamoDB as a database where you trade complex queries for near-infinite, hands-off scaling. It's a managed NoSQL service from AWS for key-value and document data, built for high-performance applications.
Database as a Service (DBaaS): Rent, Don't Build
DBaaS is like leasing a database instead of owning it. A cloud provider handles the backups, patching, and scaling, so you can focus on your app. The main footgun is assuming "managed" means you can ignore configuration, query performance, and costs.
Database Proxies: A Manager for Your Database Traffic
A database proxy is a manager between your app and database, handling requests to improve performance and security. It pools connections, caches queries, and balances load, preventing any single server from being overwhelmed.
ORM Lazy Loading: Defer Queries Until Needed
An ORM's lazy loading fetches related data only when you access it, not with the initial query. This speeds up the first query if you don't need related objects. The footgun is the N+1 problem, where a loop triggers many hidden, slow database queries.
SQL Query Builders: Write SQL Without Writing SQL
An SQL query builder is a translator for your database, converting visual clicks or chained code methods into raw SQL. It's used to write safer, database-agnostic code or to let non-technical users build queries. The footgun is generating inefficient queries.
Connection String: Your App's Key and Address to Data
A connection string is your app's address and key to a data source. It bundles the host, port, database name, and credentials into a single string for a driver to use. The main footgun is committing credentials to version control by hardcoding the string in.
ODBC: The Universal Translator for Databases
ODBC acts as a universal translator, letting one application speak to many different relational databases. Your app uses the standard ODBC interface, and a specific "driver" handles the unique protocol for each database. The footgun is performance overhead.
JDBC: Java's Universal Translator for Databases
JDBC is Java's universal adapter for databases, letting your app speak SQL to any database via a standard API. It's used for connecting, querying, and managing transactions. The biggest footgun is building SQL strings directly; always use PreparedStatements.
SQL Injection: When User Input Becomes a Command
SQL injection tricks a database into running unintended commands by sneaking them into user input. It's a common attack on websites where user data is directly stitched into SQL queries. The footgun is trusting input; always use prepared statements instead.
Database Encryption: Protecting Data at Rest
Database encryption turns your data into useless gibberish for anyone without the key. It protects sensitive data at rest, like PII or financial records, from direct theft of the database files.
Database Auditing: Your Database's Security Camera
Think of database auditing as a security camera for your data, recording who did what and when. It's essential for security investigations and compliance, but the footgun is treating it as a substitute for access control—it only records a breach, it doesn't…
BASE: Trading Consistency for Availability
BASE is a database design philosophy that prioritizes availability over strict consistency, the opposite of ACID. It's used in large-scale systems like social media where uptime is key and slightly stale data is okay.
Key-Value Store: The Simplest Database Model
A key-value store is a giant dictionary. You give it a unique key, like "user:123", and it returns the associated data. It's the foundation for caching and session management. The footgun is trying to query by value—it's built for key lookups only.
Query Rewriting: Your Database's Unseen Optimizer
A database's query rewriter is like a smart GPS, finding a faster route (execution plan) to the same destination (your query result). It happens automatically to speed up joins and filters. The footgun: your handwritten query isn't what actually runs.
External Merge Sort: Sorting Data Bigger Than RAM
External merge sort handles datasets too big for RAM. It sorts the data in memory-sized chunks, writes them to disk, then merges the sorted chunks back together. It's crucial for database indexing, but its speed is limited by disk I/O, not CPU.
Sort-Merge Join: The 'Line Up and Walk' Join
A sort-merge join is like merging two sorted lines of people. It's efficient when tables are already sorted on the join key or memory is tight. The footgun: if data isn't pre-sorted, the initial sort can make it slower than other join methods.
Hash Join: Faster Database Joins with Hash Tables
A hash join speeds up database joins by building an in-memory lookup table (a hash table) for the smaller table, then streaming the larger one past it to find matches. It's ideal for large, unsorted equijoins.
Get Database bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.