Golang SQLite Examples: A Complete Guide

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. Its simplicity, reliability, and small footprint make SQLite an ideal database for embedded applications.

Golang is a statically-typed, compiled programming language designed by Google. It is syntactically similar to C but provides memory safety through garbage collection and supports concurrent programming out of the box.

In this comprehensive guide, we will explore various examples of using SQLite database with Golang. Specifically, we will cover:

• Connecting to an existing SQLite database
• Creating a new SQLite database
• Creating tables
• Inserting data
• Querying data
• Updating data
• Deleting data

So let‘s get started!

Prerequisites

Before we look at the examples, you need to have Golang and SQLite installed on your system.

To check if you have Golang installed, run:

$ go version

For SQLite, run:

$ sqlite3 --version

If you don‘t have them installed already, follow these steps:

Install Golang

1. Download the latest Golang package from golang.org/dl
2. Extract the archive to /usr/local
3. Add /usr/local/go/bin to PATH environment variable

Install SQLite

$ sudo apt install sqlite3 libsqlite3-dev

With the prerequisites met, let‘s connect Golang with SQLite!

Connect to an Existing SQLite Database

First, we need an existing SQLite database file. Let‘s create one using the sqlite3 command line shell:

$ sqlite3 test.db 
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE users(
   ...> id INTEGER PRIMARY KEY,
   ...> name TEXT NOT NULL,
   ...> age INTEGER NOT NULL
   ...> );
sqlite> .quit

This creates a file named test.db in the current directory with a users table inside it.

Next, write a simple Golang program to connect to this database:

package main

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

