↓Skip to main content
  1. Tutorials/

PostgreSQL in Go: Complete Tutorial with database/sql, pgx, and GORM

··30 mins
Data Db Sql Postgresql Pgx Gorm Repository Orm Crud

Table of Contents

To use PostgreSQL in Go, you need a database driver - a client that allows you to connect to and perform operations on the database. This comprehensive tutorial demonstrates three different approaches to working with PostgreSQL: using the standard database/sql package with the pgx driver, using the pgx client directly, and using the GORM ORM.

Click here to go directly to the summary and full code examples.

Pure SQL or ORM #

Before we start installing PostgreSQL and writing code, we need to decide which way of accessing the database we prefer.

The database/sql package #

Go has a built-in universal interface for operating on SQL databases in the database/sql package. Since this package provides universal functions, we need another component, a driver, to make it work for a particular database. The driver is a package compatible with the database/sql/driver interface and provides direct access to the database. In other words, a driver is a client of a specific database in Go, compatible with the database/sql/driver interfaces, and thanks to that, it can be used by the database/sql package.

The advantage of database/sql is its simplicity and versatility. If you want to start working with a new SQL database, you will almost certainly find an open source, well-tested driver for it. They are all compatible with the database/sql, so if you would like to swap one database for another, then all you need to do is replace the driver with little or no changes.

Also, working with the database/sql is about writing pure SQL commands, getting results from functions, and converting them to a specific format. You have to do all these steps by yourself. So if you want full control over the whole process of reading or writing data to a database, then the database/sql is a great choice.

A database client #

Since the database/sql package is universal and offers the same functionality for all databases, you may be wondering how to use the specific features of a given database in Go.

For such cases, many databases provide a custom client package. Such a package is something like a non-standard driver extended with all the functionality of the database. Typically, clients are low-level, and because they are dedicated to a particular database, they can be faster than database/sql compatible drivers. So if you do not need the versatility that database/sql offers but rather specific database features, then using a dedicated client may be a good choice.

An ORM or other tools #

The database/sql package and dedicated clients run at a low level, and not everyone likes this type of work. Especially if the database you want to implement has many tables, you expect some sort of speedup by not having to write every query by hand. In this case, the best solution would be to use ORM or other tools, such as those that generate code from SQL commands.

ORMs are packages that make things easier by providing automatic Struct-Table mapping and no need to write error-prone pure SQL statements. They often have features not included in the standard database/sql library, such as automatic migration, caching, pagination of results, and many others. They speed things up at the expense of a higher level of abstraction and less control over the data you get.

However, if you prefer to operate on SQL queries but still want to get some speedup over database/sql, a good solution is to use a code generator such as sqlc. It works by generating type-safe code from SQL: you write SQL commands, run sqlc, get a model, and well-defined Go functions that read from or write to the database.

What we are going to use #

In this tutorial, we are going to show you three approaches to creating a database access layer. First, using the pgx PostgreSQL database/sql compatible driver as this is the simplest and most natural approach for Go. Second, using the pgx custom client that offers all the features of PostgreSQL in Go. As a bonus, we will also show you how to create the same functions using GORM ORM. As a complete Go programmer, you should know all these approaches and use the best one for a given use case.

Read more about the pgx driver and toolkit package, GORM, and other drivers and ORMs in our list of the best drivers and ORMs for PostgreSQL in Go.

Repository pattern #

Clean and maintainable projects require the presentation, application, and persistence layers be kept separate from each other. No one likes spaghetti code that mixes bits of direct data retrieval and bits of data processing. In our opinion, only two things are needed to create a clean design in terms of data.

First of all, the code that retrieves and writes data to the database for a given Domain object should be extracted as a separate service so that there is only one place to access the domain object data in the entire project - the data access layer.

Second, database implementation details should be hidden behind an abstraction - an interface. This approach keeps the code database-independent and resilient to future changes in the infrastructure, since one database can easily be replaced in the code by another by creating a new implementation of the same interface for a new database. In addition, working on interfaces rather than a specific implementation helps you focus on the domain rather than on how the data is stored.

