How to Use SQLite With Go

January 27, 2025
Written by
Elijah Asaolu
Contributor
Opinions expressed by Twilio contributors are their own
Reviewed by

There’s no conversation about database management without mentioning SQL (Structured Query Language) — the most widely used language for interacting with relational databases. Additionally, among the various SQL vendors, SQLite stands out for its lightweight and serverless option.

In this tutorial, we’ll explore how to integrate SQLite with Go using the go-sqlite3 library, starting off by setting up a basic integration. Then, we'll build on that knowlege by creating a note-sharing app using the Gin web framework.

Prerequisites

To follow along with this tutorial, the following prerequisites are required.

  • Go 1.22 or above
  • SQLite 3 installed on your computer.
  • Familiarity with Go
  • Basic knowledge of SQL

Create a new Go project

With SQLite installed, create a new Go project, change into the new project directory, and initialise a new Go module by running the following commands.

mkdir go-sqlite-integration
cd go-sqlite-integration
go mod init go-sqlite

Next, install go-sqlite3 by running the following command:

go get github.com/mattn/go-sqlite3

However, it cannot directly communicate with specific databases by itself, because it doesn't know how to "speak" the language of each database. That’s where go-sqlite3 comes in. It acts as a bridge between the database/sql package and SQLite.

With the basic setup completed, we can now start integrating SQLite directly into our Go application.

Create a new database

Let’s start by creating a new database and creating a table inside the database. Create a new main.go file in your project's root folder and paste the following code into it.

package main

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

