Database Development Guide

This guide provides a comprehensive overview of database technologies, including SQL and NoSQL systems, with practical examples for developers working with data storage, retrieval, and optimization.

1. NoSQL Databases

NoSQL databases offer flexible schemas and are designed to handle large volumes of unstructured or semi-structured data.

1.1 MongoDB

MongoDB is a document-oriented NoSQL database storing JSON-like documents with dynamic schemas.

Connection & Authentication

# Local connection
mongo
mongosh  # New MongoDB shell

# Remote connection
mongo "mongodb://username:password@hostname:27017/database_name"
mongosh "mongodb://username:password@hostname:27017/database_name"

# Connection with options
mongo --host hostname --port 27017 --username user --password --authenticationDatabase admin

Database Operations

show dbs                  // List databases
db.adminCommand('listDatabases')  // Detailed list
use database_name          // Switch database
db                         // Current database
db.dropDatabase()          // Drop database
db.stats()                 // Database statistics

Collection Operations

show collections
db.listCollections()
db.createCollection("collection_name")
db.collection_name.stats()
db.collection_name.drop()

CRUD Operations

// Insert documents
db.users.insertOne({name: "John", age: 30, email: "john@example.com"})
db.users.insertMany([{name: "Jane", age: 25}, {name: "Bob", age: 35}])

// Find documents
db.users.find()
db.users.find({age: {$gte: 25}})
db.users.find({name: "John"}).pretty()
db.users.findOne({_id: ObjectId("...")})

// Update documents
db.users.updateOne({name: "John"}, {$set: {age: 31}})
db.users.updateMany({age: {$lt: 30}}, {$set: {status: "young"}})
db.users.replaceOne({name: "John"}, {name: "John", age: 32, email: "john.doe@example.com"})

// Delete documents
db.users.deleteOne({name: "John"})
db.users.deleteMany({age: {$lt: 18}})

Advanced Queries

// Comparison
db.users.find({age: {$gt: 25, $lte: 35}})

// Logical
db.users.find({$or: [{age: {$lt: 25}}, {age: {$gt: 35}}]})

// Array & element
db.users.find({skills: {$all: ["JavaScript", "MongoDB"]}})
db.users.find({skills: {$size: 3}})

// Regex
db.users.find({name: {$regex: /^J/}})

Aggregation Example

db.orders.aggregate([
    {$match: {status: "completed"}},
    {$group: {_id: "$customer_id", total: {$sum: "$amount"}}},
    {$sort: {total: -1}},
    {$limit: 10}
])

Indexing

db.users.createIndex({email: 1})             // Single field
db.users.createIndex({name: 1, age: -1})     // Compound
db.articles.createIndex({title: "text"})     // Text
db.places.createIndex({location: "2dsphere"}) // Geospatial
db.users.getIndexes()
db.users.dropIndex({email: 1})

1.2 Redis

Redis is an in-memory data structure store used as a database, cache, and message broker.

# Connect
redis-cli -h hostname -p 6379 -a password

Basic Commands

# Strings
SET key "value"
GET key
INCR counter

# Lists
LPUSH mylist "a"
RPUSH mylist "b"
LRANGE mylist 0 -1

# Sets
SADD myset "member1"
SMEMBERS myset

# Hashes
HSET user:1 name "John" age "30"
HGETALL user:1

# Sorted Sets
ZADD leaderboard 100 "player1"
ZRANGE leaderboard 0 -1 WITHSCORES

1.3 Cassandra

Apache Cassandra is a distributed NoSQL database for large-scale data.

cqlsh hostname 9042 -u username -p password

Keyspace Operations

CREATE KEYSPACE mykeyspace WITH REPLICATION = {'class':'SimpleStrategy', 'replication_factor':3};
USE mykeyspace;
DESCRIBE KEYSPACES;

Table Operations

CREATE TABLE users (user_id uuid PRIMARY KEY, name text, email text, age int);
INSERT INTO users (user_id, name, email, age) VALUES (uuid(), 'John Doe', 'john@example.com', 30);
SELECT * FROM users;
UPDATE users SET age = 31 WHERE user_id = uuid_value;
DELETE FROM users WHERE user_id = uuid_value;

1.4 CouchDB

Document-oriented DB with HTTP API and JSON storage.

# Create database
curl -X PUT http://admin:password@localhost:5984/mydb

# CRUD
curl -X POST http://admin:password@localhost:5984/mydb -H "Content-Type: application/json" -d '{"name":"John","age":30}'
curl -X GET http://admin:password@localhost:5984/mydb/doc_id
curl -X PUT http://admin:password@localhost:5984/mydb/doc_id -H "Content-Type: application/json" -d '{"_rev":"1-xxx","name":"John","age":31}'
curl -X DELETE http://admin:password@localhost:5984/mydb/doc_id?rev=revision_id

1.5 Neo4j

Graph database using nodes, relationships, and properties.

// Nodes
CREATE (n:Person {name:'John', age:30})
CREATE (m:Person {name:'Jane', age:25})

// Relationships
MATCH (a:Person {name:'John'}), (b:Person {name:'Jane'})
CREATE (a)-[:KNOWS]->(b)

// Queries
MATCH (n:Person) RETURN n
MATCH (a:Person)-[r:KNOWS]->(b:Person) RETURN a, r, b
MATCH path = (a:Person)-[*]-(b:Person) RETURN path

2. SQL Databases

2.1 MySQL

mysql -u username -p database_name

Basic SQL

SHOW DATABASES;
USE database_name;
SHOW TABLES;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 30);
SELECT * FROM users;
UPDATE users SET age=31 WHERE id=1;
DELETE FROM users WHERE id=1;

Joins

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

2.2 PostgreSQL

psql "postgresql://username:password@hostname:5432/database_name"

Advanced Features

-- JSON operations
CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);
INSERT INTO products (data) VALUES ('{"name":"Laptop","price":999}');
SELECT data->>'name' FROM products WHERE data->>'price'>'500';

-- Full-text search
SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('database & query');

-- Window functions
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

-- CTEs
WITH high_salary AS (
  SELECT * FROM employees WHERE salary > 50000
)
SELECT department, COUNT(*) FROM high_salary GROUP BY department;

3. Database Design Best Practices

3.1 Normalization

1NF: Eliminate repeating groups
2NF: Remove partial dependencies
3NF: Remove transitive dependencies
BCNF: Ensure all determinants are candidate keys

3.2 Indexing Strategies

Index frequently queried columns
Use composite indexes for multi-column queries
Balance index overhead vs query speed
Monitor and optimize index usage

3.3 Performance Optimization

Use EXPLAIN to analyze queries
Optimize slow queries with indexing
Implement connection pooling
Add caching layers (Redis, Memcached)
Consider sharding for large datasets

3.4 Security Best Practices

Use parameterized queries to prevent SQL injection
Implement authentication and role-based access control
Encrypt sensitive data at rest and in transit
Regularly backup databases
Monitor logs for anomalies

3.5 Backup and Recovery

Automate backups and test restoration
Use point-in-time recovery when possible
Store backups in multiple locations