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.

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.

• 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.
• Write csv, tsv, and ltsv output in Shift-JIS, EUC-JP, ISO-2022-JP, or UTF-16 as well as UTF-8.
• 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).

Two inputs are the same source only when they are in the same place. dir/users.csv and dir/users.csv.gz are one dataset offered twice, and the plain one is read; a/users.csv and b/users.csv are two files, and both are loaded. What happens when both then want the table users is the loading API's business: Open and OpenContext build a fresh database and refuse it with ErrDuplicateTable, while LoadInto and LoadIntoTx load into a database you own and keep their last-wins rule, so the later input replaces the table. Neither one silently drops a file.

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.

Format and compression are separate. FileType names the format only — FileTypeCSV is a CSV whether or not a codec wraps it — and a path says which codec that is. A reader has no path, so AddReader takes the codec as an option:

gz, err := os.Open("users.csv.gz")
if err != nil {
	return err
}
defer gz.Close()

builder.AddReader(gz, "users", filesql.FileTypeCSV, filesql.WithCompression(filesql.CompressionGZ))

Without WithCompression the reader's bytes are read as the format directly, so the ordinary three-argument call is unchanged.

go get github.com/nao1215/filesql

Requirements:

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

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

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)
	}
}

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, DISTINCT ON, or MySQL's XOR) 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.

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)
	}
}

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()
}

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, _ := frame.Row(row).Int("qty")
		price, _ := frame.Row(row).Int("price")
		return qty * price
	})

	northOnly := sales.Filter(func(row map[string]any) bool {
		region, _ := frame.Row(row).String("region")
		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"])
	}
}

CSV, TSV, LTSV, and XLSX carry no types, so filesql reads the values and picks INTEGER, REAL, or TEXT per column. Parquet, ACH, and Fedwire bring their own schema and are not inferred.

Which of the three a column gets is decided from a sample. Whether a cell survives the load is not: three kinds of value are damaged by a numeric column, so any one of them anywhere in the file makes the column TEXT, wherever it sits.

What is not on that list is decimal formatting. 2.50 loads as the REAL 2.5, 1.00 as 1, and 1e3 as 1000: the quantity is preserved and the way it was written is not. Storing those as TEXT would keep the spelling and break the arithmetic — SQLite compares a TEXT column against a number as text, so WHERE amount > 9.5 over 9.00 and 10.00 returns nothing at all. A column of money is worth more as numbers than as the string it was typed as, so the trailing zeros go. Keep the source file if you need the original spelling, or read the column into a TEXT column of your own before loading.

frame applies the same rules to its own values. 1, 1.0 and 1.00 are one value there too, so Distinct collapses them to a single row and Join matches them to each other, while 007 and 7 stay two codes. A frame row therefore holds an int64 or a float64 where the file held text, which is what frame.Row is for: frame.Row(row).String("code") gives the value as the file spelled it, and Int and Float give it as a number, so a predicate does not have to guess which type the inference picked.

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.

Because the rows end up in that database rather than on the Go heap, the heap is not where the cost is. Loading CSVs of 16 MB through 131 MB, the Go heap stayed flat at about 24 MB — chunked loading holds roughly a chunk, not the file — while resident memory grew by about 2x the file's size. Budget from the file size, and expect the database, not the parser, to be what occupies it.

Measured on Linux with go test -tags benchmark -run TestLoadMemoryFootprint -v ., which prints the table it is drawn from so the figure can be re-derived rather than taken on trust. Note that B/op from go test -benchmem answers a different question: it counts every byte a load ever allocated, garbage included, so it runs several times higher than the memory actually held.

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.

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.

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.

DumpOptions decides the format, the compression, and the text encoding of csv, tsv, and ltsv output:

options := filesql.NewDumpOptions().
	WithFormat(filesql.OutputFormatCSV).
	WithEncoding(filesql.EncodingShiftJIS)

err := filesql.DumpDatabase(db, "./output", options)

Output is UTF-8 unless WithEncoding says otherwise, which is what a save wrote before the option existed. EncodingShiftJIS, EncodingEUCJP, EncodingISO2022JP, EncodingUTF16LE, and EncodingUTF16BE are the others; the UTF-16 pair write a byte-order mark, so the read side recognizes them without being told. A value the encoding has no way to write fails the save with ErrEncoding and leaves the destination as it was, rather than being replaced with a substitute character — the same answer the read side gives to bytes it cannot decode. Parquet and XLSX carry their own encoding and ignore the option.

A workbook can hide a sheet, and a hidden sheet often holds the spreadsheet's own working-out rather than data anyone meant to publish. filesql loads every sheet by default, hidden or not, so existing programs keep the tables they have.

Ask for only the sheets the workbook shows with WithExcelSheetPolicy:

validatedBuilder, err := filesql.NewBuilder().
	AddPath("book.xlsx").
	WithExcelSheetPolicy(filesql.ExcelSheetPolicyVisibleOnly).
	Build(ctx)

The policy applies to every source — a path, a directory, an embedded filesystem, a reader, and a compressed workbook alike. Table names are worked out after it has run, so a hidden sheet that would sanitize to the same table as a visible one is not a collision when it is not loaded.

Excel separates "hidden", which a reader can undo from the sheet tabs, from "very hidden", which only the VBA editor can. The library filesql reads workbooks with reports one boolean covering both, so filesql does not tell them apart: ExcelSheetPolicyVisibleOnly leaves out either kind.

ExcelSheetsInFile reports what a workbook holds without loading it, which is how a caller explains which sheets a policy left behind:

sheets, err := filesql.ExcelSheetsInFile("book.xlsx")
for _, sheet := range sheets {
	fmt.Println(sheet.Name, sheet.Visible)
}

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.

Control records are derived, not stored: writing an ACH file rebuilds each batch control and the file control from the entries, so an edited amount is balanced by the write rather than by the caller. An edit to a control column (total_debit, total_credit, entry_hash, entry_addenda_count) is therefore overwritten by the recalculation.

Writing needs the source file. Neither format can be rebuilt from its SQL tables alone: fields no table exposes exist only in the original. DumpACH and DumpFedWire therefore read the file the tables were loaded from and apply the edits to it, and fail with ErrSourceUnavailable, naming the file, when it is gone or unreadable. A database loaded from an io.Reader has no such file: parse the reader with parser/ach or parser/wire and pass the result to DumpACHWithTableSet or DumpFedWireWithTableSet.

Each database records its own source, so two databases loaded from files that share a name in different directories each export their own data. The record lives in a reserved table named _filesql_sources. Table names beginning with _filesql_ belong to this package: they are hidden from DumpDatabase and from the table listings filesql returns, and an input that would load into one is refused with ErrReservedTableName, the way SQLite refuses its own sqlite_ prefix.

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.

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

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

If filesql is useful in your work:

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

filesql is released under the MIT License.