Both of these things can be achieved by using the Repository pattern. It focuses on abstracting the data access layer and separating data storage from the specific database implementation.

In this tutorial we will show you how to create the correct data access layer using this design pattern.

Some people think there is no point in using a design pattern like Repository if your project is small. However, we strongly recommend using this pattern for any project that will work in a production environment. Adapting to changes is part of a programmer’s job, and with the Repository pattern and the loose coupling between the database part and the business logic, any code change is easier.

Install PostgreSQL #

After defining the basic assumptions about the project, it is time to install PostgreSQL.

The most convenient and fastest way is to run the PostgreSQL instance as a Docker container. If you are not familiar with Docker, check out the documentation here and how to get Docker here.

If you want to install PostgreSQL directly on your machine, check out the instructions on the official PostgreSQL site.

The command on the right starts the PostgreSQL database instance inside a Docker container.

What we are going to build #

As part of our introduction to PostgreSQL in Go, we will create a mini ranking of websites that we will store in the database. So, Website will be our domain object. In addition to creating and reading objects from the database, we also want to be able to delete and update Website records. Therefore, the application we will make should perform all CRUD (create, read, update, delete) operations.

Database operations will be defined in the Repository interface you have seen before. We will create three concrete implementations of this interface - the first using the database/sql package, the second using the pgx client package, and the third using the GORM ORM.

Init project structure #

So let’s start creating our project. Open Terminal and create a new directory named postgresql-intro in a location of your choice.

mkdir postgresql-intro

Go to the directory:

cd postgresql-intro

and create a new Go module for our project:

go mod init postgresql-intro

Then create three new directories in the postgresql-intro project:

mkdir cmd
mkdir website
mkdir app

Domain object and repository #

The next step is to define the domain object and the repository. In the website package, create two new files: website.go and repository.go, and copy their contents that you can see on this page into them.

A Website is a struct that contains simple data about the website - its name, URL, position in the ranking, and numeric identifier. Objects of this type will be stored and retrieved from the database.

The Repository is our interface for reading and writing Website data from and to the database. Note that the methods of this interface do not depend on PostgreSQL at all. This is one of the main goals of the Repository pattern - hiding database implementation details and providing a simple API to interact with any database.

After the changes, the project structure should look like this:

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

We will go through what each method does in the next step, but by now, you may be wondering why every method signature has ctx context.Context in the parameter list.

In short, the context.Context is an object that is a common Go concept used by web applications to send request-scoped values, cancellation, and deadline signals to deeper layers of services. Let’s assume that in your application you want to wait a maximum of 5 seconds for a response from the database to prevent the application to wait idly if there are any connection problems. To ensure this, you can create a new Context with a defined timeout of 5 seconds. The functions of a client or driver operating directly on the database, while receiving such a Context, respect it, which means that after the timeout of 5 seconds is exceeded, the connection is interrupted, and the application can continue to run.

In our demo application, we will also use this feature and set a timeout for all operations using the context.Context.

Repository methods #

Our repository functions perform migration and basic CRUD operations:

  • Migrate(ctx context.Context) error - The method responsible for migrating the repository, i.e., adjusting the PostgreSQL table to the domain object and importing the initial data. In our case, this function will be responsible for creating a new websites table. So there is no need to log into the GUI database client and manually create the table. However, it is important to remember that this function should be executed first, before reading or writing to the database.
  • Create(ctx context.Context, website Website) (*Website, error) - Method that creates a new website record in the repository. It returns the website saved to the database with the generated ID or an error in case of problems.
  • All(ctx context.Context) ([]Website, error) - It extracts all records from the repository and returns as a slice or returns an error if there are problems.
  • GetByName(ctx context.Context, name string) (*Website, error) - Gets a single Website record with the specified name or returns an error if there are problems. The name of each Website must be unique, so there is no risk of having two records with the same name.
  • Update(ctx context.Context, id int64, updated Website) (*Website, error) - Updates the Website record with the id identifier with the values found in the updated struct. It returns the updated record or an error in case of problems.
  • Delete(ctx context.Context, id int64) error - Deletes a record with the id identifier and returns an error if there are problems.

