raw-sql Command

Execute custom SQL queries using the Akron CLI across SQLite, MySQL, PostgreSQL, and MongoDB.

Overview

The raw-sql command allows you to execute custom SQL queries directly against your database. Perfect for data analysis, debugging, and one-off database operations.

Basic Syntax

akron raw-sql --db <database_url> --query <sql_query>

⚠️ Safety Warning

This command executes SQL directly against your database. Be extremely careful with UPDATE, DELETE, and DROP statements, especially on production databases.

Parameters

--db

Type: str (required)

Database connection URL to execute query against.

--query

Type: str (required)

SQL query to execute. Use quotes for multi-word queries.

--file

Type: str (optional)

Path to SQL file to execute (alternative to --query).

--format

Type: str (optional)

Output format: "table", "json", or "csv". Default: "table"

--output

Type: str (optional)

Save results to file instead of displaying in terminal.

Examples

Basic SELECT Queries

1# Simple SELECT query
2akron raw-sql --db "sqlite:///myapp.db" --query "SELECT * FROM users LIMIT 5"
3
4# Query with WHERE clause
5akron raw-sql --db "mysql://user:pass@host:3306/store" --query "SELECT name, price FROM products WHERE price > 50"
Expected Output
Query Results:
==============
+----+----------+-------------------+
| id | username | email             |
+----+----------+-------------------+
| 1  | admin    | admin@example.com |
| 2  | john_doe | john@example.com  |
| 3  | jane_s   | jane@example.com  |
+----+----------+-------------------+

3 rows returned in 0.002 seconds

Data Analysis Queries

1# Count records by category
2akron raw-sql --db "postgres://user:pass@host:5432/ecommerce" --query "
3 SELECT category, COUNT(*) as product_count, AVG(price) as avg_price
4 FROM products
5 GROUP BY category
6 ORDER BY product_count DESC
7"
Expected Output
Query Results:
==============
+-------------+---------------+-----------+
| category    | product_count | avg_price |
+-------------+---------------+-----------+
| Electronics | 150           | 249.99    |
| Clothing    | 89            | 45.50     |
| Books       | 45            | 19.99     |
+-------------+---------------+-----------+

3 rows returned in 0.045 seconds

Execute from File

1# Create SQL file
2echo "
3SELECT
4 u.username,
5 COUNT(p.id) as post_count,
6 MAX(p.created_at) as last_post
7FROM users u
8LEFT JOIN posts p ON u.id = p.user_id
9GROUP BY u.id, u.username
10ORDER BY post_count DESC;
11" > user_stats.sql
12
13# Execute file
14akron raw-sql --db "sqlite:///blog.db" --file user_stats.sql
Expected Output
Query Results from user_stats.sql:
===================================
+----------+------------+---------------------+
| username | post_count | last_post           |
+----------+------------+---------------------+
| alice    | 25         | 2024-01-15 14:30:00 |
| bob      | 18         | 2024-01-14 09:15:00 |
| charlie  | 12         | 2024-01-13 16:45:00 |
+----------+------------+---------------------+

3 rows returned in 0.012 seconds

Different Output Formats

1# JSON output
2akron raw-sql --db "mongodb://localhost:27017/social" --query "
3 db.users.find({}, {username: 1, email: 1})
4" --format json
5
6# CSV output for spreadsheet import
7akron raw-sql --db "mysql://user:pass@host:3306/sales" --query "
8 SELECT date, product_name, quantity, revenue FROM sales_report
9" --format csv --output sales_data.csv
Expected Output
JSON Output:
============
[
  {
    "_id": "507f1f77bcf86cd799439011",
    "username": "alice",
    "email": "alice@example.com"
  },
  {
    "_id": "507f1f77bcf86cd799439012", 
    "username": "bob",
    "email": "bob@example.com"
  }
]

CSV saved to: sales_data.csv

Database-Specific Queries

SQLite System Queries

