Databases

Reference guide to SQL and NoSQL databases: MongoDB, Redis, Cassandra, CouchDB, Neo4j, MySQL, and PostgreSQL, plus design, indexing, and security practices.

What this covers

This page covers working with SQL and NoSQL database technologies — connecting, querying, aggregating, and indexing across MongoDB, Redis, Cassandra, CouchDB, Neo4j, MySQL, and PostgreSQL — along with core database design, performance, security, and backup practices.

When to use it

Connecting to or authenticating against a specific database engine (MongoDB, Redis, Cassandra, CouchDB, Neo4j, MySQL, PostgreSQL)
Writing CRUD, aggregation, or advanced queries against a document, key-value, wide-column, or graph store
Designing or normalizing a relational schema, or deciding how to index it
Diagnosing slow queries or planning performance work (indexing, connection pooling, caching, sharding)
Applying database security practices (parameterized queries, access control, encryption) or planning backup and recovery

NoSQL Databases

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

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})

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

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;

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

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

SQL Databases

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;

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;

Database Design Best Practices

Normalization

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

Indexing Strategies

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

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

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

Backup and Recovery

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

← Back to Homepage