Databases and Ruby on Rails

SQLite is a minimalist database that is excellent for small projects. Although it does not support many fancy features, it is a great choice for projects that will not grow very large. It supports ACID transactions* out of the box. SQLite is a library that is linked into your program; there is no server process to speak of. The library code residing in your application's process space accesses a database file.

SQLite provides no concurrency, as there is no server process to enforce the ACID properties. Therefore, it uses file-level locking: the entire database file is locked at the filesystem level during a transaction. Still, for many small applications, it fits the bill perfectly. It is a good replacement for data that may have been stored in flat files, as it supports most of the SQL-92 standard and would be easy to migrate to a more traditional DBMS as needs grow.

Though Rails grew up in the Linux/Unix world, it has developed great community support for the Windows platform as well. Not only are Microsoft SQL Server database connections supported in Rails, there are also provisions for connecting to SQL Server from a Linux-based systems as well, using the FreeTDS library.†

From a Windows client, the standard approach is to use Ruby-DBI (a Ruby database-independent adapter) with ADO. The configuration looks like this:

  development:
    adapter: sqlserver
    host: server_name
    database: my_db
    username: user
    password: pass

Your configuration may vary, depending on the version of SQL Server and the ADO libraries you have installed. Once the database configuration is in place, the standard ActiveRecord API methods can be used to manipulate data.

Rails supports Oracle versions 8i, 9i, and 10g through the ruby-oci8 library,‡ which supports the OCI8 API. Windows, Linux, and OS X are supported as clients. The connection configuration is fairly standard, using oci as the connection adapter name.  

However, the Oracle client library still maps net service names to connection specifications, so the host parameter provides a service name rather than a physical hostname:

  development:
    adapter: oci
    host: ORCL
    username: user
    password: pass

The ORCL in the preceding configuration corresponds to an entry in the TNSNAMES. ORA file, which will look something like this:

  ORCL =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = srv)(PORT = 1521))
      )
      ...
    )

Alternatively, you can provide the connection specification on one line with the Rails database configuration:

  development:
    adapter: oci
    host: (DESCRIPTION = (ADDRESS_LIST = (...)))
    username: user
    password: pass

The connection setup is the hardest part. Once the database is connected, Rails supports Oracle connections just as it does connections to any other DBMS. Stored procedures and other Oracle-specific syntax are available through the standard methods that expose an SQL interface, such as ActiveRecord::Base.find_by_sql.