1 min read

How to make a join table with Liquibase and Gorm

Featured Image

A new tool for SQL database relationships

A join table is the traditional tool used to define a "many-to-many" relationship in a SQL database. Here is a real-world example of how Merit's team created a join table with Liquibase and Gorm.

Merit is a digital credentialing company offering a check-in app. It enables organizations using the platform to issue digital credentials to individuals who register for events and restrict entrance to those with proper credentials. Our Merit Check-in app has a "many-to-many" relationship between kiosks, where people enter events, and events, which can be on multiple kiosks.


Your turn to experiment!

Events and kiosks have a simple model, just a name, id, and three timestamps. See gorm.Model for more information about the timestamps. The join table is a set of id pairs that link arbitrary kiosks and events.


Start with a Liquibase file

This Liquibase file can be used to create the kiosks and events tables and is about as simple as Liquibase gets. It generates two tables, each with its own primary key.



Set up the join table

Once the kiosks and events tables are created, the join table can be set up. With only two fields, it’s a small Liquibase file, but has two important characteristics. First, it’s two columns are used as a composite primary key which ensures there is only one link between a given kiosk and event.  Second, both event_id and kiosk_id are foreign keys on the events and kiosks table, respectively. This ensures the join table will always reference valid kiosks and events.



Configure structs with gorm annotations


Create an event or kiosk


A kiosk is now a valid place for event check in because of the link between the event and the kiosk. By adding a new entry to the join table, a kiosk and an event are linked. The two objects are joined together as part of their "many-to-many" relationship.



Read from the database

You can view a kiosk and its associated events. Gorm offers several methods to create what is essentially a SQL join. Preload was used to populate this many-to-many relationship. It issues multiple SQL queries, one for each table needed.



Use this function for endless real-word applications Here are more examples in a working repo here. Golang, Liquibase, and Docker need to be installed to construct similar tables. You can see instructions in the README.md file. This is just one more case study of how Merit engineers build software. If you're interested in joining our team, please visit our Careers page!

Get the latest news with Merit’s monthly newsletters

Merit and Propel Partner to Help Kansans Access Education Services Weeks Faster Than Before

We're excited to announce a strategic partnership with Propel, creator of the free app, Providers, which helps low-income families manage their...

Read More

Merit + Credential Engine: An Ecosystem of Nationwide Credentialing

In order to give states the digital credentialing tools to build a system for standards-based workforce reciprocity between states, Merit and ...

Read More

A History-Making Weekend for Military Families, Kansas Governor Laura Kelly, ASPIRE, and Merit

Military families have long struggled with the challenges posed by regular reassignments to new states. These PCS (Permanent Change of Station) moves...

Read More