As you can see, all of these methods return errors. Some of them may be due to the action of the user, for example, when someone tries to add a Website that already exists (with the same name). It is a good practice to define such errors and return them from the functions so that the application can handle them and respond with an appropriate error message to the user. We will do this on the next page.

Repository errors #

Add the error definitions to the website/repository.go file and let’s analyze them:

  • An ErrDuplicate error means that the user is trying to add a record that already exists, i.e., with the same name, because in our repository, we assume that each website should have a unique name. This error should be returned from the methods that write to the database - Create() or Update() when a violation of the uniqueness constraint is detected.
  • An error ErrNotExist means no record with the specified parameters in the database. This should be returned from any functions that pull a single object from the repository. In our case, this is the GetByName() method.
  • An ErrUpdateFailed error is an error returned by the Update() method. It should appear when no row has been affected by the requested update, such as when the change was to a row with a non-existent id.
  • An ErrDeleteFailed error should occur when no row was deleted as a result of executing the Delete() method, e.g., because there is no row with the given id.

Of course, when using the repository, a whole bunch of other errors can also occur, e.g., network errors, connection failures, timeouts, etc. However, these are unexpected errors, and not every application needs to handle them in a special way. But user errors like the above should always be handled so that the user can react and correct bad input.

Init classic database/sql repository #

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.

Implement Create method #

We have already created the repository structure and the Migrate() method, so it is time to implement the “C” from the CRUD abbreviation, which is the Create() function. Copy the code for this method into our classic repository file and make sure you have all the necessary imports.

Create() method #

The Create() function takes the website given as an argument and inserts it into the table. Generally, the DB.Query*() methods of the DB struct are used for reading the data from the database, and DB.Exec*() for inserts, updates, and deletes. So you may wonder why we use the DB.QueryRowContext() method for insertion instead of DB.ExecContext(). To explain that, look at the last part of the INSERT query:

RETURNING id

It causes the query to return the id of just inserted record as a result, which needs to be read using the Scan() method of the Row object returned by the DB.QueryRowContext().

err := r.db.QueryRowContext(ctx, "INSERT INTO websites(name, url, rank) values($1, $2, $3) RETURNING id", website.Name, website.URL, website.Rank).Scan(&id)

With the DB.ExecContext(), we would not be able to get the id of the last inserted record. This function returns a Result object (and an error) that has the LastInsertId() method, but due to the specific behavior of the INSERT command in PostgreSQL, this method is not supported by the pq and pgx drivers. More information here.

Note how we pass arguments to the INSERT query. We use the symbols $1, $2, $3, which at the execution stage are replaced with the values in the first, second, and third place after the SQL query in the DB.QueryRowContext() function. This way is supported by PostgreSQL and much safer in terms of defending against SQL injection attacks than using the fmt.Sprintf() function to build the final query.

After executing the INSERT command, we check if an error has occurred. If so, we check if it is an instance of the pgconn.PgError error and if its code indicates the unique constraint violation (code 23505). Such an error means that a row with the same UNIQUE field already exists in the table. For the websites table it means that a website with the same Name already exists. In this case, we can map the internal PostgreSQL error pgconn.PgError to the general Repository error ErrDuplicate, which we defined earlier. As the last thing, we assign the ID of the inserted item to the object that will be returned from the Create() function so that it reflects the current state in the database.

Implement reading methods #

Now, we are going to implement the reading methods: All() and GetByName(). Let’s add them to the repository_postgresql_classic.go file.

All() method #

The All() method returns all rows from the websites table. It uses the DB.QueryContext() method to get the rows for a SQL SELECT query. It returns them in the form of an sql.Rows object, which represents a cursor to the DB rows. It is important to remember that after you finish working with the sql.Rows object, it should be closed, which is what the line below does (defer keyword means that the rows.Close() will be called at the end of the All() method):

defer rows.Close()

