The Database (SQL)

Using Crails with ODB

If you wish to use a SQL database along with Crails, we warmly recommend you to use the excellent ODB ORM.

Note that our documentation will not teach you how to use ODB. You will need to read and learn the ways of the ODB ORM before using it in a Crails application: have a good look at ODB's manual first.

Apart from integrating ODB within Crails' database system, the crails-odb module comes with a few tools to integrate odb's compiler within our guard-based pipeline, and to take care of database migrations for you. However, if some of our decisions don't match your needs, you can our guard plugin and our migrating task at anytime, and use odb's tools as you see fit instead.

Adding ODB to your project

Compile the crails-odb module

Once you've installed ODB, following the instructions from its documentation, you will need to compile the crails-odb module. If ODB wasn't installed when you first compiled Crails, re-compile it now. It will re-install Crails along with the crails-odb scripts and libraries.

Adding the crails-odb module

In your Crails application, use the crails module odb install command. It will change your CMakeLists.txt to compile your server with libcrails-odb, and add the crails-odb task to your Guardfile.

Managing backends support

You can enable or disable the support for some SQL backends using the ODB_WITH... options that were added to your CMakeLists.txt.

Using the ODB guard plugin

When using the ODB ORM, you are supposed to use the odb compiler on your models. The CrailsODB guard plugin takes care of running the odb compiler on all the hpp files it finds in the folder app/models/, unless they don't contain any occurences of #pragma db.

Database migration

If you are using the CrailsODB guard plugin, then you can also use the odb_migrate task that comes with the crails-odb module. It is used to apply odb's generated schema on a target database.

The task take the key of your database configuration in config/databases.cpp as a parameter, to find out which configuration to load. For instance, if you wanted to prepare the "my_sql_db" database configuration, here's the command you'd launch, from your application directory:

build/tasks/odb_migrate/task my_sql_db

odb's version pragma

If you are not using odb's version pragma, you'll have to drop your database and migrate it again whenever there's a change to your database schema. Otherwise, odb_migrate will figure that your database is up to date, and will display "Nothing to do".

Dropping the schema

If you wish to drop the schema for your database, use the option -d:

build/tasks/odb_migrate/task -d my_sql_db

Using schema versions

The odb_migrate task also supports odb's version feature, as long as you are using a single version for all your models.

If you wish to implement specific behaviors during a migration, you should implement those in the task/odb_migrate/main.cpp file, by sending a lambda as a parameter when database.migrate() is called.

Configuring the odb compiler

The odb compiler provides many options, some of which you can specify as options of the CrailsODB guard plugin. These options are documented here.

odb compiler options available from CrailsODB guard plugin:
- output (defaults to lib/odb)
- include_prefix (defaults to app/models)
- table_prefix
- std (defaults to c++11)
- default_pointer
- hxx_prologue
- ixx_prologue
- cxx_prologue
- schema_prologue
- generate_session
- at_once
- embed_schema

We also provide our own options:

• requires: prepends include directives to hxx_prologue.
• defines: provides -D options for the odbcommand (defines: ['WITH_DEFINE'] would translate to odb -DWITH_DEFILE)
• embed_schema: set to true if you want your databases to be manageable directly from the server, using the ODB::Database::migrate and ODB::Database::drop methods (defaults to false which makes it only available to the odb_migrate task).

Here's a example of configuration for an application using PostgreSQL, and setting odb's default pointer type to a custom class defined in app/my_ptr_type.hpp.

# Guardfile
group :before_compile do
  guard 'crails-odb', requires: ["app/my_ptr_type.hpp"], default_pointer: "my_ptr_type" do
    watch(%r{app/models/.+h(pp|xx)?$})
  end
end

Note that crails/safe_ptr.hpp is always required, so you do not need to specify it if you wish to make Crails' safe_ptr the default pointer type for odb.

Using the CRAILS_DATABASE macro

From your Crails application, you can get a thread_local instance of a configured database using the CRAILS_DATABASE macro (see Databases to know how to configure a database for use with the CRAILS_DATABASE macro).

To use this macro with ODB, you need to include the crails odb database header:

#include <crails/databases/odb.hpp>

The macro will return an instance of ODB::Database. This object allows you then to get a reference to your database, casting it to the proper database type (that is, odb::pgsql::database, odb::sqlite::database, depending on what ODB backend you are currently using).

Here's an example of a route endpoint using ODB to fetch a model:

// app/models/person.hpp
#include <crails/databases/odb.hpp>

#pragma db object pointer(std::shared_ptr)
struct Person
{
  friend class odb::access;

