filesql

filesql loads files into an in-memory SQLite database. Open CSV, TSV, LTSV, JSON, JSONL, Parquet, XLSX, ACH, or Fedwire inputs, then query them with normal SQLite syntax.

The same module also includes two companion packages for work that usually happens before or after SQL:

• prep cleans and validates rows before they become tables.
• frame handles small in-memory transforms in plain Go.

sqly is the shell built on the same core.

Why filesql?

filesql is for cases where the data is already in a file and the fastest useful tool is SQL.

• Open files as tables without setting up a server.
• Join across CSV, TSV, LTSV, JSON, JSONL, Parquet, XLSX, ACH, and Fedwire.
• Keep edits in memory until you decide to save them.
• Clean inputs with prep before loading them.
• Reshape small datasets with frame when SQL is not the right fit.

Features

• Query file data with standard SQLite syntax, including joins, CTEs, and json_extract().
• Optionally query with MySQL, PostgreSQL, or GoogleSQL syntax via WithDialect (translated to SQLite).
• Read from file paths, directories, io.Reader, and embed.FS.
• Handle compressed CSV, TSV, LTSV, JSON, JSONL, Parquet, and XLSX files transparently.
• Load into a new in-memory database or into a *sql.DB you already manage.
• Save changes with DumpDatabase, EnableAutoSave, or EnableAutoSaveOnCommit.
• Stay in one module for loading (filesql), cleanup (prep), and lightweight transforms (frame).

Supported File Formats

Extension	Format	Notes
.csv	CSV	Header row becomes column names
.tsv	TSV	Tab-separated text
.ltsv	LTSV	Labeled tab-separated text
.json	JSON	Query nested data with json_extract()
.jsonl	JSONL	One JSON value per line
.parquet	Parquet	Columnar format
.xlsx	Excel XLSX	One sheet becomes one table, named file_sheet (just file when the sheet repeats it)
.ach	ACH (NACHA)	Experimental
.fed	Fedwire	Experimental

Compressed wrappers are supported for CSV, TSV, LTSV, JSON, JSONL, Parquet, and XLSX: .gz, .bz2, .xz, .zst, .z, .snappy, .s2, .lz4.

ACH and Fedwire do not use external compression wrappers.

Installation

go get github.com/nao1215/filesql

Requirements:

• Go 1.25 or later
• Linux, macOS, or Windows

Quick Start

For a one-off load, filesql.Open is fine. OpenContext is the better default when you already have a context or want a timeout.

Query files with SQLite

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/nao1215/filesql"
)

func main() {
	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	defer cancel()

	db, err := filesql.OpenContext(ctx, "users.csv", "orders.jsonl")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	rows, err := db.QueryContext(ctx, `
		SELECT
			u.name,
			COUNT(*) AS order_count
		FROM users u
		JOIN orders o
			ON u.id = json_extract(o.data, '$.user_id')
		GROUP BY u.name
		ORDER BY order_count DESC, u.name
	`)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var name string
		var orderCount int
		if err := rows.Scan(&name, &orderCount); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("%s: %d\n", name, orderCount)
	}
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
}

Query with another SQL dialect

By default queries use SQLite syntax. WithDialect lets you write queries in MySQL, PostgreSQL, or GoogleSQL (BigQuery / Cloud Spanner) instead; filesql translates them to SQLite before running. Loading files always uses SQLite, so only the queries you write are affected.

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/nao1215/filesql"
	"github.com/nao1215/filesql/dialect"
)

func main() {
	ctx := context.Background()

	builder, err := filesql.NewBuilder().
		AddPath("users.csv").
		WithDialect(dialect.PostgreSQL).
		Build(ctx)
	if err != nil {
		log.Fatal(err)
	}
	db, err := builder.Open(ctx)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// PostgreSQL syntax: "::" cast and ILIKE.
	rows, err := db.QueryContext(ctx,
		"SELECT name, age::text FROM users WHERE name ILIKE 'a%'")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	// ...
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}
	fmt.Println("ok")
}

Translation is best-effort compatibility, not a full emulator: common incompatibilities (identifier quoting, DATE_ADD, SPLIT_PART, SAFE_DIVIDE, EXTRACT, casts, …) are rewritten or backed by helper functions, constructs with no SQLite equivalent (for example QUALIFY or DISTINCT ON) return a clear error, and anything else is passed through to SQLite. A non-SQLite dialect cannot be combined with auto-save. See the dialect package for the full list of supported translations.

Load into a database you already own

package main

import (
	"context"
	"database/sql"
	"log"

	"github.com/nao1215/filesql"
	_ "modernc.org/sqlite"
)

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// A plain ":memory:" database is private per connection.
	db.SetMaxOpenConns(1)

	if err := filesql.LoadInto(context.Background(), db, "users.csv", "payments.parquet"); err != nil {
		log.Fatal(err)
	}
}