Using the Rows.Next() method in the loop, which returns true if there are any more rows in the sql.Rows object, and the Rows.Scan(), which copies the successive row values to the given variables, we can convert the raw data from the database into a slice of Website objects. Note that in case of any error in retrieving or copying the data, the whole procedure is interrupted, and an error is returned. If successful, the All() method returns a slice of Website objects.

GetByName() method #

The GetByName() function retrieves one Website record with the name given as an argument. It works similarly to All(), except that instead of using the DB.QueryContext() method, it uses DB.QueryRowContext(), which returns at most one row in the form of sql.Row, and there is no need to close it. To copy the raw values to the Website object, the same Scan() function is used as before, except that in case of an error, we check if it is an instance of sql.ErrNoRows indicating no results. If such an error appears, we map it to our predefined repository error ErrNotExist. So if a user makes a mistake and, as an argument, he passes a website name that is not in the database, he will receive a clear error indicating the reason.

Implement Update method #

Our repository should also be able to update an existing row. This is what the Update() method is for. Copy it into the code of our repository.

Update() method #

The Update() method updates the record with the specified id. It is not significantly different from other methods you have seen before.

First, we execute the SQL UPDATE query using the DB.ExecContext(), which updates the values of all columns of the websites table row. During the update, there may be a situation where the value of the updated field name already existed in the database, in which case a unique constraint violation error will occur. We handle such an error in the same way as in the Create() function.

Finally, we get the value of the RowsAffected() function from the returned sql.Result object to see how many rows were affected by our change. If 0, this indicates an error during the update, such as an invalid id was passed as an argument. We return this information to the user in the form of an ErrUpdateFailed error we defined earlier. If everything is fine, we return the updated Website object.

Implement Delete method #

The last letter in the CRUD abbreviation, D, stands for Delete() function. Add its code to the repository.

Delete() method #

The Delete(), as the name suggests, deletes a record with the given id from the database. As in the Update() method, to execute the SQL DELETE command, we use the DB.ExecContext() function and also check whether the number of affected rows is equal to 0. If so, it means a delete error, which we return as a previously defined ErrDeleteFailed error.

This way, we have finished creating our first repository based on the classic database/sql package. In the next step, we will make a test procedure and an application to check in practice what we have written.

Init the main function and the demo procedure #

Demo procedure #

Let’s start by initializing a test procedure that will perform a series of actions on the repository. After executing each function, we will check the results by writing them to the standard output or check if there was an error.

Create the app/demo.go file in the demo package we created at the beginning of the project and copy its contents.

The RunRepositoryDemo() function takes as arguments the context used in the repository methods and the repository itself. Note what type websiteRepository has. It is the website.Repository, which is our repository interface. This way, the RunRepositoryDemo() function can be used with any repository implementation, which is what we are going to do in the next steps. For now, this function does only one thing - it calls the Migrate() method to create our storage for Website objects.

The main() function #

