Migrations: default_ not working for timestamp

[tomsmalley]

I have a migration defined with a field

field "created" timestamp notNull (defaultTo_ currentTimestamp_)

But the default value is not added to the column when running the migration. If I add the default value manually, the migration doesn't remove the default value. Seems like it is just being ignored.

So when running an insert using default_ in the timestamp position we get

"null value in column \"created\" violates not-null constraint"

[tathougies]

Which backend is this? How are you running the migration?

[tomsmalley]

I made this repo to demonstrate the issue: https://github.com/tomsmalley/beam-default-time If you run that with a new DB it will perform a migration so the schema looks like

                                          Table "public.model"
    Column     |            Type             |                        Modifiers
---------------+-----------------------------+----------------------------------------------------------
 model_id      | integer                     | not null default nextval('model_model_id_seq'::regclass)
 model_created | timestamp without time zone | not null
Indexes:
    "model_pkey" PRIMARY KEY, btree (model_id)

so there is no default value added for the timestamp column.

[tathougies]

Oh I see. So what's happening here: runMigrationSilenced produces a CheckedDatabaseSettings which is an object that can be used to check that a database schema matches your own, but which can't always faithfully recreate a database. In essence, you throw away the how you constructed the database (the migration) and attempt to recreate it based on the lossy result.

automigrate takes a lossy CheckedDatabaseSettings and attempts to recreate it, without knowledge of migration. This is useful for autogenerating DDL for a schema (or autogenerating haskell code), but it is not what you want here.

To actually run a migration that you define against the database, use bringUpToDate from Database.Beam.Migrate.Simple:

Instead of

  runBeamPostgresDebug putStrLn conn $ do
    let checkedDb = runMigrationSilenced @PgCommandSyntax migration
    autoMigrate migrationBackend checkedDb

do

  runBeamPostgresDebug putStrLn conn $ do
     res <- bringUpToDate migrationBackend (migrationStep "Initial schema" migration)
     case res of
       Nothing -> fail "Something is wrong with your database"
       Just checkedDb -> ... do something with checkedDb ...

[tathougies]

Sorry for the confusion here. beam-migrate has a lot of type conversion functions and many are lossy.

[tomsmalley]

I tried to add autoMigrate migrationBackend checkedDb to run the migration automatically - this fails with

*** Exception: autoMigrate: Not performing automatic migration due to data loss

Adding some print statements to autoMigrate gives *** Exception: ["DROP TABLE \"beam_migration\"","DROP TABLE \"beam_version\""] Am I doing something wrong here?

[tomsmalley]

Aha! It seems that replacing my

db = defaultDbSettings

with

db = unCheckDatabase (runMigrationSilenced migration)

does the trick.

[tomsmalley]

Oh no, I lied, I still get the attempts to drop beam_migration and beam_version. Sorry for the notification spam :)

To get that error I just replaced the failure string in autoMigrate with show (pgRenderSyntaxScript . fromPgCommand . migrationCommand <$> cmds).

[tathougies]

Did you try with bringUpToDate?

[tomsmalley]

Yes, and that does work, but I'm really looking for automatic migrations (for rapid prototyping). I'd like to be able to modify the initial migration and have the database updated accordingly - it seems that bringUpToDate requires specifying changes as a list of migration steps. With bringUpToDate, changing that initial migration results in no migration happening if a field is added, I guess because beam checks the migration info tables and determines that migration step has already been run on the database?

autoMigrate seems like what I need, it appears to work completely except for the defaultTo_ part.

Thanks for your patience with my confusion!