Unions and Interfaces

Single Table Inheritance

Abstract types (unions and interfaces) can be used when you want a field to return two or more different object types. Modeling inheritance in relational databases is tricky, but can be accomplished by using single table inheritance. The basic idea is to create a single table that will represent all the models that will be lumped together. Columns that aren't part of a particular model are simply left null for those particular records. The table will also typically have a specific column that identifies which model a particular record represents.

Let's look at an example. Let's create a table that represents one of two models: Movie or Show.

CREATE TABLE titles (
id SERIAL PRIMARY KEY,
-- Both movies and shows have a name
name TEXT NOT NULL,
-- Only movies have a duration
duration INTEGER,
-- Only shows have a season count
season_count INTEGER,
-- This enum has two values -- 'Movie' and 'Show'
kind title_kind_enum NOT NULL,
);

We can then include the types for Movie and Show in our schema:

type Movie implements Title {
id: ID!
name: String!
duration: Integer
}
type Show implements Title {
id: ID!
name: String!
season_count: Integer
}

Now let's add the Title interface and make that a model:

interface Title @model(table: "titles", pk: "id", includes: ["kind"]) {
id: ID!
name: String!
}

Note that we've set the includes argument to tell Sqlmancer to always include the kind column when querying the table. Now we just need to include a resolveType function in our resolver map:

resolvers = {
Title: {
__resolveType: (title) => {
// Will always return 'Movie' or 'Show', which matches the
// names of the two types in our schema
return title.kind
}
}
}
tip

When resolving an abstract type, GraphQL by default looks for a __typename field on the returned object to determine its type. If you rename the kind column to __typename, you can skip adding a resolveType function altogether.

You can now query Title like any other model. Sqlmancer will combine all the fields from both the Movie and Show types to determine the columns and relationships for the Title model.

Using views

The above pattern will work pretty smoothly but requires you to utilize a single table. Having separate tables for each model is often desirable, though. You may also be adapting an existing database to use with Sqlmancer. In these cases, you can still use a view to combine two or more tables. For this example, we'll define our model using a CTE to create an "inline view", but you could define a view in your database and use that instead as well.

interface Title @model(
pk: "id",
includes: ["kind"]
cte: """
SELECT
id, name, duration, NULL as season_count, 'Movie' as kind
FROM
movies
UNION
SELECT
id, name, NULL as duration, season_count, 'Show' as kind
FROM
shows
"""
) {
id: ID!
name: String!
}

The above will work great if the primary key column is a UUID. If it's an auto-incrementing integer instead, you'll end up with collisions between the two tables so your CTE should account for that:

SELECT
'Movie_' || id as id, name, duration, NULL as season_count, 'Movie' as kind
FROM
movies
UNION
SELECT
'Show_' || id as id, name, NULL as duration, season_count, 'Show' as kind
FROM
shows

However, you'll probably want to make sure you convert these ids back to their original values in the response. You can do so using resolvers. For example:

const resolvers = {
Movie: {
id: (movie) => movie.id.replace(/\D/g,'')
}
Show: {
id: (show) => show.id.replace(/\D/g,'')
}
}