Getting Started#

In this article we will set up Rumba RDBM 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 Rumba RDBM generates the SQL for you.
  • Imperative schema definition - You write the SQL-scripts on your own each time you put the database schema changes, Rumba 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.

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

Installation#

Installing the Debian package

Download and install the public signing key:

wget -O - https://www.dbinvent.com/dist/DBinventLLC.gpg.key | sudo apt-key add -

Add our repository to your sources.list:

sudo sh -c 'echo "deb http://apt.dbinvent.com/$(lsb_release -cs) $(lsb_release -cs) non-free" > /etc/apt/sources.list.d/dbinvent.list'

Install the Rumba RDBM package:

sudo apt update && sudo apt install rdbm

Installing from tar.gz package

curl https://www.dbinvent.com/dist/rdbm-unix-latest.tar.gz -o rdbm-latest.tar.gz && tar -zxvf./rdbm-latest.tar.gz

Installing from zip package

curl https://www.dbinvent.com/dist/rdbm-unix-latest.zip -o rdbm-latest.zip && unzip ./rdbm-latest.zip

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

sudo apt install unzip
sudo dnf install unzip
sudo zypper install unzip
sudo pacman -S unzip

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.

In the previous step, you have downloaded an archive that contains a limited trial license file evaluation.license, that allows the following connections without registration:

  • Host: localhost, IP-address
  • Database: $USER, postgres

You also can Register and create a regular trial license for any connection you want.

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.cfg
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:

sudo su - postgres -c "psql -c \"ALTER ROLE postgres WITH PASSWORD 'new_passwd';\""

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

./rdbm -c db.cfg --dry_run=y license

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:

S1__users.yaml
---
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:

./rdbm -c db.cfg --license_file=evaluation.license migrate

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.

If the database does not exist, the database migration command will look like:

./rdbm -c db.cfg --license_file=evaluation.license --createdb=yes migrate

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:

S1__users.yaml
---
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:

V2__posts.sql
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:

P1__prepare.sql
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:

S1__users.yaml
---
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:

./rdbm -c db.cfg --license_file=evaluation.license migrate

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

sudo su - postgres -c "psql -c \"INSERT INTO users (first_name, last_name, email, password) VALUES ('John', 'Doe', 'JohnDoe@example.com', '-');\""

Make sure the database has only emails in lower case:

sudo su - postgres -c "psql -c \"SELECT * FROM users;\""
 id | first_name | last_name |        email        | password | valid
----+------------+-----------+---------------------+----------+-------
  1 | John       | Doe       | johndoe@example.com | -        | f
(1 row)