DBBox

Simple SQLite database utility with CRUD operations via CLI

Part of the Box Suite - a modular Python CLI application framework.

Features

• Simple CRUD operations via command-line flags (-c, -r, -u, -d)
• Schema management with type-safe column definitions
• Cross-platform storage using ConfBox for OS-specific directories
• Pretty-printed output for SELECT queries
• Auto-increment IDs by default
• Zero configuration - just install and use

Installation

pip install dbbox

Or install from source:

git clone https://github.com/jmmirabile/dbbox.git
cd dbbox
pip install -e .

Storage Location

DBBox uses ConfBox to store databases in OS-specific data directories:

OS	Storage Location
Linux	~/.local/share/dbbox/
macOS	~/Library/Application Support/dbbox/
Windows	%APPDATA%\dbbox\

Quick Start

# Create a table with schema
dbbox mydb users --schema name:TEXT age:INTEGER email:TEXT

# Insert data
dbbox mydb users -c 'John Doe' 30 'john@example.com'
dbbox mydb users -c 'Jane Smith' 25 'jane@example.com'

# Read all rows
dbbox mydb users -r

# Read specific row by ID
dbbox mydb users -r 1

# Update a row
dbbox mydb users -u 1 'John Updated' 31 'john.new@example.com'

# Delete a row
dbbox mydb users -d 2

# Show table info
dbbox mydb users --info

# List all tables
dbbox mydb --list

# Show database path
dbbox mydb --path

Listing Databases and Tables

DBBox provides multiple ways to list databases and tables for flexibility:

List All Databases

# Four equivalent ways:
dbbox databases         # Positional command (recommended)
dbbox -l                # Short flag
dbbox --databases       # Explicit flag
dbbox --list            # Long flag alias

List Tables in a Database

# Four equivalent ways:
dbbox mydb tables       # Positional command (recommended)
dbbox mydb -l           # Short flag
dbbox mydb --tables     # Explicit flag
dbbox mydb --list       # Long flag alias

The -l/--list flag is context-aware:

• Without database name: lists databases
• With database name: lists tables

Usage

Create Table

dbbox <dbname> <table> --schema <col:type> <col:type> ...

Example:

dbbox mydb products --schema name:TEXT price:REAL stock:INTEGER

Note: An id INTEGER PRIMARY KEY AUTOINCREMENT column is added automatically.

Supported SQLite types: TEXT, INTEGER, REAL, BLOB, NUMERIC

Insert (Create)

dbbox <dbname> <table> -c <value1> <value2> ...

Example:

dbbox mydb products -c 'Widget' 19.99 100

Select (Read)

# Read all rows
dbbox <dbname> <table> -r

# Read specific row by ID
dbbox <dbname> <table> -r <id>

Examples:

dbbox mydb products -r           # All rows
dbbox mydb products -r 5         # Row with id=5

Update

dbbox <dbname> <table> -u <id> <value1> <value2> ...

Example:

dbbox mydb products -u 5 'Super Widget' 24.99 150

Delete

dbbox <dbname> <table> -d <id>

Example:

dbbox mydb products -d 5

Drop Table

dbbox <dbname> <table> --drop-table

Example:

dbbox mydb products --drop-table
# Prompts for confirmation before dropping

Warning: This permanently deletes the table and all its data. Cannot be undone.

Drop Database

dbbox <dbname> --drop-database

Example:

dbbox mydb --drop-database
# Prompts for confirmation before dropping

Warning: This permanently deletes the entire database file. Cannot be undone.

Database Management

# List all tables in a database
dbbox <dbname> --list

# Show table schema/info
dbbox <dbname> <table> --info

# Show database file path
dbbox <dbname> --path

# Drop a table (with confirmation)
dbbox <dbname> <table> --drop-table

# Drop entire database (with confirmation)
dbbox <dbname> --drop-database

Complete Example

# Create a contacts database
dbbox contacts people --schema name:TEXT phone:TEXT email:TEXT

# Add some contacts
dbbox contacts people -c 'Alice Smith' '555-1234' 'alice@example.com'
dbbox contacts people -c 'Bob Jones' '555-5678' 'bob@example.com'

# View all contacts
dbbox contacts people -r

# Output:
# id              | name            | phone           | email
# --------------------------------------------------------------------------
# 1               | Alice Smith     | 555-1234        | alice@example.com
# 2               | Bob Jones       | 555-5678        | bob@example.com
#
# 2 row(s) returned

# Update a contact
dbbox contacts people -u 2 'Robert Jones' '555-5678' 'robert@example.com'

# Delete a contact
dbbox contacts people -d 1

# Show table structure
dbbox contacts people --info

# List all tables
dbbox contacts --list

# Drop a table (with confirmation prompt)
dbbox contacts people --drop-table

# Drop entire database (with confirmation prompt)
dbbox contacts --drop-database

Use Cases

• Quick data storage for scripts and automation
• Prototyping database schemas
• Data tracking for personal projects
• Testing SQL queries and table designs
• Simple CRUD apps without writing database code
• Local caching of data

The Box Suite

DBBox is part of the Box Suite - a collection of modular Python packages for building CLI applications:

• ConfBox ✅ - Cross-platform configuration management
• DBBox ✅ - SQLite database utility (this package)
• PlugBox 🔨 - Plugin system (planned)
• CLIBox 🔨 - Complete CLI framework (planned)
• RestBox 🔨 - REST API testing tool (planned)

Each package can be used independently or together for maximum flexibility.

Python API

DBBox can also be used as a Python library:

from dbbox import DBManager

# Create database manager
with DBManager("mydb") as db:
    # Create table
    db.create_table("users", ["name:TEXT", "age:INTEGER"])

    # Insert data
    user_id = db.insert("users", ["John Doe", 30])

    # Select data
    rows = db.select("users")
    for row in rows:
        print(dict(row))

    # Update data
    db.update("users", user_id, ["Jane Doe", 31])

    # Delete data
    db.delete("users", user_id)

Development

Install development dependencies:

pip install -e ".[dev]"

Run tests:

pytest

Requirements

• Python 3.8+
• confbox

License

MIT License - see LICENSE file for details.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Links

• PyPI: https://pypi.org/project/dbbox/ (coming soon)
• GitHub: https://github.com/jmmirabile/dbbox
• ConfBox: https://github.com/jmmirabile/confbox
• Box Suite Design: See ConfBox repository for the full Box Suite vision