7 July, 20237 minute read

Don’t put foreign keys in your GraphQL schema

GraphQL schema design is hard, and a lot of engineering teams end up making mistakes when designing their graph architecture—if there’s a design phase at all. As I’ve written previously, one of the big USPs of GraphQL is its ability to reshape your underlying data into formats more suitable for product code. GraphQL is at its best when you don’t mirror your database models 1:1.

In this post, I’ll share a rule of thumb for schema design and talk about a recent project I worked on where this rule of thumb was violated—resulting in me needing to waterfall requests in one of Crimson’s frontend apps.

Here’s the rule: never put foreign keys in your GraphQL schema. You will always end up regretting it.

Case study: Fetching a user’s spoken languages

Crimson’s flagship product is the Crimson App. While other admissions consulting businesses are working out of Excel spreadsheets, our students and staff access everything they need through the Crimson App.

We’re a global business that works with students from all over the place, so naturally we need to store the language(s) that each user speaks. It’s very important that a student and their strategist can actually understand each other, after all.

The way we’ve stored user languages has been fine for many years, but I ran into a problem recently when trying to roll out the AI session summaries feature I’d been building. As part of the launch, we wanted to limit the feature to staff members who speak English because both Zoom transcription and GPT have worse performance for non-English languages.

Here’s the issue: a user’s languages are stored in an array column on the user table. Each element of this array is a language ID, which you can use to look up the language inside the language table. How do you think this is represented in the GraphQL schema? Well…

