SQLite with pure Go

Sqinn

GoDoc Reference Go Report Card Build Status License: Unlicense Mentioned in Awesome Go

Sqinn-Go is a Go (Golang) library for accessing SQLite databases in pure Go. It uses Sqinn https://github.com/cvilsmeier/sqinn under the hood. It starts Sqinn as a child process (os/exec) and communicates with Sqinn over stdin/stdout/stderr. The Sqinn child process then does the SQLite work.

If you want SQLite but do not want cgo, Sqinn-Go can be a solution.

Usage

$ go get -u github.com/cvilsmeier/sqinn-go/sqinn
import "github.com/cvilsmeier/sqinn-go/sqinn"

// Simple sqinn-go usage. Error handling is left out for brevity.
func main() {

	// Launch sqinn. Terminate at program exit.
	sq, _ := sqinn.Launch(sqinn.Options{})
	defer sq.Terminate()

	// Open database. Close when we're done.
	sq.Open("./users.db")
	defer sq.Close()

	// Create a table.
	sq.ExecOne("CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name VARCHAR)")

	// Insert users.
	sq.ExecOne("INSERT INTO users (id, name) VALUES (1, 'Alice')")
	sq.ExecOne("INSERT INTO users (id, name) VALUES (2, 'Bob')")

	// Query users.
	rows, _ := sq.Query("SELECT id, name FROM users ORDER BY id", nil, []byte{sqinn.ValInt, sqinn.ValText})
	for _, row := range rows {
		fmt.Printf("id=%d, name=%s\n", row.Values[0].AsInt(), row.Values[1].AsString())
	}

	// Output:
	// id=1, name=Alice
	// id=2, name=Bob
}

Before running that program, Sqinn must be installed on your system. The most convenient way is to download a pre-built executable from https://github.com/cvilsmeier/sqinn/releases and put it somewhere on your $PATH, or %PATH% on Windows.

If you want to store the Sqinn binary in a non-PATH folder, you must specify it when opening a Sqinn connection:

    // take from environment...
    sq, _ := sqinn.New(sqinn.Options{
        SqinnPath: os.Getenv("SQINN_PATH"),
    })

    // ...or set path directly
    sq, _ := sqinn.New(sqinn.Options{
        SqinnPath: "/path/to/sqinn",
    })

If do not want to use a pre-built Sqinn binary, you can compile Sqinn yourself. See https://github.com/cvilsmeier/sqinn for instructions.

Pros and Cons

Advantages

  • No need to have gcc installed on development machines.
  • Go cross compilation works.
  • Faster build speed than cgo (1s vs 3s for sample program).
  • Smaller binary size than cgo (2MB vs 10MB for sample program).

Disadvantages

  • No built-in connection pooling.
  • Sqinn-Go is not a Golang database/sql Driver.
  • Sqinn covers only a subset of SQLite's C APIs.

Sample code

For more examples, see directory examples or file examples_test.go. The godoc page contains examples also. Even more sample code can be found in the benchmark repository at https://github.com/cvilsmeier/sqinn-go-bench.

Performance

Performance tests show that Sqinn-Go performance is comparable to cgo solutions, depending on the use case.

For benchmarks I used github.com/mattn/go-sqlite3 and crawshaw.io/sqlite. Numbers are given in milliseconds, lower numbers are better.

                   mattn  crawshaw     sqinn
simple/insert       2901      2140      1563
simple/query        2239      1287      1390
complex/insert      2066      1817      1683
complex/query       1458      1129      1338
many/N=10             97        78       134
many/N=100           246       194       276
many/N=1000         1797      1240      1436
large/N=2000         119        87       341
large/N=4000         361       322       760
large/N=8000         701       650      1531
concurrent/N=2      1332       865       951    
concurrent/N=4      1505       989      1207    
concurrent/N=8      2347      1557      2044     

See https://github.com/cvilsmeier/sqinn-go-bench for details.

Testing

Sqinn-Go comes with a large set of automated unit tests. Follow these steps to execute all tests on linux_amd64:

