Getting Started#

In this article we will set up Schema Guard and run the database migrations for a new project. We'll try two approaches to database schema definition, and will figure out when each of them suits the best for a particular project stage.

• Declarative schema definition - You put the changes into a single file that describes your database, then Schema Guard generates the SQL for you.
• Imperative schema definition - You write the SQL-scripts on your own each time you put the database schema changes, Schema Guard tracks the script execution and versioning between the migrations.

This Getting Started Guide assumes that you run some Linux version on your workstation, and you have PostgreSQL installed. If you don't have PostgreSQL installed and would like an extra guidance doing so, see the Miscellaneous section.

You can see examples of Schema Guard usage from our tests we have posted on GitHub.

Before we perform the first database migration, please download Schema Guard's binary.

Installation#

Installing the Debian package

Download and install the public signing key:

Add our repository to your sources.list:

Install the Schema Guard package:

Installing from tar.gz package

Installing from zip package

If you have no unzip installed, here are sample commands for different Linux package management systems:

Configuring the Database Connection#

RDBM accepts configuration parameters from the command-line, configuration file, and environment variables. You can read more in the "Configuration" section.

To keep the command line parameter list short and avoid typing the same thing again then again, let's put database connection params into the configuration file. Create the following file:

db_host=localhost
db_name=postgres
db_port=5432
db_user=postgres
db_password=passwd

If you don't remember the postgres user password you can change it with the following command:

To check that your database connection settings comply with your license, you can run rdbm with --dry_run key:

Declarative schema definition#

Let's start our project by creating the table for a user's authentication. Since our project is at the very beginning, the declarative schema definition can dramatically save time when we decide to expand the project with new features and maybe even redesign some parts.

Create the version 1, S-type file:

---
database:
  - schema:
    tables:
      - table:
          tableName: users
          columns:
            - column:
                name: id
                type: serial
                constraint:
                  primaryKey: true
                  nullable: false
            - column:
                name: first_name
                type: varchar(100)
                constraint:
                  nullable: false
            - column:
                name: last_name
                type: varchar(100)
                constraint:
                  nullable: false
            - column:
                name: email
                type: varchar(100)
                constraint:
                  nullable: false
            - column:
                name: password
                type: varchar(40)
                constraint:
                  nullable: false

RDBM splits the scripts and schema definition files by stages of particular database migration. "S" at the beginning of the file name means that this file is for Schema Generation phase. You can find more details in Script Versions and Types section.

Let us run the first migration:

If you have an unlimited trial, or you have a paid license, you may have changed the postgres database to a not existing one in your configuration file. In this case you need to add --createdb=yes flag at the first run.

Now let's assume that while implementing the user authentication, you decide to expand the table with valid field to keep the user's email verification result. Just include the new field with a proper data type into your schema definition file. Changed S1__users.yaml file will have the following content:

---
database:
  - schema:
    tables:
      - table:
          tableName: users
          columns:
            - column:
                name: id
                type: serial
                constraint:
                  primaryKey: true
                  nullable: false
            - column:
                name: first_name
                type: varchar(100)
                constraint:
                  nullable: false
            - column:
                name: last_name
                type: varchar(100)
                constraint:
                  nullable: false
            - column:
                name: email
                type: varchar(100)
                constraint:
                  nullable: false
            - column:
                name: password
                type: varchar(40)
                constraint:
                  nullable: false
            - column:
                name: valid
                type: boolean
                defaultValue: "false"
                constraint:
                  nullable: false

You can create new tables in the same way. All the database schema can be described in a single file. Summarizing the above, S-type files are mutable schema definition files, rdbm will apply the required changes on the next database migration run.

Plain-SQL#

Except the S-type mentioned above, all other file types are plain-SQL scripts. You can read about the script execution order, script types and versioning in Script Versions and Types section.

You can mix the declarative schema definition with plain-SQL scripts, or use just plain-SQL approach.

If you stay on the plain-SQL, write an SQL script that creates the same table structure as S-type file in the example above and start the file name with "V" instead of "S". V-type may be considered as "Versioned Immutable SQL scripts".

For example, we can make the following scrip to expand our project with a table for storing user posts:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users,
    title VARCHAR NOT NULL,
    body TEXT NOT NULL,
    published BOOLEAN NOT NULL DEFAULT 'f'
);

Mixing up SQL with YAML#

Let's mix the existing declarative schema definition from the S1__users.yaml file with a plain-SQL script that creates a trigger function for converting user's email fields to lower case.

Create the following function in the P1__prepare.sql file:

CREATE OR REPLACE FUNCTION make_lower_email()
    returns trigger
as
$$
begin
    new.email := lower(new.email);
return new;
end;
$$ language plpgsql;

In the S1__users.yaml file from the previous part, add the following trigger in table section:

triggers:
  - trigger:
      name: ensure_lower_email_trg
      event: before update or insert
      when: for each row
      proc: make_lower_email()

As a result, S1__users.yaml file has the following content:

---
database:
  - schema:
    tables:
      - table:
          tableName: users
          columns:
            - column:
                name: id
                type: serial
                constraint:
                  primaryKey: true
                  nullable: false
            - column:
                name: first_name
                type: varchar(100)
                constraint:
                  nullable: false
            - column:
                name: last_name
                type: varchar(100)
                constraint:
                  nullable: false
            - column:
                name: email
                type: varchar(100)
                constraint:
                  nullable: false
            - column:
                name: password
                type: varchar(40)
                constraint:
                  nullable: false
            - column:
                name: valid
                type: boolean
                defaultValue: "false"
                constraint:
                  nullable: false
          triggers:
            - trigger:
                name: ensure_lower_email_trg
                event: before update or insert
                when: for each row
                proc: make_lower_email()

Run the migration:

To prove that the created trigger is working, insert new user with capital letters in the email field:

Make sure the database has only emails in lower case:

 id | first_name | last_name |        email        | password | valid
----+------------+-----------+---------------------+----------+-------
  1 | John       | Doe       | johndoe@example.com | -        | f
(1 row)