Tutorial: Implementing Repository with GORM and SQLite

In the previous part of the series we created the required interface for you invoice generation/management web application.

In this post we are going to provide and implementation of the CompanyRepository .

In our requirements in the first part of the series we decided that we are going to use SQLite as our DBMS. Let's implement together the integration with SQLite.

Implementing the CompanyRepository

We are going to use GORM to interact with the database. However, we are going to ensure that our database models and code will be decoupled so we can switch ORM/driver or even database in the future.

We are going to work in the sqlite package.

Let's first create a branch

 git checkout -b company-repo

and then install the dependencies:

go get gorm.io/gorm
go get gorm.io/driver/sqlite
go get gorm.io/datatypes"

then in our sqlite/sqlite.go add

package sqlite

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

func SetupDB(path string) (*gorm.DB, error) {
    db, err := gorm.Open(sqlite.Open(path), &gorm.Config{})
    if err != nil {
        return nil, err
    }

    err = db.AutoMigrate(
        &dbcompany{},
    )
    if err != nil {
        return nil, err
    }

    return db, nil
}

Explanation:

SetupDB function creates a connection to the SQLite database and runs the migrations, meaning it creates the required tables if not exists or modifies them according to our schema.

and then create a file sqlite/company.go

package sqlite

import (
    "context"
    "invoicehub"

    "gorm.io/datatypes"
    "gorm.io/gorm"
)

var _ invoicehub.CompanyRepository = (*companyRepository)(nil)

type dbcompany struct {
    ID           int    `gorm:"primaryKey"`
    Name         string `gorm:"type:text"`
    Address      datatypes.JSONType[invoicehub.Address]
    Email        string `gorm:"type:text"`
    TaxID        string `gorm:"type:text"`
    VatID        string `gorm:"type:text"`
    BankAccounts datatypes.JSONSlice[invoicehub.BankAccount]
}

type companyRepository struct {
    db *gorm.DB
}

func NewCompanyRepository(db *gorm.DB) invoicehub.CompanyRepository {
    return &companyRepository{db: db}
}

func (r *companyRepository) Create(ctx context.Context, company *invoicehub.Company) (int, error) {
    // Convert invoicehub.Company to dbcompany
    dbCompany := dbcompany{
        Name:         company.Name,
        Address:      datatypes.NewJSONType(company.Address),
        Email:        company.Email,
        TaxID:        company.TaxID,
        VatID:        company.VatID,
        BankAccounts: datatypes.NewJSONSlice(company.BankAccounts),
    }

    // Save to database

    if err := r.db.WithContext(ctx).Create(&dbCompany).Error; err != nil {
        return 0, err
    }

    company.ID = dbCompany.ID

    return company.ID, nil
}

func (r *companyRepository) Update(ctx context.Context, company *invoicehub.Company) error {
    var existingCompany dbcompany
    if err := r.db.WithContext(ctx).First(&existingCompany, company.ID).Error; err != nil {
        return err
    }

    existingCompany.Name = company.Name
    existingCompany.Address = datatypes.NewJSONType(company.Address)
    existingCompany.Email = company.Email
    existingCompany.TaxID = company.TaxID
    existingCompany.VatID = company.VatID
    existingCompany.BankAccounts = datatypes.NewJSONSlice(company.BankAccounts)

    if err := r.db.WithContext(ctx).Save(&existingCompany).Error; err != nil {
        return err
    }

    return nil
}

func (r *companyRepository) Get(ctx context.Context, id int) (invoicehub.Company, error) {
    var dbCompany dbcompany
    if err := r.db.WithContext(ctx).First(&dbCompany, id).Error; err != nil {
        return invoicehub.Company{}, err
    }

    company := invoicehub.Company{
        ID:           dbCompany.ID,
        Name:         dbCompany.Name,
        Address:      dbCompany.Address.Data(),
        Email:        dbCompany.Email,
        TaxID:        dbCompany.TaxID,
        VatID:        dbCompany.VatID,
        BankAccounts: dbCompany.BankAccounts,
    }

    return company, nil
}

