minisql

MiniSQL is an embedded single file database written in Golang, inspired by SQLite (however borrowing some features from other databases as well). It originally started as a research project aimed at learning about internals of relational databases. Over time it has progressed and grown to its current form. It is a very early stage project and it might contain bugs and is not battle tested. Please employ caution when using this database.

To use minisql in your Go code, import the driver:

import (
  _ "github.com/RichardKnop/minisql"
)

And create a database instance:

// Simple path
db, err := sql.Open("minisql", "./my.db")

// With connection parameters
db, err := sql.Open("minisql", "./my.db?journal=false")

// Multiple parameters
db, err := sql.Open("minisql", "./my.db?journal=true&log_level=debug")

Connection Pooling

MiniSQL is an embedded, single-file database (like SQLite). Always configure your connection pool to use a single connection and serialize all writes through it.

db.SetMaxOpenConns(1)
db.SetMaxIdleConns(1)

Why? Multiple connections to the same file cause:

Lock contention at the OS level
Connection pool overhead (97% lock time in benchmarks)
No performance benefit (writes are serialized anyway)

This is the same recommendation as SQLite.

Connection String Parameters

MiniSQL supports optional connection string parameters:

| Parameter | Values | Default | Description | |-----------|--------|---------|-------------| | journal | true, false | true | Enable/disable rollback journal for crash recovery | | log_level | debug, info, warn, error | warn | Set logging verbosity level | | max_cached_pages | positive integer | 2000 | Maximum number of pages to keep in memory cache |

Examples:

// Disable journaling for better performance (no crash recovery)
db, err := sql.Open("minisql", "./my.db?journal=false")

// Enable debug logging
db, err := sql.Open("minisql", "./my.db?log_level=debug")

// Set cache size to 500 pages (~2MB memory)
db, err := sql.Open("minisql", "./my.db?max_cached_pages=500")

// Combine multiple parameters
db, err := sql.Open("minisql", "/path/to/db.db?journal=true&log_level=info&max_cached_pages=2000")

Note: Disabling journaling (journal=false) improves performance but removes crash recovery protection. If the application crashes during a transaction commit, the database may become corrupted.

Write-Ahead Rollback Journal

MiniSQL uses a rollback journal to achieve atomic commit and rollback . Before committing a transaction,a journal file with -journal suffix is created in the same directory as the database file. It contains original state of the pages (and database header if applicable) before the transaction began. In case of an error encountered during flushing of pages changed by the transaction to the disk, the database quits and recovers to original state before the transaction from the journal file.

Storage

Each page size is 4096 bytes. Rows larger than page size are not supported. Therefore, the largest allowed inline row size is 4065 bytes (with exception of root page 0 which has first 100 bytes reserved for config). Variable text colums can use overflow pages and are not limited by page size.

4096 (page size) 
- 7 (base header size) 
- 8 (internal / leaf node header size) 
- 8 (null bit mask) 
- 8 (internal row ID / key) 
= 4065

All tables are kept track of via a system table minisql_schema which contains table name, CREATE TABLE SQL to document table structure and a root page index indicating which page contains root node of the table B+ Tree.

Each row has an internal row ID which is an unsigned 64 bit integer starting at 0. These are used as keys in B+ Tree data structure.

Moreover, each row starts with 64 bit null mask which determines which values are NULL. Because of the NULL bit mask being an unsigned 64 bit integer, there is a limit of maximum 64 columns per table.

Database Header Format

The first 100 bytes of page 0 are reserved for the MiniSQL database header. This is part of the on-disk file format.

Current header fields:

| Offset | Size | Field | Description | |---|---:|---|---| | 0 | 8 | magic | minisql\0 file signature | | 8 | 4 | format version | Current value: 1 | | 12 | 4 | page size | Current value: 4096 | | 16 | 4 | first free page | Head of the free-page linked list | | 20 | 4 | free page count | Number of free pages currently tracked | | 24 | 76 | reserved | Reserved for future file-format metadata |

Notes:

MiniSQL now requires the header magic/version/page size to be present when opening a database file.
The remaining bytes are reserved so the header can grow without immediately changing the page layout again.
The rest of page 0 after the first 100 bytes is used as a normal root B+ tree page.

Concurrency

MiniSQL uses Optimistic Concurrency Control or OCC. It is close to PostgreSQL's SERIALIZABLE isolation Transaction manager follows a simple process:

Track read versions - Record which version of each page was read
Check at commit time - Verify no pages were modified during the transaction
Abort on conflict - If a page changed, abort with ErrTxConflict

You can use ErrTxConflict to control whether to retry because of a tx serialization error or to return error.

SQlite uses a snapshot isolation with MVCC (Multi-Version Concurrency Control). Read how SQLite handles isolation. I have chosen a basic OCC model for now for its simplicity.

Example of a snapshot isolation:

Time 0: Read TX1 starts, sees version V1
Time 1: Write TX2 modifies page and commits → creates version V2
Time 2: TX1 continues reading, still sees V1 (not V2!)
Time 3: TX1 completes successfully

System Table

All tables and indexes are tracked in the system table minisql_schema. For empty database, it would contain only its own reference:

 type   | name               | table_name         | root_page   | sql                                                
--------+--------------------+--------------------+-------------+----------------------------------------
 1      | minisql_schema     |                    | 0           | create table "minisql_schema" (        
        |                    |                    |             | 	type int4 not null,                  
        |                    |                    |             | 	name varchar(255) not null,          
        |                    |                    |             | 	table_name varchar(255),             
        |                    |                    |             | 	root_page int4,                      
        |                    |                    |             | 	sql text                             
        |                    |                    |             | )

Let's say you create a table such as:

create table "users" (
	id int8 primary key autoincrement,
	email varchar(255) unique,
	name text,
	age int4,
	created timestamp default now()
);
create index "idx_created" on "users" (
	created
);

It will be added to the system table as well as its primary key and any unique or secondary indexes. Secondary index on created TIMESTAMP column created separately will also be added to the system table.

You can check current objects in the minisql_schema system table by a simple SELECT query.

// type schema struct {
// 	Type      int
// 	Name      string
// 	TableName *string
// 	RootPage  int
// 	Sql       *string
// }

rows, err := db.QueryContext(context.Background(), `select * from minisql_schema;`)
if err != nil {
	return err
}
defer rows.Close()

var schemas []schema
for rows.Next() {
	var aSchema schema
	if err := rows.Scan(&aSchema.Type, &aSchema.Name, &aSchema.TableName, &aSchema.RootPage, &aSchema.SQL); err != nil {
		return err
	}
	schemas = append(schemas, aSchema)
}
if err := rows.Err(); err != nil {
	return err
}

 type   | name               | table_name         | root_page   | sql                                                
--------+--------------------+--------------------+-------------+----------------------------------------
 1      | minisql_schema     |                    | 0           | create table "minisql_schema" (        
        |                    |                    |             | 	type int4 not null,                  
        |                    |                    |             | 	name varchar(255) not null,          
        |                    |                    |             | 	table_name varchar(255),             
        |                    |                    |             | 	root_page int4,                      
        |                    |                    |             | 	sql text                             
        |                    |                    |             | )                                      
 1      | users              |                    | 1           | create table "users" (                 
        |                    |                    |             | 	id int8 primary key autoincrement,   
        |                    |                    |             | 	email varchar(255) unique,           
        |                    |

Minisql

Install / Use

README