bakelite

README

bakelite

Pure Go SQLite file exporter

This library writes SQLite files from scratch. You hand it the data you want in the tables, and you get back your .SQLite file.

No dependencies. No C. No SQL.

use case

At work, we have customers who have campaigns with millions of SMSs and IVRs. The details can be downloaded as an .XLSX file, but dealing with 500Mb files in Excel is no fun. Bakelite gives a light way to generate a .sqlite file.

example

    // New() keeps the whole database in memory. See NewTmp() for a disk based option.
    db := bakelite.New()
    defer db.Close()

    // Table with all data from a slice
    err := db.AddSlice("planets", []string{"name", "moons"}, [][]any{
        {"Mercury", 0},
        {"Venus", 0},
        {"Earth", 1},
        {"Mars", 2},
        {"Jupiter", 80},
        {"Saturn", 83},
        {"Uranus", 27},
        {"Neptune", 4},
    })

    // Table with all data from a channel
    stars := make(chan []any, 10)
    stars <- []any{"Alpha Centauri", "4"}
    stars <- []any{"Barnard's Star", "6"}
    stars <- []any{"Luhman 16", "6"}
    stars <- []any{"WISE 0855−0714", "7"}
    stars <- []any{"Wolf 359", "7"}
    err := db.AddChan("stars", []string{"name", "lightyears"}, stars)

    b := &bytes.Buffer{}
    err := db.WriteTo(b)

status

Not used in production yet. It can write files and SQLite is happy with those files.

Main todos:

• support more Go datatypes, such as int32 and bool.
• hasn't seen a profiler.

What this library won't do:

• add indexes. (but every row gets an internal "row id", which we could expose as "integer primary key" data type, since sqlite uses the rowid for those)
• concurrency. This library generates the file, which you can then save and use concurrently, as any normal sqlite database file. But while the file is being generated, it can't be used.
• updates. This is a write-once affair.

links

https://sqlite.org/fileformat2.html
https://litecli.com/

Documentation

Overview

Example

db := New()
defer db.Close()

// Table with all data in memory already
db.AddSlice("planets", []string{"name", "moons"}, [][]any{
	{"Mercury", 0},
	{"Venus", 0},
	{"Earth", 1},
	{"Mars", 2},
	{"Jupiter", 80},
	{"Saturn", 83},
	{"Uranus", 27},
	{"Neptune", 4},
})

// Table with all data from a channel
stars := make(chan []any, 10)
stars <- []any{"Alpha Centauri", "4"}
stars <- []any{"Barnard's Star", "6"}
stars <- []any{"Luhman 16", "6"}
stars <- []any{"WISE 0855−0714", "7"}
stars <- []any{"Wolf 359", "7"}
db.AddChan("stars", []string{"name", "lightyears"}, stars)

b := &bytes.Buffer{}
err := db.WriteTo(b)
_ = err // ..
err = os.WriteFile("/tmp/universe.sqlite", b.Bytes(), 0600)
_ = err // ..

Index

• Constants
• type DB
  • func New() *DB
  • func NewTmp(dir string) (*DB, error)
  • func (db *DB) AddChan(table string, columns []string, rows <-chan []any) error
  • func (db *DB) AddSlice(table string, columns []string, rows [][]any) error
  • func (db *DB) Close() error
  • func (db *DB) WriteTo(w io.Writer) error

Examples

• Package

Constants

const (
	// database file page size
	PageSize = 1 << 12
)

Types

type DB

type DB struct {
	// contains filtered or unexported fields
}

func New

func New() *DB

Create a new, in memory, db. Does nothing on db.Close()

func NewTmp

func NewTmp(dir string) (*DB, error)

Create a new db, with a tmp file stored in `dir`. See os.CreateTemp: if dir is empty this uses the default directory for temporary files. Unlinks the file on db.Close()

func (*DB) AddChan

func (db *DB) AddChan(table string, columns []string, rows <-chan []any) error

Add a new table with the given columns and rows. Table and column names should probably be lowercase simple strings. The number of items in every row is allowed to be shorter than the number of columns, according to the sqlite docs. What happens when there are more is not defined. Don't add the same table twice. Don't use this from multiple Go routines at the same time.

Supported Go datatypes:

• int
• float64
• string
• []byte
• nil

(yup, that's all for now)

This returns an error if any value is of an unsupported type. From then on any other call to AddChan() will return the same error, and so will WriteTo(). If there is an error we will drain the rows channel.

func (*DB) AddSlice

func (db *DB) AddSlice(table string, columns []string, rows [][]any) error

AddSlice is a helper to call AddChan.

func (*DB) Close

func (db *DB) Close() error

Cleanup resources. If the database was created with NewTmp() Close() will remove the temp file.

func (*DB) WriteTo

func (db *DB) WriteTo(w io.Writer) error

Write the whole file to the writer. You probably don't want to use the db again. If any previous AddChan() or AddSlice() returned an error, then this will return the same error.