Write the first migration

Now that we've got our database and ORM installed, we should do something with them. Maybe store some data, in some kind of structured way?

Diesel makes changes to the database structure as a series of "migrations". For each migration, we need to provide the SQL commands required both to implement whatever database change we're making, and to undo that change.

So whenever we write a migration that creates a new table, we also need to provide the SQL to remove that table if our migration needs to be undone.

Generate the skeleton

We'll use diesel's cli to generate our first migration:

$ diesel migration generate task

This will create a subdirectory under ./migrations/ which is named with a date/time stamp and the suffix "_task" (which you gave the generate command). In that timestamped subdirectory are two stub files. One is the "up" migration where we will create our new table, and the other is the "down" migration where we will remove the new table.

Write the SQL

Make up.sql look like this:

CREATE TABLE task (
    id INTEGER NOT NULL,
    title TEXT NOT NULL,
    PRIMARY KEY (id)
);

This will create a very simple table for storing our task list.

Test the migration

Let's test our migration:

$ diesel migration run
Running migration 2019-08-19-023055_task
$ echo .dump | sqlite3 testdb.sqlite3

That last command should show the table we created. Great!

The other part of testing a migration is ensuring that rollbacks work. This is done via the redo command, which reverts the latest migration and then re-runs it.

$ diesel migration redo
Rolling back migration 2019-08-19-023055_task
Running migration 2019-08-19-023055_task
Executing migration script
/projects/mytodo/migrations/2019-08-19-023055_task/up.sql
Failed with: table task already exists

Oh no! Our redo failed because we forgot to modify down.sql. Let's fix that -- to revert the change we made in the up migration, make down.sql look like this:

DROP TABLE task;

Now try running diesel migration redo again. This time it should be successful.

At this point, I don't know about you, but I'm itching to write some code. Good news: the next chapter has a bunch of code.