Back to Details

db

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Database Manager

数据库管理器

Query and manage databases across SQLite, PostgreSQL, and MySQL.
对SQLite、PostgreSQL和MySQL数据库进行查询和管理。

Prerequisites

前置条件

Install database CLIs as needed:
bash
undefined
根据需要安装数据库CLI工具：
bash
undefined

SQLite (usually pre-installed on macOS/Linux)

SQLite (usually pre-installed on macOS/Linux)

sqlite3 --version
sqlite3 --version

PostgreSQL

PostgreSQL

brew install postgresql
brew install postgresql

or

or

apt install postgresql-client
apt install postgresql-client

MySQL

MySQL

brew install mysql-client
brew install mysql-client

or

or

apt install mysql-client
undefined
apt install mysql-client
undefined

CLI Reference

CLI参考

SQLite

SQLite

bash
undefined
bash
undefined

Connect to database

连接到数据库

sqlite3 database.db
sqlite3 database.db

Execute query

执行查询

sqlite3 database.db "SELECT * FROM users LIMIT 10"
sqlite3 database.db "SELECT * FROM users LIMIT 10"

Output as CSV

以CSV格式输出

sqlite3 -csv database.db "SELECT * FROM users"
sqlite3 -csv database.db "SELECT * FROM users"

Output as JSON (requires sqlite 3.33+)

以JSON格式输出（需要sqlite 3.33+版本）

sqlite3 -json database.db "SELECT * FROM users"
sqlite3 -json database.db "SELECT * FROM users"

Column headers

显示列标题

sqlite3 -header database.db "SELECT * FROM users"
sqlite3 -header database.db "SELECT * FROM users"

Execute SQL file

执行SQL文件

sqlite3 database.db < queries.sql
sqlite3 database.db < queries.sql

Schema commands

架构命令

sqlite3 database.db ".schema" sqlite3 database.db ".tables" sqlite3 database.db ".schema users"
undefined
sqlite3 database.db ".schema" sqlite3 database.db ".tables" sqlite3 database.db ".schema users"
undefined

PostgreSQL

PostgreSQL

bash
undefined
bash
undefined

Connect

连接

psql postgresql://user:pass@host:5432/dbname
psql postgresql://user:pass@host:5432/dbname

Execute query

执行查询

psql -c "SELECT * FROM users LIMIT 10" postgresql://...
psql -c "SELECT * FROM users LIMIT 10" postgresql://...

Tuples only (no headers)

仅返回数据行（无标题）

psql -t -c "SELECT count(*) FROM users" postgresql://...
psql -t -c "SELECT count(*) FROM users" postgresql://...

No alignment (machine-readable)

无对齐格式（机器可读）

psql -t -A -c "SELECT id,name FROM users" postgresql://...
psql -t -A -c "SELECT id,name FROM users" postgresql://...

Execute SQL file

执行SQL文件

psql -f queries.sql postgresql://...
psql -f queries.sql postgresql://...

List tables

列出所有表

psql -c "\dt" postgresql://...
psql -c "\dt" postgresql://...

Describe table

查看表结构

psql -c "\d users" postgresql://...
psql -c "\d users" postgresql://...

Output format

输出格式

psql -c "SELECT * FROM users" --csv postgresql://... psql -c "SELECT * FROM users" --html postgresql://...
undefined
psql -c "SELECT * FROM users" --csv postgresql://... psql -c "SELECT * FROM users" --html postgresql://...
undefined

MySQL

MySQL

bash
undefined
bash
undefined

Connect

连接

mysql -h host -u user -p dbname
mysql -h host -u user -p dbname

Execute query

执行查询

mysql -h host -u user -p -e "SELECT * FROM users LIMIT 10" dbname
mysql -h host -u user -p -e "SELECT * FROM users LIMIT 10" dbname

Batch mode (no headers)

批处理模式（无标题）

mysql -h host -u user -p -B -e "SELECT * FROM users" dbname
mysql -h host -u user -p -B -e "SELECT * FROM users" dbname

Execute SQL file

执行SQL文件

mysql -h host -u user -p dbname < queries.sql
mysql -h host -u user -p dbname < queries.sql

Show tables

显示所有表

mysql -h host -u user -p -e "SHOW TABLES" dbname
mysql -h host -u user -p -e "SHOW TABLES" dbname

Describe table

查看表结构

mysql -h host -u user -p -e "DESCRIBE users" dbname
undefined
mysql -h host -u user -p -e "DESCRIBE users" dbname
undefined

Common Operations

通用操作

Schema Inspection

架构检查

SQLite

SQLite

bash
undefined
bash
undefined

All tables

所有表

sqlite3 db.sqlite ".tables"
sqlite3 db.sqlite ".tables"

Table schema

表结构

sqlite3 db.sqlite ".schema tablename"
sqlite3 db.sqlite ".schema tablename"

All schemas

所有架构

sqlite3 db.sqlite ".schema"
undefined
sqlite3 db.sqlite ".schema"
undefined

PostgreSQL

PostgreSQL

bash
undefined
bash
undefined

All tables

所有表

psql -c "\dt" $DATABASE_URL
psql -c "\dt" $DATABASE_URL

Table schema

表结构

psql -c "\d tablename" $DATABASE_URL
psql -c "\d tablename" $DATABASE_URL

Table with indexes

带索引的表信息

psql -c "\d+ tablename" $DATABASE_URL
undefined
psql -c "\d+ tablename" $DATABASE_URL
undefined

MySQL

MySQL

bash
undefined
bash
undefined

All tables

所有表

mysql -e "SHOW TABLES" -h host -u user -p dbname
mysql -e "SHOW TABLES" -h host -u user -p dbname

Table schema

表结构

