# OctoSQL — Query Multiple Databases and Files Using SQL

> OctoSQL is a CLI tool that lets you join, analyse, and transform data from multiple databases and file formats using standard SQL queries.

## Install

Save in your project root:

# OctoSQL — Query Multiple Databases and Files Using SQL

## Quick Use
```bash
go install github.com/cube2222/octosql/cmd/octosql@latest
# Query a CSV file
octosql "SELECT * FROM ./data.csv WHERE age > 30"
# Join a JSON file with a PostgreSQL table
octosql "SELECT j.name, p.email FROM ./users.json j JOIN pg.users p ON j.id = p.id"
```

## Introduction
OctoSQL lets you run SQL queries across multiple data sources simultaneously. Instead of writing custom scripts to extract and join data from different systems, you write a single SQL query that spans CSV files, JSON documents, PostgreSQL tables, MySQL databases, and Redis stores.

## What OctoSQL Does
- Runs SQL queries against CSV, JSON, Parquet, and Excel files
- Connects to PostgreSQL, MySQL, and Redis as data sources
- Joins data across different sources in a single query
- Supports aggregations, subqueries, and window functions
- Streams results to stdout in table, JSON, or CSV format

## Architecture Overview
OctoSQL parses SQL using a custom query planner that builds a logical plan, optimizes it, and produces a physical execution plan. Each data source implements a plugin interface that handles schema discovery and record iteration. The engine uses a pull-based execution model with support for streaming results from large datasets.

## Self-Hosting & Configuration
- Install via `go install` or download prebuilt binaries for Linux and macOS
- Configure database connections in a YAML config file
- Reference local files directly in queries using file paths
- Set output format with `--output` flag: table, json, or csv
- Use `DESCRIBE` to inspect schemas of any configured source

## Key Features
- Cross-source SQL joins without ETL pipelines
- Automatic schema inference for files (CSV, JSON, Parquet)
- Streaming execution for processing large datasets
- Table, JSON, and CSV output formats
- Plugin architecture for adding new data sources

## Comparison with Similar Tools
- **DuckDB** — In-process analytical database; OctoSQL adds cross-database joins and live database queries
- **q** — Runs SQL on CSV files; OctoSQL supports multiple databases and file formats in one query
- **dsq** — Similar multi-format SQL tool; OctoSQL adds database connectivity and streaming
- **Trino** — Enterprise-grade distributed SQL; OctoSQL is a lightweight single-binary CLI tool

## FAQ
**Q: Does OctoSQL modify the source data?**
A: No. OctoSQL is read-only. It queries data sources but never writes back to them.

**Q: Can I query remote databases?**
A: Yes. Configure PostgreSQL, MySQL, or Redis connection strings in the config file and reference them by alias in queries.

**Q: How does OctoSQL handle schema mismatches across sources?**
A: OctoSQL infers schemas per source and applies SQL type coercion rules when joining. Mismatched columns result in NULL values.

**Q: Is there a size limit for files I can query?**
A: No hard limit. OctoSQL streams records so memory usage stays proportional to the query plan, not the file size.

## Sources
- https://github.com/cube2222/octosql
- https://github.com/cube2222/octosql#usage

---
Source: https://tokrepo.com/en/workflows/asset-542eedaf
Author: AI Open Source