C++: Practice: Simple Database
Last updated: 2026-08-26
In lesson 51, we built an address book system.
Now, we'll build our second comprehensive practical project — a simple database.
This project uses many advanced C++ features, making it a great test of what you've learned.
1. Project Requirements
(1) 1.1 Functional Requirements
| Feature | Description |
|---|---|
| CREATE TABLE | Create a table |
| INSERT | Insert data |
| SELECT | Query data |
| WHERE | Conditional filtering |
| Index | Speed up queries |
| Transaction | Guarantee ACID |
(2) 1.2 Simplified Design
Since this is an educational project, we'll make a simplified version:
- Only support
intandstringtypes - Only support single-table queries
- Indexes implemented with
std::map - Data stored in memory (optional persistence)
2. Table Structure Design
(1) 2.1 Column Definition
▶ Example 2: Code Example (Difficulty ⭐)
TEXT
📖 Display only
#include <iostream>
#include <string>
#include <variant>
#include <vector>
// Column definition
struct Column {
std::string name;
enum Type { INT, STRING } type;
};
// Value type (use variant to store different types)
using Value = std::variant<int, std::string>;
// Row (one row of data)
using Row = std::vector<Value>;
// Table
class Table {
private:
std::string name;
std::vector<Column> columns;
std::vector<Row> rows;
public:
Table(const std::string& name, const std::vector<Column>& columns)
: name(name), columns(columns) {}
// Insert a row
void insert(const Row& row) {
if (row.size() != columns.size()) {
std::cerr << "Column count mismatch" << std::endl;
return;
}
rows.push_back(row);
}
// Query (simplified: return all rows)
std::vector<Row> select() {
return rows;
}
// Display table structure
void describe() const {
std::cout << "Table name: " << name << std::endl;
std::cout << "Columns: " << std::endl;
for (const auto& col : columns) {
std::cout << " " << col.name << " (";
if (col.type == Column::INT) {
std::cout << "INT";
} else {
std::cout << "STRING";
}
std::cout << ")" << std::endl;
}
}
};
Output:
TEXT
📖 Display only
Table name:
Columns:
(
INT
STRING
)
3. Query Implementation
(1) 3.1 WHERE Clause
CPP
// Predicate function (for WHERE)
using Predicate = std::function<bool(const Row&)>;
// Query with condition
std::vector<Row> selectWhere(Predicate pred) {
std::vector<Row> result;
for (const auto& row : rows) {
if (pred(row)) {
result.push_back(row);
}
}
return result;
}
// Example: find rows where age > 18
auto predicate = [](const Row& row) {
int age = std::get<int>(row[1]); // Assume column 2 is age
return age > 18;
};
auto result = table.selectWhere(predicate);
4. Index Implementation
(1) 4.1 Simple Index
TEXT
📖 Display only
#include <map>
// Index (simplified: only index int types)
class Index {
private:
std::map<int, std::vector<int>> index; // Value -> row number list
public:
void build(const std::vector<Row>& rows, int colIndex) {
index.clear();
for (int i = 0; i < rows.size(); i++) {
int key = std::get<int>(rows[i][colIndex]);
index[key].push_back(i);
}
}
std::vector<int> lookup(int key) {
auto it = index.find(key);
if (it != index.end()) {
return it->second;
}
return {};
}
};
5. Transaction Support
(1) 5.1 Simplified Transaction
CPP
#include <stack>
class Transaction {
private:
std::stack<std::vector<Row>> undoStack;
public:
// Begin transaction (save current state)
void begin(Table& table) {
undoStack.push(table.rows);
}
// Commit (clear undo stack)
void commit() {
while (!undoStack.empty()) {
undoStack.pop();
}
}
// Rollback (restore previous state)
void rollback(Table& table) {
if (!undoStack.empty()) {
table.rows = undoStack.top();
undoStack.pop();
}
}
};
6. Complete Example
▶ Example 1: Using the Simple Database (Difficulty ⭐⭐⭐)
TEXT
📖 Display only
int main() {
// Create table
Table t("users", {{"name", Column::STRING}, {"age", Column::INT}});
// Insert data
t.insert({"Alice", 25});
t.insert({"Bob", 30});
t.insert({"Charlie", 20});
// Query all
auto rows = t.select();
std::cout << "All users:" << std::endl;
for (const auto& row : rows) {
std::cout << std::get<std::string>(row[0]) << ", "
<< std::get<int>(row[1]) << std::endl;
}
return 0;
}
Output:
TEXT
📖 Display only
All users:
,
7. Extension Directions
(1) 7.1 Feature Extensions
| Feature | Difficulty | Description |
|---|---|---|
| Persistence | ⭐⭐ | Save to file |
| JOIN | ⭐⭐⭐⭐ | Multi-table joins |
| SQL Parser | ⭐⭐⭐⭐⭐ | Parse SQL statements |
| Concurrency Control | ⭐⭐⭐⭐ | Thread safety |
❓ Exercises
(1) Basic Exercise (Difficulty ⭐⭐)
Add a DELETE feature to the database.
(2) Intermediate Exercise (Difficulty ⭐⭐⭐)
Add an UPDATE feature to the database.
(3) Challenge Exercise (Difficulty ⭐⭐⭐⭐)
Implement a simple SQL parser that supports SELECT * FROM table WHERE age > 18.
▶ Example 3: Database Table Structure Definition (Difficulty ⭐)
CPP
#include <iostream>
#include <string>
#include <vector>
#include <variant>
// Value type (supports multiple data types)
using Value = std::variant<int, std::string, double>;
// One row of data
struct Row {
int id;
Value name;
Value age;
Value score;
void display() const {
std::cout << "ID: " << id << std::endl;
std::cout << "Name: " << std::get<std::string>(name) << std::endl;
std::cout << "Age: " << std::get<int>(age) << std::endl;
std::cout << "Score: " << std::get<double>(score) << std::endl;
}
};
int main() {
Row r = {1, std::string("Alice"), 20, 95.5};
r.display();
return 0;
}
Output:
TEXT
📖 Display only
ID:
Name:
Age:
Score:
❓ FAQ
Q How fast is map lookup?
A map is implemented with a red-black tree internally, so lookup, insertion, and deletion are all O(log n). For 1 million records, worst case requires only about 20 comparisons.
Q Why use CSV for file storage?
A CSV format is simple, human-readable, and can be opened directly in Excel. JSON supports nested structures but is slightly more complex to parse. For production, SQLite is recommended.
Q How to prevent SQL injection?
A This is a simple implementation. In real production environments, always use parameterized queries or prepared statements. Never directly concatenate user input into SQL strings.
📖 Summary
| Knowledge Point | Application |
|---|---|
| variant | Store values of different types |
vector<Row> |
Store table data |
| map | Implement indexes |
| Function objects | Implement WHERE predicates |
- Simple database: in-memory data + file persistence
- Use map for O(log n) key lookup
- Support CRUD command interface
- CSV or JSON format for data storage
- Exception handling for program stability
📝 Exercises
-
Basic (Difficulty ⭐): Run the database program, create a "students" table (fields: student ID, name, age, score), insert 3 records, and query all records.
-
Intermediate (Difficulty ⭐⭐): Add a "sorted query" feature — support
SELECT * FROM table ORDER BY age. Hint: usestd::sort+ lambda expression to sort by a specified field. -
Challenge (Difficulty ⭐⭐⭐): Implement "multi-table join" — create two tables (students, grades), support
SELECT students.name, grades.score FROM students JOIN grades ON students.id = grades.student_id. You need to design the JOIN logic and result merging.
Next lesson: Course Summary and Advanced Roadmap (#53)
