Kelvin O Omereshone

Developer. Teacher. Speaker.

← Back to blog

Build a polls API with Platformatic DB

After having Matteo Collina come to a TKYT session to teach me Platformatic DB, I was keen on checking out Platformatic - a tech(and company) that aims to remove the friction of day-to-day backend development by providing a suite of open-source tools.

Since it’s been a while I wrote a guide article, and with Platformatic inviting me to participate in their Launch Week, I decided to build a polls API using one of the Platformatic tools - Platformatic DB

What is Platformatic DB?

Platformatic DB solves the problem of having to create endpoints whose sole purpose is to return data from a database.

With Platformatic DB, you don’t need to write those endpoints anymore as Platformatic DB will map your Database to REST/GraphQL endpoints to expose to your API consumers.

When I was introduced to Platformatic DB, the quickest thing I could relate it to was Sails Blueprint.

But unlike Sails Blueprint, Platformatic DB also exposes a GraphQL API which to me is really convenient as you write once and have both a REST-based API and a GraphQL API for clients that want to consume your API via GraphQL. Win-win.

Prerequisites​

To follow along and build the polls API with this article, you’ll need to have the following installed:

Polls API

As a quick way to get my hands dirty with Platformatic DB, I decided to build a polls API. This project was inspired by the AdonisJS polls app. Basically the polls API we will be building in this article won’t have a UI but it will expose the API necessary for anyone to implement a UI on top of it.

And of course since we are using Platformatic DB, we will be able to expose both REST and GraphQL endpoints with minimal work.

Here is what our polls API will be able to do for its first version:

Alright, now that we have an high-level overview of what we will be building, let’s get started.

Create a new Platformatic DB project

To get started we will create a new Platformatic DB project by running the below bash command in a terminal

Make sure you are in the directory you want to create the project in

npm create platformatic@latest

When you run the above command, you will be asked what sort of project you want to create, choose DB. You can follow the prompt and choose the options you desire.

Note for this project, we are assuming you chose No for TypeScript and we will be using JavaScript

When asked if you want to run the migrations, select no as we don’t want the example migrations to be ran but rather we want to customize those migrations.

Once the setup has complete, the Platformatic CLI would have generated the files needed for your Platformatic DB project as well as installed your project’s dependencies

Setting up migrations

For our polls API, we need 2 tables, polls and poll_options, let’s edit the migrations generated by the Platformatic CLI to create the polls table

Creating the polls table

Polls are central to the functioning of this API, so we will kick-off things by creating the polls table. To create the polls table, edit migration file - migrations/001.do.sql - to look like this:

CREATE TABLE IF NOT EXISTS polls (
  id INTEGER PRIMARY KEY,
  question TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Finally, we will need to edit migrations/001.undo.sql to look like so:

DROP TABLE polls;

Let’s apply the migration we just created by running the below command:

npx platformatic db migrations apply

I think its worth mentioning that Platformatic will use SQLite as the default Database for a Platformatic DB application.

You can find the SQLite file in your project root directory and also see how it’s defined in the .env file generated when you used the Platformatic CLI to generate your Platformatic DB application.

For more on what’s in the Platformatic DB application, see this how-to guide on the Platformatic YouTube channel.

You will also noticed that when we ran the migrations, we got a Poll.d.ts TypeScript type file created in the types/ directory. This will come in handy for type hinting when querying the database.

Now let’s start the Platformatic DB dev server by running the below command:

npm start

Next, in our terminal, let’s make a request to our server’s REST API to create a new poll.

I will be using HTTPie CLI for the request but you can use CURL or even a GUI that let’s you test an endpoint. The bottom line is that you are able to make a request to the server.

http POST :3042/polls question="Do you use Node.js?"

I made a tutorial on how to use HTTPie CLI if you like it as a CURL alternative.

We should now receive a response similar to the output below:

{
  "createdAt": "1678780330537",
  "id": 1,
  "question": "Do you use Node.js?"
}

Isn’t this exciting? Without creating the REST endpoint, we are able to make requests to the Platformatic DB server because it automatically expose CRUD endpoints to the database. Brilliant!

Another subtle thing you might have missed is that even if we specified the createdAt field as created_at in the table column, Platformatic DB serialized it to camel case - createdAt. I think that’s pretty neat.

Creating the poll’s options table

Currently our polls table can’t do much because we’ve got questions but no options(polls do need options to function well, right?). Let’s fix that.

We will begin by creating a new migration file by running the following command in the terminal:

npx platformatic db migrations create

The above command will create two files in your migrations/ folder - 002.do.sql and 002.undo.sql

Let’s edit 002.do.sql to create the poll_options table and then set up a relationship between the polls and poll_options table because a poll can have many options.

CREATE TABLE IF NOT EXISTS poll_options (
  id INTEGER PRIMARY KEY,
  poll_id INTEGER REFERENCES polls(id),
  title TEXT NOT NULL,
  vote_counts INTEGER DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Let’s also edit the 002.undo.sql file to drop the poll_options table:

DROP TABLE poll_options;

Okay, let’s apply our latest migration:

npx platformatic db migrations apply

After the above command has been successfully ran, let’s now give the poll we created earlier, some options.

This time we won’t use the REST API. We can, but I want to show you how we can use GraphQL as well with Platformatic DB.

GraphiQL

Platformatic DB comes bundled with a GraphQL playground - GraphiQL - and we can use it to test our GraphQL query. To access the playground, make sure your Platformatic DB server is running and then visit http://127.0.0.1:3042/graphiql.

You can clear out the comments so the editor is empty.

To test things out, let’s write a query to show us the poll we have created:

query {
  polls {
    id
    question
    createdAt
  }
}

You should get a similar output to the one below on the GraphiQL output window:

{
  "data": {
    "polls": [
      {
        "id": "1",
        "question": "Do you use Node.js?",
        "createdAt": "1678780549373"
      }
    ]
  }
}

A cool thing about GraphQL is that we have the flexibility to form complex queries, now let’s return the polls and their poll options.

query {
  polls {
    id
    question
    createdAt
    pollOptions {
      title
      voteCounts
    }
  }
}

We get a similar output like the one we had earlier but with a new field - pollOptions

{
  "data": {
    "polls": [
      {
        "id": "1",
        "question": "Do you use Node.js?",
        "createdAt": "1678780549373",
        "pollOptions": []
      }
    ]
  }
}

However, we have an empty array at this time, because we haven’t really created a poll option for the poll we currently have, let’s create a GraphQL mutation to fix that. So back in GraphiQL, write the following mutation:

mutation {
  savePollOption(input: { pollId: 1, title: "Yes" }) {
    id
    title
  }
}

And we get:

{
  "data": {
    "savePollOption": {
      "id": "1",
      "title": "Yes"
    }
  }
}

Let’s create one more:

mutation {
  savePollOption(input: { pollId: 1, title: "No" }) {
    id
    title
  }
}

Now we get:

{
  "data": {
    "savePollOption": {
      "id": "2",
      "title": "No"
    }
  }
}

Finally let’s run the previous GraphQL query to get polls and their options:

query {
  polls {
    id
    question
    createdAt
    pollOptions {
      title
      voteCounts
    }
  }
}

We now get:

{
  "data": {
    "polls": [
      {
        "id": "1",
        "question": "Do you use Node.js?",
        "createdAt": "1678780549373",
        "pollOptions": [
          {
            "title": "Yes",
            "voteCounts": 0
          },
          {
            "title": "No",
            "voteCounts": 0
          }
        ]
      }
    ]
  }
}

Create a custom mutation

Though its okay to first create a poll and then with multiple queries, we can create the options for that poll, it should be more convenient to have a custom mutation that let’s you create a poll and its options in one query.

Fortunately, Platformatic DB let us create custom mutations and we already created the file that we can write this custom mutation in - plugin.js. So let’s write it.

First we will define a new mutation type. So edit plugin.js to look have the following code:

app.graphql.extendSchema(`
  extend type Mutation {
    savePollWithOptions(poll: PollInput!, pollOptions: [PollOptionInput!]!): Poll
  }
`)

Next let’s define an async function called savePollWithOptions:

async function savePollWithOptions(poll, pollOptions) {
  const newPoll = await app.platformatic.entities.poll.save({ input: poll })
  if (newPoll) {
    pollOptions.map(async (pollOption) => {
      const pollOptionToSave = { pollId: newPoll.id, ...pollOption }
      return await app.platformatic.entities.pollOption.save({
        input: pollOptionToSave,
      })
    })
  }
  return newPoll
}

Finally we will define the mutation resolver like so:

app.graphql.defineResolvers({
  Mutation: {
    savePollWithOptions: async (_, { poll, pollOptions }) =>
      await savePollWithOptions(poll, pollOptions),
  },
})

Your plugin.js should look like so:

/// <reference path="./global.d.ts" />
'use strict'

/** @param {import('fastify').FastifyInstance} app */
module.exports = async function (app) {
  app.graphql.extendSchema(`
    extend type Mutation {
      savePollWithOptions(poll: PollInput!, pollOptions: [PollOptionInput!]!): Poll
    }
  `)

  async function savePollWithOptions(poll, pollOptions) {
    const newPoll = await app.platformatic.entities.poll.save({ input: poll })
    if (newPoll) {
      pollOptions.map(async (pollOption) => {
        const pollOptionToSave = { pollId: newPoll.id, ...pollOption }
        return await app.platformatic.entities.pollOption.save({
          input: pollOptionToSave,
        })
      })
    }
    return newPoll
  }

  app.graphql.defineResolvers({
    Mutation: {
      savePollWithOptions: async (_, { poll, pollOptions }) =>
        await savePollWithOptions(poll, pollOptions),
    },
  })
}

Okay let’s test this new mutation we’ve just made, head back to the GraphiQL playground and click the ‘Re-fetch GraphQL schema’ button in the left-hand sidebar, and then run the following mutation:

mutation {
  savePollWithOptions(
    poll: { question: "JavaScript or TypeScript?" }
    pollOptions: [{ title: "JavaScript" }, { title: "TypeScript" }]
  ) {
    id
    question
  }
}

You should get an output similar to the one below:

{
  "data": {
    "savePollWithOptions": {
      "id": "2",
      "question": "JavaScript or TypeScript?"
    }
  }
}

Let’s run the previous query that returns polls with their options to see if we really did create a poll with its options in a single mutation:

query {
  polls {
    id
    question
    createdAt
    pollOptions {
      title
      voteCounts
    }
  }
}

You should now have 2 polls and their options:

{
  "data": {
    "polls": [
      {
        "id": "1",
        "question": "Do you use Node.js?",
        "createdAt": "1678811779896",
        "pollOptions": [
          {
            "title": "Yes",
            "voteCounts": 0
          },
          {
            "title": "No",
            "voteCounts": 0
          }
        ]
      },
      {
        "id": "2",
        "question": "JavaScript or TypeScript?",
        "createdAt": "1678812120125",
        "pollOptions": [
          {
            "title": "JavaScript",
            "voteCounts": 0
          },
          {
            "title": "TypeScript",
            "voteCounts": 0
          }
        ]
      }
    ]
  }
}

Super neat right? What’s even more cool is that we can now reuse the same savePollsWithOptions function to create a custom REST endpoint that allow us create a poll with its options. Let’s do that next.

Custom REST endpoint

In the same plugin.js file, let’s set up a custom endpoint to create a poll with options. To do so add the following code below the line you defined app.graphql.defineResolvers()

app.post('/polls/new', async function (request, response) {
  return {
    poll: await savePollWithOptions(
      request.body.poll,
      request.body.pollOptions
    ),
  }
})

Making a request to this new endpoint will now create a poll with the options of the poll.

Voting on a poll

We are almost done with the first version of our polls API, but to finish off, let’s implement voting on the poll.

To do this, we will create a custom mutation and endpoint which will make it easy to provide the pollId and the options id to increment the voteCounts.

Let start with an async function called vote. Edit plugin.js to have the below function:

async function vote(pollId, optionId) {
  const { db, sql } = app.platformatic
  const result = await db.query(sql`
      UPDATE poll_options SET vote_counts = vote_counts + 1 WHERE id=${optionId} AND poll_id=${pollId} RETURNING vote_counts
    `)
  return result[0]?.vote_counts
}

Notice, how we can drop down to using SQL statements when we need to. Let’s update the already existing app.graphql.extendSchema() to create a mutation type called vote.

Your call to app.graphql.extendSchema should now look like this:

app.graphql.extendSchema(`
    extend type Mutation {
      savePollWithOptions(poll: PollInput!, pollOptions: [PollOptionInput!]!): Poll
      vote(pollId: ID!, optionId: ID!): Int
    }
  `)

Finally, in the already existing app.graphql.defineResolvers(), let’s ad a new mutation to create the vote mutation.

Your app.graphql.defineResolvers() should now look like this:

app.graphql.defineResolvers({
  Mutation: {
    savePollWithOptions: async (_, { poll, pollOptions }) =>
      await savePollWithOptions(poll, pollOptions),
    vote: async (_, { pollId, optionId }) => vote(pollId, optionId),
  },
})

Head back to GraphiQL and click the ‘Re-fetch GraphQL schema’ button again and then run the vote mutation:

mutation {
  vote(pollId: 1, optionId: 1)
}

You should get similar output:

{
  "data": {
    "vote": 1
  }
}

Finally let’s implement the REST endpoint to vote:

app.patch('/polls/:id/vote', async function (request, response) {
  return { voteCounts: await vote(request.params.id, request.body.optionId) }
})

And that’s it, we’ve covered the feature set for version 1 of our polls API built with Platformatic DB.

Next steps

There is so much you can add to this API. For example you can:

And so much more we can add.

You can also fork the repo for this article to start implementing more features on it.

Conclusion

In this article we set up a polls API using Platformatic DB to expose both REST and GraphQL APIs, we also created custom mutations and endpoints to further extended Platformatic DB’s functionalities.

It was really fun trying out Platformatic DB and the convenience of having both a REST API and GraphQL API still blows my mind. Let me know what you think about it.

Newsletter