dbcli

Automatically convert SQL query results to JSON without struct mapping.

Features

• Schema-free — no struct definitions or #[derive] needed; works directly on raw query rows
• Multi-database — supports MySQL, PostgreSQL, and SQLite via feature flags (all enabled by default), plus ODBC for Access (.mdb/.accdb) and Excel (.xls/.xlsx)
• CLI tool — interactive REPL mode with SQL history, colored table output, and JSON export
• Vector support — pgvector (vector, halfvec, sparsevec), MySQL HeatWave / MySQL 9.0+, sqlite-vec
• Customizable — override type conversion logic per-column with the ToJsonCustomizer trait
• Performant — function pointer array built once per query; zero overhead per row during iteration
• ODBC support — query Microsoft Access (.mdb/.accdb), Excel (.xls/.xlsx), and any ODBC-compatible data source; auto-detects driver from file extension

Installation

By default, all features are enabled (CLI + all databases):

[dependencies]
# Default: all features enabled (CLI + all databases)
dbcli = "0.1"

# Or select specific databases (as library, without CLI)
dbcli = { version = "0.1", default-features = false, features = ["postgres"] }

Each feature automatically pulls in the corresponding sqlx driver and auxiliary crates:

Note: The odbc feature is not included in the default feature set because it requires the system-level unixODBC library. Enable it explicitly when needed.

Quick Start

PostgreSQL

use sqlx::PgPool;
use dbcli::to_json::postgres::to_json;

async fn query_to_json(pool: &PgPool) -> anyhow::Result<()> {
    let rows = sqlx::query("SELECT id, name, created_at FROM users")
        .fetch_all(pool)
        .await?;

    // data    — Vec<serde_json::Value>, one JSON object per row
    // columns — Vec<ColumnBaseInfo>, column name / type / index metadata
    let (data, columns) = to_json(rows)?;

    println!("{}", serde_json::to_string_pretty(&data)?);
    Ok(())
}

MySQL

use sqlx::MySqlPool;
use dbcli::to_json::mysql::to_json;

async fn query_to_json(pool: &MySqlPool) -> anyhow::Result<()> {
    let rows = sqlx::query("SELECT id, name, created_at FROM orders")
        .fetch_all(pool)
        .await?;

    let (data, _columns) = to_json(rows)?;
    println!("{}", serde_json::to_string_pretty(&data)?);
    Ok(())
}

SQLite

use sqlx::SqlitePool;
use dbcli::to_json::sqlite::to_json;

async fn query_to_json(pool: &SqlitePool) -> anyhow::Result<()> {
    let rows = sqlx::query("SELECT id, title FROM notes")
        .fetch_all(pool)
        .await?;

    let (data, _columns) = to_json(rows)?;
    println!("{}", serde_json::to_string_pretty(&data)?);
    Ok(())
}

ODBC (Access / Excel)

use dbcli::to_json::odbc::to_json;
use odbc_api::{Environment, ConnectionOptions};

fn query_access() -> anyhow::Result<()> {
    let env = Environment::new()?;
    let conn = env.connect_with_connection_string(
        r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\data\mydb.mdb;",
        ConnectionOptions::default(),
    )?;

    if let Some(mut cursor) = conn.execute("SELECT * FROM Users", (), None)? {
        let (data, columns) = to_json(&mut cursor)?;
        println!("{}", serde_json::to_string_pretty(&data)?);
    }
    Ok(())
}

ODBC is synchronous; when used in an async context, execute_raw_sql wraps calls in tokio::task::spawn_blocking automatically.

Example output:

[
  { "id": 1, "name": "Alice", "created_at": "2024-01-15 09:30:00" },
  { "id": 2, "name": "Bob",   "created_at": "2024-03-22 14:05:11" }
]

CLI Tool

When built with the cli feature (included by default), dbcli provides a standalone command-line tool for interactive SQL querying.

Build & Install

# Build with all databases (default)
cargo build -p dbcli --release

# Or select specific databases
cargo build -p dbcli --release --no-default-features --features "cli,postgres"

# Build with ODBC support
cargo build -p dbcli --release --no-default-features --features "cli,odbc"

# Build with ODBC + PostgreSQL
cargo build -p dbcli --release --no-default-features --features "cli,odbc,postgres"

Usage

