Posted on

Note: I wrote this some time ago and forgot to publish. The original thing is a gist on github, in case something looks weird here.

Not sure if I’m that stupid or people are starting to make life harder for everyone. I lost a day to figure stuff out, all while growing a couple more gray hair. I also cursed a lot and considered quitting programming (again).

Maybe it’s just me, maybe I overthink everything. I suppose I though GraphQL was something else. I suppose I expected it to help me write an API faster, but it’s not like that. It’s easier to query the API, but you still have to code everything on your own to make this possible (like the queries).

There are things like join monster (example below) that generate SQL SELECT queries for you (based on the GraphQL query you throw at it), but apart from that, you are on your own.

4 concepts you need to understand

Schema

Where you define the structure of the database. You can define it in (probably) two ways, which I will describe below.

Query

A query is a GET endpoint you would like to expose. It’s not exactly like that, but it actually is. When defining a query you can omit the keyword query, and you can add a name after query. The things below are equivalent. I lost an hour just figuring out why queries look different in different articles I read. 🤯

{
  tables {
    number
  }
}

# or

query {
  tables {
    number
  }
}

# or

query Tables {
  tables {
    number
  }
}

The result would kind of replace:

const results = await fetch(`${API_URL}/tables`, {
  method: 'get'
})
console.log(results.number)

Mutation

Where you define the “PUT, POST or DELETE endpoints” you would like to expose. It starts with the keyword `mutation` and can also have a name. It can take parameters or not (depending on how you define them in the schema), and it returns what you code it to return (again in the schema).

mutation {
  createTable(number: 1) {
    id
    number
  }
}

This would replace:

const results = await fetch(`${API_URL}/tables`, {
  method: 'post',
  body: {
    number: 1
  }
})
console.log(result.id, results.number)

Resolver

You have to code the same stuff you would code with a REST api. It’s the exact same code. You can copy/paste it from your existing REST api. Really.

If you want to make your GraphQL queries look super cool, and return only the nested stuff you need (you see these everywhere you fucking look), you will most probably have to join a bunch of tables in your SQL queries, and probably have to create an object for each nested value, and add that to the result set (example Requests & Table). It’s a pain in the ass.

Check the examples below, where I’m building schemas (I use a mysql database in this case).

Careful if your query returns Promises

If your query returns Promises (like in my case), you should wrap the whole function inside another Promise, and pass this Promise to the resolver, otherwise it will be null (you will see it right away).

2 ways to define schemas

Using buildSchema:

const schema = buildSchema(`
  type Table {
    id: ID!
    number: Int!
    description: String
  }
  type Query {
    tables: [Table]
  }
  type Mutation {
    createTable(number: Int!, description: String): Table!
  }
`)