func main() {

    db, err := sql.Open("sqlite3", "./test.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    log.Printf("Successfully connected to SQLite database.")
}

Here‘s how it works:

• Import database/sql package which provides the database/sql API. Also import github.com/mattn/go-sqlite3 a popular SQLite driver.
• sql.Open() opens a database connection.
• The connection is closed using defer db.Close() which gets executed after the main function exits.
• We print a message if the connection succeeds without any error.

Run it:

$ go run connect.go
2019/05/17 11:13:38 Successfully connected to SQLite database.

EASY! We now have a program that can connect to the existing SQLite database.

Create a New SQLite Database

Instead of using an existing database, let‘s see how to create a new SQLite database from a Golang program.

We will use the os.Create() function to create a new empty file.

package main

import (
  "log"
  "os"
)

func main() {

  // Create SQLite file
  f, err := os.Create("new.db") 
  if err != nil {
    log.Fatal(err.Error())
  }
  log.Printf("SQLite database created")
  defer f.Close()

}

Run it:

$ go run create_newdb.go  
2019/05/19 17:46:22 SQLite database created
$ 
$ ls new.db 
new.db

A new empty database file new.db is created successfully!

While this creates an empty SQLite database file, there are no tables inside it yet. Let‘s look at how to create a new table next.

Create a New Table

To create tables, we need to execute SQL CREATE TABLE statements.

The database/sql package provides two handy methods:

1. db.Exec() to execute queries that modify data like CREATE TABLE, INSERT etc.

2. db.Query() to execute SELECT queries that return data.

Let‘s use db.Exec() here:

package main

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

func main() {

  db, _ := sql.Open("sqlite3", "./new.db")
  defer db.Close()

  sql_table := ` CREATE TABLE IF NOT EXISTS users (
                    id integer PRIMARY KEY, 
                    name text NOT NULL,
                    age integer NOT NULL)`

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

  log.Printf("Users table created successfully")

}

The key things to note here:

• We execute the SQL CREATE TABLE statement using db.Exec().
• It returns two values – a Result and an error.
• We only need to check for the error to see if table was created properly.

On running it:

$ go run create_table.go  
2019/05/20 19:23:37 Users table created successfully

Let verify that the table exists now:

$ sqlite3 new.db 
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.  
sqlite> .tables
users  

Great! Our Golang program successfully created a new SQLite table.

Now that we know how to create tables, let‘s look at inserting data next.

Insert Data into Table

To insert data into SQLite tables, we can again use the db.Exec() method.

Let‘s populate the users table with some sample data:

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

func main() {

  db, _ := sql.Open("sqlite3", "./new.db")
  defer db.Close()

  // insert 
  sql_insert := `INSERT INTO users (name, age) VALUES ("John", 25)`  
  _, e := db.Exec(sql_insert)

  // insert multiple
  sql_insert = `INSERT INTO users (name, age) VALUES ("Peter", 30), ("Sam", 35)`
  _, e = db.Exec(sql_insert)

  if e != nil {
    log.Fatal(e)
  }
}

Here we:

• Use a simple INSERT statement to add a single row.
• Then bulk insert multiple rows using multiple value pairs.
• As always just need to handle any errors from db.Exec().

Verify the inserted rows:

sqlite> SELECT * FROM users; 
id          name        age  
----------  ----------  ----------
1           John        25
2           Peter       30
3           Sam         35

And there we have it! Our Golang program successfully inserted new data into the SQLite database table.

Query Data from Table

So far we have seen statements like CREATE TABLE and INSERT that modify data.

To query data from SQLite database tables, we can use the db.Query() method.

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

func main() {

  db, _ := sql.Open("sqlite3", "./new.db")
  defer db.Close()

  // perform a SELECT 
  rows, err := db.Query("SELECT name, age FROM users")
  if err != nil {
    log.Fatal(err)
  }

  defer rows.Close() // good practice to close rows

  var name string
  var age int 

  // iterate over the rows, reading the data
  for rows.Next() {
    err := rows.Scan(&name, &age)  
    if err != nil {
      log.Fatal(err)
    }
    log.Printf("%s is %d\n", name, age)   
  }

  // check for errors after we are done iterating 
  err = rows.Err() 
  if err != nil {
    log.Fatal(err)
  }

}

The main things to note here:

• We use db.Query() to execute a SELECT statement.
• It returns *Rows – which allows us to iterate over result rows one by one.
• Each row‘s data is read by calling rows.Scan() into Go variables.
• We call rows.Next() to check if there‘s another row after scanning each row.
• Finally check for any error after processing all rows.

On running it prints:

John is 25  
Peter is 30
Sam is 35

And thats it! We have now successfully implemented a query to read SQLite data in Golang.

Update Data in Table

To update existing data, we can again leverage db.Exec() to run UPDATE queries.

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

func main() {

  // Open database
  db, _ := sql.Open("sqlite3", "./new.db")
  defer db.Close()

  // execute update query  
  sql_update := `UPDATE users SET age=36 WHERE name="Sam"` 
  _, e := db.Exec(sql_update)

  if e != nil {
    log.Fatal(e)
  }
}

That updates the age for user "Sam". Verify it:

 sqlite> SELECT * FROM users WHERE name="Sam";  
 id          name        age
----------  ----------  ----------
3           Sam         36

There we go! Simple UPDATE operation to modify existing data.

Delete Rows from Table

Finally, let‘s discuss deleting data from SQLite table.

Again similar to UPDATE, we can run DELETE statement with db.Exec().

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

func main() {

  // Open database
  db, _ := sql.Open("sqlite3", "./new.db")
  defer db.Close()

  // execute delete statement
  sql_delete := `DELETE FROM users WHERE age > 30`
  _, e := db.Exec(sql_delete)  

  if e != nil {
    log.Fatal(e)
  }  
}

This deletes users with age over 30. Let‘s verify:

sqlite> SELECT * FROM users;
id          name        age        
----------  ----------  ----------
1           John        25

Excellent! With DELETE statement implemented, we have covered all major SQLite operations with Golang.

Conclusion

And thats a wrap! In this detailed guide, we went through several useful examples demonstrating how to work with SQLite databases using Golang – connecting to SQLite, creating tables, inserting data, querying data, updating data and deleting data.

As you can see, the database/sql package coupled with a go-sqlite3 driver provides a straightforward API to work with SQLite databases in Golang.

I hope these examples provide you the foundation to start building practical Golang applications using SQLite for data storage!