A database is like a digital filing cabinet that helps applications store and retrieve data efficiently. Whether you're building a social media platform, an e-commerce site, or a simple blog, understanding databases is crucial for modern web development.
This guide covers fundamental database concepts and best practices for working with data in your applications.
Schema: The structural blueprint of a database that defines how data is organized, including tables, fields, relationships, and constraints. Think of it like an architect's plan for a building.
Primary Key: A unique identifier for each record in a table (like a social security number for people). No two records can have the same primary key.
Foreign Key: A field that links to a primary key in another table, creating relationships between data. Think of it like a reference from one book to another in a library catalog.
🎥 Video: SQL Explained in 100 seconds
Before diving into specific database types, it's important to understand the two main categories of databases. Think of relational databases like spreadsheets with strict rules, while NoSQL databases are more like flexible documents that can change shape as needed.
| Feature | Relational (SQL) | NoSQL |
|---|---|---|
| Data Structure | Tables with fixed columns and rows | Flexible documents, key-value pairs, graphs, or wide-columns |
| Schema | Strict, predefined (see above definition) | Flexible, can change on the fly |
| Scaling | Vertical (bigger servers) | Horizontal (more servers) |
| Relationships | Strong, enforced through foreign keys (see above definition) | Flexible, can be modeled through references |
| Example Use Cases | Financial systems, inventory management | Social media, content management, IoT (Internet of Things) data |
| ACID Compliance | Full support | Varies by database |
| Query Language | Standard SQL | Database-specific |
| Learning Curve | Steeper (SQL + schema design) | Generally easier to start |
-- Example of a relational database table
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- Unique identifier for each user
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);// Example of a NoSQL document
{
"_id": ObjectId("507f1f77bcf86cd799439011"), // Document's unique identifier
"username": "john_doe",
"email": "john@example.com",
"preferences": {
"theme": "dark",
"notifications": true
},
"posts": [
{ "title": "Hello World", "likes": 42 }
]
}🎥 Video: SQL vs NoSQL Explained (4 minutes)
SQL (Structured Query Language) is the standard language for working with relational databases. Think of SQL like a very specific language for talking to your database - it has its own grammar and vocabulary for asking questions about your data.
-- Create: Insert new data
INSERT INTO users (username, email)
VALUES ('jane_doe', 'jane@example.com');
-- Read: Query existing data
SELECT username, email
FROM users
WHERE id = 1;
-- Update: Modify existing data
UPDATE users
SET email = 'new.email@example.com'
WHERE username = 'jane_doe';
-- Delete: Remove data
DELETE FROM users
WHERE username = 'jane_doe';Database operations flow:
🎥 Video: Learn SQL Basics in Just 15 Minutes
Data normalization is the process of organizing data to reduce redundancy and improve data integrity. Think of it like organizing a library - books are categorized by genre, author, etc., rather than having all information in one big list.
-- Example: User and Profile
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50)
);
CREATE TABLE profiles (
id SERIAL PRIMARY KEY,
user_id INTEGER UNIQUE REFERENCES users(id),
bio TEXT
);-- Example: User and Posts
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
content TEXT
);-- Example: Posts and Tags
CREATE TABLE posts_tags (
post_id INTEGER REFERENCES posts(id),
tag_id INTEGER REFERENCES tags(id),
PRIMARY KEY (post_id, tag_id)
);Relationship visualization:
🎥 Video: One-to-many and many-to-many relationships in SQL
Indexes are like the table of contents in a book - they help the database find data quickly without scanning every record. Understanding indexing is crucial for database performance.
-- Basic index
CREATE INDEX idx_username ON users(username);
-- Composite index
CREATE INDEX idx_name_email ON users(first_name, last_name);
-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);Query optimization flow:
🎥 Video: Database Indexing best practices (4 minutes)
Transactions ensure that multiple database operations either all succeed or all fail together. Think of it like transferring money between bank accounts - either both accounts should be updated, or neither should change.
-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Transaction with rollback
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Something goes wrong
ROLLBACK;Transaction states:
🎥 Video: What is transaction in SQL?
ORMs translate between database tables and application objects. Instead of writing raw SQL, you work with your programming language's objects and classes.
// Example using Prisma ORM
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
// Create a user
async function createUser() {
const user = await prisma.user.create({
data: {
email: 'jane@example.com',
name: 'Jane Doe',
posts: {
create: {
title: 'Hello World'
}
}
}
})
}ORM workflow:
🎥 Video: Prisma in 100 seconds 🎥 Video: Learn Prisma In 60 Minutes
Database migrations are like version control for your database structure. They help track and apply database changes across different environments. With Prisma, migrations are handled through schema changes and the Prisma CLI.
// schema.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
createdAt DateTime @default(now())
}
# Create a migration
npx prisma migrate dev --name add_user_table
# Apply migrations to production
npx prisma migrate deploy// Updated schema.prisma
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
createdAt DateTime @default(now())
// Adding new fields
role String @default("USER")
posts Post[]
}
// Adding a new model
model Post {
id Int @id @default(autoincrement())
title String
content String?
author User @relation(fields: [authorId], references: [id])
authorId Int
createdAt DateTime @default(now())
}
Migration process:
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
async function main() {
// Create a new user
const user = await prisma.user.create({
data: {
email: 'alice@example.com',
name: 'Alice',
posts: {
create: {
title: 'Hello World',
content: 'My first post!'
}
}
}
})
// Query users with their posts
const users = await prisma.user.findMany({
include: {
posts: true
}
})
}🎥 Video: Prisma Migrations: A Step-by-Step Guide
