database

Files (1)

• SKILL.md

SKILL.md (first lines)

# Database

Interact with various databases using CLI tools.

## Setup

```bash
# Check each tool
command -v psql    # PostgreSQL
command -v mysql   # MySQL
command -v mongosh # MongoDB
command -v redis-cli
command -v sqlite3

# Install PostgreSQL client — macOS: brew install libpq | Ubuntu: sudo apt install postgresql-client
# Install MySQL client    — macOS: brew install mysql-client | Ubuntu: sudo apt install mysql-client
# Install MongoDB shell   — macOS: brew install mongosh | Ubuntu: https://www.mongodb.com/try/download/shell
# Install Redis CLI       — macOS: brew install redis | Ubuntu: sudo apt install redis-tools
# Install SQLite3         — macOS: brew install sqlite | Ubuntu: sudo apt install sqlite3

# For connection strings with passwords, use vault:
#   vault_save postgres_url "postgresql://user:pass@host:5432/dbname"
#   vault_save mysql_password "secret"
# Keys use lowercase_underscore; auto-inject as UPPERCASE env vars (e.g. $POSTGRES_URL)
```

## PostgreSQL

```bash
# Connect to database
psql -h localhost -U user -d dbname

# Connection string
psql "postgresql://user:pass@host:5432/dbname"

# Execute query
psql -h localhost -U user -d dbname -c "SELECT * FROM users LIMIT 10;"

# Execute from file
psql -f queries.sql

# List databases
psql -l

# List tables
psql -d dbname -c "\dt"

# Describe table
psql -d dbname -c "\d tablename"

# Export to CSV
psql -d dbname -c "COPY (SELECT * FROM users) TO STDOUT WITH CSV HEADER" > users.csv

# Import CSV
psql -d dbname -c "\COPY users FROM 'users.csv' WITH CSV HEADER"
```

## MySQL

```bash
# Connect
mysql -h localhost -u user -p dbname

# Execute query
mysql -h localhost -u user -p dbname -e "SELECT * FROM users LIMIT 10;"

# Execute from file
mysql -h localhost -u user -p dbname < queries.sql

# Export database
mysqldump -u user -p dbname > backup.sql

# Import database
mysql -u user -p dbname < backup.sql

# Export single table
mysqldump -u user -p dbname tablename > table.sql
```

## MongoDB

```bash
# Connect
mongosh "mongodb://user:pass@host:27017/dbname"

# Or with connection string
mongosh "mongodb+srv://cluster.example.com/dbname" --username user

# Execute query (eval)
mongosh dbname --eval 'db.users.find().limit(10)'

# Export collection
mongoexport --uri="mongodb://host/dbname" --collection=users --out=users.json

# Import collection
mongoimport --uri="mongodb://host/dbname" --collection=users --file=users.json

# Dump database
mongodump --uri="mongodb://host/dbname" --out=backup/

# Restore database
mongorestore --uri="mongodb://host/dbname" backup/dbname/
```

## Redis

```bash
# Connect
redis-cli -h localhost -p 6379

# With password
redis-cli -h localhost -p 6379 -a password

# Execute command
redis-cli -h localhost GET mykey

# Set value
redis-cli -h localhost SET mykey "value"

# List all keys

From jholhewres/devclaw-skills, MIT.

More from jholhewres/devclaw-skills