repository·main·Indexed 23 days ago
https://github.com/chaisql/chaiChaiSQL is a modern, embedded SQL database written in pure Go that provides a PostgreSQL-inspired API. It implements the standard database/sql interface and supports both in-memory and disk-based storage. The project includes a CLI tool for interactive SQL shells, database dumping, restoration, and performance benchmarking. Key features include support for creating/dropping tables and indexes, basic SELECT queries, and a Pebble-based storage engine.
ChaiSQL supports both ephemeral in-memory storage and persistent disk-based storage via the connection string in sql.Open("chai", <connection_string>).
:memory: for a database that exists only for the duration of the process.ChaiSQL implements the standard database/sql interface. You can use it to create tables, insert data, and run queries using a PostgreSQL-inspired syntax.
Note that you must import the driver with a blank identifier _ "github.com/chaisql/chai" to register it with the sql package.
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/chaisql/chai"
)
func main() {
// Open an on-disk database called "mydb"
db, err := sql.Open("chai", "mydb")
if err != nil {
log.Fatal(err)
}
defer db.Close()
// Create schema
_, err = db.Exec(`
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
age INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
`)
if err != nil {
log.Fatal(err)
}
// Insert some data
_, err = db.Exec(`
INSERT INTO users (id, name, email, age)
VALUES
(1, 'Alice', 'alice@example.com', 30),
(2, 'Bob', 'bob@example.com', 25),
(3, 'Carol', 'carol@example.com', 40);
`)
if err != nil {
log.Fatal(err)
}
// Query active adults
rows, err := db.Query(`
SELECT id, name, email, age
FROM users
WHERE age >= 18
ORDER BY age DESC
`)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id, age int
var name, email string
if err := rows.Scan(&id, &name, &email, &age); err != nil {
log.Fatal(err)
}
fmt.Printf("User %d: %s (%s), %d years old\n", id, name, email, age)
}
}To use ChaiSQL in your Go projects or via the command line, install the driver and the CLI tool using go install:
go install github.com/chaisql/chai@latest
go install github.com/chaisql/chai/cmd/chai@latestThe Chai TUI distinguishes between standard SQL queries and shell commands based on the input format:
;) to be executed if they are on a single line..) on a single line are treated as shell-specific commands (e.g., .exit).The Chai shell supports several built-in commands that start with a . prefix. These are used for administrative tasks rather than SQL queries.
| Command | Description |
|---|---|
.help | Displays help information |
.tables | Lists all tables in the database |
.indexes | Lists indexes (optionally for a specific table) |
.timer on/off | Toggles the display of execution time for queries |
.dump [args] | Dumps the database contents |
.save <path> | Saves the current database to the specified path |
.schema [args] | Displays the database schema |
.import <args> | Imports data into the database |
.restore <path> | Restores a database from a file |
.exit | (Implicitly handled via TUI) Exits the shell |
Use the following commands to explore the structure of your database:
.tables: Lists all user-defined tables (filtering out internal __chai_ tables)..schema [table_name]: Shows the CREATE statements used to define tables. If no table name is provided, it shows the schema for all tables..indexes [table_name]: Displays all indexes in the database. If you provide a table_name, it only shows indexes for that specific table.To execute a SQL query in the interactive shell, ensure your input ends with a semicolon (;).
Example:
SELECT * FROM users;The Chai shell provides an interactive Terminal User Interface (TUI) for executing SQL queries. It supports multi-line input, command history, and real-time query execution feedback.
Enter.;) or start with a dot (.) to trigger execution.Up and Down arrow keys to navigate through previously executed queries.exit or .exit and press Enter.Ctrl+D when the input buffer is empty.Ctrl+C to cancel a running query or exit.Ctrl+C to cancel the execution.The chai command is the primary entrypoint for interacting with the ChaiSQL database. It can be used in two modes:
chai [dbpath] starts an interactive shell for the specified database.cat queries.sql | chai [dbpath]), it executes the SQL from stdin and outputs the results to stdout, then exits.Available subcommands include version, dump, restore, bench, and pebble.
ChaiSQL allows you to persist your current database state to a file or restore it from a previously saved file.
Use .save to write the current database content to a file. If the target file already exists, it will be overwritten.
.save backup.sqlUse .restore to load a database from a text file (typically a file generated by .dump).
.restore backup.sqlChaiSQL is designed for simpler schemas and embedded use cases.
Supported features:
SELECT queries with filtering, ordering, and groupingDISTINCT, UNION, and UNION ALLNot yet implemented:
psql, pg_dump, or standard Postgres drivers/ORMs)You can import data from a CSV file into a ChaiSQL table using the .import command. The command will automatically create the table if it does not exist, using the CSV headers as column names with TEXT types.
Syntax:
.import csv <path_to_file> <table_name>
Note: The TYPE argument must be csv (case-insensitive).