Adding a new non-nullable column

We’ll show how, to add a new non-nullable column:

first, we create the initial version of the table without the ‘new’ column
we then add the column, but only as nullable
we make sure all values are present on the nullable column
we then migrate the column to be non-nullable

You’ll need a Haskell project named using-migrations for this tutorial. The setup is identical to the setup in Getting Started aside from the package name, so we’ll avoid explaining it again here. Instead we’ll get straight on creating our src/Main.hs file.

src/Main.hs : haskell

module Main
  ( main
  ) where

import qualified Orville.PostgreSQL as O
import qualified Orville.PostgreSQL.AutoMigration as AutoMigration
import qualified Orville.PostgreSQL.Raw.RawSql as RawSql

import           Control.Monad.IO.Class (MonadIO(liftIO))
import qualified Data.Int as Int

data Foo1 = Foo1
  { foo1Id :: Int.Int32
  }
  deriving Show

data Foo2 = Foo2
  { foo2Id :: Int.Int32
  , foo2Age :: Maybe Int.Int32
  }
  deriving Show

data Foo3 = Foo3
  { foo3Id :: Int.Int32
  , foo3Age :: Int.Int32
  }
  deriving Show

fooIdField :: O.FieldDefinition O.NotNull Int.Int32
fooIdField =
  O.integerField "id"

fooAgeField :: O.FieldDefinition O.NotNull Int.Int32
fooAgeField =
  O.integerField "age"

foo1Marshaller :: O.SqlMarshaller Foo1 Foo1
foo1Marshaller =
  Foo1
    <$> O.marshallField foo1Id fooIdField

foo2Marshaller :: O.SqlMarshaller Foo2 Foo2
foo2Marshaller =
  Foo2
    <$> O.marshallField foo2Id fooIdField
    <*> O.marshallField foo2Age (O.nullableField fooAgeField)

foo3Marshaller :: O.SqlMarshaller Foo3 Foo3
foo3Marshaller =
  Foo3
    <$> O.marshallField foo3Id fooIdField
    <*> O.marshallField foo3Age fooAgeField

Note how the following tables have the same SQL table names. Imagine that table1 is the initial version of the table, which then is changed to table2, and so on. In practice, they can keep their Haskell names, since they won’t need to co-exist, like they do in this document.

Orville’s AutoMigration tool sees that the difference between the tables is, that the second table has an additional column, and it will generate and execute the DDL to add the column. It needs to be nullable, because the database won’t have any values for it when it is added.

src/Main.hs : haskell

table1 :: O.TableDefinition (O.HasKey Int.Int32) Foo1 Foo1
table1 =
  O.mkTableDefinition "migration_demo1" (O.primaryKey fooIdField) foo1Marshaller

table2 :: O.TableDefinition (O.HasKey Int.Int32) Foo2 Foo2
table2 =
  O.mkTableDefinition "migration_demo1" (O.primaryKey fooIdField) foo2Marshaller

table3 :: O.TableDefinition (O.HasKey Int.Int32) Foo3 Foo3
table3 =
  O.mkTableDefinition "migration_demo1" (O.primaryKey fooIdField) foo3Marshaller

Now let’s add a main function invokes Orville’s auto-migration tool to demonstrate making changes to the table.

src/Main.hs : haskell

main :: IO ()
main = do
  pool <-
    O.createConnectionPool
        O.ConnectionOptions
          { O.connectionString = "host=localhost user=postgres password=postgres"
          , O.connectionNoticeReporting = O.DisableNoticeReporting
          , O.connectionPoolStripes = O.OneStripePerCapability
          , O.connectionPoolLingerTime = 10
          , O.connectionPoolMaxConnections = O.MaxConnectionsPerStripe 1
          }

  O.runOrville pool $ do
    O.executeVoid O.DDLQuery (RawSql.fromString "DROP TABLE IF EXISTS migration_demo1")
    AutoMigration.autoMigrateSchema AutoMigration.defaultOptions [ AutoMigration.SchemaTable table1 ]
    _ <- O.insertEntity table1 Foo1 { foo1Id = 0 }
    AutoMigration.autoMigrateSchema AutoMigration.defaultOptions [ AutoMigration.SchemaTable table2 ]
    _ <- O.updateEntity table2 0 Foo2 { foo2Id = 0, foo2Age = Just 91 }
    AutoMigration.autoMigrateSchema AutoMigration.defaultOptions [ AutoMigration.SchemaTable table3 ]
    liftIO . print =<< O.findEntity table3 0

Dropping a column

Orville won’t automatically drop a column in the SQL database that isn’t in the SqlMarshaller. The column will just be ignored.

We have to tell Orville explicitly about the columns that are safe to drop. This is done using the dropColumns combinator. Add this to the bottom of the main function:

src/Main.hs : haskell

    AutoMigration.autoMigrateSchema AutoMigration.defaultOptions [ AutoMigration.SchemaTable $ O.dropColumns ["age"] table1 ]
    liftIO . print =<< O.findEntity table1 0

Conclusion

This concludes this tutorial. You can build an execute this as usual:

shell

stack build
stack exec using-migrations

And you should see the following output:

output : plaintext

Just (Foo3 {foo3Id = 0, foo3Age = 91})
Just (Foo1 {foo1Id = 0})

Using Migrations

Adding a new non-nullable column

Dropping a column

Conclusion