Adding a Database to our AWS Server

In the last few articles on the blog, we've been exploring how to launch a Haskell web server using AWS. Here are the steps we've done so far:

In this final part of the series, we're going to learn to attach a database to our application.

There are a few gotchas to this. Setting up the database for first time use is a bit tricky, because we have to do some initial migrations. Then we need to use environment variables to ensure it works both locally and on the remote server. Let's get started.

A Basic Schema

Let's first assume we have a super basic schema using the Persistent library. (If you want some details on how this works, see our Real World Haskell series). We'll just have one type in our database, and users will use server endpoints to create or fetch these "text entries".

import           Database.Persist.Sql
import qualified Database.Persist.TH as PTH
import           Data.Text (Text)

PTH.share [PTH.mkPersist PTH.sqlSettings, PTH.mkMigrate "migrateAll"] [PTH.persistLowerCase|

  TextEntry sql=text_entries
    text Text

|]

An important product of this template Haskell sequence is the migrateAll function, which will run the proper commands to migrate a Postgres database to fit our schema by creating tables.

Whenever we first create a database, we have to make sure it's migrated. But before we even do that we have to make sure we've created a database for Postgres to use! Let's see the commands we need for this, and how to run them in Haskell.

Running Setup Commands

When you install Postgres on your machine, you'll have separate "databases" on your system to help you keep your data separate. Separating data allows each database to have its own "users" table without having any name conflicts, for one example. By default, Postgresql comes installed with a database called postgres.

But we don't want to use this to store our data. We want to create a separate database. We want to create this database if it's the first time we're running the server with a database. But otherwise, we just want to make sure its migrations are up to date.

Now, the command we would run to create this database is simple:

CREATE DATABASE quiz;

But we can first run this command to see if this database already exists:

SELECT datname FROM pg_database WHERE datname = 'quiz';

Both these commands assume we are connected to the postgres database.

Since these first two instructions are raw commands, we can run them using the Postgresql Simple library. Here's some code to do this.

createDBIfMissing :: String -> IO ()
createDBIfMissing connString = do
  connection <- connectPostgreSQL (pack connString)
  putStrLn "Checking/Creating 'quiz' Database"
  let checkQuery = "SELECT datname FROM pg_database WHERE datname = 'quiz';"
  (checkResult :: [Only String]) <- query_ connection checkQuery
  when (null checkResult) $ do
    putStrLn "Not found! Creating 'quiz' database!"
    let createQuery = "CREATE DATABASE quiz;"
    void $ execute_ connection createQuery

When we run checkQuery, it sees if the quiz database exists. If its result is null, then we'll run the additional command to create our database.

Once we have this function, we can write a wrapper that will create the database and then migrate it for our schema. Here's what this wrapper looks like:

migrateDb :: String -> String -> IO ()
migrateDb baseConnString quizConnString = do
  createDBIfMissing baseConnString
  putStrLn "Migrating Database"
  runPG quizConnString (runMigration migrateAll)

runPG :: String -> SqlPersistT (LoggingT IO) a -> IO a
runPG connString action = runStdoutLoggingT $
  withPostgresqlConn (pack connString) $ \backend ->
    runReaderT action backend

Notice migrateDb takes two different connection strings. One is for the base (postgres) database. The other is for our new quiz database. The creation queries run on the first, the migration runs on the second.

But how do we use these functions within our server?

Loading the URI

When we kick off our server, we have to load the database URI for our Postgres database. We'll use the format of {host}:{port}. If you're running it locally, this would just be localhost:5432. But when we deploy the server, we'll use a different URI. So let's write a function to load the host and port (separated by a colon) from an environment variable named DATABASE_URI.

loadDatabaseEnv :: IO (String, String)
loadDatabaseEnv = do
  dbEnv <- lookupEnv "DATABASE_URI"
  if isNothing dbEnv || ':' `notElem` fromJust dbEnv
    then return ("localhost", "5432")
    else return (span (/= ':') (fromJust dbEnv))

Now we need to construct the full Postgres connection string. This has the following general format:

host={host} port={port} dbname={dbname} user={user} password={password}

As a default value, you can often just have the username and password both be postgres (though of course this isn't recommended for a serious database). But let's make a function to substitute in the other values:

mkPostgresUri :: String -> String -> String -> String
mkPostgresUri host port dbname =
  "host='" <> host <> "' port=" <> tail port <> " dbname='" <> dbname <> "' user='postgres' password='postgres'"

Finally, we'll pull our different pieces together, get both URIs, and launch our server. In my example, I'm using a Servant server (more details on that in this article), and this will often require passing the database string as an argument.

server :: String -> Server QuizAPI
server connString = ...

runServer :: IO ()
runServer = do
  (host, port) <- loadDatabaseEnv
  let baseConnString = mkPostgresUri host port "postgres"
  let quizConnString = mkPostgresUri host port "quiz"
  migrateDb baseConnString quizConnString
  putStrLn "Running Server!"
  run 8080 (serve api (server quizConnString))

Having made all these modifications to our server, of course we have to rebuild and redeploy our docker image for that! We can create the new local image with:

docker build -t quiz-server .

Then for more detailed instructions on deploying it, refer to part 2 and part 3 of this series!

When you deploy the server, you'll find it's crashing of course, because we haven't configured the database! So let's get to the real meat of this article…setting up the database on AWS!

Create a Database with RDS

This process is not actually too challenging. The first thing we're going to do is use RDS (Relational Database Service) to set up our database. This is easily done from the AWS console.

Select the RDS service
Hit the orange "Create Database" button
Go through the creation wizard, making sure to select "Postgres" and the "Free Tier" option (assuming you're just making a test app).

Most of the default options are fine, but as I mentioned above I specified postgres for the username and password of the database. I also unchecked the box for "Performance Insights" since this could lead to additional billing charges if you forget to turn it off.

Once you've created your database, you can then click the "databases" link on the sidebar, and select your new database. On that screen, you'll be able to see the "endpoint" and "port" of your database. These are the values you'll need for your environment!

Add Environment Variable

To connect your environment to the database, now you just have to add an environment variable! To do this, you have to access the configuration from the web portal:

Go to the Elastic Beanstalk service
Select "Environments" from the sidebar and then click the environment you have running your server.
Click on the "Configuration" link on the side, and then select the "Edit" button in the "Software" section.
At the very bottom, you'll find the "Environment Properties" section. Fill in DATABASE_URI as the key, and the {host}:{port} combination you got from your database in RDS.
Click "Apply" to make the change!

By adding an environment variable, you are changing the configuration of your server, so it will reboot. Once it relaunches, you should find that it works, and you can persist information from your database!

Conclusion

Hopefully this series has helped you learn how to deploy your Haskell code to AWS! If you'd like to see all this article in video form, you can check out our YouTube video covering these steps!

For more tips on creating a "Real World" application, you can read our series on web skills! You can also download our Haskell Production checklist for some ideas of other libraries and tools you can use to improve your Haskell!