[Golang] sqlite3 Database Example

Introduction

This post gives an example of Go application which uses SQLite SQL database engine. We will write a Go program that opens a database, creates a table in the database, writes some data to the table, and then reads the data from the table.

Golang provides a generic interface around SQL (or SQL-like) databases [5]. To use SQLite, we need to install the Go SQLite driver first. We will use the popular mattn/go-sqlite3 driver in our program. Install the driver by:

$ go get -u github.com/mattn/go-sqlite3

Source Code

The following is the source code of Go program that uses SQLite (version 3):

sqlite3.go | repository | view raw

package mylib

import (
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
)

type TestItem struct {
	Id	string
	Name	string
	Phone	string
}

func InitDB(filepath string) *sql.DB {
	db, err := sql.Open("sqlite3", filepath)
	if err != nil { panic(err) }
	if db == nil { panic("db nil") }
	return db
}

func CreateTable(db *sql.DB) {
	// create table if not exists
	sql_table := `
	CREATE TABLE IF NOT EXISTS items(
		Id TEXT NOT NULL PRIMARY KEY,
		Name TEXT,
		Phone TEXT,
		InsertedDatetime DATETIME
	);
	`

	_, err := db.Exec(sql_table)
	if err != nil { panic(err) }
}

func StoreItem(db *sql.DB, items []TestItem) {
	sql_additem := `
	INSERT OR REPLACE INTO items(
		Id,
		Name,
		Phone,
		InsertedDatetime
	) values(?, ?, ?, CURRENT_TIMESTAMP)
	`

	stmt, err := db.Prepare(sql_additem)
	if err != nil { panic(err) }
	defer stmt.Close()

	for _, item := range items {
		_, err2 := stmt.Exec(item.Id, item.Name, item.Phone)
		if err2 != nil { panic(err2) }
	}
}

func ReadItem(db *sql.DB) []TestItem {
	sql_readall := `
	SELECT Id, Name, Phone FROM items
	ORDER BY datetime(InsertedDatetime) DESC
	`

	rows, err := db.Query(sql_readall)
	if err != nil { panic(err) }
	defer rows.Close()

	var result []TestItem
	for rows.Next() {
		item := TestItem{}
		err2 := rows.Scan(&item.Id, &item.Name, &item.Phone)
		if err2 != nil { panic(err2) }
		result = append(result, item)
	}
	return result
}

Now we open database and create a table in our test program. Then write some data to the table and then read the data from the table.

sqlite3_test.go | repository | view raw

package mylib

import "testing"

func TestAll(t *testing.T) {
	const dbpath = "foo.db"

	db := InitDB(dbpath)
	defer db.Close()
	CreateTable(db)

	items := []TestItem{
		TestItem{"1", "A", "213"},
		TestItem{"2", "B", "214"},
	}
	StoreItem(db, items)

	readItems := ReadItem(db)
	t.Log(readItems)

	items2 := []TestItem{
		TestItem{"1", "C", "215"},
		TestItem{"3", "D", "216"},
	}
	StoreItem(db, items2)

	readItems2 := ReadItem(db)
	t.Log(readItems2)
}

Output of Test Code

=== RUN   TestAll
--- PASS: TestAll (0.76s)
        sqlite3_test.go:19: [{1 A 213} {2 B 214}]
        sqlite3_test.go:28: [{3 D 216} {2 B 214} {1 C 215}]
PASS

Tested on: Ubuntu Linux 15.10, Go 1.5.2.

References:

[1]	Google Search go sqlite

[2]	Google Search golang initialize struct array

[3]	mattn/go-sqlite3 · GitHub

[4]	SQLDrivers · golang/go Wiki · GitHub

(SQLite)

[5]	sql - The Go Programming Language

[6]	sqlite - INSERT IF NOT EXISTS ELSE UPDATE? - Stack Overflow

[7]	SQLite "INSERT OR REPLACE INTO" vs. "UPDATE ... WHERE" - Stack Overflow

[8]	How do I check in SQLite whether a table exists? - Stack Overflow

[9]	constraints - insert if not exists statement in sqlite - Stack Overflow

[10]	php - INSERT or REPLACE is creating duplicates - Stack Overflow

[11]	sqlite - How to get INSERT OR IGNORE to work - Stack Overflow

[12]	android - SQLiteDatabase insert or replace if changed - Stack Overflow

[13]	sql - SQLite Order By Date - Stack Overflow

[14]	inserting current date and time in sqlite database - Stack Overflow

[15]	ios - SQLite inserting bool value - Stack Overflow

(RSS)

[16]	news feed - RSS update single item - Stack Overflow

[17]	syndication - RSS Item updates - Stack Overflow

(News)

[18]	Early preview of a DB driver for SQLite without CGO (ATM Linux/Intel only) : golang GitHub - cznic/sqlite: Package sqlite is an in-process implementation of a self-contained, serverless, zero-configuration, transactional SQL database engine. (Work In Progress)

[19]	Blog Post: Playing Around with BoltDB : golang Playing Around with BoltDB - zupzup

[20]	Badger – A fast key-value store written natively in Go \| Hacker News GitHub - dgraph-io/badger: Fastest key/value store in Go.

[21]	Good approach to interacting with databases? : golang

[22]	sqlingo is a SQL DSL & ORM library in Go. It generates code from your database and lets you write SQL queries easily. : golang

[23]	Golang SQL Layer on FoundationDB : golang

[24]	ObjectBox: new super-fast DB to store for Go objects (structs) : golang ObjectBox: Fast object-oriented database for Go \| Hacker News

[25]	Sqlx, Dat, Pg. What's the difference and what should I use to connect to a Postgres DB? : golang

[26]	cloudfoundry/go-diodes: Diodes are ring buffers manipulated via atomics. : golang

[27]	Do you use database in your Go project? : golang

[28]	Any decent SQL query builders out there? : golang

[29]	Enum and SQL databases : golang

[30]	Database/sql or sqlx: should I use transactions for everything or no? : golang

[31]	How do I simulate Database errors, so I can test the fault tolerance level of my code. : golang