# Single query
dbcli -u postgres://user:pass@host:5432/db --sql "SELECT * FROM users"

# Interactive REPL mode (omit --sql)
dbcli -u postgres://user:pass@host:5432/db

# Use environment variable
export DATABASE_URL=postgres://user:pass@host:5432/db
dbcli

# JSON output (pipe-friendly)
dbcli -u ... --sql "SELECT * FROM users" --json

# Limit rows in table mode (default: 1000)
dbcli -u ... --sql "SELECT * FROM large_table" -l 100

# Test connection only
dbcli -u postgres://user:pass@host:5432/db --connect

# Query Access database via ODBC
dbcli -u "odbc:///path/to/file.mdb" --sql "SELECT * FROM Users"

# Query Excel spreadsheet via ODBC
dbcli -u "odbc:///path/to/file.xlsx" --sql "SELECT * FROM [Sheet1$]"

# Direct ODBC connection string
dbcli -u "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=/path/to/file.mdb" --sql "SELECT * FROM Users"

REPL Mode

When no --sql argument is provided, dbcli enters interactive REPL mode:

• Multi-line SQL input (terminate with ;)
• Arrow-key history navigation (persisted to ~/.db-json_history)
• Type exit, quit, or \q to leave
• Ctrl+C clears the current multi-line buffer; Ctrl+D exits immediately

Parameters

Output Modes

• Terminal (TTY): Colored table with UTF-8 borders, auto-adapted to terminal width
• Pipe / redirect: JSON array output for programmatic use
• Override with --json or --table flags

Custom Type Parsing

Implement the ToJsonCustomizer trait to override the default conversion for any column type. Register it once at application startup via set_to_json_customizer.

use dbcli::to_json::{ToJsonCustomizer, set_to_json_customizer};

struct MyCustomizer;

impl ToJsonCustomizer for MyCustomizer {
    /// Override MySQL DATETIME format to ISO 8601.
    #[cfg(feature = "mysql")]
    fn customize_mysql(
        &self,
        type_name: &str,
    ) -> Option<fn(&sqlx::mysql::MySqlRow, usize) -> serde_json::Value> {
        match type_name {
            "DATETIME" => Some(|row, idx| {
                use sqlx::Row;
                use chrono::NaiveDateTime;
                match row.try_get::<Option<NaiveDateTime>, _>(idx) {
                    Ok(Some(dt)) => serde_json::Value::String(
                        dt.format("%Y-%m-%dT%H:%M:%S").to_string(),
                    ),
                    _ => serde_json::Value::Null,
                }
            }),
            _ => None,
        }
    }

    /// Override PostgreSQL TIMESTAMP format.
    #[cfg(feature = "postgres")]
    fn customize_pg(
        &self,
        type_name: &str,
    ) -> Option<fn(&sqlx::postgres::PgRow, usize) -> serde_json::Value> {
        match type_name {
            "TIMESTAMP" => Some(|row, idx| {
                use sqlx::Row;
                use chrono::NaiveDateTime;
                match row.try_get::<Option<NaiveDateTime>, _>(idx) {
                    Ok(Some(dt)) => serde_json::Value::String(
                        dt.format("%Y-%m-%dT%H:%M:%S").to_string(),
                    ),
                    _ => serde_json::Value::Null,
                }
            }),
            _ => None,
        }
    }
}

fn main() {
    // Register once — subsequent calls are silently ignored (no panic, no overwrite)
    set_to_json_customizer(Box::new(MyCustomizer));
}

How it works:

1. determine_parsing_methods calls customize_mysql / customize_pg / customize_sqlite once per column when the first row is processed.
2. The returned function pointer (or the built-in default) is cached in a Vec.
3. All subsequent rows call the cached pointer directly — no trait dispatch, no branch per row.

Implement only the database methods you need; the other two default to built-in behavior.

Type Mapping Reference

All type_name values passed to ToJsonCustomizer are UPPERCASE strings.

MySQL

PostgreSQL

Raw PG type names are normalized to uppercase by detect_pg_type.

SQLite

ODBC

Type mapping is based on the odbc_api::DataType enum. Column names from sources without headers (e.g., Excel without HDR) are auto-generated as col_1, col_2, etc. Duplicate names are disambiguated with _2, _3 suffixes.

API Reference

Core functions

Types

Full documentation is available on docs.rs/dbcli.

License

MIT