Database & SQL — What Is a Database?
Every application stores data. The question is how. A database is a structured system for storing, retrieving, and managing data. This article covers the fundamentals.
Key sources: "Database Internals" by Alex Petrov, "SQL for Dummies" by Allen G. Taylor, PostgreSQL documentation.
What Is a Database?
A database is an organized collection of data stored electronically. It provides:
- Persistence: Data outlives the application process that created it.
- Consistency: Data maintains valid state even during concurrent access.
- Query capability: Data can be searched, filtered, and combined efficiently.
- Concurrency: Multiple users or services can access data simultaneously.
Compare this to storing data in a flat file. A file is simple but lacks structure. Finding a specific record requires reading the entire file. Multiple writers cause corruption. No query language exists.
Databases solve all of these problems.
Tables, Records, and Fields
Data in a relational database is organized into tables. A table is like a spreadsheet:
- Each column represents a field (name, age, email).
- Each row is a record (a single person's data).
- Each cell holds a single value.
| ID | Name | Email | Signup Date | |----|------|-------|-------------| | 1 | Alice | [email protected] | 2026-01-15 | | 2 | Bob | [email protected] | 2026-02-20 | | 3 | Charlie | [email protected] | 2026-03-10 |
A table definition specifies the data type for each column. This enforces consistency: you cannot store text in a date column.
SQL: The Language of Databases
Structured Query Language (SQL) is the standard language for interacting with relational databases. It has four main categories of operations:
Data Query (SELECT): Retrieve data.
sql
SELECT name, email FROM users WHERE signup_date > '2026-01-01';
Data Manipulation (INSERT, UPDATE, DELETE): Modify data.
sql
INSERT INTO users (name, email, signup_date) VALUES ('Diana', '[email protected]', '2026-04-01');
UPDATE users SET email = '[email protected]' WHERE id = 1;
DELETE FROM users WHERE id = 3;
Data Definition (CREATE, ALTER, DROP): Define the structure.
sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
signup_date DATE NOT NULL
);
Data Control (GRANT, REVOKE): Manage access permissions.
Types of Databases
Not all databases are relational. Different data models suit different problems.
| Type | Example | Best For | |------|---------|----------| | Relational | PostgreSQL, MySQL | Structured data with relationships, transactions | | Document | MongoDB, CouchDB | JSON-like documents, flexible schemas | | Key-Value | Redis, DynamoDB | Fast lookups by primary key | | Columnar | Cassandra, Bigtable | Time-series data, analytics | | Graph | Neo4j, Dgraph | Connected data, social networks |
ACID Properties
Relational databases guarantee ACID properties for transactions:
- Atomicity: A transaction completes fully or not at all. If one statement fails, the entire transaction rolls back.
- Consistency: Transactions bring the database from one valid state to another. Constraints and rules are enforced.
- Isolation: Concurrent transactions do not interfere with each other. Each appears to run alone.
- Durability: Committed data survives system failures. It is written to persistent storage.
These guarantees make relational databases the standard choice for financial systems, user accounts, and any application where data integrity is critical.
Why Not Just Use Files?
A common beginner question: why not save data to a JSON or CSV file?
Files break down at any scale:
- Concurrent access: Two processes writing to the same file causes corruption.
- Query performance: Finding one record in a 10 GB file requires scanning the entire file.
- Consistency: A crash during a write leaves the file in a partial state.
- Security: Files lack row-level access controls.
- Relationships: Joining data across files requires custom code.
Databases solve all of these problems with decades of engineering behind them.
Key Takeaways
- A database is a structured system for storing, retrieving, and managing data.
- Relational databases organize data into tables with rows and columns.
- SQL is the standard language for querying and manipulating data.
- ACID properties guarantee reliable transactions.
- Different types of databases suit different problems.
- Databases handle concurrent access, performance, and consistency in ways that flat files cannot.
Design principle: Choose your database based on your data model and access patterns, not on familiarity.