How to Use SQLite With Go
Time to read: 7 minutes
How To Use SQLite with GoLang
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.
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.
Next, install go-sqlite3 by running the following command:
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.
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:
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:
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.
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.
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.
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.
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.
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.
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.
Next, install the go-sqlite3 and gin-gonic packages.
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 routeshandlers/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:
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.
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 noteCreateNote()
: Retrieves thetitle
,content
, andisPublic
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.
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.
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.
Next, paste the code below inside the templates/create.html file.
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.
Test the application
Our note app is ready! Start the app by running:
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.
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.
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.
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.
Related Posts
Related Resources
Twilio Docs
From APIs to SDKs to sample apps
API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
Resource Center
The latest ebooks, industry reports, and webinars
Learn from customer engagement experts to improve your own communication.
Ahoy
Twilio's developer community hub
Best practices, code samples, and inspiration to build communications and digital engagement experiences.