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.

Note

The SQL Adapter documentation is still being created & revised. If something isn't documented or requires more information, please click the "Provide Feedback" buttons at the bottom of the pages and let us know. In the mean time you may need to look towards Sequels Databases & Datasets documentation for further guidance.

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.

Note

Each database type requires a separate driver gem to also be installed. Be sure to checkout the documentation of your preferred database for more information.

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

Option Value Type Description
:database String Name of the database to open after successful connection.
:user String Name of the user account to use when logging in.
:password String Password that matches the user account.
:adapter Symbol Sets the database driver which should be used when making a connection. This option is only to be used in situations where a connection string is NOT provided to the ROM Configuration instance.
Available Options:
  • :postgres
  • :sqlite
  • :oracle
  • :mysql
:host String Internet location of the database server. This option is required when the adapter option is being used.
:port Integer Port number used during connection.

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:

'postgresql://[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
Option Description Value Type Default Value
:search_path Sets the schema search path. String, Array<String> ['$user', 'public']
:encoding Sets the client_encoding option in Postgres. Available options are 'auto' or any encoding in the Postgres supported Charset Table. The most common option being 'UTF8' String
:connect_timeout Set the number of seconds to wait for a connection Integer 20
:driver_options Symbolized keys hash of options that are passed to the pg gem Hash
:sslmode Determines the priority or whether or not an SSL TCP/IP connection is to be made.
Available Options:
  • 'disable' - Only try non-SSL Connections
  • 'allow' - first try a non-SSL connection; if that fails, try an SSL connection
  • 'prefer' - first try an SSL connection; if that fails, try a non SSL connection
  • 'require' - only try an SSL connection. If a root CA file is present, verify the certificate in the same way as if verify-ca was specified
  • 'verify-ca' - only try an SSL connection, and verify that the server certificate is issued by a trusted certificate authority (CA)
  • 'verify-full' - only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the requested server host name matches that in the certificate
String 'disable'
:sslrootcert Path to the root SSL certificate to use. String
:use_iso_data_format When enabled, Sequel will ensure the ISO 8601 date format is used. Boolean true
:convert_infinite_timestamps Determines if infinite timestamps/dates will be converted. By default, an error is raised and no conversion is done.
Available Options:
  • :nil - Converts the timestamp to nil
  • :string - Leaves the timestamp as a string
  • :float - Converts to an infinite float
Symbol true

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
Option Description Value Type Default Value
:encoding Specify the encoding/character set to use for the connection. Available encodings can be found in the MySQL Charset Table. The most common option being 'UTF8' String 'UTF8'
:write_timeout Set the timeout in seconds when writing to the database. Integer
:read_timeout Set the timeout in seconds when reading query results. Integer
:connect_timeout Set the timeout in seconds before a connection attempt is abandoned. Integer
Boolean When enabled the server will refuse connection if the account password is stored in old pre-MySQL 4.1 format. :secure_auth true
:sql_mode Sets the sql_mode(s) for a given connection. eg: [:no_zero_date, :pipes_as_concat]

Available sql_modes can be found in MySQL Server SQL Modes documentation.

Array<String, Symbol>, String, Symbol
:flags Flags added to an array are added to the Default flags, while flags with a - (minus) prefix are removed from the default flags. For more information see Flag Option Parsing.
Available Options:
  • 'REMEMBER_OPTIONS'
  • 'LONG_PASSWORD'
  • 'LONG_FLAG'
  • 'TRANSACTIONS'
  • 'PROTOCOL_41'
  • 'SECURE_CONNECTION'
  • 'MULTI_STATEMENTS'
String, Array<String>
:socket Used to specify a Unix socket file to connect to instead of a TCP host & port. String
:sslmode Determines the priority or whether or not a SSL TCP/IP connection is to be made.
Available Options:
  • :disabled - Establish an unencrypted connection
  • :preferred - First try a non-SSL connection; if that fails, try an SSL connection
  • :required - Establish a secure connection if the server supports secure connections
  • :verify_ca - Only establish an SSL connection and verify the servers TLS certificate against the configured Certificate Authority (CA) certificates.
  • :verify_identity - Like :verify_ca, but additionally verify the server certificate matches the host to which the connection is attempted.
Symbol :disabled
:sslkey Path to the client key. eg: 'path/to/client-key.pem' String
:sslcert Path to the client certificate. eg: 'path/to/client-cert.pem' String
:sslca Path to the CA certificate. eg: '/path/to/ca-cert.pem' String
:sslcapath Path to the CA certificates. eg. 'path/to/cacerts' String
:sslcipher Supported ciphers can be found in the MySQL Encrypted Connection Protocols document. eg: 'DHE-RSA-AES256-SHA' String
:sslverify When set to true, the server is required to present a valid certificate. Boolean false

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.

Note

By default a SQLite in-memory database is restricted to a single connection. This is a restriction imposed by SQLite itself and for this reason, Sequel sets the maximum number of connections in the connection pool to 1. Overriding the connection pool limit will result in weird behavior as new connections will be to separate memory databases.

For more information see Sequels SQLite documentation or for URI file formats see URI Filenames in SQLite

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
Option Description Value Type Default Value
:database Path to the SQLite database file. String
:host This option is ignored. String
:port This option is ignored. String
:readonly Opens the database in read-only mode Boolean false
:timeout Busy timeout in milliseconds Integer 5000

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
Option Description Value Type Default Value
:autosequence When true Sequel's conventions will be used to guess the sequence to use for the dataset. Boolean false
:prefetch_rows Number of rows to prefetch. Larger numbers can be specified which may improve performance when retrieving large numbers of rows. Integer 100
:privilege Oracle privilege level.
Available Options:
  • :SYSDBA
  • :SYSOPER
  • :SYSASM
  • :SYSBACKUP
  • :SYSDG
  • :SYSKM
String

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 Sequels 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 Sequels JDBC sub adapters.

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

Database Required Gem
Derby jdbc-derby
H2 jdbc-h2
HSQLDB jdbc-hsqldb
JTDS jdbc-jtds
MySQL jdbc-mysql
PostgreSQL jdbc-postgres
SQLite jdbc-sqlite3

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 Sequels JDBC and Java SE - Database documentation