OctoSQL — Query Multiple Databases and Files Using SQL

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