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
structs 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.
tl;dr — you can find the complete gist here to get started
- Go installed and a basic understanding of Go
- 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
main.go file. We’ll create two structs for our example, a
User struct to represent a person, and a
Skill struct that will become part of a slice of various skills a person can have.
As you can see we have a simple association happening, the
User has a name as a string and also has Skills which is of the type
Skill (the other struct we created).
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:
gorm.Model 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
user_skill. This is the join table, and it will be automatically created later on using another Gorm feature.
We set the
Name 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
ID instead, but for the purposes of this demonstration, our
Skill 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
DSN string and
DriverName to suit your specific driver.
main.go 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:
Now we will use Gorm’s
AutoMigrate 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:
As you can see we have instructed the
AutoMigrate 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.
Create a User
We now want to create a User and add some test data to it:
We created our
User with a
Skills as a slice of
Skill . The skills follow the structure of our
We then used Gorm to create a record in our database. Gorm knows where to enter the user because during the
AutoMigration it set everything up for us, so it knows the
User has to be created in the
If successful our User will be updated by Gorm with it’s respective ID from the database
user table. Connect to your database using your
favourite manager and confirm this. I use Postico on Mac to connect to my locally running PostgreSQL database.
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:
We first want to just update everything basic like Name; simple fields. We do this by omitting
Skills 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
user_skill, and then because these skills don’t exist they would be added to the
skills table too. All automatically!
Now check that your users skills have updated
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
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
unique 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
namesince 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.