Download and Install Sqinn

$ cd $HOME
$ curl -sL https://github.com/cvilsmeier/sqinn/releases/download/v1.0.0/sqinn-dist-1.0.0.tar.gz | tar xz
$ export SQINN_PATH=$HOME/sqinn-dist-1.0.0/linux_amd64/sqinn

Get and test Sqinn-Go

$ go get -v -u github.com/cvilsmeier/sqinn-go/sqinn
$ go test github.com/cvilsmeier/sqinn-go/sqinn

Check test coverage

$ go test github.com/cvilsmeier/sqinn-go/sqinn -coverprofile=./cover.out
$ go tool cover -func=./cover.out
$ go tool cover -html=./cover.out

Test coverage is ~85% (as of 2020-06-10)

Discussion

Pure Go, almost

Sqinn-Go is pure Go, as it does not use cgo, nor does it depend on third-party cgo packages. However, Sqinn-Go has a runtime dependency on Sqinn, which is a program written in C. Sqinn has to be installed separately on each machine where a Sqinn-Go application is executing. For this to work, Sqinn has to be compiled for every target platform. As an alternative, pre-built Sqinn binaries for the most common platforms can be downloaded from the Sqinn releases page https://github.com/cvilsmeier/sqinn/releases.

No database/sql driver

Database/sql is Go's default abstraction layer for SQL databases. It is widely used and there are many third-party packages built on top of it. Sqinn-Go does not implement the database/sql interfaces. The reason is that the sql package provides low-level function calls to prepare statements, bind parameters, fetch column values, and so on. Sqinn could do that, too. But, since for every function call, Sqinn-Go has to make a inter-process communication request/response roundtrip to a sqinn child process, this would be very slow. Instead, Sqinn-Go provides higher-level Exec/Query interfaces that should be used in favor of low-level fine-grained functions.

Concurrency

Sqinn/Sqinn-Go performs well in non-concurrent as well as concurrent settings, as shown in the Performance section. However, a single Sqinn instance should only be called from one goroutine. Exceptions are the Exec and Query methods, these are mutex'ed and goroutine safe. But, since Sqinn is inherently single-threaded, Exec and Query requests are served one-after-another.

If you want true concurrency at the database level, you can spin up multiple Sqinn instances. You may even implement a connection pool. But be aware that when accessing a SQLite database concurrently, the dreaded SQLITE_BUSY error might occur.

We recommend the following: Have one Sqinn instance. You may call Exec/Query on that single Sqinn instance from as many goroutines as you want. For long-running tasks (VACUUM, BACKUP, etc), spin up a second Sqinn instance on demand, and terminate it once the long-running work is done. Use PRAGMA busy_timeout to avoid SQLITE_BUSY.

Only one active statement at a time

A Sqinn instance allows only one active statement at a time. A statement is active from the time it is prepared until it is finalized. Before preparing a new statement, you have to finalize the current statement first, otherwise Sqinn will respond with an error.

This is why we recommend using Exec/Query: These methods do a complete prepare-finalize cycle and the caller can be sure that, once Exec/Query returns, no active statements are hanging around.

Changelog

v1.1.0 (2020-06-14)

  • Use IEEE 745 encoding for float64 values, needs sqinn v1.1.0 or higher.

v1.0.0 (2020-06-10)

  • First version.

License

This is free and unencumbered software released into the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or distribute this software, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

In jurisdictions that recognize copyright laws, the author or authors of this software dedicate any and all copyright interest in the software to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights to this software under copyright law.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

For more information, please refer to https://unlicense.org

Owner
Similar Resources

Bulk query SQLite database over the network

SQLiteQueryServer Bulk query SQLite database over the network. Way faster than SQLiteProxy!

May 20, 2022

Data access layer for PostgreSQL, CockroachDB, MySQL, SQLite and MongoDB with ORM-like features.

Data access layer for PostgreSQL, CockroachDB, MySQL, SQLite and MongoDB with ORM-like features.

