Gorm, Go, and Has-Many

Using Gorm as an ORM to programmatically update many-to-many relationships in PostgreSQL

In this article I’ll explain briefly how to use Go to create a many-to-many relationship using and also how to update the relationships correctly in your database. If you’re reading this article, I assume you’re familiar with Gorm, Go and have a dabble of experience in SQL. If not, Gorm is an ORM that allows you to take a more programmatic approach to interacting with SQL databases in lieu of writing SQL syntax queries within your code.

Photo by Katie Burandt from Pexels

tl;dr — you can find the complete gist here to get started

Requirements

  • Go installed and a basic understanding of Go
  • Gorm
  • A local SQL instance — For this tutorial I am using PostgreSQL
  • (optional) Postico for Mac —you can use any database management aplpication you like to view your database records.

Create your Structs

For the purposes of demonstration we’ll keep it simple and work within a single file. We’ll create two structs for our example, a struct to represent a person, and a struct that will become part of a slice of various skills a person can have.

Create User and Skill structs

As you can see we have a simple association happening, the has a name as a string and also has Skills which is of the type (the other struct we created).

Enter Gorm

We now want to add Gorm to the mix, Gorm will be used to manage our table schema and so we need to tell it how to work with these structs. Add the following to your structs:

Add instructions for gorm to create our schema and join table

when declared inside a struct, takes that struct and translates it to a table schema for us. There will be one final step to complete this later.

We also add some instructions next to our fields, we tell gorm that the User’s slice of skills has a many-to-many relationship with a table called . This is the join table, and it will be automatically created later on using another Gorm feature.

We set the of the skill as the primary key, this ensures that only ever one particular type of skill can exist. This ensures we do not get duplicates. It is of course possible to use an instead, but for the purposes of this demonstration, our table will only have one column, .

Connect to the Database

I won’t go into depth on how to setup an SQL database and connect to it, I’ll assume that you have already reached that point so far. Otherwise there are numerous articles on getting started with Gorm and your flavour of SQL database. For this demonstration I am using a local postgres instance, simply change the string and to suit your specific driver.

Within our file we will set up an automated sequence of events to replicate a user interacting with our website or API and creating then updating a record.

First create your initial connection in the main function:

Set up a connection to our local postgresql datbase

Now we will use Gorm’s feature to take our structs and turn them into tables. It will also know to create the join table for our user/skill relationship.

Just below where you set up your connection, enter the following:

AutoMigrate our structs to our database using gorm

As you can see we have instructed the feature to take our structs and migrate them to our database. You can leave this in your code and it will not overwrite any existing data. If you update your struct it will attempt to work around your existing data or not perform any action at all.

Gorm’s AutoMigrate creates all our tables and schemas

Create a User

We now want to create a User and add some test data to it:

Create a user entry in our database

We created our with a and as a slice of . The skills follow the structure of our struct.

We then used Gorm to create a record in our database. Gorm knows where to enter the user because during the it set everything up for us, so it knows the has to be created in the table.

If successful our User will be updated by Gorm with it’s respective ID from the database table. Connect to your database using your manager and confirm this. I use Postico on Mac to connect to my locally running PostgreSQL database.

Our User record in our users table

Update the User

Now let’s mock a scenario where the end-user wants to update their skills. Where the confusion happens in these cases is understanding the sequence of events that need to happen and then translating that into Gorm.

Let’s see the code, then I’ll explain:

Update the user’s skills

We first want to just update everything basic like Name; simple fields. We do this by omitting from our first update call. In this instance if had of changed our User’s name it would have occurred here.

We then tell Gorm about our User/Skill association and then tell it to replace the User’s skills where ever they are stored. In our case, this would update the join table , and then because these skills don’t exist they would be added to the table too. All automatically!

Now check that your users skills have updated

fmt.Println(user.Skills)>> 2
>> [{Python} {JavaScript}]

Confirm this and connect to your database with Postico for example, and confirm that your join table is correct and there are no duplicates in your table.

Join table shows association between user with ID 1 and the skill name as the ID

Room for Improvement

To make things more efficient we should use a an ID for the skill instead of it’s name. This way we don’t have data duplicated between two tables.

As it is, if we update the skill name in the skills table, it will cause errors when the database tries to create the association in the join table because the name no longer exists (we changed it).

When you Update that list of skills, if they have no IDs in them to gorm they are just new entities to be added. This leads to your duplicated values. Even if you have a field, if that field isn't the entity's primary key, then gorm will try to create a new record and result in a constraint violation instead.

There are a few ways of dealing with this:

  • Making sure the end-user must supply an ID for the related skills
  • Pulling out of the database the skills’ IDs via some other surrogate key that the end-user provides, and populating those in your to-save entity. In our case that could be since it's unique.

Please reach out if there are any improvements I can make to this. I wrote this article as a way to knowledge share, if something seems incorrect or can be done better I’d love to know.

Consultant, Design Engineer working in Finance, interested in learning new things and sharing what I know.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store