We already have the repository implementation and the first version of the demo procedure, so we can put everything together and run a test application.

  1. Create a new directory classic in the cmd package for the application that demonstrates the classic repository based on the database/sql package.
  2. Add a new file main.go to the classic directory. This is our executable file.
  3. Copy the contents of the cmd/classic/main.go to your newly created main.go.
  4. Run our test app with ```shell go run main.go
If everything went well, the program should exit without error, and a new table called `websites` with columns: `id`, `name`, `url`, and `rank` should be created in the PostgreSQL database.

### What does the `main()` function do?

#### Connect to PostgreSQL database

To connect to a PostgreSQL database using the [`pgx`](https://github.com/jackc/pgx), it is necessary to register it as a [`database/sql`](https://pkg.go.dev/database/sql) driver. This is done by importing the driver package in line `11`. By using a blank identifier, we import the package, even if it is not used by the current program. Once imported, it calls the internal [`init()`](https://go.dev/doc/effective_go#init) function, which registers the driver in the [`database/sql`](https://pkg.go.dev/database/sql) interface under the name `pgx`.

> The [`pgx`](https://github.com/jackc/pgx) has a [`database/sql`](https://pkg.go.dev/database/sql) compatible driver in the package [github.com/jackc/pgx/v4/stdlib](https://github.com/jackc/pgx/tree/master/stdlib).

In lines `15-19`, we use the registered driver. Using the [`sql.Open()`](https://pkg.go.dev/database/sql#Open) function with the `pgx` as the first argument, we connect to the PostgreSQL database. The second argument is the `dataSourceName` consisting of the connection information. For PostgreSQL, we define the so-called [connection URI](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING) here. In our case, we are connecting to the database named `website` located on `localhost` on port `5432` with username `postgres` and password `mysecretpassword` because this is what we set at the stage of [running the Docker image](../install-postgresql). The result is the [`sql.DB`](https://pkg.go.dev/database/sql#DB) object (and an error if it occurs) safe for concurrent use and maintaining its own pool of idle connections. A good practice is to close the DB connection at the end of the program with the [`Close()`](https://pkg.go.dev/database/sql#DB.Close) method.

#### Create a `Website` repository, context, and run the demo procedure

In line `21`, we call a constructor of the `PostgreSQLClassicRepository` with the created [`sql.DB`](https://pkg.go.dev/database/sql#DB) object as an argument. Then, we initialize a new [`Context`](https://pkg.go.dev/context#Context) object with a 10-second timeout using the [`context.WithTimeout()`](https://pkg.go.dev/context#WithTimeout) function. Its first argument is a parent [`Context`](https://pkg.go.dev/context#Context) which in our case is an empty fresh [`Context`](https://pkg.go.dev/context#Context) instance obtained from the [`Background()`](https://pkg.go.dev/context#Background) function.

Note that in addition to the [`context.Context`]((https://pkg.go.dev/context#Context)) object, as a result, we also get a [`cancel()`](https://pkg.go.dev/context#CancelFunc) function to release the resources associated with the context, so we do this at the end of the program as soon as all operations using the context have finished:
```go
defer cancel()

In the last line of the main(), we call our demo procedure passing the created context and our classic database/sql repository as arguments.

Complete the demo procedure #

We already have a working application using the repository implementation and calling the demo procedure. However, it would be good for the demo procedure to do more than just migrate data. So complete the app/demo.go file, and let’s trace what the newly added operations do.

  1. MIGRATE REPOSITORY - As you already know, at the beginning, we executed the Migrate() function, which created the websites database table.
  2. CREATE RECORDS OF REPOSITORY - Next, in lines 18 to 43, we create 2 new Website objects and add them to the repository using the Create() method. Then we print out the inserted records to the standard output. Note how we handle errors from the Create() method. In the case of an ErrDuplicate error, we only log that fact; in the case of a non-standard, unexpected error, we abort the application execution by logging the error using the log.Fatal() function.
  3. GET RECORD BY NAME - After inserting the records to the database, we check if we can pull them from there. In lines 45-53, we get a record named GOSAMPLES using the GetByName() method. If such a row does not exist, we log an ErrNotExist error and move on. Other unknown errors are logged, and the application exits.
  4. UPDATE RECORD - In lines 55-65, we update a single record with a new ranking value. Because the Update() method may return ErrDuplicate or ErrUpdateFailed errors, we check for them and handle them by printing on the standard output.
  5. GET ALL - Lines 67 through 75 use the All() method to get a list of all records and then print them to the standard output, so we can check that the update actually was made in the database.
  6. DELETE RECORD - In lines 77-84, we delete one of the inserted records and handle errors if they occur. In the case of ErrDeleteFailed, we log the error; in the case of others, we exit the application.
  7. GET ALL - As the last thing, in lines 86-93, we again get the list of all records using the All() method and write them out to the standard output to check if the row was actually deleted.

Now you can run our cmd/classic/main.go application using

go run main.go

and check the output:

