Managing the database schema with migrations

Database migrations provide a way to reliably, consistently and repeatedly apply (and undo) changes to the structure of your database (known as "schema transformations"). They are specialised scripts for adding, removing and altering DB tables – these scripts are placed under version control and are managed by a dedicated system which knows which scripts have been run and which not, and is able to run them in the correct order.

SearchLight needs its own DB table to keep track of the state of the migrations so let's set it up:

julia> SearchLight.Migrations.init()
[ Info: Created table schema_migrations

This command sets up our database with the needed table in order to manage migrations.

PRO TIP

You can use the SearchLight API to execute random queries against the database backend. For example we can confirm that the table is really there:

julia> SearchLight.query("SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%'")
┌ Info: SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%'
└

1×1 DataFrames.DataFrame
│ Row │ name              │
│     │ String⍰           │
├─────┼───────────────────┤
│ 1   │ schema_migrations │

The result is a DataFrame object.

Creating our Book model

SearchLight, just like Genie, uses the convention-over-configuration design pattern. It prefers for things to be setup in a certain way and provides sensible defaults, versus having to define everything in extensive configuration files. And fortunately, we don't even have to remember what these conventions are, as SearchLight also comes with an extensive set of generators.

Lets ask SearchLight to create a new model:

julia> SearchLight.Generator.newresource("Book")

[ Info: New model created at /Users/adrian/Dropbox/Projects/MyGenieApp/app/resources/books/Books.jl
[ Info: New table migration created at /Users/adrian/Dropbox/Projects/MyGenieApp/db/migrations/2020020909574048_create_table_books.jl
[ Info: New validator created at /Users/adrian/Dropbox/Projects/MyGenieApp/app/resources/books/BooksValidator.jl
[ Info: New unit test created at /Users/adrian/Dropbox/Projects/MyGenieApp/test/books_test.jl

SearchLight has created the Books.jl model, the *_create_table_books.jl migration file, the BooksValidator.jl model validator and the books_test.jl test file.

HEADS UP

The first part of the migration file will be different for you!

The *_create_table_books.jl file will be named differently as the first part of the name is the file creation timestamp. This timestamp part guarantees that names are unique and file name clashes are avoided (for example when working as a team a creating similar migration files).

Writing the table migration

Lets begin by writing the migration to create our books table. SearchLight provides a powerful DSL for writing migrations. Each migration file needs to define two methods: up which applies the changes – and down which undoes the effects of the up method. So in our up method we want to create the table – and in down we want to drop the table.

The naming convention for tables in SearchLight is that the table name should be pluralized (books) – because a table contains multiple books (and each row represents an object, a single book). But don't worry, the migration file should already be pre-populated with the correct table name.

Edit the db/migrations/*_create_table_books.jl file and make it look like this:

module CreateTableBooks

import SearchLight.Migrations: create_table, column, primary_key, add_index, drop_table

function up()
  create_table(:books) do
    [
      primary_key()
      column(:title, :string, limit = 100)
      column(:author, :string, limit = 100)
    ]
  end

  add_index(:books, :title)
  add_index(:books, :author)
end

function down()
  drop_table(:books)
end

end

The code is pretty easy to follow: in the up function we call create_table and pass an array of columns: a primary key, a title column and an author column (both strings have a max length of 100). We also add two indices (one on the title and the other on the author columns). As for the down method, it invokes the drop_table function to remove the table.

Running the migration

We can see what SearchLight knows about our migrations with the SearchLight.Migrations.status command:

julia> SearchLight.Migrations.status()
|   | Module name & status                   |
|   | File name                              |
|---|----------------------------------------|
|   |                 CreateTableBooks: DOWN |
| 1 | 2020020909574048_create_table_books.jl |

So our migration is in the down state – meaning that its up method has not been run. We can easily fix this:

julia> SearchLight.Migrations.last_up()
[ Info: Executed migration CreateTableBooks up

If we recheck the status, the migration is up:

julia> SearchLight.Migrations.status()
|   | Module name & status                   |
|   | File name                              |
|---|----------------------------------------|
|   |                   CreateTableBooks: UP |
| 1 | 2020020909574048_create_table_books.jl |

Our table is ready!

Defining the model

Now it's time to edit our model file at app/resources/books/Books.jl. Another convention in SearchLight is that we're using the pluralized name (Books) for the module – because it's for managing multiple books. And within it we define a type (a mutable struct), called Book – which represents an item (a single book) which maps to a row in the underlying database.

Edit the Books.jl file to make it look like this:

# Books.jl
module Books

import SearchLight: AbstractModel, DbId, save!
import Base: @kwdef

export Book

@kwdef mutable struct Book <: AbstractModel
  id::DbId = DbId()
  title::String = ""
  author::String = ""
end

end

We defined a mutable struct which matches our previous Book type by using the @kwdef macro, in order to also define a keyword constructor, as SearchLight needs it.

Using our model

To make things more interesting, we should import our current books into the database. Add this function to the Books.jl module, under the Book() constructor definition (just above the module's closing end):

# Books.jl
function seed()
  BillGatesBooks = [
    ("The Best We Could Do", "Thi Bui"),
    ("Evicted: Poverty and Profit in the American City", "Matthew Desmond"),
    ("Believe Me: A Memoir of Love, Death, and Jazz Chickens", "Eddie Izzard"),
    ("The Sympathizer!", "Viet Thanh Nguyen"),
    ("Energy and Civilization, A History", "Vaclav Smil")
  ]

  for b in BillGatesBooks
    Book(title = b[1], author = b[2]) |> save!
  end
end