Cookies management by TermsFeed Cookie Consent

Init classic database/sql repository

10/21

Now is the time to implement your first PostgreSQL repository. We will start with a repository based on the classic database/sql package. In the website package, create a new file repository_postgresql_classic.go and copy the code of the repository structure PostgreSQLClassicRepository with its constructor and the Migrate() method.

You may wonder why we named the file repository_postgresql_classic.go when inside we have a PostgreSQLClassicRepository struct. Why is the name not postgresql_classic_repository.go? Well, this way of naming helps in locating repository files in the IDE more easily. Take a look at the project tree:

postgresql-intro
├── app
├── cmd
├── go.mod
└── website
   ├── repository.go
   ├── repository_postgresql_classic.go
   └── website.go

The files are sorted by name, so the repository_postgresql_classic.go is next to the repository.go file, and so all other future repository variants will be next to each other thanks to this naming style.

Locating specific files in a package can be difficult when the package contains a lot of files, so it is a good idea to take care of the naming convention that will help with this from the beginning of the project.

In the next parts, we will be using the pgx driver objects, so we need to add this package to the project. Run the go get command in our project directory:

go get github.com/jackc/pgx/v4

and

go get github.com/jackc/pgconn

to get the low-level package internally used by the pgx.

Now you are ready to understand what we just added and develop the project further.

The repository constructor

In lines 8-16, we define the PostgreSQLClassicRepository struct and its NewPostgreSQLClassicRepository() constructor. It accepts only one dependency as an argument: sql.DB, which represents the database connection pool for all drivers compatible with the database/sql interface. Using sql.DB, we will perform direct database operations in the repository methods.

Migrate() method

As we already mentioned, the Migrate() function in our project is used to create a schema for the websites table where we will store our data. Using the DB.ExecContext() method, we execute a SQL query that creates the table. Note that we use here a variant with passing the context.Context (there is also an analogous DB.Exec() function that omits the context), which will allow us to stop the execution of the query, for example, in case of a timeout.

website/repository_postgresql_classic.go

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

package website

import (
    "context"
    "database/sql"
)

type PostgreSQLClassicRepository struct {
    db *sql.DB
}

func NewPostgreSQLClassicRepository(db *sql.DB) *PostgreSQLClassicRepository {
    return &PostgreSQLClassicRepository{
        db: db,
    }
}

func (r *PostgreSQLClassicRepository) Migrate(ctx context.Context) error {
    query := `
    CREATE TABLE IF NOT EXISTS websites(
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL UNIQUE,
        url TEXT NOT NULL,
        rank INT NOT NULL
    );
    `

    _, err := r.db.ExecContext(ctx, query)
    return err
}
10/21