SQL

ROM supports SQL databases via the rom-sql adapter which augments and enhances Relation. rom-sql supports a sql-specific query DSL and association macros that simplify constructing joins and exposes the flexibility & power of the RDMS to relation users.

Direct interactions between the database and ROM happen through the use of the excellent Sequel gem by Jeremy Evans. However, Sequel is an implementation detail of ROM and as such should not be relied upon for functionality. If rom-sql is missing functionality that can be accomplished in Sequel then please leave a report in our issue tracker.

Installing

Depends on: ruby v2.3.0 or greater

To install rom-sql add the following to your Gemfile.

gem 'rom',     '~> 4.0'
gem 'rom-sql', '~> 2.0'

Afterwards either load rom-sql through your bundler setup or manually in your custom script like so:

require 'rom-sql'

Once loaded the SQL Adapter will register itself with ROM and become available for immediate use via the :sql identifier.

Connecting to a Database

Configuring ROM and opening a connection to a database requires three parts.

1. The name of an adapter,
2. a connection string, and
3. any additional options

The adapter name for the SQL Adapter is always :sql which makes things easy; whereas connection strings are database driver specific. Connection strings tell the SQL Adapter which driver to use for the connection along with the port and host address of the database server. Connection strings can also be used to set most of the available options, however it's generally better to keep the connection string short and focused on network routing. Additional options can be provided in a convenient hash structure or by named parameters on the configuration method signature.

An example of this can be seen below:

  opts = {
    username: 'postgres', 
    password: 'postgres', 
    encoding: 'UTF8'
  }

  # Options Hash
  config = ROM::Configuration.new(:sql, 'postgres://localhost:5432/mydbname', opts)

  # Named Parameters
  config = ROM::Configuration.new(:sql, 'postgres://localhost/mydbname', port: 5432)

General Connection Options

Options below are available to all database drivers and can be used to configure the connection between ROM and the database.

PostgreSQL

Requires: pg gem
Recommends: sequel_pg gem in addition to pg gem

The only supported structure for connecting to PostgreSQL databases is the Connection String URI format:

'postgres://[user[:password]@][host][:port][,...][/database][?param1=value1&...]'

For more detailed information on connections strings see the PostgreSQL Connection URI documentation along with the Sequel Opening Databases: Postgres documentation page.

Quick Connect

  opts = {
    username: 'postgres', 
    password: 'postgres', 
    encoding: 'UTF8'
  }
  config = ROM::Configuration.new(:sql, 'postgres://localhost/database_name', opts)

Additional Options

MySQL

Requires: mysql or mysql2 gems
Recommends: using mysql2 gem

MySQL2 driver connection string uses the following pattern:

'mysql2://[user[:password]@][host][:port][/database][?param1=value1&...]'

For more detailed information on connecting to a MySQL database see the MySQL2 project site

Quick Connect

  opts = {
    encoding: 'UTF8'
  }
  config = ROM::Configuration.new(:sql, 'mysql2://localhost/database_name', opts)

Additional Options

SQLite

Requires: sqlite3 gem

SQLite is a self contained in-process database that supports loading databases from files located on the file system or by creating and running the database completely in-memory.

Documentation on the connection string format has been skipped for this database driver as there are no configuration options supported through the uri. The below Quick Connect examples should offer enough information on how to connect to this type of database.

Quick Connect

  opts = {
    readonly: true
  }

  # Absolute path examples
  config = ROM::Configuration.new(:sql, 'sqlite://path/to/db-file.db', opts)
  config = ROM::Configuration.new(:sql, 'sqlite://C:/databases/db-file.db', opts)
  config = ROM::Configuration.new(:sql, 'sqlite:///var/sqlite/db-file.db', opts)

  # Relative path examples
  config = ROM::Configuration.new(:sql, 'sqlite://db-file.db', opts)
  config = ROM::Configuration.new(:sql, 'sqlite://../db-file.db', opts)

  # In-memory database example
  config = ROM::Configuration.new(:sql, 'sqlite::memory', opts)

Additional Options

Oracle

Requires: ruby-oci8 gem

OCI8 driver connection strings use the following pattern:

'oracle://[user[:password]@][host][:port][/database][?param1=value1&...]'

Quick Connect

  opts = {
    autosequence: true
  }
  config = ROM::Configuration.new(:sql, 'oracle://localhost/database_name', opts)

Additional Options

Others

The SQL Adapter supports other drivers and URI connection schemes outside the ones documented here.

• ado
• amalgalite
• cubrid
• db2
• dbi
• do
• fdbsql
• firebird
• ibmdb
• informix
• mysql
• odbc
• openbase
• sqlanywhere
• swift
• tinytds

These drivers have not been documented because their use is fairly uncommon however they should work and documentation for connecting with each of these drivers can be found in Sequel's Opening Databases document.

JRuby

Requires: java, & the jruby runtime

In a JRuby environment, it's best to use the JDBC driver available to you via the Java SDK. Support for databases in JRuby is handled via Sequel's JDBC sub adapters.

A list of supported databases can be found below along with additional requirements:

For the databases DB2, Oracle & SQL Server, the .jar file will need to either be in your CLASSPATH or manually preloaded before making a connection.

Connection strings are similar to their documented patterns above however all connection strings must start with jdbc: for example:

# Postgres Example
jdbc:postgresql://username@localhost/database

# MySQL Example
jdbc:mysql://localhost/database?user=root&password=root

# SQLite Example
jdbc:sqlite::memory

For more information see Sequel's JDBC and Java SE - Database documentation