1# List all tables
2akron raw-sql --db "sqlite:///app.db" --query "SELECT name FROM sqlite_master WHERE type='table'"
3
4# Check database size
5akron raw-sql --db "sqlite:///app.db" --query "SELECT page_count * page_size as size_bytes FROM pragma_page_count(), pragma_page_size()"
6
7# Analyze table statistics
8akron raw-sql --db "sqlite:///app.db" --query "SELECT * FROM sqlite_stat1"

PostgreSQL System Queries

1# Table sizes
2akron raw-sql --db "postgres://user:pass@host:5432/db" --query "
3 SELECT
4 tablename,
5 pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS size
6 FROM pg_tables
7 WHERE schemaname = 'public'
8"
9
10# Active connections
11akron raw-sql --db "postgres://user:pass@host:5432/db" --query "
12 SELECT datname, usename, client_addr, state
13 FROM pg_stat_activity
14 WHERE state = 'active'
15"

MongoDB Queries

1# Collection statistics
2akron raw-sql --db "mongodb://localhost:27017/app" --query "
3 db.stats()
4"
5
6# Find documents with aggregation
7akron raw-sql --db "mongodb://localhost:27017/social" --query "
8 db.posts.aggregate([
9 {$group: {_id: '$user_id', post_count: {$sum: 1}}},
10 {$sort: {post_count: -1}},
11 {$limit: 10}
12 ])
13"

Useful Query Patterns

Data Verification

1# Check for duplicate emails
2akron raw-sql --db "sqlite:///app.db" --query "
3 SELECT email, COUNT(*) as count
4 FROM users
5 GROUP BY email
6 HAVING COUNT(*) > 1
7"
8
9# Find orphaned records
10akron raw-sql --db "mysql://user:pass@host:3306/db" --query "
11 SELECT p.*
12 FROM posts p
13 LEFT JOIN users u ON p.user_id = u.id
14 WHERE u.id IS NULL
15"

Performance Analysis

1# Slow query analysis (PostgreSQL)
2akron raw-sql --db "postgres://user:pass@host:5432/db" --query "
3 SELECT query, mean_time, calls, total_time
4 FROM pg_stat_statements
5 ORDER BY mean_time DESC
6 LIMIT 10
7"
8
9# Index usage (MySQL)
10akron raw-sql --db "mysql://user:pass@host:3306/db" --query "
11 SELECT
12 table_name,
13 index_name,
14 seq_in_index,
15 column_name
16 FROM information_schema.statistics
17 WHERE table_schema = 'your_database'
18"

Safety and Best Practices

Dangerous Operations

  • Always backup before: DELETE, UPDATE, DROP, ALTER operations
  • Use transactions: Wrap multiple statements in BEGIN/COMMIT
  • Test on development: Never run untested queries on production
  • Use WHERE clauses: Avoid accidental full-table operations

Safe Practices

1# Count before deleting
2akron raw-sql --db "sqlite:///app.db" --query "SELECT COUNT(*) FROM old_records WHERE created_at < '2023-01-01'"
3
4# Use LIMIT for testing
5akron raw-sql --db "sqlite:///app.db" --query "UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01' LIMIT 1"
6
7# Check affected rows
8akron raw-sql --db "mysql://user:pass@host:3306/db" --query "SELECT ROW_COUNT() as affected_rows"

Error Handling

Common Errors

  • Syntax error: Check SQL syntax for target database
  • Table/column not found: Verify names with inspect-schema
  • Permission denied: Ensure user has required privileges
  • Connection timeout: Check database availability and network

Troubleshooting

1# Test connection first
2akron raw-sql --db "sqlite:///app.db" --query "SELECT 1"
3
4# Check table existence
5akron inspect-schema --db "sqlite:///app.db" --tables-only
6
7# Validate SQL syntax on smaller dataset
8akron raw-sql --db "sqlite:///app.db" --query "SELECT * FROM users LIMIT 1"

Related Commands

Schema Inspection

→ inspect-schema command→ find() API method

Data Operations

→ seed command→ insert() API method

Migrations

→ migrate command→ makemigrations command