inspect-schema Command

View and analyze database schema structure using the Akron CLI across SQLite, MySQL, PostgreSQL, and MongoDB.

Overview

The inspect-schema command provides detailed information about your database structure, including tables, columns, data types, constraints, and indexes. Essential for understanding existing databases and planning migrations.

Basic Syntax

akron inspect-schema --db <database_url> [options]

Parameters

--db

Type: str (required)

Database connection URL to inspect.

--table

Type: str (optional)

Inspect specific table only instead of entire database.

--tables-only

Type: flag (optional)

Show only table names without column details.

--format

Type: str (optional)

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

Examples

Full Database Inspection

1# Inspect entire SQLite database
2akron inspect-schema --db "sqlite:///myapp.db"
Expected Output
Database Schema: myapp.db
================================

Table: users
+------------+----------+---------+------------+
| Column     | Type     | Null    | Default    |
+------------+----------+---------+------------+
| id         | INTEGER  | NO      | NULL       |
| username   | TEXT     | NO      | NULL       |
| email      | TEXT     | YES     | NULL       |
| created_at | DATETIME | YES     | CURRENT_TS |
+------------+----------+---------+------------+

Table: posts
+------------+----------+---------+------------+
| Column     | Type     | Null    | Default    |
+------------+----------+---------+------------+
| id         | INTEGER  | NO      | NULL       |
| user_id    | INTEGER  | NO      | NULL       |
| title      | TEXT     | NO      | NULL       |
| content    | TEXT     | YES     | NULL       |
+------------+----------+---------+------------+

Total Tables: 2

Specific Table Inspection

1# Inspect specific table
2akron inspect-schema --db "mysql://user:pass@host:3306/store" --table products
Expected Output
Table: products
+---------------+---------------+---------+-------------+
| Column        | Type          | Null    | Default     |
+---------------+---------------+---------+-------------+
| id            | INT           | NO      | AUTO_INC    |
| sku           | VARCHAR(50)   | NO      | NULL        |
| name          | VARCHAR(255)  | NO      | NULL        |
| description   | TEXT          | YES     | NULL        |
| price         | DECIMAL(10,2) | NO      | 0.00        |
| category_id   | INT           | YES     | NULL        |
| in_stock      | BOOLEAN       | NO      | TRUE        |
| created_at    | TIMESTAMP     | NO      | CURRENT_TS  |
+---------------+---------------+---------+-------------+

Indexes:
- PRIMARY KEY (id)
- UNIQUE KEY sku_unique (sku)
- KEY category_idx (category_id)

Foreign Keys:
- category_id → categories(id)

Tables Only View

1# List table names only
2akron inspect-schema --db "postgres://user:pass@host:5432/app" --tables-only
Expected Output
Tables in database 'app':
- users
- posts  
- comments
- categories
- tags
- post_tags

Total: 6 tables

JSON Output Format

1# Export schema as JSON
2akron inspect-schema --db "mongodb://localhost:27017/social" --format json
Expected Output
{
  "database": "social",
  "collections": [
    {
      "name": "users",
      "schema": {
        "_id": "ObjectId",
        "username": "String",
        "email": "String", 
        "profile": "Object",
        "created_at": "Date"
      },
      "indexes": ["_id_", "username_1", "email_1"]
    },
    {
      "name": "posts",
      "schema": {
        "_id": "ObjectId",
        "user_id": "ObjectId",
        "content": "String",
        "likes": "Array",
        "created_at": "Date"
      },
      "indexes": ["_id_", "user_id_1"]
    }
  ]
}

Database-Specific Features

SQLite Details

1# SQLite shows detailed table info
2akron inspect-schema --db "sqlite:///app.db" --table users

Shows: column types, constraints, default values, primary keys

MySQL/PostgreSQL Details

1# Shows comprehensive schema info
2akron inspect-schema --db "mysql://user:pass@host:3306/db" --table orders

Shows: indexes, foreign keys, constraints, auto-increment settings

MongoDB Collections

1# MongoDB shows document structure analysis
2akron inspect-schema --db "mongodb://localhost:27017/app" --table users

Shows: inferred schema from documents, data types, nested structures

Use Cases

Development Workflow

1# Check current schema before making changes
2akron inspect-schema --db "sqlite:///dev.db"
3
4# Create new table based on existing structure
5akron create-table new_users --db "sqlite:///dev.db" --schema '{"id": "int", "name": "str"}'
6
7# Verify the new table
8akron inspect-schema --db "sqlite:///dev.db" --table new_users

Database Migration Planning

1# Document current production schema
2akron inspect-schema --db "postgres://prod" --format json > current_schema.json
3
4# Compare with development schema
5akron inspect-schema --db "postgres://dev" --format json > dev_schema.json
6
7# Plan migrations based on differences

Data Analysis

1# Quick overview of available data
2akron inspect-schema --db "mysql://analytics" --tables-only
3
4# Detailed structure for specific analysis
5akron inspect-schema --db "mysql://analytics" --table user_events

Error Handling

Common Errors

  • Connection failed: Verify database URL and credentials
  • Database not found: Ensure database exists
  • Table not found: Check table name spelling
  • Permission denied: Ensure user has read access

Troubleshooting

1# Test connection first
2akron inspect-schema --db "sqlite:///test.db" --tables-only
3
4# Check if specific table exists
5akron inspect-schema --db "mysql://user:pass@host:3306/db" --tables-only | grep "table_name"

Related Commands

Schema Management

→ create-table command→ drop-table command

Data Operations

→ raw-sql command→ seed command

Migrations

→ makemigrations command→ migrate command