upper/db is a productive data access layer (DAL) for Go that provides agnostic tools to work with different data sources

Jan 3, 2023

squirrelbyte is a "proof of concept" document / search server backed by sqlite.

🐿️ squirrelbyte is a "proof of concept" document / search server backed by sqlite.

May 20, 2022

Low-level Go interface to SQLite 3

zombiezen.com/go/sqlite This package provides a low-level Go interface to SQLite 3. It is a fork of crawshaw.io/sqlite that uses modernc.org/sqlite, a

Dec 21, 2022

This application is used as an example HTTP/SQLite application for Litestream tutorials.

This application is used as an example HTTP/SQLite application for Litestream tutorials. It simply maintains a count of HTTP requests and persists it to a SQLite database.

Apr 2, 2022

The lightweight, distributed relational database built on SQLite

The lightweight, distributed relational database built on SQLite

rqlite is a lightweight, distributed relational database, which uses SQLite as its storage engine. Forming a cluster is very straightforward, it grace

Jan 2, 2023

Streaming replication for SQLite.

Litestream Litestream is a standalone streaming replication tool for SQLite. It runs as a background process and safely replicates changes incremental

Jan 9, 2023

Go sqlite3 http vfs: query sqlite databases over http with range headers

sqlite3vfshttp: a Go sqlite VFS for querying databases over http(s) sqlite3vfshttp is a sqlite3 VFS for querying remote databases over http(s). This a

Dec 27, 2022

Terraform provider for SQLite database engine

Terraform provider for SQLite database engine !!! WARNING !!! This is an educational project. Not intended for any production use! !!! WARNING !!! Her

Jun 11, 2022

DonutDB: A SQL database implemented on DynamoDB and SQLite

DonutDB: A SQL database implemented on DynamoDB and SQLite

Dec 21, 2022

A web forum built in Golang and SQLite and designed in SCSS

A web forum built in Golang and SQLite and designed in SCSS

Forum "Fairfax" 👉 What is it? A web forum built in Golang and SQLite and designed in SCSS. Members of the forum can take a personality test and be so

Nov 10, 2021

This benchmark provides a overview of the different SQLite driver performances available in Go.

SQLite/HTTP Server Performance Benchmark This benchmark provides a overview of the different SQLite driver performances available in Go. For benchmark

Aug 8, 2022

BQB is a lightweight and easy to use query builder that works with sqlite, mysql, mariadb, postgres, and others.

Basic Query Builder Why Simple, lightweight, and fast Supports any and all syntax by the nature of how it works Doesn't require learning special synta

Dec 7, 2022

Experimental implementation of a SQLite backend for go-mysql-server

go-mysql-sqlite-server This is an experimental implementation of a SQLite backend for go-mysql-server from DoltHub. The go-mysql-server is a "frontend

Dec 23, 2022

Demo of schema change failures with SQLite INTEGERs.

SQLite Schema Migration Bug This is a repository to reproduce a bug with Ent and the modernc.org/sqlite (non-CGO) SQLite 3 driver. Reproduce Simply ru

Dec 9, 2021

SQLite extension for accessing other SQL databases

dblite SQLite extension for accessing other SQL databases, in SQLite. Similar to how Postgres Foreign Data Wrappers enable access to other databases i

Dec 23, 2022

Golang database driver for SQLite