const resolvers = {
  // notice the Promise?
  tables: () => new Promise((res, rej) => {
    const query = 'SELECT * FROM tables ORDER BY number ASC'
    
    // 'pool' can be a local or global variable (depending on where you call it from)
    pool.getConnection(function (err, connection) {
      if (err) throw err

      connection.query(query, (err, rows) => {
        connection.release()

        if (err) {
          return rej(new Error(err))
        }
        else {
          const results = rows.map(row => {
            return new Table(row.id, row.number, row.description)
          })
          return res(results)
        }
      })
    })
  }),
  
  // see the promise again?
  // also note we are passing an object as param
  createTable: ({ number, description }) => new Promise((res, rej) => {
    let query = 'INSERT INTO ?? (??, ??) VALUES (?, ?)'
    const values = ['tables', 'number', 'description', number, description]
    query = mysql.format(query, values)

    pool.getConnection(function (err, connection) {
      if (err) throw err

      connection.query(query, (err, rows) => {
        connection.release()

        if (err) {
          rej(err)
          throw new Error(err)
        }
        else {
          const table = new Table(rows.insertId, number, description)
          res(table)
        }
      })
    })
  }
}

BTW I have a simple Table class (so I can return a new Table):

class Table {
  private id
  private number
  private description
  constructor(id, number, description) {
    this.id = id
    this.number = number
    this.description = description
  }
}

Also notice that the createTable resolver takes an object as argument. (That’s because it also takes other shit as arguments that you probably don’t need when you start. Or maybe you do, I have no idea, because everything I read is so complicated, and I will probably understand that shit in a year or so.)

To use these with graphqlHTTP:

app.use('/graphql', 
  graphqlHTTP({
    schema: schema,
    rootValue: resolvers
  })
}

Using GraphQLSchema:

const TableType = new GraphQLObjectType({
  name: 'Table',
  fields: () => ({
    id: {
      type: GraphQLInt
    },
    number: {
      type: GraphQLInt
    },
    description: {
      type: GraphQLString
    }
  })
})

const QueryRoot = new GraphQLObjectType({
  name: 'Query',
  fields: () => ({
    tables: {
      type: new GraphQLList(TableType),
      resolve: () => new Promise((res, rej) => {
        const query = 'SELECT * FROM tables ORDER BY number ASC'
	
        pool.getConnection(function(err, connection) {
          if (err) throw err
    
          connection.query(query, (err, rows) => {
            connection.release()
    
            if (err) {
				const error = new Error(err)
              return rej(error)
            }
            else {
              const results = rows.map(row => {
                return new Table(row.id, row.number, row.description)
              })
              return res(results)
            }
          })
        })
      })
    }
  })
})

const MutationRoot = new GraphQLObjectType({
  name: 'Mutation',

  fields: () => ({
    createTable: {
      type: TableType,
      args: {
        number: { type: GraphQLInt! },
        description: { type: GraphQLString }
      },
      resolve: (parent, args) => {
        return createTable({ number: args.number, description: args.description })
      }
    }
  })
})

const createTable = ({ number, description }) => {
  return new Promise((res, rej) => {
    var query = 'INSERT INTO ?? (??, ??) VALUES (?, ?)'
    var values = ['tables', 'number', 'description', number, description]
    query = format(query, values)

    pool.getConnection(function(err, connection) {
      if (err) throw err

      connection.query(query, (err, rows) => {
        connection.release()

        if (err) {
          rej(err)
          throw new Error(err)
        }
        else {
          const table = new Table(rows.insertId, number, description)
          res(table)
        }
      })
    })
  })
}

const schema = new GraphQLSchema({
  description: 'Damn Schema',
  query: QueryRoot,
  mutation: MutationRoot
})

And to use with graphqlHTTP:

app.use('/',
  graphqlHTTP({
    schema: schema
  })
)

Oh, don’t forget to import stuff you need from graphql.

And you can also separate resolver code into their own functions, which will make everything more readable, something like:

const QueryRoot = new GraphQLObjectType({
  name: 'Query',
  fields: () => ({
    tables: {
      type: new GraphQLList(TableType),
      resolve: () => fetchTables()
    }
  })
})

const fetchTables = () => {
  return new Promise((res, rej) => {
    const query = 'SELECT * FROM tables ORDER BY number ASC'
    
    pool.getConnection(function(err, connection) {
      if (err) throw err

      connection.query(query, (err, rows) => {
        connection.release()

        if (err) {
          const error = new Error(err)
          return rej(error)
        }
        else {
          const results = rows.map(row => {
            return new Table(row.id, row.number, row.description)
          })
          return res(results)
        }
      })
    })
  })
}

Using join-monster to help with SELECTs

1. Install it with npm i join-monster.
2. If you separated the resolver as described above, change QueryRoot to:

    // unchanged
    tables: {
      type: new GraphQLList(TableType),
      resolve: (parent, args, context, resolveInfo) => {
      	// remember above I didn't know what these other parameters are for? lol
        // joinMonster needs, and returns, a Promise
	// this is always the same btw, just copy / pasta it (replace fetchTables though)
        return joinMonster(resolveInfo, {}, sql => {
          return fetchTables(sql)
        }, { dialect: 'mysql' })
      }
    }
    // unchanged

3. Change the fetchTables function to accept the generated SQL as parameter:

export const fetchTables = (sql) => {
  return new Promise((res, rej) => {
    // const query = 'SELECT * FROM tables ORDER BY number ASC'
    const query = sql
    // unchanged
  })
}

4. Actually, at this point, thanks to jsonMonster, you can have a single file that handles SELECT queries:

export const query = (sql) => {
  return new Promise((res, rej) => {
    const query = sql
    
    pool.getConnection(function(err, connection) {
      if (err) throw err

      connection.query(query, (err, rows) => {
        connection.release()

        if (err) {
          const error = new Error(err)
          return rej(error)
        }
        else {
          const results = rows.map(row => {
            return row
          })
          return res(results)
        }
      })
    })
  })
}

And you are done. Now you can query something like:

query {
  tables {
    number
  }
}

and the query will be generated for you by joinMonster, on the fly (which is nice, thanks joinMonster).

If you need to JOIN two tables with join-monster

tables

id number
1 1
2 2

users

id name table_id
1 John 1
2 Jane 1

Joining on specific fields (like tables.id = users.table_id) is simple, you have to change field in your Type definition to use sqlJoin (docs). It should look something like this:

export const TableType = new GraphQLObjectType({
  sqlTable: 'tables',
  // unchanged
  users: {
    type: UserType,
    sqlJoin(tables, users) {
      return `${tables}.id = ${users}.table_id`
    }
  },
  // unchanged
})

A couple of things to notice from the example above (you can get this from the docs as well):

– this can be used to query something like { tables { users { name } } }
– the first argument in sqlJoin (tables) is the sql_table defined in the Type we are working on (in this case TableType, so tables)
– the second argument (users) is the sql_table you have defined in UserType (probably something like users)
– John and Jane are having a good time while I’m going crazy with this shit

If you have to join through a link table with join-monster

items

id name price
1 Beer 5.00
2 Vodka 6.00

orders

id table_id timestamp
1 1 2021-06-05 14:06:30
2 2 2020-02-20 13:37:00

order_items

id order_id item_id
1 1 3
2 1 4
3 2 1

If you have a link table like order_items, you can modify your Types use junctions (docs). Here’s a simple example, totally for free:

export const OrderType = new GraphQLObjectType({
  sqlTable: 'orders',
  // unchanged
  items: {
    type: new GraphQLList(ItemType), // boop
    junction: {
      sqlTable: 'order_items', // beep
      sqlJoins: [
        (orders, orderItems) => `${orders}.id = ${orderItems}.order_id`, // boom
        (orderItems, items) => `${orderItems}.item_id = ${items}.id`,
      ]
    }
  }
  // unchanged
}

– boom: orders will be the sql_table defined in the Type we are working on (in my case OrderType, so orders)
– boop: because we are returning an ItemType, items will be the sql_table defined inside ItemType
– beep: I have a table named order_items in the database (like the one above)

Hope this helps anybody. I know it helped me 🍺

Leave a Reply

Your email address will not be published. Required fields are marked *