Click to copy
type User {  user_id: ID!  # ...  primaryLanguage: Int  languages: [Int!]} type Language {  id: ID!  # ...}

When this schema was first written years ago, the engineer in charge decided to simply mirror the database structure 1:1 in the schema. This is a massive pain, because now we can’t easily evolve the API. Adding any kind of metadata to the languages field requires a breaking change to our schema, because you can’t add extra fields to the primitive Int type. You either need to add another field (with an ugly name like languagesData) or start waterfalling requests in the frontend.

There was some time pressure on getting this release cut and code in the frontend which was already making waterfalled requests for language data, so I took the easy path instead of making schema adjustments. Making multiple round trips to fetch your data with GraphQL is one of the biggest and most obvious code smells that something is wrong with your schema—the whole point of GraphQL is that you can get everything in one request!

Note also the primaryLanguage field in our schema. This isn’t a special field computed by our backend—there’s literally a primaryLanguage column in our database. If you want to look at all languages spoken by a user, you need to know to request both primaryLanguage and languages, and then manually combine these bits of data together after your query finishes. It’s not great.

How else might we have modeled this data? The obvious solution is to make languages resolve to an array of Language objects, and you would be right. An improved schema might look like the following:

Click to copy
type User {  # ...  primaryLanguage: Language  languages: [Language!]!}

Now we still have that annoying primaryLanguage field, but that’s also pretty easy to fix with a small schema change:

Click to copy
type User {  # ...  languages: [UserLanguage!]!} type UserLanguage {  isPrimary: Boolean!  language: Language!}

Much better—now you can get all languages spoken by a user in one field, and don’t need to combine different fields together in your consumer. It’s even now possible to add input arguments to the languages field which would allow you to filter the data based on some criteria.

If we want to add extra fields to a language, we can trivially do that in a backwards-compatible fashion because we’re no longer returning a primitive type. Our problems are solved!

Of course, there are a few questions that you might be wondering.

What if you aren’t sure you’ll need that join?

In the example I’ve provided we have the benefit of hindsight and know that we need the language data. What if you aren’t sure that you’ll have that requirement? Is returning just the foreign key suitable in that case?

I would argue not, and for a couple of reasons:

  1. If you aren’t going to need the data, then what possible use could you have for the foreign key anyway? What does a language ID give you?
  2. There’s (essentially) no performance penalty for returning a list of objects instead of a list of IDs. So why not just do it anyway?

Point #2 might be surprising here, because I think the default assumption a lot of people have here is that you need to perform a database query in order to return that Language type—but you don’t. If you’re only returning the ID anyway, you can simply package that ID up as an object and skip the join entirely. In Nexus it looks like so:

Click to copy
const User = objectType({  name: 'User',  definition(t) {    // ...    t.nonNull.list.field('languages', {      type: 'Language',      resolve: (user) => user.languages.map(        (languageId) => ({ id: languageId }),      ),    });});

No join necessary until you decide to expose more data. I would actually argue that this is where you should start your schema off anyway, even if you do think you might need to expose this data down the line for the simple reason that a small schema has less surface area for mistakes to occur in.

Keep your schema small, focused, and tight for as long as possible to maximize future flexibility by keeping the design space open. Adding something to a GraphQL schema is trivial, but removing something can be a nightmare.

How about pagination?

If one of our users were to speak 1,000 languages then it’s not a great idea to return all 1,000 languages in one go, and we’ll likely want to build pagination into our graph. My improved schema before showed us taking the naive strategy for returning a list of objects, instead of a more capable design such as a connection field.

Make no mistake here: GraphQL connections are almost always the best way to paginate your data. Fortunately, my improved design offers affordances for us to evolve our graph in this direction!

First and foremost, I recommend limiting fields which make joins (like our improved languages) field to something “reasonable” from day 1. Perhaps our languages field only returns the first 20 languages spoken by the user, and doesn’t support any pagination.

The key here is to get something out the door quickly so that our feature doesn’t miss its timeline, while still giving us flexibility down the line when requirements change. Shipping this field without pagination is fine so long as we have reasonable resource limits in place.

When our monitoring systems tell us we’re about to run into trouble because we hired an ultra polyglot, we simply add a languagesConnection field to our User type which does support pagination and then incrementally update our consumers off of languages. We didn’t slow down our initial release by building too much, and we were still able to adjust our graph in response to changing requirements. Good GraphQL schema design allows you to do this!

My foreign key is called thingId instead of thing, why not include that in my schema?

The astute reader will notice that the big reason we ran into trouble with our user’s languages is because the foreign key was named exactly how you’d name the field that joins in the language data. If our database column had been called languageIds then we would not have ran into a naming conflict when adding a languages field that joins in the actual language data.

And sure, you could decide to put both User.languageIds and Language.id in your schema, but I’m not a big fan of this design. In my opinion, you want to have one canonical place within your graph for each piece of data, and reduce duplication wherever possible. RESTful APIs offen end up duplicating data across different resources because they need to provide predetermined response types, and oftentimes denormalizing or proactively making some joins results in an overall reduction of resource utilization.

This isn’t the case with GraphQL where the consumer gets to decide the format they want their data in. There is no advantage to exposing the languageIds field, because it’s just as easy for the consumer to fetch languages.id. Why double up? It’s more elegant to have the information in one location.

The other piece here is that putting languageIds inside your schema leaks implementation details. Why should the consumer know that the user table has a foreign key? We could just as easily have modelled this many-to-many relationship using a join table called user_language instead.

Exposing languageIds in your schema doesn’t outright prevent you from making such a database refactor down the line, but it does make such a change far more annoying. Instead of being able to simply change how the languages field gets resolved and moving on with your life, you will also need to write custom resolver logic for languageIds to maintain backwards compatibility with any consumers relying on that field. You’d need something like the following code (again using Nexus), and it’s downright horrible:

Click to copy
const User = objectType({  name: 'User',  definition(t) {    // ...    t.nonNull.list.nonNull.id('languageIds', {      resolve: (user, _args, ctx) =>        ctx.db.userLanguage.findMany({          where: { userId: user.id },        }).then((data) => data.map((it) => it.id)),    });  },});

If you hadn’t exposed languageIds and had only had a languages field from the beginning, you wouldn’t have needed to write this kludgy code that ultimately serves very little purpose. For minimizing long-term maintenance burden, you are almost always going to be better off exposing your joined data instead of the foreign key because it makes these kind of database migrations simpler. You only need to update one resolver function instead of two.

Don’t put foreign keys in your graph!

There are few things we can be certain about in software engineering, and this is one of those things. Foreign keys should never be included in your GraphQL schema; and they always wind up limiting your ability to evolve your graph in response to changing requirements.

And even if including them was harmless, there’d still be no reason to do it because exposing your list of IDs as a list of objects is a drop-in replacement that offers far more flexibility anyway.

Your GraphQL schema is not required to perfectly mirror your database schema or your old REST endpoints, and coming at it from this perspective usually turns out badly. Adopting GraphQL is an opportunity to critically evaluate your product’s data model and adapt it into a format that makes consumers lives’ easier and allows for business logic to be centralized.

Take that opportunity and run with it—you’ll thank yourself for it in a year’s time.

Don't want to miss out on new posts?

Join 100+ fellow engineers who subscribe for software insights, technical deep-dives, and valuable advice.

Get in touch 👋

If you're working on an innovative web or AI software product, then I'd love to hear about it. If we both see value in working together, we can move forward. And if not—we both had a nice chat and have a new connection.
Send me an email at hello@sophiabits.com