go-sqlite Golang database driver for SQLite. Does not use cgo. This driver is based on pure-Go SQLite implementation (https://gitlab.com/cznic/sqlite)

Dec 30, 2022

A SQLite-based hierarchical key-value store written in Go

camellia 💮 A lightweight hierarchical key-value store camellia is a Go library that implements a simple, hierarchical, persistent key-value store, ba

Nov 9, 2022

Test - A program that validates your progress on the SQLite challenge

SQLite Challenge Tester This is a program that validates your progress on the SQ

Jan 6, 2022
Comments
  • How to deal with NULLs

    How to deal with NULLs

    Hi, first of all, thanks for your work. The concept is wonderful and it works well. I have one question though: how to tell that a result of a query is NULL - or it's (for example) zero.

    rows, _ := sq.Query("SELECT val FROM tabl", []interface{}{}, []byte{sqinn.ValInt})
    // How to tell if rows[0].Values[0] is NULL or 0?
    

    Maybe a method like rows[0].IsNull(0) could be useful. Of course, if I am not missing something.

  • Convenient way of passing NULLs to queries

    Convenient way of passing NULLs to queries

    Hi, what is the idiomatic way of passing a nil in a []interface{}? I tried the following:

    type test struct {
    	TestVal *int
    }
    
    sq, _ := sqinn.Launch(sqinn.Options{
    		SqinnPath: "../env/sqinn",
    	})
    	defer sq.Terminate()
    
    	sq.Open("../env/test.db")
    	defer sq.Close()
    
    	sq.ExecOne("create table tbl (col int null)")
    
    	value := 17
    	tst := test{}
    
    	tst.TestVal = &value
    
    	_, err := sq.Exec("INSERT INTO tbl(col) VALUES(?)", 1, 1, []interface{}{tst.TestVal})
    	if err != nil {
    		commons.Panic(fiber.StatusInternalServerError, fmt.Sprintf("Query 1 failed: %v\n", err))
    	}
    
    	tst.TestVal = nil
    
    	_, err = sq.Exec("INSERT INTO tbl(col) VALUES(?)", 1, 1, []interface{}{tst.TestVal})
    	if err != nil {
    		commons.Panic(fiber.StatusInternalServerError, fmt.Sprintf("Query 2 failed: %v\n", err))
    	}
    

    The first one fails, because cannot bind type *int, and actually it should be int, that is simply what I used to do with PostgreSQL. If I pass a int as a variable, on the other hand, I have no way to pass a nil in the same variable (say, if the variable is loaded from JSON for example, I might have this situation).

    Admittedly, this is probably an issue of mine with Go, not with the library in particular.

    Thank you!

Pure Go Postgres driver for database/sql

pq - A pure Go postgres driver for Go's database/sql package Install go get github.com/lib/pq Features SSL Handles bad connections for database/sql S

Jan 2, 2023
SAP (formerly sybase) ASE/RS/IQ driver written in pure go

tds import "github.com/thda/tds" Package tds is a pure Go Sybase ASE/IQ/RS driver for the database/sql package. Status This is a beta release. This dr

Dec 7, 2022
A pure go library to handle MySQL network protocol and replication.

A pure go library to handle MySQL network protocol and replication.

Jan 3, 2023
Package sqlite is a CGo-free port of SQLite.

sqlite Package sqlite is a CGo-free port of SQLite. SQLite is an in-process implementation of a self-contained, serverless, zero-configuration, transa

Nov 30, 2021
SQLite with pure Go

Sqinn-Go is a Go (Golang) library for accessing SQLite databases in pure Go. It uses Sqinn https://github.com/cvilsmeier/sqinn under the hood. It star

Dec 19, 2022
Pure Go SQLite file reader

Package SQLittle provides pure Go, read-only, access to SQLite (version 3) database files. What SQLittle reads SQLite3 tables and indexes. It iterates

Oct 12, 2022
The lightweight, distributed relational database built on SQLite.
The lightweight, distributed relational database built on SQLite.

rqlite is a lightweight, distributed relational database, which uses SQLite as its storage engine. Forming a cluster is very straightforward, it grace

Jan 5, 2023
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

Jan 8, 2023
beedb is a go ORM,support database/sql interface,pq/mysql/sqlite

Beedb ❗ IMPORTANT: Beedb is being deprecated in favor of Beego.orm ❗ Beedb is an ORM for Go. It lets you map Go structs to tables in a database. It's

Nov 25, 2022
Convert data exports from various services to a single SQLite database
Convert data exports from various services to a single SQLite database

Bionic Bionic is a tool to convert data exports from web apps to a single SQLite database. Bionic currently supports data exports from Google, Apple H

Dec 9, 2022