# Creating AR access to an external, readonly database

**URL:** <https://www.ruby-forum.com/t/creating-ar-access-to-an-external-readonly-database/191587>\
**Category:** Rails\
**Created:** [July 14, 2010, 9:13pm UTC](https://www.ruby-forum.com/t/creating-ar-access-to-an-external-readonly-database/191587 "2010-07-14T21:13:36Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [July 14, 2010, 9:13pm UTC](https://www.ruby-forum.com/t/creating-ar-access-to-an-external-readonly-database/191587/1 "2010-07-14T21:13:36Z")

</div>

Based on excellent suggestions by Rob B.  
[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) in

[Storing constant data in a separate database - Rails - Ruby-Forum](http://www.ruby-forum.com/topic/213083)

…here’s a recipe for setting up access to an external readonly  
database.

Motivation: My application has several very large tables that are  
constant (from the point of my Rails app). In fact, there are external  
scripts that will occasionally update the large tables, but such updates  
are infrequent. I want my Rails app to access but not modify these  
tables.

Overall approach: we create an external database (‘myapp\_external’) and  
a database user (‘readonly\_user’) that is granted only SELET privileges  
on the database. We then create a subclass of ActiveRecord  
(‘ReadonlyDB’) that connects to the database through readonly\_user, so  
any attempts to modify the database will raise an error.

=== Step 1: extend config/database.yml with a new entry specifying a  
database named ‘myapp\_external’ and a username of “readonly\_user”. For  
mysql, it might look like this:

readonly\_db:  
adapter: mysql  
encoding: utf8  
reconnect: false  
database: myapp\_external # see note 1  
pool: 5  
username: readonly\_user  
password: readonly\_pass # see note 2  
socket: /tmp/mysql.sock

[note 1]: You could, in fact specify “myapp\_development” or  
“myapp\_production”, in which case you’d have a readonly window onto your  
development or production database. In our case, we want a separate  
database altogether.

[note 2]: Naturally, you can pick any username and password that you  
want for the readonly user.

=== Step 2: create the external database and readonly user.

% mysql -uroot -pxxxxx  
mysql\> create database myapp\_external;  
Query OK, 1 row affected (0.15 sec)

mysql\> CREATE USER ‘readonly\_user’@‘localhost’ IDENTIFIED BY  
‘readonly\_pass’;  
Query OK, 0 rows affected (0.82 sec)

mysql\> GRANT SELECT ON _._ TO ‘readonly\_user’@‘localhost’ IDENTIFIED BY  
‘readonly\_pass’;  
Query OK, 0 rows affected (0.00 sec)

mysql\> SHOW GRANTS FOR ‘readonly\_user’@‘localhost’;  
±-------------------------------------------------------------------------+  
| Grants for readonly\_user@localhost |  
±-------------------------------------------------------------------------+  
| GRANT SELECT ON _._ TO ‘readonly\_user’@‘localhost’ IDENTIFIED BY PASS… |  
±-------------------------------------------------------------------------+  
1 row in set (0.00 sec)

mysql\> quit

=== Step 3: populate the external database with some data Here, we’re  
just copying a table from myapp\_development into myapp\_external.

% mysqldump -c -uroot -pxxxxx myapp\_development employees \>  
/tmp/empsl.sql  
% mysql -uroot -pxxxx myapp\_external \< /tmp/empls.sql

=== Step 4: Create ReadonlyDB \< ActiveRecord::Base and a test subclass

— file: apps/models/readonly\_db.rb:  
class ReadonlyDB \< ActiveRecord::Base  
establish\_connection :readonly\_db

# Mark ReadonlyDB as abstract so AR won’t try to access any underlying

# tables. But subclasses will be concrete (abstract\_class?() =\> true)

@abstract\_class = true

# This hook is called when ReadonlyDB is subclassed. We use it to set

# the table name from the class name (just like ActiveRecord does)

def self.inherited(c)  
super  
c.set\_table\_name(c.name.tableize)  
end

# I’m not sure this is 100% required, but it allows AR to catch

attempts

# to modify the db earlier than if it went all the way down to the db.

def readonly?  
true  
end

end

— file: apps/models/employee.rb:  
class Employee \< ReadonlyDB  
end

=== Step 5: Test it out!

bash-3.2$ script/console  
Loading development environment (Rails 2.3.8)

> > Employee.count  
> > =\> 2  
> > Employee.first  
> > =\> #\<Employee id: 1, name: “Roger Dodger”, created\_at: “2010-06-17  
> > 00:05:52”…\>  
> > Employee.create(:name =\> “Pete Shelley”) # (…“I don’t exist”)  
> > ActiveRecord::ReadOnlyRecord: ActiveRecord::ReadOnlyRecord  
> > from  
> > /Users/r/Development/Ruby/lib/ruby/gems/1.9.1/gems/activerecord-2.3.8/lib/active\_record/base.rb:2914:in  
> > `create_or_update' ... Employee.first.delete ActiveRecord::StatementInvalid: Mysql::Error: DELETE command denied to user 'readonly_user'@'localhost' for table 'employees': DELETE FROM `employees` WHERE (`id`IN (1)) from /Users/r/Development/Ruby/lib/ruby/gems/1.9.1/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract_adapter.rb:221:in`rescue in log’  
> > …  
> > ReadonlyDB # Show that AR knows that ReadonlyDB is abstract  
> > =\> ReadonlyDB(abstract)  
> > Employee # Show that AR knows the structure of Employee  
> > =\> Employee(id: integer, name: string, created\_at: datetime, …)

=== Summary & Comments:

I’ve only tried this with Rails 2.3.8 and mysql 5.1.36 – YMMV. But the  
approach should be generally applicable: create a database user that has  
readonly privileges, create a new entry in config/database.yml to  
reference that user, create a subclass of AR that connects through that  
entry. Voila: a readonly external database.

As hinted at in Step 1 Note 1 (above), your entry in config/database.yml  
doesn’t have to reference an external database: it can reference one of  
your existing databases (e.g. your development or production database).  
Since it connects via a readonly user, anything subclassed under  
ReadonlyDB won’t be allowed to modify the database. This may be useful  
for some applications.

Send comments, questions, improvements. I’m still learning myself.

- ff