mysql -e "DESCRIBE tablename" -h host -u user -p dbname
mysql -e "DESCRIBE tablename" -h host -u user -p dbname

Create statement

表创建语句

mysql -e "SHOW CREATE TABLE tablename" -h host -u user -p dbname
undefined
mysql -e "SHOW CREATE TABLE tablename" -h host -u user -p dbname
undefined

Query Explanation

查询执行计划

bash
undefined
bash
undefined

SQLite

SQLite

sqlite3 db.sqlite "EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'x'"
sqlite3 db.sqlite "EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'x'"

PostgreSQL

PostgreSQL

psql -c "EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'x'" $DATABASE_URL
psql -c "EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'x'" $DATABASE_URL

MySQL

MySQL

mysql -e "EXPLAIN SELECT * FROM users WHERE email = 'x'" dbname
undefined
mysql -e "EXPLAIN SELECT * FROM users WHERE email = 'x'" dbname
undefined

Data Export

数据导出

bash
undefined
bash
undefined

SQLite to CSV

SQLite导出为CSV

sqlite3 -csv -header db.sqlite "SELECT * FROM users" > users.csv
sqlite3 -csv -header db.sqlite "SELECT * FROM users" > users.csv

PostgreSQL to CSV

PostgreSQL导出为CSV

psql -c "\COPY users TO 'users.csv' CSV HEADER" $DATABASE_URL
psql -c "\COPY users TO 'users.csv' CSV HEADER" $DATABASE_URL

MySQL to CSV

MySQL导出为CSV

mysql -e "SELECT * FROM users" -B dbname | tr '\t' ',' > users.csv
undefined
mysql -e "SELECT * FROM users" -B dbname | tr '\t' ',' > users.csv
undefined

AI-Assisted Query Generation

AI辅助查询生成

Use Gemini to help write queries:
bash
undefined
使用Gemini帮助编写查询：
bash
undefined

Describe what you want

描述你的需求

gemini -m pro -o text -e "" "Write a SQL query to:
  • Find all users who signed up in the last 30 days
  • Who have made at least one purchase
  • Order by purchase count descending
Table schemas:
  • users (id, email, created_at)
  • purchases (id, user_id, amount, created_at)
Output PostgreSQL-compatible SQL."
undefined
gemini -m pro -o text -e "" "Write a SQL query to:
  • Find all users who signed up in the last 30 days
  • Who have made at least one purchase
  • Order by purchase count descending
Table schemas:
  • users (id, email, created_at)
  • purchases (id, user_id, amount, created_at)
Output PostgreSQL-compatible SQL."
undefined

Safe Query Review

安全查询审核

bash
undefined
bash
undefined

Generate query

生成查询

QUERY=$(gemini -m pro -o text -e "" "Write SQL for: [your request]")
QUERY=$(gemini -m pro -o text -e "" "Write SQL for: [your request]")

Review before executing

执行前审核

echo "Generated query:" echo "$QUERY"
echo "Generated query:" echo "$QUERY"

Then execute if safe

确认安全后再执行

psql -c "$QUERY" $DATABASE_URL

psql -c "$QUERY" $DATABASE_URL

undefined
undefined

Migration Patterns

迁移模式

Schema Changes

架构变更

bash
undefined
bash
undefined

Create migration file

创建迁移文件

cat > migrations/001_add_column.sql << 'EOF' ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active'; EOF
cat > migrations/001_add_column.sql << 'EOF' ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active'; EOF

Apply migration

应用迁移

psql -f migrations/001_add_column.sql $DATABASE_URL
undefined
psql -f migrations/001_add_column.sql $DATABASE_URL
undefined

Safe Migration Workflow

安全迁移流程

bash
undefined
bash
undefined

1. Test on copy first

1. 先在副本上测试

createdb test_migration pg_dump $DATABASE_URL | psql test_migration
createdb test_migration pg_dump $DATABASE_URL | psql test_migration

2. Run migration on test

2. 在测试环境执行迁移

psql -f migration.sql test_migration
psql -f migration.sql test_migration

3. Verify

3. 验证

psql -c "\d tablename" test_migration
psql -c "\d tablename" test_migration

4. Apply to production

4. 应用到生产环境

psql -f migration.sql $DATABASE_URL
psql -f migration.sql $DATABASE_URL

5. Cleanup

5. 清理

dropdb test_migration
undefined
dropdb test_migration
undefined

Environment Variables

环境变量

Store connection strings securely:
bash
undefined
安全存储连接字符串：
bash
undefined

.env file (don't commit!)

.env文件（不要提交到版本库！）

DATABASE_URL=postgresql://user:pass@host:5432/dbname SQLITE_DB=./data/app.db
DATABASE_URL=postgresql://user:pass@host:5432/dbname SQLITE_DB=./data/app.db

Usage

使用方式

psql $DATABASE_URL sqlite3 $SQLITE_DB
undefined
psql $DATABASE_URL sqlite3 $SQLITE_DB
undefined

Best Practices

最佳实践

  1. Never hardcode credentials - Use environment variables
  2. Review AI-generated queries - Before executing
  3. Use EXPLAIN - Check query performance
  4. Test migrations - On copy before production
  5. Backup before changes - Especially destructive ones
  6. Use transactions - For multi-statement changes
  7. Limit results - Always use LIMIT during exploration
  1. 切勿硬编码凭据 - 使用环境变量
  2. 审核AI生成的查询 - 执行前务必审核
  3. 使用EXPLAIN - 检查查询性能
  4. 测试迁移 - 在生产环境前先在副本上测试
  5. 变更前备份 - 尤其是破坏性变更
  6. 使用事务 - 处理多语句变更时
  7. 限制结果 - 探索数据时始终使用LIMIT