Databases and SQL

In the section “Models, Data Access and Querying” we tell you how to define models and controllers. Such controllers can use either the file system or a SQL database as a back-end. This section specficially tells you how to work with SQL.

Configuring a database

In your stallion.toml settings file, add a section:

[database]
url = "jdbc:mysql://localhost:3306/my_database?allowMultiQueries=true&autoReConnect=true"
username = "root"
password = ""

The URL should be a valid JDBC database URL.

If you don’t want to store your password in plaintext, you can configure encrypted secrets.

If you have a different database configuration for qa or prod, be sure to put that configuration in the stallion.qa.toml file and stallion.prod.toml file.

Creating default tables

Once you configure a database, all default services, such as the User management, job status management, async tasks, etc, will expect to use a database. Thus you will need to create database tables. You can generate the default database tables using:

>bin/stallion sql-migrate

SQL Migrations

You can put your own SQL migrations in a folder called sql. Migrations should be in the form: <numeric version>-<custom-name>.<db engine>.sql or <numeric version>-<custom-name>.<db engine>.js For example: 00010-create-events-table.mysql.sql or 00030-add-column.postgres.sql.

If your file ends with .sql just put in raw SQL. If your file ends in .js, it is an executable javascript file. You have access to all Java methods, plus you have a global object called “db” which is an instance of io.stallion.dataAccess.db.DB.

You can execute SQL like so:

if (!db.hasColumn('user_profiles', loggedInWith')) {
    db.execute('''
    ALTER TABLE `user_profiles` 
        ADD COLUMN `loggedInWith`  varchar(40)  NOT NULL  DEFAULT 'email' ;
    ''');
}

For your convenience, these javascript migrations support multi-line strings with three single quotes.

You can then run these migrations via the command bin/stallion sql-migrate -env=local. Use the ‘env’ to run against the correct environment.

You can also generate migrations based on your registered models. Stallion will automatically generate CREATE tables and ALTER table statements. Use the command bin/stallion sql-generate.

Querying the database directly

You can use the singleton helper class io.stallion.dataAccess.db.DB to access your database.

It has various helper methods:

// Find objects for the given model that matched the SQL WHERE clause, use the 45-second cache
List<Book> books = DB.instance().cachedWhere(Book.class, "author=? AND NOT title=?", "Mark Twain", "Tom Sawyer");