Databases Cheatsheet

Essential database commands, concepts, and best practices for MongoDB, PostgreSQL, Redis, and more.

MongoDB

mongosh

Start MongoDB shell

db.collection.find()

Find all documents

db.collection.insertOne({ field: 'value' })

Insert one document

db.collection.updateOne({ _id: id }, { $set: { field: 'value' } })

Update one document

db.collection.deleteOne({ _id: id })

Delete one document

PostgreSQL

psql -U username -d database

Connect to database

\l

List databases

\dt

List tables

\d table_name

Describe table

\q

Quit psql

Redis

redis-cli

Start Redis CLI

SET key value

Set key-value pair

GET key

Get value by key

DEL key

Delete key

KEYS *

List all keys

Database Design

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100))

Create table with primary key

ALTER TABLE users ADD COLUMN email VARCHAR(255) UNIQUE

Add unique column

CREATE INDEX idx_name ON users(name)

Create index

CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id))

Create table with foreign key

CREATE VIEW user_orders AS SELECT * FROM users JOIN orders ON users.id = orders.user_id

Create view

Database Maintenance

VACUUM ANALYZE table_name

Vacuum and analyze table

REINDEX DATABASE database_name

Rebuild all indexes

pg_dump database_name > backup.sql

Create database backup

psql database_name < backup.sql

Restore from backup

ANALYZE table_name

Update statistics

Performance

EXPLAIN SELECT * FROM table

Show query plan

EXPLAIN ANALYZE SELECT * FROM table

Show query execution time

SELECT pg_size_pretty(pg_total_relation_size('table_name'))

Show table size

SELECT * FROM pg_stat_activity

Show active queries

SELECT * FROM pg_stat_user_tables

Show table statistics