Managing the database schema with SearchLight 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.

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
Table of Contents