❓why I used a dbcompany structs that basically reassembles our invoicehub.Company struct

We want our repository implementation to be decoupled with the domain Entity. The reason is flexibility to change in the future. We don't want our domain Entity to include any gorm related code (see the struct tags).

Writing unit tests

We have to verify that our implementation works. Let's write a unit test for that.

For unit test I like to use the testify package

go get github.com/stretchr/testify

Create a file sqlite/company_test.go .

package sqlite_test

import (
    "context"
    "invoicehub"
    "invoicehub/sqlite"
    "testing"

    "github.com/stretchr/testify/require"
)

func Test_companyRepository(t *testing.T) {
    db, err := sqlite.SetupDB(":memory:")
    require.NoError(t, err)

    repo := sqlite.NewCompanyRepository(db)
    ctx := context.Background()

    company := invoicehub.Company{
        Name:  "Example Ltd",
        Email: "info@example.com",
        TaxID: "123456789",
        VatID: "987654321",
        Address: invoicehub.Address{
            Street:     "123 Example St",
            City:       "Limassol",
            PostalCode: "12345",
            Country:    "CY",
        },
        BankAccounts: []invoicehub.BankAccount{
            {
                BankName:      "Example Bank",
                AccountNumber: "1234567890",
                IBAN:          "EX12345678901234567890",
                BIC:           "EXBIC123",
            },
        },
    }

    // Create a new company
    id, err := repo.Create(ctx, &company)
    require.NoError(t, err)
    require.NotZero(t, id)
    require.Equal(t, id, company.ID)

    // Get the company
    company2, err := repo.Get(ctx, id)
    require.NoError(t, err)
    require.Equal(t, company, company2)

    // Update the company
    company.Name = "Example Ltd 2"
    err = repo.Update(ctx, &company)
    require.NoError(t, err)

    updatedCompany, err := repo.Get(ctx, id)
    require.NoError(t, err)
    require.Equal(t, company, updatedCompany)

    // not found
    _, err = repo.Get(ctx, 999)
    require.Error(t, err)
}

💥 We cover just the basics test cases so we have some confidence that it works.

Let's run the tests

go test -v ./...

and you should get something like:

Pass the dependency in our main.go

Open cmd/main.go and do the following changes:

Pay attention, that I set a default path that will work with our docker setup

Also add to the .gitignore the data.sqlite3 file.

echo "data.sqlite3" >> .gitignore

Now let's test our docker setup.

make dev

notice the created file:

Let's open the file with an sqlite client. On Linux run:

Let's now commit our code so far

As usual you can find the code so far in the relate github branch .

Deployment

We need to setup a new environment variable in our production .env .

FH_DB_PATH=invoices.sqlite3

Now you can go back to your machine and run

make deploy

After deployment check that the invoices.sqlite3 file was created .

Conclusion

In this tutorial, we successfully implemented the CompanyRepository using GORM and SQLite, ensuring our code remains flexible and decoupled from the domain entity. We also wrote unit tests to verify our implementation and made necessary adjustments to our main.go to integrate the repository. Finally, we configured our production environment and verified the deployment.

In the next article we are going to implement the InvoiceRepository .

❤️ Please follow me on X or LinkedIn

If you have any questions please reach out to me via a comment or a DM in X.

Find the rest of the tutorials in this series here

Tutorial: Implementing Repository with GORM and SQLite

Comments

Building a Web App with Golang

Tutorial: Repository pattern in Golang with Test Driven Development

More from this blog

How to Create a SOCKS5 Proxy with VPN Tunneling Using Docker

How to generate PDF in Go

Crafting a Web app in Golang: Implementing the Service Layer

Tutorial: Repository pattern in Golang with Test Driven Development

Implementing the CompanyRepository

Writing unit tests

Pass the dependency in our main.go

Deployment

Conclusion

Command Palette

Comments

Building a Web App with Golang

Tutorial: Repository pattern in Golang with Test Driven Development

More from this blog

Implementing the CompanyRepository

Writing unit tests

Pass the dependency in our main.go

Deployment

Conclusion