Clean rows before loading with prep

Use prep when the file needs normalization before it becomes a table: trimming, case normalization, defaults, and validation errors with row numbers.

package main

import (
	"context"
	"fmt"
	"io"
	"log"
	"strings"

	"github.com/nao1215/filesql"
	"github.com/nao1215/filesql/prep"
)

type User struct {
	Name  string `prep:"trim" validate:"required"`
	Email string `prep:"trim,lowercase" validate:"required,email"`
	Role  string `prep:"trim,uppercase" validate:"required,oneof=ADMIN USER"`
}

func main() {
	csvData := `name,email,role
  Alice  ,ALICE@EXAMPLE.COM, admin
Bob,bob@example.com,user
`

	processor := prep.NewProcessor(prep.FileTypeCSV)
	var users []User

	reader, result, err := processor.Process(strings.NewReader(csvData), &users)
	if err != nil {
		log.Fatal(err)
	}
	if result.HasErrors() {
		log.Fatal(result.ValidationErrors())
	}

	fmt.Println(users[0].Name, users[0].Email, users[0].Role)

	cleaned, err := io.ReadAll(reader)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Print(string(cleaned))

	ctx := context.Background()
	validatedBuilder, err := filesql.NewBuilder().
		AddReader(strings.NewReader(string(cleaned)), "users", filesql.FileTypeCSV).
		Build(ctx)
	if err != nil {
		log.Fatal(err)
	}

	db, err := validatedBuilder.Open(ctx)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
}

Transform small datasets with frame

Use frame when the data should stay in Go values instead of SQL: filter rows, add calculated columns, and aggregate small or medium datasets in memory.

package main

import (
	"fmt"
	"log"
	"strings"

	"github.com/nao1215/filesql/frame"
)

func main() {
	csvData := `region,product,qty,price
north,apple,2,100
south,apple,1,100
north,orange,3,80
north,apple,1,100
`

	df, err := frame.NewDataFrame(strings.NewReader(csvData), frame.CSV)
	if err != nil {
		log.Fatal(err)
	}

	sales := df.Mutate("revenue", func(row map[string]any) any {
		qty, _ := row["qty"].(int64)
		price, _ := row["price"].(int64)
		return qty * price
	})

	northOnly := sales.Filter(func(row map[string]any) bool {
		region, _ := row["region"].(string)
		return region == "north"
	})

	grouped, err := northOnly.GroupBy("product")
	if err != nil {
		log.Fatal(err)
	}

	summary, err := grouped.Sum("revenue")
	if err != nil {
		log.Fatal(err)
	}

	for _, row := range summary.ToRecords() {
		fmt.Printf("%s: %.0f\n", row["product"], row["sum_revenue"])
	}
}

Important Notes

Memory and streaming

filesql loads data into an in-memory SQLite database. CSV, TSV, and JSON arrays are read in chunks while loading. LTSV, non-array JSON/JSONL values, Parquet, XLSX, ACH, and Fedwire are read in full before they are turned into rows.

Use SetDefaultChunkSize on the builder when you need to tune chunked loading:

validatedBuilder, err := filesql.NewBuilder().
	AddPath("large.csv").
	SetDefaultChunkSize(5000).
	Build(ctx)

The final memory cost is still dominated by the size of the in-memory SQLite database. Chunking reduces loader overhead; it does not make a large dataset free.

Concurrency

The *sql.DB returned by Open and OpenContext is safe to share across goroutines. filesql uses a shared-cache in-memory SQLite database so pooled connections can see the same tables.

LoadInto is different: you own the database and pool settings there. If you use sql.Open("sqlite", ":memory:"), keep SetMaxOpenConns(1) so every query hits the same in-memory database.

Saving changes

Changes live in memory until you save them.

• DumpDatabase writes the current database out to files when you want an explicit export step.
• EnableAutoSave saves when db.Close() runs.
• EnableAutoSaveOnCommit saves after each committed transaction.

ACH and Fedwire

ACH (.ach) and Fedwire (.fed) support are experimental. They are useful for inspection, joins, and controlled updates, but the exported files still need domain knowledge from the caller.

Examples

API example index

The GoDoc examples are fully tested with go test. The tables below show the fastest path from a feature name in the README to the exact example function in the repo.

filesql

