1. What is a Database?
A database is a system for storing and organizing information so that an application can efficiently save, find, update, and delete data.
| ID | Name | |
|---|---|---|
| 1 | Rahul | rahul@gmail.com |
| 2 | Priya | priya@gmail.com |
| 3 | Aman | aman@gmail.com |
For example, the backend can ask:
Give me the user whose ID is 2.The database can return:
Priya | priya@gmail.comWhy not just use files?
You could store data in JSON, but as an application grows:
- Finding data becomes slower.
- Multiple users may modify data simultaneously.
- Relationships become difficult to manage.
- Security becomes harder.
- Data consistency becomes a problem.
- Large amounts of data are difficult to handle.
2. Database Fundamentals
SQL / Relational
Uses tables and relationships.
Examples: MySQL, PostgreSQL, Microsoft SQL Server.
NoSQL
Uses flexible data structures such as documents.
Example: MongoDB.
3. SQL vs NoSQL
SQL
SQL stands for Structured Query Language. SQL databases generally organize information into tables.
| Users | Name | |
|---|---|---|
| 1 | Rahul | rahul@gmail.com |
| 2 | Priya | priya@gmail.com |
| 3 | Aman | aman@gmail.com |
Creating a table
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255)
);SQL is particularly useful when:
- Data has a clear structure.
- Relationships between data are important.
- You need complex queries.
- Data consistency is critical.
Popular SQL databases
4. NoSQL
NoSQL databases do not necessarily organize data into traditional tables. MongoDB, for example, stores documents.
{
"_id": 1,
"name": "Rahul",
"email": "rahul@gmail.com",
"age": 22
}Another document can contain different fields:
{
"_id": 2,
"name": "Priya",
"email": "priya@gmail.com",
"skills": ["JavaScript", "React"]
}| SQL | NoSQL |
|---|---|
| Tables | Documents / collections |
| Fixed / defined schema | Flexible schema |
| SQL queries | Database-specific query APIs/languages |
| Strong relational model | Often optimized for flexible/document data |
| Great for complex relationships | Great for flexible structures |
| MySQL, PostgreSQL | MongoDB |
5. Tables, Records, and Relationships
These concepts are extremely important for SQL databases.
| ID | Name | |
|---|---|---|
| 1 | Rahul | rahul@gmail.com |
| 2 | Priya | priya@gmail.com |
Table
A table organizes related data into rows and columns.
Record / Row
Each individual entry is a record, commonly called a row.
Column
A column represents a particular type of information such as id, name or email.
6. Primary Keys
A primary key uniquely identifies a record.
For example, id might be the primary key.
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255)
);7. Relationships
One user can make many orders. This is a One-to-Many relationship.
Foreign Keys
| ID | User ID | Total |
|---|---|---|
| 101 | 1 | 5000 |
| 102 | 1 | 3000 |
| 103 | 2 | 7000 |
user_id points to the user's ID and is called a foreign key.
8. Types of Relationships
One-to-One
One person has one passport.
One-to-Many
One customer has many orders.
Many-to-Many
Students can enroll in many courses, and courses can have many students.
Junction / Link Table
A many-to-many relationship is usually implemented using a junction table.
| Student ID | Course ID |
|---|---|
| 1 | 101 |
| 1 | 102 |
| 2 | 101 |
9. CRUD Operations
Create
Read
Update
Delete
Create
INSERT INTO users (name, email)
VALUES ('Rahul', 'rahul@gmail.com');Read
SELECT * FROM users;
SELECT * FROM users
WHERE id = 1;Update
UPDATE users
SET email = 'newemail@gmail.com'
WHERE id = 1;Delete
DELETE FROM users
WHERE id = 1;| HTTP | Database Operation |
|---|---|
| POST | CREATE |
| GET | READ |
| PUT / PATCH | UPDATE |
| DELETE | DELETE |
10–13. MySQL, PostgreSQL and MongoDB
MySQL
MySQL is one of the most widely used relational databases.
PostgreSQL
PostgreSQL is another major relational database.
SELECT * FROM users;
INSERT INTO users (name, email)
VALUES ('Rahul', 'rahul@gmail.com');
UPDATE users
SET name = 'Rahul Sharma'
WHERE id = 1;MongoDB
MongoDB stores data as documents in collections.
{
"_id": "123",
"name": "Rahul",
"email": "rahul@gmail.com",
"skills": [
"JavaScript",
"Node.js",
"React"
]
}MongoDB CRUD
// Create
db.users.insertOne({
name: "Rahul",
email: "rahul@gmail.com"
});
// Read
db.users.find();
// Find one
db.users.findOne({
name: "Rahul"
});
// Update
db.users.updateOne(
{ name: "Rahul" },
{ $set: { age: 22 } }
);
// Delete
db.users.deleteOne({
name: "Rahul"
});14–15. Connecting a Database to a Backend
The frontend should normally communicate with a backend rather than connecting directly to the database.
Example Backend Flow
POST /users
{
"name": "Rahul",
"email": "rahul@gmail.com"
}app.post("/users", async (req, res) => {
const { name, email } = req.body;
// Save user to database
res.json({
message: "User created"
});
});The backend can then execute:
INSERT INTO users (name, email)
VALUES ('Rahul', 'rahul@gmail.com');16–20. Database Security
1. Never expose database credentials
Keep credentials on the server.
DATABASE_URL=...
DATABASE_PASSWORD=...2. Hash passwords
Never store plain-text passwords.
password = "rahul123"Store a secure password hash instead. Common tools include bcrypt and Argon2.
3. Prevent SQL Injection
Do not blindly concatenate user input into SQL.
const result = await db.query(
"SELECT * FROM users WHERE email = $1",
[email]
);4. Validate Input
- Is age a number?
- Is email actually an email?
- Is name present?
- Is the string too long?
- Is the value allowed?
Authentication vs Authorization
Authentication
Who are you?
Example: email + password → You're Rahul.
Authorization
What are you allowed to do?
Example: a regular user may view products but not delete another user's account.
Other Important Security Practices
Example Index
CREATE INDEX idx_users_email
ON users(email);Example Transaction
21. Putting Everything Together
Imagine building an e-commerce application.
Possible Database Tables
users
id, name, email, password_hash
products
id, name, price, stock
orders
id, user_id, total, status
order_items
id, order_id, product_id, quantity, price
Example API
POST /users
GET /users/:id
PATCH /users/:id
DELETE /users/:id
GET /products
POST /products
POST /orders
GET /orders/:id