A standard library for mattn/go-sqlite3

As an alternative to compiling C extensions like extension-functions.c and sqlean into mattn/go-sqlite3, this package implements many of these functions (and more from PostgreSQL) in Go.

These are in addition to all builtin functions provided by SQLite.

Continue reading for all functions, notes and examples.

Why would I use this?

This library is used in DataStation and dsq to simplify and power data analysis in SQL.

Read the DataStation blog post to better understand the background.

Example

package main

import (
	"fmt"
	"database/sql"

	_ "github.com/mattn/go-sqlite3"
	stdlib "github.com/multiprocessio/go-sqlite3-stdlib"
)

func main() {
	stdlib.Register("sqlite3_ext")
	db, err := sql.Open("sqlite3_ext", ":memory:")
	if err != nil {
		panic(err)
	}

	var s string
	err = db.QueryRow("SELECT repeat('x', 2)").Scan(&s)
	if err != nil {
		panic(err)
	}

	fmt.Println(s)
}

Alternatively if you want to be able to add your own additional extensions you can just use the ConnectHook:

package main

import (
	"database/sql"
	"fmt"

	sqlite3 "github.com/mattn/go-sqlite3"
	stdlib "github.com/multiprocessio/go-sqlite3-stdlib"
)

func main() {
	sql.Register("sqlite3_ext",
		&sqlite3.SQLiteDriver{
			ConnectHook: stdlib.ConnectHook,
		})
	db, err := sql.Open("sqlite3_ext", ":memory:")
	if err != nil {
		panic(err)
	}

	var s string
	err = db.QueryRow("SELECT repeat('x', 2)").Scan(&s)
	if err != nil {
		panic(err)
	}

	fmt.Println(s)
}

Functions

Strings

Name(s)	Notes	Example
repeat, replicate		repeat('f', 5) = 'fffff'
strpos, charindex	0-indexed position of substring in string	strpos('abc', 'b') = 1
reverse		reverse('abc') = 'cba'
lpad	Omit the third argument to default to padding with spaces	lpad('22', 3, '0') = '022'
rpad	Omit the third argument to default to padding with spaces	rpad('22', 3, '0') = '220'
len	Shorthand for length	len('my string') = '9'
split_part	Split string an take nth split piece	split('1,2,3', ',', 0) = '1', split('1,2,3', ',' -1) = '3'
regexp	Go's regexp package, not PCRE	x REGEXP '[a-z]+$', REGEXP('[a-z]+$', x)
regexp_count	Number of times the regexp matches in string	regexp_count('abc1', '[a-z]1') = '1'
regexp_split_part	Regexp equivalent of split_part	regexp_split_part('ab12', '[a-z]1', 0) = 'a'

Aggregation

Most of these are implemented as bindings to gonum.

Name(s)	Notes	Example
stddev, stdev, stddev_pop		stddev(n)
mode		mode(n)
median		median(n)
percentile, perc	Discrete	perc(response_time, 95)
percentile_25, perc_25, percentile_50, perc_50, percentile_75, perc_75, percentile_90, perc_90, percentile_95, perc_95, percentile_99, perc_99	Discrete	perc_99(response_time)
percentile_cont, perc_cont	Continuous	perc_cont(response_time, 95)
percentile_cont_25, perc_cont_25, percentile_cont_50, perc_cont_50, percentile_cont_75, perc_cont_75, percentile_cont_90, perc_cont_90, percentile_cont_95, perc_cont_95, percentile_cont_99, perc_cont_99	Continuous	perc_cont_99(response_time)

Net

Name(s)	Notes	Example
url_scheme		url_scheme('https://x.com:90/home.html') = 'https'
url_host		url_host('https://x.com:90/home.html') = 'x.com:90'
url_port		url_port('https://x.com:90/home.html') = '90'
url_path		url_path('https://x.com/some/path.html?p=123') = '/some/path.html'
url_param		url_param('https://x.com/home.html?p=123&z=%5B1%2C2%5D#section-1', 'z') = '[1,2]'
url_fragment		url_fragment('https://x.com/home.html?p=123&z=%5B1%2C2%5D#section-1') = 'section-1'

Date

Best effort family of date parsing (uses dateparse) and date part retrieval. Results will differ depending on your computer's timezone.

Name(s)	Notes	Example
date_year		date_year('2021-04-05') = 2021
date_month	January is 1, not 0	date_month('May 6, 2021') = 5
date_day		date_day('May 6, 2021') = 6
date_yearday	Day offset in year	date_yearday('May 6, 2021') = 127
date_hour	24-hour	date_hour('May 6, 2021 4:50 PM') = 16
date_minute		date_minute('May 6, 2021 4:50') = 50
date_second		date_second('May 6, 2021 4:50:20') = 20
date_unix		date_unix('May 6, 2021 4:50:20') = 1588740620
date_rfc3339		date_rfc3339('May 6, 2021 4:50:20') = 2020-05-06T04:50:20Z

Math

Name(s)	Notes	Example
acos		acos(n)
acosh		acosh(n)
asin		asin(n)
asinh		asinh(n)
atan		atan(n)
atanh		atanh(n)
ceil, ceiling		ceil(n)
cos		ceil(n)
cosh		cosh(n)
degrees		degrees(radians)
exp	e^n	exp(n)
floor		floor(n)
ln, log		log(x)
log10		log10(x)
log2		log2(x)
mod		mod(num, denom)
pi		pi()
pow, power		pow(base, exp)
radians		radians(degrees)
sin		sin(n)
sinh		sinh(n)
sqrt		sqrt(n)
tan		tan(n)
tanh		tanh(n)
trunc, truncate	Rounds up to zero if negative, down to zero if positive.	trunc(-10.9) = -10, trunc(10.4) = 10.0

Encoding

Name(s)	Notes	Example
base64	Convert string to base64	base64(s)
from_base64	Convert string from base64	from_base64(s)
base32	Convert string to base32	base32(s)
from_base32	Convert string from base32	from_base32(s)
md5	Hex md5 sum of string	md5(s)
sha1	Hex sha1 sum of string	sha1(s)
sha256	Hex sha256 sum of string	sha256(s)
sha512	Hex sha512 sum of string	sha512(s)
sha3_256	Hex sha3_256 sum of string	sha3_256(s)
sha3_512	Hex sha3_512 sum of string	sha3_512(s)
blake2b_256	Hex blake2b_256 sum of string	blake2b_256(s)
blake2b_512	Hex blake2b_512 sum of string	blake2b_512(s)

How is this tested?

There is 95% test coverage and automated tests on Windows, macOS and Linux.

I just want to use it as a CLI or GUI

See dsq (a command-line tool for executing SQL on data files) and DataStation, a GUI application for querying and building reports with data from databases, servers, and files.

Contribute

Join the #dev channel on the Multiprocess Labs Discord.

If you have an idea for a new function, say so on the Discord channel or open an issue here.

Make sure the function doesn't already exist in dsq (or the sqlite3 CLI).

License

This software is licensed under an Apache 2.0 license.