Bob Query Builder CLI Documentation

Overview

The Bob Query Builder CLI provides command-line tools for testing database connections and building SQL queries without executing them. This is useful for development, debugging, and understanding the SQL generated by the query builder.

Installation

The CLI is automatically available after installing the bob-the-builder package:

composer require marwen-brini/bob-the-builder
chmod +x vendor/bin/bob

Available Commands

test-connection

Test a database connection and display available tables.

bob test-connection <driver> [options]

Drivers: mysql, pgsql, sqlite

Options:

• --host=<host> - Database host (default: localhost)
• --port=<port> - Database port (default: driver-specific)
• --database=<db> - Database name
• --username=<user> - Database username
• --password=<pass> - Database password
• --path=<path> - SQLite database path

Examples:

# Test MySQL connection
bob test-connection mysql --host=localhost --database=mydb --username=root --password=secret

# Test SQLite connection
bob test-connection sqlite --path=/path/to/database.db

# Test PostgreSQL connection
bob test-connection pgsql --host=localhost --database=postgres --username=postgres

build

Build and display SQL queries without executing them.

bob build <driver> <query>

Query Syntax:

The query uses a simple colon-separated format:

• select:<columns> - Columns to select (use * for all)
• from:<table> - Table name
• where:<field>,<value> or where:<field>,<operator>,<value>
• orderBy:<column>,<direction>
• limit:<number>
• offset:<number>
• join:<table>,<first>,<second>
• whereIn:<field>,<value1>,<value2>,...
• whereNull:<field>
• groupBy:<column1>,<column2>,...

Examples:

# Simple select query
bob build mysql "select:* from:users where:active,1"

# Complex query with joins
bob build mysql "select:u.name,p.title from:users join:posts,users.id,posts.user_id where:users.active,1 orderBy:created_at,desc limit:10"

# Query with multiple conditions
bob build sqlite "select:id,name,email from:users where:age,>,18 whereNotNull:email orderBy:name,asc"

# Aggregation query
bob build pgsql "select:category,COUNT(*) from:products groupBy:category having:COUNT(*),>,5"

version

Display version information.

bob version

help

Display help information.

bob help

Configuration File

You can create a .bob.json file in your project root to store default connection settings:

{
  "mysql": {
    "host": "localhost",
    "port": 3306,
    "database": "myapp",
    "username": "appuser",
    "password": "secret"
  },
  "pgsql": {
    "host": "localhost",
    "port": 5432,
    "database": "myapp",
    "username": "postgres"
  }
}

Output Format

test-connection Output

Testing mysql connection...
Connection successful!
Database version: 8.0.32

Available tables:
  - users
  - posts
  - comments

build Output

Generated SQL:
SELECT * FROM `users` WHERE `active` = ? ORDER BY `created_at` DESC LIMIT 10

Bindings:
  [0] => 1

Formatted query:
SELECT * FROM `users` WHERE `active` = 1 ORDER BY `created_at` DESC LIMIT 10

Error Handling

The CLI provides colored output for different message types:

• Success (green): Connection successful, query built
• Info (cyan): General information, version details
• Error (red): Connection failures, invalid syntax

Exit Codes

• 0 - Success
• 1 - Error (connection failed, invalid syntax, unknown command)

Development Usage

The CLI is particularly useful during development for:

1. Testing Connections: Verify database credentials before running your application
2. Query Debugging: See the exact SQL generated by your query builder code
3. Learning: Understand how different query builder methods translate to SQL
4. Migration Planning: Test queries before implementing them in code

Integration with CI/CD

The CLI can be used in CI/CD pipelines for database connectivity tests:

# GitHub Actions example
- name: Test Database Connection
  run: |
    ./vendor/bin/bob test-connection mysql \
      --host=${{ secrets.DB_HOST }} \
      --database=${{ secrets.DB_NAME }} \
      --username=${{ secrets.DB_USER }} \
      --password=${{ secrets.DB_PASS }}

Troubleshooting

Command not found

Make sure the bin/bob file is executable:

chmod +x vendor/bin/bob

Autoloader not found

Run composer install:

composer install

Connection refused

Check your database server is running and credentials are correct:

# For MySQL
mysql -h localhost -u root -p

# For PostgreSQL
psql -h localhost -U postgres

# For SQLite - check file exists and has correct permissions
ls -la /path/to/database.db