Feature	Example function	Source
Open files and query them	ExampleOpen, ExampleOpenContext	example_api_test.go, example_test.go
Load files into an existing *sql.DB	ExampleLoadInto, ExampleDBBuilder_LoadInto	example_api_test.go
Build from readers, paths, or embedded FS	ExampleNewBuilder, ExampleDBBuilder_AddReader, ExampleDBBuilder_AddPath, ExampleDBBuilder_AddFS	example_test.go
Tune chunked loading	ExampleDBBuilder_SetDefaultChunkSize	example_api_test.go
Handle malformed CSV/TSV rows	ExampleDBBuilder_WithMalformedRowPolicy	example_api_test.go
Attach your own logger	ExampleDBBuilder_WithLogger, ExampleNewSlogAdapter	example_api_test.go
Open a read-only wrapper	ExampleDBBuilder_OpenReadOnly	example_api_test.go
Save on close or commit	ExampleDBBuilder_EnableAutoSave, ExampleDBBuilder_EnableAutoSaveOnCommit, ExampleDBBuilder_DisableAutoSave	example_api_test.go, example_test.go
Export tables with format/compression options	ExampleDumpDatabase, ExampleNewDumpOptions, ExampleDumpOptions_WithFormat, ExampleDumpOptions_WithCompression	example_api_test.go, example_test.go
Work with compression helpers directly	ExampleNewCompressionHandler, ExampleNewCompressionFactory, ExampleCompressionFactory_DetectCompressionType	example_api_test.go
Strip compression suffixes and inspect file types	ExampleCompressionFactory_RemoveCompressionExtension, ExampleCompressionFactory_GetBaseFileType	example_api_test.go
Build contextual errors	ExampleNewErrorContext, ExampleMalformedRowPolicy_String	example_api_test.go

prep

Feature	Example function	Source
Strict tag parsing for invalid prep/validate tags	ExampleWithStrictTagParsing	prep/example_api_test.go
Keep only valid rows in the output stream	ExampleWithValidRowsOnly	prep/example_api_test.go
Clean CSV data into structs and a reusable reader	ExampleProcessor_Process	prep/example_api_test.go
Convert JSON arrays into JSONL output	ExampleProcessor_Process_json	prep/example_api_test.go
Stream cleaned output into any writer	ExampleProcessor_ProcessToWriter	prep/example_api_test.go
Inspect validation counts	ExampleProcessResult_InvalidRowCount, ExampleProcessResult_HasErrors	prep/example_api_test.go
Read validation error details	ExampleProcessResult_ValidationErrors	prep/example_api_test.go
Read preprocessing error details	ExampleProcessResult_PrepErrors	prep/example_api_test.go
Detect compressed inputs	ExampleIsCompressed, Example_detectFileType	prep/example_api_test.go, prep/example_test.go
Check output and original formats	ExampleStream_Format, ExampleStream_OriginalFormat	prep/example_api_test.go
Rewind and reread the processed stream	Example_streamLen, Example_streamSeek	prep/example_api_test.go

frame

Feature	Example function	Source
Create a DataFrame from a reader or path	ExampleNewDataFrame, ExampleNewDataFrameFromPath	frame/example_api_test.go
Build a DataFrame directly from Go records	ExampleNewDataFrameFromRecords	frame/example_api_test.go
Write transformed data back to CSV or TSV	ExampleDataFrame_ToCSV, ExampleDataFrame_ToTSV	frame/example_api_test.go
Select, filter, and mutate rows	ExampleDataFrame_Select, ExampleDataFrame_Filter, ExampleDataFrame_Mutate	frame/example_api_test.go
Join in-memory tables	ExampleDataFrame_Join	frame/example_api_test.go
Append frames with matching or mixed schemas	ExampleDataFrame_Concat, ExampleConcatAll	frame/example_api_test.go
Group rows for aggregation	ExampleDataFrame_GroupBy, ExampleGroupedDataFrame_Count	frame/example_api_test.go
Run built-in or custom aggregations	ExampleGroupedDataFrame_Sum, ExampleGroupedDataFrame_Agg	frame/example_api_test.go
Sort by one or multiple columns	ExampleDataFrame_Sort, ExampleDataFrame_SortBy	frame/example_api_test.go
Remove duplicates by key columns	ExampleDataFrame_DistinctBy	frame/example_api_test.go
Keep only the first rows	ExampleDataFrame_Head	frame/example_api_test.go
Rename columns in bulk	ExampleDataFrame_RenameColumns	frame/example_api_test.go
Fill or drop missing values	ExampleDataFrame_FillNAByColumn, ExampleDataFrame_DropNASubset	frame/example_api_test.go

Integration examples

The examples directory shows how to use filesql with regular Go database tooling:

Example	Description
basic	Basic CSV queries
multi-format	Join across CSV, TSV, LTSV, and Parquet
sqlc	Use filesql with sqlc
gorm	Use filesql with GORM
sqlx	Use filesql with sqlx
bun	Use filesql with Bun
squirrel	Use filesql with Squirrel
ent	Use filesql with Ent

Related Projects

Project	Description
sqly	Interactive shell for ad-hoc SQL against files
filesql/prep	Row cleanup and validation before SQL
filesql/frame	Lightweight in-memory transforms in Go

Contributing

Contributions are welcome. See CONTRIBUTING.md before sending a PR.

Support

If filesql is useful in your work:

• give it a star
• use it in your tools
• support development at https://github.com/sponsors/nao1215

License

filesql is released under the MIT License.