1. MIGRATE REPOSITORY
2. CREATE RECORDS OF REPOSITORY
&{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:2}
&{ID:2 Name:Golang official website URL:https://golang.org Rank:1}
3. GET RECORD BY NAME
&{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:2}
4. UPDATE RECORD
5. GET ALL
{ID:2 Name:Golang official website URL:https://golang.org Rank:1}
{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:1}
6. DELETE RECORD
7. GET ALL
{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:1}

Feel free to experiment on your own. If this is your first application connecting to PostgreSQL, change function input arguments, change data, run the application multiple times and see what happens, check what errors appear, and try to fix them. The code we have created is just an introduction, which you should use as a base to create something more advanced.

Remember that when you run the application for the second time, you have already inserted data into the websites table. If you want to clean the database, just close the running PostgreSQL Docker container and restart it using docker run as in the beginning.

This way, we finished building the application that performs CRUD operations using the repository based on the database/sql package. In the following pages, we will show you how to create a repository based on the pgx client package, and using an ORM called GORM.

Repository based on the pgx client #

As we know, the pgx package, in addition to the database/sql compatible driver, also has its own DB client, which is faster and has more features, so it is even more recommended than the classic driver.

Let’s add a repository based on this client. Create a new file repository_postgresql_pgx.go in the website package. Then copy its contents, and let’s trace how such a repository differs from the one we previously created.

To use the pgxpool package, we need to add it to our project:

go get github.com/jackc/pgx/v4/pgxpool

This repository is very similar to our classic database/sql based repository. It differs actually only in two things:

  • Instead of using the sql.DB to connect with the database, we use a pgxpool.Pool object here. Like sql.DB, it represents a pool of connections to a database and is concurrency safe.
  • All the database methods we use are practically the same as in database/sql based repository, except that you always have to pass context.Context and there is no version without context. So we have the Query() method instead of QueryContext(), Exec() instead of ExecContext(), etc.

Using pgx client is therefore not significantly different from using a database/sql compatible driver. The authors of pgx recommend this way of connecting to PostgreSQL but explain in more detail when to choose which version here.

Run the demo procedure for pgx-based repository #

To run our demo procedure with the created pgx client-based repository, we need to make a new app. Create a new pgx directory in the cmd and add a new main.go file.

As you can see in the code, the main.go file, in this case, is also very similar to the version with the classic repository. The difference is the initialization of the pgxpool.Pool instead of the sql.DB, but note that we are using the same connection string as before and also have to take care of closing the connection at the end. Also, in imports section, you does not have to import the database/sql compatible driver. The rest of the code is analogous to the classic version.

Remember that our previous application has already inserted data into the websites table. So to clean the database, just close the running PostgreSQL Docker container and restart it using docker run as in the beginning.

If you run the cmd/pgx/main.go, you will see the same output as for the cmd/classic/main.go:

1. MIGRATE REPOSITORY
2. CREATE RECORDS OF REPOSITORY
&{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:2}
&{ID:2 Name:Golang official website URL:https://golang.org Rank:1}
3. GET RECORD BY NAME
&{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:2}
4. UPDATE RECORD
5. GET ALL
{ID:2 Name:Golang official website URL:https://golang.org Rank:1}
{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:1}
6. DELETE RECORD
7. GET ALL
{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:1}

GORM repository #

The last repository we want to create in this tutorial is a repository based on the ORM package GORM. Create a new file repository_postgresql_gorm.go in our domain package website and copy the contents of the repository there.

You will need the gorm.io/gorm package to make everything work, so add it to your project:

go get gorm.io/gorm

Let’s go through what our repository looks like.

Check out GORM great documentation if you want to learn more about how to use it.

The gormWebsite structure #

GORM is based on models, i.e., structs that represent a database row and describe the table schema using struct tags. In our repository, we have defined the gormWebsite model, which describes the structure of the websites table. Look at how the struct tags define the table columns, and the TableName() method sets the table name. As you can probably guess, since the entire table is defined in code, we do not have to use SQL to perform operations on the database, even to create the table.

If you want to learn more about declaring models in GORM, check out the documentation.

The repository constructor #

The repository structure PostgreSQLGORMRepository (and the constructor NewPostgreSQLGORMRepository) takes only one argument, the gorm.DB object that represents, as in the previous cases, a concurrent safe connection to the database.

Migrate() method #

GORM has the AutoMigrate() method to perform automatic migration, which is, creating or modifying a table schema as defined in the model struct. Look at the line 34. We create a database object that respects the passed context.Context using the WithContext() function, and then call the AutoMigrate() method with the model object as an argument. It is enough to create websites table in the database with the columns defined in the model.

Create() method #

To insert records into a DB table, we can use the GORM DB.Create() method, which takes a model object as an argument. Since we are operating on a domain object of type Website, we should remember to convert it to gormWebsite, which we do in lines 38-42. After successfully inserting the record, the GORM function DB.Create() also updates the value of the inserted object with the generated ID. So we just need to convert the inserted value back to Website (line 54) and return this object from our Create() method as an inserted Website record. In this method, as with previous repository implementations, we also handle the unique constraint violation error. Since the PostgreSQL driver for GORM internally uses the pgx package, all we need to do is check that we get an appropriate error of type pgconn.PgError.

All() method #

To get all the records from the table, we just need to initialize an empty slice of gormWebsite objects for results and set it as an argument to GORM’s Find() method (line 61). Then, to be able to return a slice of type []Website, the result needs to be converted, which we do in lines 65-68.

GetByName() method #

The GetByName() method is very similar to All() except that we use the GORM Where() function to add the SQL WHERE clause to our query, and we handle the gorm.ErrRecordNotFound error that is returned when there is no record with the given criteria. Note that the GORM requires ? instead of $1 as a placeholder in the Where() query. As with the All() method, we use the Find() function here to get the result, but this time with a single gormWebsite object as an argument. In the end, we convert the result to our Website domain object.

Update() method #

If you have followed the previous methods, then Update() is nothing new for you. We use the GORM Save() function here to update the value of a gormWebsite object in the database. Finally, in addition to checking that we did not create a duplicate by updating, we also get the number of rows affected by this change. If it is equal to 0, we return the ErrUpdateFailed error, as in previous repository implementations.

Delete() method #

To delete a record, we use the GORM Delete() function in a variant that removes a row with the specified primary key. If no row has been affected by the delete, we return the ErrDeleteFailed error.

Run the demo procedure for GORM repository #

To run our demo procedure for the GORM repository, create a new gorm directory in cmd and put a new main.go file in it. Then copy the code there, most of which you should already know well.

In the main() function, we do almost the same as in the previous applications: open the connection to the DB, create the repository, and run the demo procedure. The main difference is in the initialization of the database connection. GORM has a function gorm.Open() which opens the DB using a set SQL dialect. In our case, we want to use the PostgreSQL dialect, so we create it using the GORM’s PostgreSQL driver function postgres.Open() with the same connection string as in the previous apps. The postgres driver is a separate package, and be sure to add it to our project:

go get gorm.io/driver/postgres

In the main(), we open the connection in the simplest way, but GORM has a lot of configuration options. You can read about them here.

Then, we create a new GORM Website repository (PostgreSQLGORMRepository) and run our demo procedure. The output should be the same as for the previous apps.

Remember that our previous applications have already inserted data into the websites table. So to clean the database, just close the running PostgreSQL Docker container and restart it using docker run as in the beginning.

1. MIGRATE REPOSITORY
2. CREATE RECORDS OF REPOSITORY
&{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:2}
&{ID:2 Name:Golang official website URL:https://golang.org Rank:1}
3. GET RECORD BY NAME
&{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:2}
4. UPDATE RECORD
5. GET ALL
{ID:2 Name:Golang official website URL:https://golang.org Rank:1}
{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:1}
6. DELETE RECORD
7. GET ALL
{ID:1 Name:GOSAMPLES URL:https://gosamples.dev Rank:1}

As always, we encourage you to experiment, change the code, see what comes out, get errors and find their solution.

Summary #

Finally, we have reached the end of our tutorial. We presented in it how to connect to PostgreSQL from the Go language using three different approaches, how to perform CRUD operations and how to create a well-structured data access layer using clean code practices and the Repository design pattern.

It was just an introduction to give a general overview of what an application’s integration with the PostgreSQL database should look like. However, with these basics, you can create much more advanced projects being sure that they are robust to any future changes.

Good luck!

The project code #

The full code of the project created in this tutorial is available on Github here.