# Tutorial:  Implementing Repository with GORM and SQLite

In the [previous](https://blog.gkomninos.com/tutorial-defining-the-domain-entities) part of the [series](https://blog.gkomninos.com/series/webapp-using-golang) 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](https://blog.gkomninos.com/series/webapp-using-golang) 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](https://gorm.io/index.html) 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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1718554572316/c0807d86-3dd7-4ff7-8dc6-9b103e33204b.png align="center")

Let's first create a branch

```bash
 git checkout -b company-repo
```

and then install the dependencies:

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

then in our `sqlite/sqlite.go` add

```go
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`

```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](https://github.com/stretchr/testify) package

```bash
go get github.com/stretchr/testify
```

Create a file `sqlite/company_test.go` .

```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

```bash
go test -v ./...
```

and you should get something like:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1718557926092/177442a8-6009-4f09-856e-0b6de6114315.png align="center")

### Pass the dependency in our main.go

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1718558262745/caac1712-5fe5-41d6-bcd8-0ed4922d881c.png align="center")

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

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

```bash
echo "data.sqlite3" >> .gitignore
```

Now let's test our docker setup.

```bash
make dev
```

notice the created file:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1718558412661/56b1f567-3600-4780-919b-c123e4758126.png align="center")

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

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1718558551372/07da25e5-c344-465d-8c39-08fff45f3670.png align="center")

Let's now commit our code so far

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1718558629539/bfeaec18-1415-4df1-9c32-287f11585599.png align="center")

As usual you can find the code so far in the relate [github branch](https://github.com/gosom/freelance-invoice-hub/tree/company-repo) .

### Deployment

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

Login to your VPS and add the line:

```bash
FH_DB_PATH=invoices.sqlite3
```

Now you can go back to your machine and run

```bash
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](https://x.com/gkomdev) or [LinkedIn](http://www.linkedin.com/in/georgios-komninos-172508147)

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](https://blog.gkomninos.com/series/webapp-using-golang)