Colin Woodbury - Beam: Database Initialization

Beam is a database library for Haskell. The guide has a good beginner tutorial, but has us initialize the database tables ourselves by opening sqlite3 on the command line. It goes on to say that beam-migrate can be used to do this automatically, but then doesn't elaborate. This blog post provides a minimal working example of such automatic table generation.

Types

Following the Beam guide, we'd have something like:

  -- | A database table.
  data UserT f = User
    { userEmail     :: C f Text
    , userFirstName :: C f Text
    , userLastName  :: C f Text
    , userPassword  :: C f Text }
    deriving stock (Generic)
    deriving anyclass (Beamable)

  -- | Boilerplate to define the type of our table's Primary Key.
  instance Table UserT where
    data PrimaryKey UserT f = UserId (C f Text)
      deriving stock (Generic)
      deriving anyclass (Beamable)

    primaryKey = UserId . userEmail

  -- | A generic `be` parameter allows this db definition
  -- to be used by any backend.
  data ShoppingCartDb f = ShoppingCartDb
    { shoppingCartUsers :: f (TableEntity UserT) }
    deriving stock (Generic)
    deriving anyclass (Database be)

Database Initialization

beam-migrate provides the function createSchema which uses your Haskell types to create tables. Before we can use it, we need some extra plumbing.

Normally we define our database "pointer" like so:

  database :: DatabaseSettings be ShoppingCartDb
  database = defaultDbSettings

Notice that we've been completely generic about the backend (the be) until now. To write the true initialization code, we'll need to commit to one.

  import Database.Beam.Migrate
  import Database.Beam.Sqlite

  database :: CheckedDatabaseSettings Sqlite ShoppingCartDb
  database = defaultMigratableDbSettings

Now we can write some IO code to run createSchema:

  import Database.Beam.Migrate.Simple (createSchema)
  import Database.Beam.Sqlite.Migrate (migrationBackend)

  -- | `migrationBackend` is a function that is expected to be
  -- provided by each backend. You just drop it in - no need
  -- to construct its type yourself.
  createTables :: IO ()
  createTables = bracket (open "yourdata.db") close $ \conn ->
    runBeamSqlite conn $ createSchema migrationBackend database

This assumes your table doesn't exist, and will create it if it doesn't. But what if it does?

  -- | Create the DB tables if necessary.
  initializeTables :: IO ()
  initializeTables = bracket (open "yourdata.db") close $ \conn ->
    runBeamSqlite conn $ verifySchema migrationBackend database >>= \case
      VerificationFailed _  -> createSchema migrationBackend database
      VerificationSucceeded -> pure ()

We could use this variant instead of createTables. This avoids the table creation if Beam could find it already. Yes, this is what IF NOT EXISTS is for, but for some reason createSchema doesn't do that automatically.