Using Sqlite in Golang With Gorm Series - Introduction

Sqlite is a super power. It’s the most use database system in the world. Yet, a lot of people don’t think of Sqlite as a serious alternative to Postgeres, MySQL and other more recent SQL databases; In this series I hope I can change that mindset and impression about the super flexible and handy database.In this post I will show you why Sqlite is fantastic choice for many projects, how to use it with Golang and most importantly why it’s a serious contender and toolset you should have in your stack.

Needless to say the othe star in this series is GORM. Gorm is the defacto ORM for Golang. While there are other alternatives, but GORM takes the crown with super strong community support and super strong feature list.

What is GORM?

GORM is a popular ORM library designed specifically for Go. It abstracts away the complexity of database operations, allowing you to interact with your data using idiomatic Go syntax. This means you can focus on writing business logic without worrying about the intricacies of SQL queries or database connections. GORM feature list includes everthing you can expect from a ORM; Associations, Hooks, Eager Loading, Automigarations and more. With GORM you won’t have to reach out to any other tool to interact with your database in Golang.

Some of the key benefits of using GORM include:

• Declarative syntax: Define your models and relationships in a straightforward way.
• SQL query generation: GORM automatically generates efficient SQL queries for you.
• Database abstraction: Support for multiple databases, including SQLite, MySQL, PostgreSQL, and more.
• Auto Migrations: Save time on writing repetitive SQL code to make your tables and change them.

Overall, GORM is a great choice for all your database needs in Golang. In this series, we will learn how to implement Associations, Migrations, CRUD and more.

Why use SQLite?

SQLite is an embedded relational database that runs out from a single file. No server needed or a running process. Simple as reading data from a file. This makes it perfect for all the embedded usecases; think game history stored on your phone, last tunedin radio channel in your car and more. While Sqlite has always been a very good choice for those usecases, recently it has been re-embraced by the web development community. Sqlite bring some feature that are unique to it making it a really special system:

• Lightweight: SQLite has minimal resource requirements, making it ideal for any small VPS or a project you know won’t need to scale for long time.
• Self-contained: The entire database is stored in a single file, reducing the risk of data corruption and facilitating easier backup and migration processes.
• Easy to use: SQLite’s syntax is simple and intuitive, with a vast array of built-in features.
• Zero cost lookup: Unlike other database systems where you would have to face the N+1 Query Problem Sqlite has dirt cheap cost to look things up which opens up the door for simplicity never imagined before.

It’s important to also understand that Sqlite comes with some drawbacks mainly because of the insistance of the team to provide long lasting backward compatility. In other words, Sqlite doesn’t use the best defaults out of the box. To get the best out of it, you would need to tweak a couple of things which we will go through in this series as well.

The Power of Using SQLite with Golang

Combining SQLite with Go using GORM offers numerous advantages:

• Fast and Effecient: SQLite is highly optimized for performance, using techniques like caching and query optimization to deliver fast results.
• Little to no config needed: SQLite doesn’t require any configuration or setup, making it easy to get started quickly.
• Support transactions: SQLite supports transactions, which ensures data consistency and integrity in case of failures or errors.
• Flexibility: Using GORM enables you to easily switch between different databases or adapt your codebase for various use cases.
• Fully compiled server: Forget about complex deployment steps and running different processes, all you need is your compiled Go Binary - Nothing else

Sure, Sqlite is not the best choice for every project, but surely brings some unique features to the table. Okay enough with all the introduction and theory, let’s get started with the code. 🤓

Making and connecting to Sqlite Database in Golang

Let’s start by making a new go project in a new folder.

Starting a new go project/module

Firstly, let’s start by creating a new go module. You can do this by creating a new folder, navigating into the folder and running the go init command.

mkdir go-sqlite
cd go-sqlite
go mod init github.com/user/go-sqlite

Nice, now let’s create a new main.py file.

touch main.py

So now the folder looks like this:

.
├── go.mod
└── main.go

1 directory, 2 files

Installing the needed packages

To run GORM and use it with Sqlite, we need two packages for this. Firsly we need to install GORM itself. Secondly we need a sqlite adapter for Go. It’s important to notice that the main sqlite driver shipped with GORM depends on CGO. This means during build, your machine will need to have the C binaries along with the Go language itself. This is not the best way to handle this as there is another alternative we can use. I always prefer to use the adapter written by glebarez as it doesn’t depend on C library and it’s fully and purely writeen in Go. So let’s use that instead.

go get github.com/glebarez/sqlite 
go get gorm.io/gorm 

With those two packages installed we ready to make some Sqlite magic. That’s all it takes. 😼

Making and connecting to our Sqlite Database

As explained earlier unlike other db engines, the whole db syem in sqlite is stores on one file. Sqlite can aklso work straight out of memory which is amazing but we won’t get to that for now. Open up the main.go file in your favorite text editor and let’s write some code.

package main

import (
	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
)

func main() {
	db, err := gorm.Open(sqlite.Open("local.db"), &gorm.Config{})
	if err != nil {
		panic("Error connecting/creating the sqlite db")
	}
}

Notice the highlighted line above, in this line we connect to a database called local.db file in the same folder and if the file doesn’t exist we will create that. Also notice we are passing an empty gorm.Config{} struct there, this is where we can customize our connection to the db. We will explore this later. To run this code you simply run go run main.go in the same folder. Let’s run this code now and see the changes made to the folder content:

.
├── go.mod
├── go.sum
├── main.go
└── test.db

1 directory, 4 files

Notice the created .db file that was created automatically by GORM.

Making Users Table

Now that we have a database connection set up, let’s create a simple User model using GORM. First, we need to decide on the shape of the data we wish to store on the table. We do this by using the native Go struct, but we need to ensure it inherits the gorm.Model struct. This will allow GORM to add required properties automatically like created_at and updated_at which we don’t have to worry about them then.

type User struct {
	gorm.Model
	Name     string
	Email    string
	Password string
}

With this struct ready we can then pass this data shape to GORM and it will help us run automatic migrations to make a table on our databse that can hold our data in this shape. To do this we simply call AutoMigrate method with a refernce to this data structe and GORM will handle the rest.

package main

import (
	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
)

type User struct {
	gorm.Model
	Name     string
	Email    string
	Password string
}

func main() {
	db, err := gorm.Open(sqlite.Open("local.db"), &gorm.Config{})
	if err != nil {
		panic("Error connecting/creating the sqlite db")
	}
	db.AutoMigrate(&User{})
}

Notice the call on the highlighted line. Notice we also pass a pointer to that datastrucure/struct and GORM will take care of the rest. Let’s run this code go run main.go. By running this code, GORM will run the correct SQL queries to create the table to match our GO struct.

Checking the Shape of the Table in the DB

Let’s have a peack into the database now and see what GORM has done for us after we ran this code. You can do this by running sqlite3 local.db '.schema users'. Here we using the .schema function to show the structure of the table. Here is the output:

CREATE TABLE `users` (`id` integer PRIMARY KEY AUTOINCREMENT,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`name` text,`email` text,`password` text);
CREATE INDEX `idx_users_deleted_at` ON `users`(`deleted_at`);

Notice how we have an automatic index created on deleted_at property. This is done automatically by GORM to to make finding soft deleted records fast. Notice as well that id has been added and it’s useing an integer - later we will see how we can change that to be UUID if needed. Finally notice that multiple properties were added by GORM. This is the result of making our struct extending gorm.Model.

Conclusion

In this post we leared:

• How to make a simple sqlite database
• Make a user model
• Run auto migrations
• Create the user table.

This wraps up the first part of this series. In the next post we will learn how to make CRUD operations on our users table.