Raymond Camden wrote a good post about Building a Database-driven Eleventy Site. In it, he makes a direct connection to a MySQL database using the mysql npm package to pull the posts for a simple blog that is statically generated (aka pre-rendered). While Ray does this for Eleventy specifically, you could leverage a similar workflow to generate content from a MySQL database in other static site generators (SSGs) like Next.js for instance.
This got me thinking. While there are relatively straightforward methods for pulling from various external APIs or databases using the JavaScript-based SSGs, the same cannot be said for some other traditional SSGs like Hugo or Jekyll. I’m a big Hugo fan, so could I replicate this for Hugo? Or what if I needed to connect more than just a database?
As it turns out, StepZen just released a Netlify Build Plugin that will deploy a GraphQL API to StepZen along with your Netlify site. Since my StepZen GraphQL API can pull from MySQL databases, I can then pull that data and populate my site.
In this post, we’ll explore how I recreated Ray’s data-driven Jamstack site using StepZen as a backend and Hugo as the SSG. One of the key benefits of this approach is that it will not just work for MySQL but also for any number of sources including REST APIs and Postgres databases, regardless of what SSG you use. Rather than relying on a single npm MySQL connector, we could even combine data from any or all of these sources into a single API and populate our site with this.
The completed code for this tutorial is available on GitHub.
Getting Set Up
There are two pieces we need to get set up. The first is the data backend in MySQL and then the web frontend using Hugo.
Creating Your MySQL Database
The first thing we’ll need is a MySQL database. I chose to set mine up via Heroku, which uses Cleardb MySQL because it is free for my purposes, but there are a number of other options you can choose. If you want to copy my setup, here are the steps:
- If you don’t already have one, sign up for a free Heroku account
- Click New > Create New App and give it a name.
- Within the new app, go to the “Resources” tab and under Add-ons, type “cleardb mysql” to find and select the ClearDB MySQL add on. The Ignite - Free plan is sufficient (Note: although the plan is free, you may need to add a credit card to your account before you can add it). Click “Provision.”
- To get the connection details, go to the Heroku dashboard and then to the Settings tab on your app and, in the “Config Vars” section, click “Reveal Config Vars.” Click on the Edit Pencil, and you will see a new pane open up. You will see value like
mysql://user:password@hostaddress/databasename
(there might be an additional?
at the end, you must remove that in thedatabasename
above).
The database I created is very basic. You can create and populate it using this SQL file or just create your own with the following tables:
- A
posts
table with an ID (int), title (varchar), body (blob) and published (datetime) - A
categories
table with an ID (int) and name (varchar) - A
posts_categories
table with an ID (int), postid (int) and categoryid (int)
Setting Up Hugo
Hugo is a Go-based static site generator that is famous for being extremely fast at generating sites - even large ones with thousands of pages.
Technically, Hugo is just an executable that you can download and run anywhere (though you’d have to add it to your path), but the easiest way to install it on a Mac is using Homebrew:
brew install hugo
There are multiple other options for installing on Windows or Linux. Check the installation instructions.
Let’s create a new site.
hugo new site pets-blog
cd pets-blog
This command just created the structure and shell of a Hugo site, but we need at least a theme to run it. There are a lot of Hugo themes, but I chose Hugo Vitae for this project, which offers a basic blog site design.
The easiest way to do this is to download the latest version of the theme, unzip it and place it in our site’s /themes
folder. (Note that, after unzipping, you may need to rename the folder within the theme directory hugo-vitae
)
Finally, let’s tell Hugo to use the theme in the hugo-vitae
folder. Open the config.toml
in the root of our Hugo site and change the values for title
and theme
:
baseURL = "http://example.org/"
languageCode = "en-us"
title = "My Pet Blog"
theme = "hugo-vitae"
The value of theme
should be the folder for our theme within the /themes
directory (i.e. hugo-vitae
) The title
will show in the header of the theme. You can ignore the languageCode
and baseURL
values for now.
You can actually run your site locally now using the hugo serve
command but it will only show the “My Pets Blog” header as we haven’t added any posts yet. Also, as we’re going to use Netlify, let’s install the Netlify CLI as we’ll need it later.
npm install netlify-cli -g
Now, instead of running hugo serve
to run the site locally, we can use netlify dev
. At the moment, it will simply run hugo serve
for us, but we’ll enhance that later.
Creating a GraphQL Schema Connected to MySQL
StepZen allows us to write a GraphQL schema and then connect it to a variety of data sources including REST APIs and databases. We can connect multiple data sources to a single GraphQL API and even combine them to get resources from multiple backends in a single query. However, in this case, we’ll be connecting our schema to just a single MySQL datasource.
Within our Hugo project, let’s create a folder in our root directory named stepzen
to hold our schema. This is the folder the StepZen Netlify Build Plugin assumes. Inside that folder, we’ll place the .graphql
files that represent our GraphQL types. These types will mirror the MySQL backend we just set up.
Before we create the types however, let’s set up the configuration that we’ll use to tell StepZen how to connect to our MySQL database. We need to put this in a config.yaml
file in our stepzen
folder. Inside that, we’ll provide StepZen with the DSN information we copied from Heroku earlier. Note that the DSN should be formatted as shown in the example below.
configurationset:
- configuration:
name: mysql_config
dsn: [username]:[password]@tcp([datacenter].cleardb.com)/[database_name]
Be sure to add this config.yaml
file to your .gitignore
so as not to check protected information into your repository.
Next, let’s create a type for the blog post. First, make a post.graphql
file in the /stepzen
folder. Let’s define the properties of this type.
type Post {
id: ID!
title: String!
body: String!
published: String!
}
We’ll want to be able to query to get data containing posts including getting all our blog posts (i.e. getPosts
) and getting a single post by id (i.e. getPost
). In order to connect this to our MySQL database, we’ll use StepZen’s @dbquery
directive. Place the following queries below the type definition:
type Query {
getPosts: [Post]
@dbquery(type: "mysql", table: "posts", configuration: "mysql_config")
getPostByID(id: ID!): Post
@dbquery(type: "mysql", table: "posts", configuration: "mysql_config")
}
All that is needed to connect this type to MySQL data is the database type (mysql
), the table we are pulling the data from (posts
) and the configuration to use for the connection (the mysql_config
that we defined earlier in the config.yaml
file).
Next, let’s create a type for our blog post category in a file in the stepzen
directory called category.graphql
.
type Category {
id: ID!
name: String!
}
type Query {
getCategories: [Category]
@dbquery(type: "mysql", table: "categories", configuration: "mysql_config")
getCategoryByID(id: ID!): Category
@dbquery(type: "mysql", table: "categories", configuration: "mysql_config")
}
Now we have two types but they don’t yet know about each other. What we want is that when we query for blog posts, we get the categories they’ve been assigned. Let’s fix that.
First, in category.graphql
, let’s add a third query to get categories by post ID. Rather than specify a table
to connect to in MySQL, this GraphQL query will specify a SQL query
. The ?
in the query will be replaced by the id
argument that is passed into the GraphQL query.
getCategoriesByPostID(id: ID!): [Category]
@dbquery(
type: "mysql"
configuration: "mysql_config"
query: "SELECT categories.id, categories.name FROM posts_categories RIGHT JOIN categories ON categoryid = categories.id WHERE postid = ?"
)
Now we’ll use another special StepZen directive, @materializer
, to populate categories in our Post
type. We just need to supply the name of the query on our Category
type that StepZen will use to populate categories
, which will be the getCategoriesByPostID
query we just created above.
type Post {
id: ID!
title: String!
body: String!
published: String!
categories: [Category] @materializer(query: "getCategoriesByPostID")
}
We’re almost ready to test this schema. We just need to create an index.graphql
file in the stepzen
directory to tell StepZen how to assemble our schema.
schema @sdl(files: ["post.graphql", "category.graphql"]) {
query: Query
}
Ok. Let’s deploy and test our schema before we connect it to Netlify. You’ll need the StepZen CLI if you don’t already have it (of course, you’ll need a StepZen account too).
npm install -g stepzen
If this is your first time using the CLI, you’ll need to run stepzen login
and supply your account name and admin key from your My Account page. Once the CLI is configured, make sure you cd
into the stepzen
folder and run the following command:
stepzen start
You’ll be asked to give a name to the API in the format of [folder_name]/[endpoint_name]
. By default, the StepZen Netlify Build Plugin wants a folder name of netlify
, so enter netify/pets-blog
. The CLI will launch a browser window with a query editor and schema explorer that allows us to test the queries we just launched.
For more details on how to build GraphQL APIs connected to a database, check out this blog post.
Connecting the StepZen Netlify Build Plugin
Now that we have an initial API built and deployed, what we’d like is to be able to build and deploy our site and our schema at the same time, that way any changes to our schema are deployed whenever we update our site on Netlify. Even better, we’d like this schema to be deployed prior to Netlify running its build so that we can access the updated schema at build time. Thankfully, the StepZen Netlify Build Plugin does just that.
The first step is to deploy this site to Netlify. We haven’t done anything to populate the content yet, but we need a Netlify site to connect the plugin to. If you haven’t already done so, push this site code to a GitHub, GitLab or Bitbucket repository. Then head over to Netlify and click the “New site from git” button in your Netlify dashboard.
Walk through the steps, leaving the defaults for now. For example, the build command can be left as hugo
and the publish directory as public
. However, when it asks you to set environment variables, set one for HUGO_VERSION
and set the version as 0.74.3
, which is the current version as of this writing (if you’re on a newer version of Hugo, feel free to use that instead).
Once the initial deploy is done, head to the “Plugins” tab in the dashboard and then click on “Go to Plugins Directory”. Search for “StepZen” and then click the “Install” button.
We’ll be asked which site we want to install the plugin on. Choose the site we just created.
The plugin requires a few additional environment variables to configure it. Head to your site’s Netlify dashboard and choose “Site Settings > Build and Deploy > Environment.” We should see the HUGO_VERSION
we added during the creation process. Add the following variables:
STEPZEN_ACCOUNT
- is your account name from your My Account page.STEPZEN_ADMIN_KEY
is your admin key from your My Account page.STEPZEN_NAME
is the endpoint name we gave for our API. When we deployed and tested our API, we called itnetlify/pets-blog
, so here we only need thepets-blog
asnetlify
is the default folder the plugin assumes (we can override the default folder with theSTEPZEN_FOLDER
environment variable).
That’s all that is needed for the plugin to work, but let’s set one last variable we’ll need when calling our StepZen GraphQL API. Set another environment variable named STEPZEN_API_KEY
and set it to the value of your API key from your My Account page.
Finally, click the “Save” button. Our plugin is configured and any changes we make to our StepZen schema will now be deployed whenever a Netlify build is triggered by checking code into our GitHub repository.
Connecting Our StepZen API to Hugo
We’re ready to connect everything to Hugo. Hugo does not have a built-in way to pull data from an API and generate pages, so we’re actually going to write a Node script that will do the work for us. We’ll integrate this script into the build process as well and, since the StepZen API is generated before the build is run, this means that we’ll be pulling content off the latest version of the API for every build.
First, we’ll need to install one npm library called slugify that helps us turn the titles of posts into a slug that we can use to generate the Markdown file name.
npm install slugify
Note that we’ll hook this all up to the build using
netlify dev
, which automatically injects the environment variables we created in the Netlify dashboard. However, if you want to run this script directly, outside the scope ofnetlify dev
, you’ll need a library like dotenv and a local.env
file with the value ofSTEPZEN_API_KEY
to make it work.
One final step before we work on the script – you’ll need a folder for /content/blog/
, which is where the generated Markdown files will be placed. Since these will be generated on each build, it can be empty, however we need to create a simple .gitignore
file within the /content/blog/
directory with the following contents so that the empty folder will still commit in your git repository.
# Ignore everything in this directory
*
# Except this file
!.gitignore
Next, we need to create a file named pullContent.js
in the root of our site. This will be the script that pulls the content and writes the files. Let’s walk through what it is doing.
- It sets up a GraphQL query that will be sent to our StepZen GraphQL API to get all the posts and categories.
- It sets up a simple template literal that will be used to generate the Markdown files from the data provided by the API.
- It sets up the
options
that will be sent viahttps
to the API, including our StepZen API key environment variable that we set up in our Netlify dashboard and which will be automatically injected for us bynetlify dev
. Note that you need to replace the value ofhostname
with your account name in place of mine (i.e.biggs
) - We call the GraphQL API, loop through the results and generate a page using the template literal for each result. The content is written to a Markdown file in the
/content/blog/
folder we created.
const https = require('https');
const fs = require('fs');
const slugify = require('slugify');
const data = JSON.stringify({
query: `{
getPosts {
title
body
published
id
categories {
name
}
}
}`,
});
const markdownFileTemplate = (post, categories) => `
---
title: ${post.title}
categories: ${categories}
published: ${post.published}
---
${post.body}
`;
const options = {
hostname: 'biggs.stepzen.net',
path: '/netlify/pets-blog/__graphql',
port: 443,
method: 'POST',
headers: {
'Content-Type': 'application/json',
'Content-Length': data.length,
Authorization: 'Apikey ' + process.env.STEPZEN_API_KEY,
'User-Agent': 'Node',
},
};
const req = https.request(options, (res) => {
let data = '';
res.on('data', (d) => {
data += d;
});
res.on('end', () => {
const results = JSON.parse(data).data.getPosts;
results.forEach((post) => {
let categories = '';
post.categories.forEach((category) => {
categories += '\n- ' + category.name;
});
let content = markdownFileTemplate(post, categories);
let filename = './content/blog/' + slugify(post.title) + '.md';
fs.writeFileSync(filename, content);
});
});
});
req.on('error', (error) => {
console.error(error);
});
req.write(data);
req.end();
The final step in the process is to update our build scripts so that this runs every time we test the site locally as well as every time Netlify builds the site. Let’s open the package.json
file in the root directory of our site and add the following scripts:
"scripts": {
"build": "node pullContent.js && hugo",
"dev": "node pullContent.js && hugo serve"
}
To make Netlify dev use our new build command instead of the Hugo default, create a netlify.toml
file in the root of your site with the following contents:
[dev]
command = "npm run dev"
We also need to link our local site to the deployed site on Netlify so that it pulls our environment variables locally. From the root of your site, run the following command:
netlify link
You should be able to automatically link your site using the external repository URL (i.e. GitHub, GitLab or Bitbucket). Once that’s done, let’s test this out locally by running it:
netlify dev
You should see something like the following in your command line (note that the environment variables are being injected and that it is overriding the default command and running npm run dev
instead).
The local site should also automatically open in the browser.
We’re almost done. Before this will work on Netlify, we also need to update our build command there. In our Netlify site dashboard, open “Site Settings > Build & deploy > Build settings.” Click the “Edit settings” button, change the build command to “npm run build” and click the “Save” button.
If you check all the changes into your external Git repository, it should now trigger a build in Netlify that pulls content from our MySQL database and generates the content files to populate our site. 🥳 You can see mine running here.
Where To Go From Here
While there were a number of steps involved, we’ve created a pipeline of content that runs from our MySQL database directly into our static Hugo site generated on Netlify. The key thing to consider about this approach and using StepZen as part of the process is that this same process will work for any data source StepZen connects to, including REST APIs or other databases.
We also don’t need to create separate scripts or queries if we have multiple data sources, which we probably will in a “real world” situation. Our one GraphQL query can get data from all our sources in a single query. For instance, if we used a separate API to manage comments for our blog, we could connect that to StepZen and get the post’s comments along with the post data in the same query.
I should also note that the core of this process would work for any SSG. We could modify our script, for instance, to work for a Jekyll site. We could generate pages from data in Eleventy using its built-in functionality. We could call our StepZen GraphQL API from getStaticPaths()
and getStaticProps()
in Next.js to generate blog posts and more. Or we could even create a simple plugin for Gatsby to integrate our API into Gatsby’s data layer, which is also GraphQL-based.
Finally, we could take this some steps further by having database updates call a webhook in Netilfy to trigger a build automatically. Or we could even create a simple admin interface that calls our GraphQL API to allow content editing from within the site (using Netlify Identity, perhaps, for authentication). The point is, GraphQL can help you free yourself from worrying about which backends you are connecting to or how many, opening up a ton of possibilities.