filesql

filesql is a Go library that loads various file formats into an in-memory SQLite database, allowing you to query them using standard SQL syntax. It is designed for scenarios where data is already in files and SQL is the most efficient way to process it.

Core Capabilities:

• Join across formats: Join data from CSV, TSV, LTSV, JSON, JSONL, Parquet, XLSX, ACH, or Fedwire.
• Flexible Input: Read from file paths, directories, io.Reader, or embed.FS.
• Transparent Compression: Automatically handles .gz, .bz2, .xz, .zst, .z, .snappy, .s2, and .lz4 wrappers for supported text and columnar formats.
• Companion Packages:
  • prep: For cleaning and validating rows before they are loaded as tables.
  • frame: For performing in-memory transformations on small/medium datasets using plain Go instead of SQL.

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

Important for LoadInto: When using LoadInto, you manage the database and pool settings. If you are using sql.Open("sqlite", ":memory:"), you must set SetMaxOpenConns(1) to ensure every query hits the same in-memory database instance.

filesql loads data into an in-memory SQLite database. To manage memory usage, the library uses chunked loading for certain formats.

Memory Behavior:

• Chunked Loading: CSV, TSV, and JSON arrays are read in chunks. This keeps the Go heap usage low (roughly the size of one chunk), but the resident memory will grow to approximately 2x the file's size because the data is stored in the in-memory SQLite database.
• Full Loading: LTSV, non-array JSON/JSONL, Parquet, XLSX, ACH, and Fedwire are read in full before being converted to rows.

To tune the memory/performance tradeoff for chunked formats, use SetDefaultChunkSize on the builder.

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

When using Bun with filesql, you must define your Go structs to match the file's structure using Bun's struct tags:

• Embed bun.BaseModel in your struct.
• Use the bun:"table:xxx" tag to specify the name of the file/table.
• Use bun:"column,pk" tags to define primary keys and map specific columns.

type User struct {
	bun.BaseModel `bun:"table:users"` // Maps to the users file

	ID    int64  `bun:"id,pk"`      // Primary key
	Name  string `bun:"name"`       // Column mapping
	Email string `bun:"email"`      // Column mapping
	Age   int    `bun:"age"`        // Column mapping
}

When using GORM with filesql, you must explicitly map struct fields to the CSV column names using the gorm:"column:xxx" tag. Additionally, because filesql treats files as tables, you must implement the TableName() string method on your model struct to specify the filename (excluding the extension) that GORM should target.

type User struct {
    ID    int    `gorm:"column:id"` 
    Name  string `gorm:"column:name"` 
}

func (User) TableName() string {
    return "users" // targets users.csv
}

To use Ent in your project, install the Ent framework using go get:

go get entgo.io/ent

Changes made to the data live in memory until an explicit save operation is performed. You can use the following methods to persist data:

• DumpDatabase: Writes the current database state to files (explicit export).
• EnableAutoSave: Automatically saves changes when db.Close() is called.
• EnableAutoSaveOnCommit: Automatically saves changes after every committed transaction.

To run the standalone Squirrel integration example, ensure you have the Squirrel dependency installed, then navigate to the example directory to run the main entry point.

go get github.com/Masterminds/squirrel

cd examples/squirrel
go mod tidy
go run main.go

The sqlx integration requires the github.com/jmoiron/sqlx package. Install it using:

go get github.com/jmoiron/sqlx

To use the GORM integration, you need to install the GORM core library and the SQLite driver:

go get gorm.io/gorm
go get gorm.io/driver/sqlite

To run the provided integration example, navigate to the example directory and use the Go CLI:

cd examples/sqlx
go run main.go