Write a sqlite client with golang

I usually use the sqlite3 command line tool to query data from sqlite database, but there are some problems to use the sqlite3 command line…

Stephen Lee

Oct 4, 2022 — 3 min read

I usually use the sqlite3 command line tool to query data from sqlite database, but there are some problems to use the sqlite3 command line tool.

We can’t use left arrow key, and right arrow key to modify the sql statements
We can’t use up key and down key to navigate in the history

In order to fix the problem, I wrote an application with golang, with called dbcat , with this tool, I can navigate between history and use the left or right key to navigate the sql and update it.

Sqlite3 command line tool$ sqlite3 test.db
SQLite version 3.32.2 2021-07-12 15:00:17
Enter ".help" for usage hints.
sqlite> create table tbl1(one text, two int);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite> ^[[A^[[B^[[D^[[C^[[A is up arrow key
^[[B is down arrow key
^[[C is right arrow key
^[[D is left arrow key

dbcat command line tool

The following is the output of the dbcat running on android physical device.$ dbcat -dbPath=test.db
dbcat> create table tbl1(one text, two int);
dbcat> insert into tbl1 values('hello!',10);
dbcat> insert into tbl1 values('goodbye', 20);
dbcat> select * from tbl1;
one two
---------- ----------
hello! 10
goodbye 20
dbcat> CREATE TABLE tbl2 (
...> f1 varchar(30) primary key,
...> f2 text,
...> f3 real
...> );
dbcat> .tables
name
----------
tbl1
tbl2
dbcat> .schema
CREATE TABLE tbl1(one text, two int)CREATE TABLE tbl2 ( f1 varchar(30) primary key, f2 text, f3 real )dbcat> .schema tbl1
CREATE TABLE tbl1(one text, two int)dbcat> .columns tbl2
cid name type notnull dflt_value pk
---------- ---------- ---------- ---------- ---------- ----------
0 f1 varchar(30) 0 <nil> 1
1 f2 TEXT 0 <nil> 0
2 f3 REAL 0 <nil> 0
dbcat> .q
2022/10/04 21:05:18 shell.go:168: debug - History saved to /var/folders/fj/qqqdntgs3hg2s31zhlxgxzmw0000gn/T/.dbcat

Code snippet

The following is the code snippet to implement the keys, I use the third party library liner, which I found in the SeaweedFS shell implementation.line = liner.NewLiner()
defer prepareExit()line.SetCtrlCAborts(true)
line.SetTabCompletionStyle(liner.TabPrints)setCompletionHandler()
loadHistory()cmdLines := make([]string, 0)
isMultiLines := false
for {
prompt := fmt.Sprintf("%s> ", common.AppName)
if isMultiLines {
prompt = fmt.Sprintf("%*s> ", len(common.AppName), "...")
}
cmd, err := line.Prompt(prompt)
if err != nil {
if err != io.EOF {
fmt.Printf("%v\n", err)
}
return err
}cmd = strings.TrimSpace(cmd)
if cmd == "" {
continue
}if cmd == "\\c" {
cmdLines = cmdLines[:0]
isMultiLines = false
continue
}if !strings.HasPrefix(cmd, ".") && !strings.HasSuffix(cmd, ";") {
cmdLines = append(cmdLines, cmd)
isMultiLines = true
continue
}if isMultiLines {
cmdLines = append(cmdLines, cmd)
cmd = strings.Join(cmdLines, " ")
cmdLines = cmdLines[:0]
isMultiLines = false
}if err = processCmd(cmd, commandEnv); errors.Is(err, common.ErrExit) {
break
}
}

Build for android devices

To build application run on android device, because I use the sqlite3 driver, it need CGO, the doc Cross Compiling from MAC OSX describe how to build other platform from Mac, I added the build tasks on Makefile , the content for the Makefile is like below:appName=dbcatamd64-build:
@echo "build ${appName} for android emulator"
@CC=x86_64-linux-musl-gcc CXX=x86_64-linux-musl-g++ GOARCH=amd64 GOOS=linux CGO_ENABLED=1 go build -ldflags "-linkmode external -extldflags -static" -o ${appName}_amd64 main.go
@upx ${appName}_amd64arm64-build:
@echo "build ${appName} for android physical machine"
@CC=aarch64-linux-musl-gcc CXX=aarch64-linux-musl-g++ GOARCH=arm64 GOOS=linux CGO_ENABLED=1 go build -ldflags "-linkmode external -extldflags -static" -o ${appName}_arm64 main.go
@upx ${appName}_arm64mac-build:
@echo "build ${appName} for mac"
@go build -o ${appName}_mac main.go