I recently launched a mini side project, IsraelVC.co, that catalogues the latest investments in Israeli high tech.
It’s a simple site: one page containing a list of the 20 most recent investments that have happened in Israel. It also has a Google Sheet connected to it which acts as the database for the site. The database contains 4000 investments. In the near future the site will show all the investments too.
This post is about how I built it.
Tech Stack
- Gatsby (React)
- Google Sheets
- Now Serverless
- Netlify
- Mailchimp
Gatsby
Gatsby is a React based static site generator. It allows you to easily create sites that are lightning fast and SEO friendly.
Hosting a Gatsby site is very simple. You can use a service such as Netlify or Now to host the site for free. Free doesn’t mean these services are bad. These services are the best available. They will serve your content from a CDN located near your users. You can’t ask for much better speed and is infinitely scalable without needing to maintain any servers. Win. Win. Win.
Netlify and Now both have continuous deployment set up. What this means is that you can push to your GitHub repo and your site will automatically updated. If you update and push a commit on a development branch, a new deploy will be published at its own url for that branch. There is even a published site for every commit. It’s a great all round developer experience. If you use Now you can also deploy with their command line tool, by simply typing now.
Google Sheets
I use Google Sheets as the database for the project. A typical site would use PostgreSQL, MongoDB or some other database. This project was small enough that I could rely on Sheets to do the job.
Using Sheets as a database may come as a surprise to some people. That’s not how you develop I hear you shout!
So why use Sheets?
For one it’s super simple to use. Anyone can use it. To add information you just open it up and start typing. Compare that to a traditional database. You have to be a developer to update it or read information in it.
Sheets provides permission based read/write access out the box. No need to build an admin panel.
You don’t have to worry about server maintenance or scaling. That’s Google’s job. If I had to list millions of investments I may have run into issues, but that wasn’t a case I had to deal with.
In this specific case I also wanted to give users easy access to the data so that they could play with it as they wish. The spreadsheet itself is public.
Sheets has a tonne of functionality built in. For example, being able to sort or search through thousands of investments. This saved me the trouble of building out the functionality myself.
One other thing you can do is connect a Google Forms to Sheets, with the form responses going straight into the spreadsheet.
Traditional databases have their benefits. Most web or mobile apps need a
backend with a database. Performance, large scale, schemas, backups, triggers, joins, fine grained user access control. The list goes on. You could probably figure out a way to implement some of these features using a spreadsheet, but ultimately this probably isn’t a good idea. Luckily, IsraelVC didn’t need these benefits.
Alternatives
There are some alternatives to Sheets. One is simply storing the information in the repo as JSON. Another option is storing Markdown files in the repo containing the data. This is how a typical Gatsby blog is built. For example, https://overreacted.io/ by Dan Abramov, but there’s an endless list of tech blogs just like it.
The first iteration of IsraelVC actually did this. I used Netlify CMS to help. It gives you a simple admin interface in which you can update your blog posts (or any other data on the site). Once you hit “Publish” it commits the changes to your git repo and if you’re using a CD tool will update your site automatically.
For a blog or landing page where you want to update data easily, Netlify CMS is a great option. You can write straight to the repo and for many developers this is a good solution, but if you want to allow less technical members of your team to update items without needing your help, a CMS is a better solution.
Netlify CMS has its downsides. It’s a little slow to publish and update posts. For the occasional blog post this doesn’t matter, but if you’re entering a lot of data often this can be annoying.
Other headless CMS systems include Contentful, Prismic, and GraphCMS. Wordpress also offers a headless CMS system. I haven’t used any of these so I won’t comment on them, but I assume they provide a more expansive solution than Netlify CMS and will be better a choice for many teams.
Now Serverless
I use Now as the API for my site. All the API does is query my Google sheet of investments and return it.
You can see the code for it on GitHub here:
All in all it’s 60 lines of code and you can deploy it with a single command:
now
It’s also possible to set up continuous deployment for Now with a few clicks, but I have yet to do that.
There are a few pieces of magic I’d like to talk about here.
I don’t have to maintain a server, it’s infinitely scalable, and free (well I pay a minimal amount for it, but you can go with the free plan too and get the same functionality).
It’s fast. Now recently launched serverless pre-rendering
(SPR). What it does is cache responses from your serverless function. My function makes a call to Google Sheets API which can take a few seconds to respond. I don’t want my users waiting a few seconds for a response. They have more important things to do like investing in startups.
SPR caches the response at the edge on CDN and responds immediately. In the background, the function grabs the latest sheets data or whatever else it’s doing and updates the CDN for the next users that visit the site.
To enable SPR, you have to add a single line of code to your function:
res.setHeader(‘Cache-Control’, ‘s-maxage=30, stale-while-revalidate=3600, max-age=0’)
s-maxage=30— tells the CDN to cache the response for 30 seconds.
stale-while-revalidate=3600 — tells the CDN that if the data is stale (i.e. the cached data was updated more than 30 seconds ago), then the stale version of the site should be served. And the cache will be updated in the background so that the data is fresh for the next user that comes to the site. If someone hasn’t been to the site in a long time and the stale data is more than 3600 seconds old (1 hour), the CDN is instructed to not serve the stale data, but instead fetch the latest data from Sheets and serve that to the client directly.
max-age=0— tells the client not to cache the response. The client will always request the latest data from our API.
To test the serverless function locally while developing I used now dev. It’s a new tool and has its problems. It randomly crashes every so often and hot reloading can take a while. The Now team has been working on it and releasing updates. Recently it has worked better, but it’s still not the best experience.
Netlify also provides serverless. It’s called Netlify Functions. I had a little more trouble setting this up. The settings needed for it in the netlify.toml file are confusing, and netlify dev seems even buggier than now dev.
Both netlify dev and now dev are very new, so I will cut them some slack. These are two amazing companies if I haven’t already made it clear how I feel about them :)
Netlify
I currently use Netlify for frontend hosting. I love using Netlify for static deployments. It’s so easy to use. 10/10 on that front. It’s free and even has features like automatic lossless image compression that Now is missing. Having CD setup out the box is a joy to use, especially when developing a project on a team.
There is a downside to having the frontend hosted on Netlify and the serverless backend on Now. In the future I will likely move the frontend to Now as well. The main reason is that I’d like to avoid an extra DNS lookup which happens when the API and frontend are at different domains. This is a bit of a micro optimisation that isn’t strictly necessary, but having seen the DNS lookup for the API take 2.5 seconds one time I’d like to cut that out. Another way to solve the problem is to move the backend to Netlify Functions, but I prefer Now Serverless.
UPDATE:
After posting this the team at Gatsby pointed out another way of integrating Gatsby with Sheets without the need for the Now API server.
There is a Gatsby Sheets plugin:
https://www.gatsbyjs.org/packages/gatsby-source-google-sheets/
This plugin grabs the data from your spreadsheet at build time. You use Gatsby graphql queries to grab the data you want from the sheet.
Taking this approach, you need to trigger a rebuild of your site whenever the Sheets data changes. You can do this by going to your Netlify admin, using the Netlify API, or by adding a button to the sheet that calls the Netlify API and rebuilds the site on click. This third approach is cool and documented here.
Mailchimp
Not much to write here. I will be sending out a weekly email newsletter with that week’s investments so I added a simple Mailchimp form to the site. It’s free up to 2000 users and no hassle. For now I will be manually sending the emails. In the future I may automate this process and use a solution such as Mailgun or SendGrid to do the sending.
Summary
Some great tools exist to massively speed up development, minimise maintenance and lower costs.
Handling servers, scaling, and performance can all be headaches. Paying a few dollars per month for every mini side project you launch is also annoying. Nowadays you can use the best of the best for free.
The stack I covered is simple, but extremely powerful. As a freelance developer I deal with databases and deployments on a weekly basis. I also maintain a site with hundreds of thousands of users. I didn’t choose to use this stack for lack of other options. I chose it because it’s an awesome solution. Deploying to Now or Netlify feels like cheating, but it’s not. It’s the smart choice in many cases.
I’d love to hear your thoughts or any questions in the comments below.
If you enjoyed this post and would like to read more like it in the future, be sure to smash the follow button below. Feel free to reach out to me on Twitter @elie2222:
Or at my website: elie.tech.
Top comments (3)
Thanks for the great article! Regarding triggering a rebuild of the site when you edit the Google Sheet, you mention that "the third option is really cool and documented here:" but there is no link. Are you able to provide this as this is exactly what I'm looking for! Thanks
Try this link:
medium.com/@eliezer/how-i-built-th...
This: jmolivas.weknowinc.com/how-use-goo...
Thanks Elie! Unfortunately that link no longer seems to work but thanks anyway!