Setup Database Migrations

To do interesting things with a database you need actual tables in that database.

Unfortunately this means we also need to come up with something for this hypothetical app to work with. I am choosing to go with caveman themed examples.

• Initialize your migrations directory.

  This is the first step that uses MyBatis migrations.

  MyBatis is the name of a whole Java database toolset, but the only part relevant to us is its command line tool for running migrations.

  $ mkdir -p migrations
  $ cd migrations
  $ migrate init
  ------------------------------------------------------------------------
  -- MyBatis Migrations - init
  ------------------------------------------------------------------------
  Initializing: .
  Creating: environments
  Creating: scripts
  Creating: drivers
  Creating: README
  Creating: development.properties
  Creating: bootstrap.sql
  Creating: 20241022225617_create_changelog.sql
  Creating: 20241022225618_first_migration.sql
  Done!
  
  ------------------------------------------------------------------------
  -- MyBatis Migrations SUCCESS
  -- Total time: 2s
  -- Finished at: Tue Oct 22 18:56:18 EDT 2024
  -- Final Memory: 5M/504M
  ------------------------------------------------------------------------
  

• Put the postgres driver in the migrations/drivers folder.

  Forgive the jank-ness of this step.

  We need to get the postgres driver jar into the migrations/drivers folder. The easiest way to do this is to just download it manually - the Clojure CLI does not have an equivalent of mvn dependency:copy-dependencies to dump all the jars from resolved classpath into a directory.

  You can find the latest driver on Maven Central.

  Its only around a megabyte, so it's not the worst thing in the world to have committed to git either. Not the best, but not the worst.

• Fill in migrations/environments/development.properties with the info needed to connect to your local database.

  This is going to have duplicate information with .env. C'est la vie.

  ...
  
  ## JDBC connection properties.
  driver=org.postgresql.Driver
  url=jdbc:postgresql:postgres
  username=postgres
  password=postgres
  
  ...

• Validate all is well by running migrate up in the migrations directory.

  Try to remember to get rid of the autogenerated first migration. We will make real tables soon. Also delete migrations/scripts/bootstrap.sql. You only need that if you are starting from an existing database.

  $ migrate up
  ------------------------------------------------------------------------
  -- MyBatis Migrations - up
  ------------------------------------------------------------------------
  ========== Applying: 20241022225617_create_changelog.sql =======================
  -- // Create Changelog
  
  -- Default DDL for changelog table that will keep
  -- a record of the migrations that have been run.
  
  -- You can modify this to suit your database before
  -- running your first migration.
  
  -- Be sure that ID and DESCRIPTION fields exist in
  -- BigInteger and String compatible fields respectively.
  
  CREATE TABLE CHANGELOG (
  ID NUMERIC(20,0) NOT NULL,
  APPLIED_AT VARCHAR(25) NOT NULL,
  DESCRIPTION VARCHAR(255) NOT NULL
  );
  
  ALTER TABLE CHANGELOG
  ADD CONSTRAINT PK_CHANGELOG
  PRIMARY KEY (id);
  
  
  
  
  ------------------------------------------------------------------------
  -- MyBatis Migrations SUCCESS
  -- Total time: 0s
  -- Finished at: Tue Oct 22 19:15:12 EDT 2024
  -- Final Memory: 14M/504M
  ------------------------------------------------------------------------
  

  The CHANGELOG table is what will be used to track what migrations have been applied and which have not. If you object to its name or structure this is the time to change it up.