func main() {
	db, err := sql.Open("sqlite3", "./test.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	sqlStmt := `
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        name TEXT
    );
    `
	_, err = db.Exec(sqlStmt)
	if err != nil {
		log.Fatal(err)
	}
	log.Println("Table 'users' created successfully")
}

The code above starts by importing the necessary libraries. Then, it creates a new database using the sql.Open() function — this function is part of the database/sql package and accepts two parameters: a SQL driver and a data source.

In our case, "sqlite3" is the driver, indicating that a SQLite database will be connected to, and we attempt to open a new SQLite databasenamed test.db in the project's top-level directory. If the database doesn’t exist, it will automatically be created.

The code then defines an SQL statement to create the users table with id and name columns, and execute it using the db.Exec() function, while properly handling any errors that may occur.

Execute the code by running the following command:

go run main.go

Running this command will create the new users table, and you should see a success message. You can also verify that the database was created by running the following command:

sqlite3 test.db .schema

This command displays the schema of the database, and you should see output showing the tables and their definitions.

Insert data into the database

With the database created, you can proceed to run CRUD (Create, Read, Update, and Delete) operations on it as you would other SQL databases. For example, we'll run an INSERT query to insert a new user.

First, update main.go with the code below.

package main
import (
	"database/sql"
	"log"
	_ "github.com/mattn/go-sqlite3"
)
func main() {
	db, err := sql.Open("sqlite3", "./test.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	_, err = db.Exec("INSERT INTO users(name) VALUES(?)", "John Doe")
	if err != nil {
		log.Fatal(err)
	}
	log.Println("New user inserted successfully")
}

Run your application with the command below,and you should again get a success message if everything went well or a descriptive error message otherwise.

go run main.go

Select data from the database

To execute a query that returns rows, such as a SELECT statement, the database/sql package provides a db.Query() method. This is in contrast to the db.Exec() method, which only executes a query without returning any rows.

Update main.go to match the code below.

package main

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

func main() {
	db, err := sql.Open("sqlite3", "./test.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	rows, err := db.Query("SELECT id, name FROM users")
	if err != nil {
		log.Fatal(err)
	}

	defer rows.Close()
	for rows.Next() {
		var id int
		var name string
		err = rows.Scan(&id, &name)
		if err != nil {
			log.Fatal(err)
		}
		log.Printf("User: %d, %s", id, name)
	}
	if err = rows.Err(); err != nil {
		log.Fatal(err)
	}
}

As shown in the code above, we use db.Query() to execute a SELECT query and retrieve data from the "users" table. We stored the result in a row object and looped through it using rows.Next(). Furthermore, inside the loop, we use rows.Scan() to copy the data from each row into variables (id and name), and then log each user.

If you run your app at this point, you should see output similar to the one below, depending on the data you’ve inserted into your users table.

Terminal output showing users records from the sqlite database

Update and delete data in thedatabase

To update or delete records in the database, we return to utilizing the db.Exec() method. Update main.go to match the code below.

package main

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

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

	defer db.Close()
	// Update rows in the database
	result, err := db.Exec("UPDATE users SET name = ? WHERE id = ?", "Jane Doe", 3)
	if err != nil {
		log.Fatal(err)
	}
	affectedRows, err := result.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	log.Printf("Updated %d row(s)\n", affectedRows)

	// Delete rows from the database
	result, err = db.Exec("DELETE FROM users WHERE id = ?", 4)
	if err != nil {
		log.Fatal(err)
	}
	affectedRows, err = result.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	log.Printf("Deleted %d row(s)\n", affectedRows)
}

In this example, we used the db.Exec() method to run an UPDATE query and set a new value for the "name" column where id is 3. We also used the result.RowsAffected() method from the result to check how many rows were modified. Similarly, we used the same method to execute a DELETE query and remove the row where the "id" column is set to 4.

If you run your app, the name of the user with id 3 should be updated to "Jane Doe" and the user with id 4 should be deleted.

You can find all the code used in this section in this GitHub repository if you missed any steps.

Let's create a note sharing app with go-sqlite3 and Gin

So far, we’ve covered the basics of using SQLite in Go. We explored how to programmatically create a SQLite database from a Go app and perform CRUD operations on it.

Now, we'll focus on leveraging everything we’ve learned alongside other packages to build a simple note-taking app. For the web backend, we'll use the Gin web framework to handle routing and HTTP requests.

Our note-sharing app will allow users to create both private and public notes, delete notes, and share a link to any public note. All of the application's data will be stored in SQLite. Additionally, its implementation will be straightforward, with three routes: an index route to view all notes, a route to create new notes, and a final route to preview a shared note.

Set up a new project

Let’s start by creating a new project for the note app by running the three commands below.

mkdir noteapp
cd noteapp
go mod init noteapp

Next, install the go-sqlite3 and gin-gonic packages.

go get github.com/mattn/go-sqlite3 github.com/gin-gonic/gin

Now, inside your new Go project, create the following files and directories:

  • main.go: This file will define our main() function, and set up the application's routes

  • handlers/main.go: This file will contain all the route handler functions that determine what actions to take when specific routes are visited

  • templates/index.html: This file will include the HTML markup to render the index route, displaying all the notes

  • templates/create.html: This will contain the markup for the form that allows users to create new notes

  • templates/preview.html: This file will hold the markup to preview a shared note

After creating these files, your project structure should look like this:

noteapp/
├── handlers/
│   └── main.go
├── templates/
│   ├── create.html
│   ├── index.html
│   └── preview.html
└── main.go

Define the routes and their handlers

We can now start defining the core logic of our note sharing app. Let's start by creating all the handler functions that our app will rely on. Open the handlers/main.go file and paste the following code into it.

package handlers

import (
	"database/sql"
	"net/http"
	"time"

	"github.com/gin-gonic/gin"
)

type Note struct {
	ID        int       `json:"id"`
	Title     string    `json:"title"`
	Content   string    `json:"content"`
	CreatedAt time.Time `json:"created_at"`
	IsPublic  bool      `json:"is_public"`
}

func ShowCreateNotePage(c *gin.Context) {
	c.HTML(http.StatusOK, "create.html", nil)
}

func CreateNote(db *sql.DB) gin.HandlerFunc {
	return func(c *gin.Context) {
		title := c.PostForm("title")
		content := c.PostForm("content")
		isPublic := c.PostForm("is_public") == "on"

		_, err := db.Exec(`INSERT INTO notes (title, content, is_public) VALUES (?, ?, ?)`, title, content, isPublic)
		if err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to create note"})
			return
		}

		c.Redirect(http.StatusFound, "/")
	}
}

func ListNotes(db *sql.DB) gin.HandlerFunc {
	return func(c *gin.Context) {
		rows, err := db.Query("SELECT id, title, content, is_public, created_at FROM notes")
		if err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to retrieve notes"})
			return
		}
		defer rows.Close()

		var notes []Note
		for rows.Next() {
			var note Note
			if err := rows.Scan(&note.ID, &note.Title, &note.Content, &note.IsPublic, &note.CreatedAt); err != nil {
				c.JSON(http.StatusInternalServerError, gin.H{"error": "Error while scanning notes"})
				return
			}
			notes = append(notes, note)
		}

		c.HTML(http.StatusOK, "index.html", gin.H{
			"Notes": notes,
		})
	}
}

func DeleteNote(db *sql.DB) gin.HandlerFunc {
	return func(c *gin.Context) {
		id := c.Param("id")

		_, err := db.Exec(`DELETE FROM notes WHERE id = ?`, id)
		if err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to delete note"})
			return
		}

		c.Redirect(http.StatusFound, "/")
	}
}

func PreviewSharedNote(db *sql.DB) gin.HandlerFunc {
	return func(c *gin.Context) {
		id := c.Param("id")

		var note Note
		query := "SELECT id, title, content, created_at FROM notes WHERE id = ? AND is_public = 1"
		err := db.QueryRow(query, id).Scan(&note.ID, &note.Title, &note.Content, &note.CreatedAt)
		if err != nil {
			c.HTML(http.StatusNotFound, "404.html", nil)
			return
		}

		c.HTML(http.StatusOK, "preview.html", note)
	}
}

The code above defines a Note struct to represent a note and includes five handler functions: ShowCreateNotePage(), CreateNote(), ListNotes(), DeleteNote(), and PreviewSharedNote(). Each function is tied to a specific route for managing notes:

  • ShowCreateNotePage(): Renders the create.html template, which allows users to create a new note

  • CreateNote(): Retrieves the title, content, and isPublic fields from an HTML form and inserts the data into the database using an SQL insert statement. After creating the note, the user is redirected to the home page.

  • ListNotes(): Fetches all the notes from the database using the Select statement. It then populates a list with the retrieved notes and passes them to the index.html template for display.

  • DeleteNote(): Extracts the note's ID from the URL parameters and deletes the corresponding note from the database using an SQL Delete statement. After the deletion, the user is redirected back to the home page.

  • PreviewSharedNote(): Retrieves a specific note by its ID, but only if the note is public (is_public = 1). If found, it passes the note to the preview.html template for display; otherwise, it shows a 404 page.

Create the main function

The next step is to create our app's database, define all the necessary routes, and attach these routes to their respective handler functions. To do this, paste the following code inside the main.go file.

package main

import (
	"database/sql"
	"log"

	"noteapp/handlers"

	"github.com/gin-gonic/gin"
	_ "github.com/mattn/go-sqlite3"
)

var db *sql.DB

func initDB() {
	var err error
	db, err = sql.Open("sqlite3", "./notes.db")
	if err != nil {
		log.Fatal(err)
	}

	createTableQuery := `CREATE TABLE IF NOT EXISTS notes (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		title TEXT NOT NULL,
		content TEXT NOT NULL,
		is_public INTEGER NOT NULL DEFAULT 0,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);`

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

func main() {
	initDB()
	router := gin.Default()

	router.LoadHTMLGlob("templates/*")

	router.GET("/", handlers.ListNotes(db))
	router.GET("/create", handlers.ShowCreateNotePage)
	router.POST("/create", handlers.CreateNote(db))
	router.POST("/notes/:id/delete", handlers.DeleteNote(db))
	router.GET("/share/:id", handlers.PreviewSharedNote(db))

	port := ":8080"
	log.Printf("Server is running on http://localhost%s", port)

	if err := router.Run(port); err != nil {
		log.Fatalf("Error starting server: %s", err)
	}
}

In the first part of the code above, we import all the necessary packages, including the handlers package we created earlier. Next, we defined an initDB() function to create a new "notes" database with fields for id, title, content, is_public, and created_at.

In the `main()` function, we start by defining the initDB() function. After that, we set up the Gin router to load all HTML files from the templates folder using LoadHTMLGlob() so we can render them easily.

Next, we define three GET routes for displaying the main routes: one for listing all notes, another for showing the note creation page, and a third for previewing shared notes. We also define two POST routes to handle creating new notes and deleting existing ones.

Finally, we configure the app to run on port 8080. Once it starts running, a message will be logged so we know the server is up and running.

Build the route templates

What's left now is to add the necessary code to the HTML template files we created earlier. Paste the following code inside the templates/index.html file.

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Your Notes</title>
    <script src="https://cdn.tailwindcss.com"></script>
  </head>
  <body>
    
    <div class="max-w-4xl mx-auto p-4">
      <a href="/create" class="text-lg underline text-blue-600">Create New Note</a>
    </div>

    <div class="max-w-4xl mx-auto p-4">
      {{if not .Notes}}
      <p class="text-gray-600">You haven't created any notes yet.</p>
      {{else}}
      <div class="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-3 gap-4">
        {{range .Notes}}
        <div class="bg-white p-4 border-2 border-gray-300 rounded-xl">
          <div class="flex items-center">
            <h2 class="text-xl font-semibold">{{.Title}}</h2>
            {{if .IsPublic}}
            <span
              class="ml-auto text-xs bg-green-200 text-green-800 py-1 px-2 rounded-full">Public</span>
            {{end}}
          </div>

          <p class="pb-12 text-sm text-gray-500">{{.Content}}</p>

          <div class="mt-2 flex">
            <form action="/notes/{{.ID}}/delete" method="POST">
              <button class="text-sm text-orange-400 p-2 rounded" type="submit">Delete</button>
            </form>

            {{if .IsPublic}}
            <a href="/share/{{.ID}}" class="text-sm text-gray-500 p-2 rounded">Share</a>
            {{end}}
          </div>
        </div>
        {{end}}
      </div>
      {{end}}
    </div>
  </body>
</html>

This template retrieves and displays all the notes sent via the handler function. If any note has not been created, it displays a custom message.

We are leveraging Tailwind CSS utility classes for basic styling.

Next, paste the code below inside the templates/create.html file.

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <title>Create New Note</title>
  <script src="https://cdn.tailwindcss.com"></script>
</head>

<body>
  <div class="max-w-4xl mx-auto p-4">
    <form action="/create" method="POST" class="bg-white p-6">
      <div class="flex justify-between items-center mb-4 pb-4 border-b">
        <div class="flex items-center">
          <input type="checkbox" id="is_public" name="is_public" class="mr-2" />
          <label for="is_public" class="text-lg">Public Note</label>
        </div>
        <button type="submit" class="bg-blue-500 text-white py-2 px-4 rounded hover:bg-blue-600">
          Create Note
        </button>
      </div>

      <div class="mb-4">
        <input type="text" id="title" name="title" placeholder="Title" required
          class="w-full p-2 text-4xl font-bold focus:outline-none" />
      </div>

      <div class="mb-4">
        <textarea id="content" name="content" rows="10" placeholder="Start typing here..." required
          class="w-full p-2 text-lg focus:outline-none"></textarea>
      </div>
    </form>
  </div>
</body>

</html>

This template contains the form for users to input the note's title, content, and whether the note should be public or private.

Finally, paste the following code inside the templates/preview.html file to display the content of a shared (public) note.

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Shared Note</title>
    <script src="https://cdn.tailwindcss.com"></script>
  </head>
  <body>
    <div class="max-w-4xl mx-auto p-6 mt-10 rounded-lg">
      <h1 class="text-3xl font-bold mb-4">{{.Title}}</h1>
      <p class="text-sm text-gray-500"><em>Created on {{.CreatedAt}}</em></p>
      <p class="text-lg text-gray-700 mt-10 mb-6">{{.Content}}</p>
    </div>
  </body>
</html>

Test the application

Our note app is ready! Start the app by running:

go run main.go

Next, visit localhost:8080 in your browser. You should see an output similar to the one below indicating that we haven’t created any notes yet.

Screenshot of the note-taking app in Safari on macOS with no notes created, with a Create New Note link near the top left of the page.

Click the Create New Note link, and you should be redirected to the new note form as shown below. Fill in any data you want here; you should also try creating both public and private notes.

Screenshot of the note-taking app in Safari on macOS showing the edit note form, blank, with a Create Note button near the top right-hand corner.

Once you’ve created some notes, they should be visible on the index page, as shown below; you can also choose to delete notes or share public ones.

Screenshot of the note-taking app in Safari on macOS showing showing three existing notes in a horizontal grid, with options to delete and share them, and a Create New Note above the three notes.

That's how to create a note-sharing app with go-sqlite3 and Gin

In this tutorial series, we covered the basics of integrating an SQLite database with Go.

We started off by exploring how to create SQLite databases directly from our Go application, as well as how to create tables and perform CRUD operations on them. Then, we integrated SQLite with a web application built using the Gin web framework.

You can find the code used in this tutorial in this GitHub repository. Thanks for reading!

Elijah Asaolu is a technical writer and software engineer. He frequently enjoys writing technical articles to share his skills and experience with other developers.