  #pragma db id auto
  unsigned long id;
  std::string first_name;
  std::string last_name;
};

// app/route_handler.cpp
#include "app/models/person.hpp"
#include "app/models/person-odb.hxx" // don't forget to include the ODB generated headers

using namespace std;

shared_ptr<Person> get_person_from_id(unsigned long id)
{
  auto& database = CRAILS_DATABASE(ODB, "my_configuration_name").get_database<odb::pgsql::database>();

  return database.query_one<Person>(odb::pgsql::query<Person>::id == person_id);
}

void route_endpoint(Crails::Params& params, std::function<void (DataTree)> callback)
{
  DataTree response, response_object;
  shared_ptr<Person> person = get_person_from_id(params["id"].as<unsigned long>());

  response_object["person"]["first_name"] = person->first_name;
  response_object["person"]["last_name"] = person->last_name;
  response["body"] = response_object["person"].to_json();
  callback(response);
}

Using Crails Models

The crails-odb module also provides more advanced classes to work with your SQL databases.

Db::Model

The Db::Model class serves as a basis for your future odb models.

#ifndef  PERSON_HPP
# define PERSON_HPP

# include <crails/odb/model.hpp>

# pragma db object pointer(std::shared_ptr)
struct Person : public Db::Model
{
  // You do not need to declare an id attribute. Db::Model will handle the id.
  // Declare your attributes as you would with any odb object
  std::string firstname, lastname;

  // Objects inheriting Db::Model will by default be bound to the "default" database.
  // If you wish to use another database, you may overload the get_database_name method such as this:
  std::string get_database_name() const { return "database_key"; }

  // If you intend to use Db::Model with Db::Connection, you MUST provide a Count subclass such as:
  #pragma db view pointer(std::shared_ptr) object(Person)
  class Count
  {
    #pragma db column("count(" + Person::id + ")")
    size_t value;
  };

  // The soft-delete behavior is disabled by default. Overload `with_soft_delete` to enable it:
  bool with_soft_delete() const { return true; }

  // The following hooks are also provided:
  void before_save() {}
  void after_save() {}
  void before_destroy() {}
  void after_destroy() {}
};
#endif

The Db::Connection object

The Db::Connection object handles a transactions for Db::Model objects.

#include <crails/odb/connection.hpp>
#include "app/models/person.hpp"
#include "app/models/person-odb.hxx"

void route_get_person(Crails::Params& params, std::function<void (DataTree)> callback)
{
  Db::Connection database;
  DataTree response, response_object;
  shared_ptr<Person> person;

  database.find_one(person, params["id"].as<Db::id_type>());
  response_object["person"]["first_name"] = person->first_name;
  response_object["person"]["last_name"] = person->last_name;
  response["body"] = response_object["person"].to_json();
  callback(response);
}

void route_get_person_index(Crails::Params&, std::function<void (DataTree)> callback)
{
  Db::Connection database;
  DataTree response, response_object;
  odb::result<Person> persons;

  database.find<Person>(persons);
  for (const Person& person : persons)
  {
    std::stringstream id;
    std::string string_id;
    id << person.get_id();
    id >> string_id;
    response_object[string_id]["first_name"] = person.first_name;
    response_object[string_id]["last_name"]  = person.last_name;
  }
  response["body"] = response_object.to_json();
  callback(response);
}

void route_add_person(Crails::Params& params, std::function<void (DataTree)> callback)
{
  Db::Connection database;
  DataTree response, response_object;
  Person person;

  person.first_name = params["person"]["first_name"];
  person.last_name  = params["person"]["last_name"];
  database.save(person); // will save the object to the database and set the Person's object id
  database.commit();     // you must always commit your changes using `Db::Connection::commit`
  response_object["person"]["id"] = person.get_id();
  response["body"] = response_object["person"].to_json();
  callback(response);
}

void route_delete_person(Crails::Params& params, std::function<void (DataTree)> callback)
{
  Db::Connection database;
  DataTree response, response_object;
  shared_ptr<Person> person;

  database.find_one(person, params["id"].as<Db::id_type>());
  if (person)
  {
    database.destroy(*person);
    database.commit();
  }
  else
    response["status"] = 404;
  callback(response);
}

Notes

• Note that there can only be once instance of Db::Connection per thread. If such an instance already exists for the current thread, you can access it from anywhere using the global pointer defined as Db::Connection::instance.

• If you use several databases, note that only one transaction can be opened at the same time, and transactions cannot be shared between databases. As a result, if you interact with a second database, without having committed the changes made to the first database, those changes will be rollbacked.