Getting Started with Sqlite in Go using Gorm

In this post I will show you how to communicate with a database in Go using Gorm, an ORM library that simplifies database interactions. We’ll be working with an SQLite database locally, but Gorm supports multiple databases. Let’s get started!

Setting Up the project with Gorm

To begin, we need to install two dependencies:

1. Gorm libary.
2. Sqlite adapter for go. For this post we will use a pure Go SQLite driver that doesn’t require C-Go.

Here’s how you can install them:

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

Now, let’s create a connection to our SQLite database.

package main

import (
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
    "log"
)

func main() {
    db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    if err != nil {
        log.Fatal("Failed to connect to database: ", err)
    }
}

This code snippet sets up a connection to an SQLite database named test.db. You can read more about this in the get started section in gorm docs

Defining the Data Structure for GORM in Sqlite database

Let’s assume we’re building a blog. The first thing we need is a Post model. Here’s a simple structure for it:

type Post struct {
    gorm.Model
    Title string
    Slug  string
}

The gorm.Model struct includes fields for ID, CreatedAt, UpdatedAt, and DeletedAt. This simplifies our model definition significantly. You can read more about this behaviour gorm docs

Auto-Migration in Gorm

Gorm can automatically create tables based on our model definitions. Automigration is a great feature in Gorm and can save you a lot of time. You can read more about this in gorm docs. Let’s auto-migrate our Post model:

db.AutoMigrate(&Post{})

Notice I’m passing a reference to Post and not the Post variable itself. You will see this pattern multiple times working with gorm. Think about it this way, Post defines a shape of part of the memory and we need to pass the location of that memory not the actual memory and its contents. Back to migration!

Running the previous command will create a table named posts with the appropriate columns.

Verifying the Schema

To verify, open the SQLite database and check the schema:

sqlite3 test.db
.schema posts

You should see something like this:

CREATE TABLE `posts` (
    `id` integer,
    `created_at` datetime,
    `updated_at` datetime,
    `deleted_at` datetime,
    `title` text,
    `slug` text,
    PRIMARY KEY (`id`),
    UNIQUE (`slug`)
);

Adding a Unique Index

To ensure each post’s slug is unique, we can add a unique index:

type Post struct {
    gorm.Model
    Title string
    Slug  string `gorm:"uniqueIndex"`
}

Notice this is done by using the template descriptors in go. This is a very unique way to customize database behaviour in gorm. Works really well. You can read more in gorm docs Rerun the auto-migrate command to update the schema.

Manual Migrations

Gorm has first citizin support for manual migrations as well. Depending on your project setup, you might need to do things manually. For instance, let’s add a Likes field to our Post model:

db.Migrator().AddColumn(&Post{}, "Likes")

This adds an integer column named Likes to the posts table.

Creating Entries

Next, let’s create a function to add posts to our database:

func createPost(db *gorm.DB, title, slug string) (*Post, error) {
    post := Post{Title: title, Slug: slug}
    result := db.Create(&post)
    if result.Error != nil {
        return nil, result.Error
    }
    return post, nil
}

Notice how we pass a reference to the post struct into db.Create function. This function will create the entry and update the post struct with other details such as id, createdAt and so on.

Here’s how to use this function:

post, err := createPost(db, "New Post Title", "new-slug")
if err != nil {
    log.Fatal("Failed to create post: ", err)
}
fmt.Println(post)

Retrieving Entries

To retrieve a post by its slug, we can use the following function:

func getPostBySlug(db *gorm.DB, slug string) (*Post, error) {
    post := Post{} 
    result := db.First(&post, "slug = ?", slug)
    if result.Error != nil {
        return nil, result.Error
    }
    return &post, nil
}

Similar pattern happens here, we pass a reference to the empty struct and gorm will populate that struct with the values when found.

Here’s how to use it:

post, err := getPostBySlug(db, "new-slug")
if err != nil {
    log.Fatal("Failed to retrieve post: ", err)
}
fmt.Println(post)

Custom String Representation

To print our Post struct nicely, we can implement the Stringer interface:

func (p Post) String() string {
    return fmt.Sprintf("Post Title: %s, Slug: %s", p.Title, p.Slug)
}

Now, when we print a Post instance, it will display in a readable format.

Full Example

Here’s the complete code for reference:

package main

import (
    "fmt"
    "log"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type Post struct {
    gorm.Model
    Title string
    Slug  string `gorm:"uniqueIndex"`
    Likes int
}

func (p Post) String() string {
    return fmt.Sprintf("Post Title: %s, Slug: %s", p.Title, p.Slug)
}

func main() {
    db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
    if err != nil {
        log.Fatal("Failed to connect to database: ", err)
    }

    db.AutoMigrate(&Post{})

    post, err := createPost(db, "New Post Title", "new-slug")
    if err != nil {
        log.Fatal("Failed to create post: ", err)
    }
    fmt.Println(post)

    retrievedPost, err := getPostBySlug(db, "new-slug")
    if err != nil {
        log.Fatal("Failed to retrieve post: ", err)
    }
    fmt.Println(retrievedPost)
}

func createPost(db *gorm.DB, title, slug string) (*Post, error) {
    post := &Post{Title: title, Slug: slug}
    result := db.Create(post)
    if result.Error != nil {
        return nil, result.Error
    }
    return post, nil
}

func getPostBySlug(db *gorm.DB, slug string) (*Post, error) {
    var post Post
    result := db.First(&post, "slug = ?", slug)
    if result.Error != nil {
        return nil, result.Error
    }
    return &post, nil
}

Conclusion

In this blog post, we covered how to use Gorm with an SQLite database in Go. We learned how to set up the environment, define data structures, perform auto-migrations, add unique indices, create and retrieve entries, and customize the string representation of our data models. Gorm is a powerful ORM that simplifies database operations in Go, and I hope you find it as useful as I do. Let me know what you want to see next!

Happy coding!