CODEWITHSUNDEEP
sqlruby-on-railsrubydatabaseorm
Reed G. Law
Reed G. Law

Reputation: 3945

Use SQL files in Ruby

I'm a fan of Yesql-style templates that consist of raw SQL with placeholders for parameters.

Here is an example from the docs:

SELECT *
FROM users
WHERE country_code = ?

This snipped is stored in a file and pulled into the application like so:

(defquery users-by-country "some/where/users_by_country.sql")
(users-by-country db-spec "GB")

Is there any gem with the same functionality in Ruby? Or is there a way to at least load raw SQL from a file and execute it, storing the result in an array or json?

Upvotes: 2

Views: 830

Answers (1)

Jared Beck
Jared Beck

Reputation: 17528

Absolutely, and this (a separate SQL file) is a good technique for longer queries. Any of the database adapter gems can do this. In pg, which I'm most familiar with,

  1. Read your query from a SQL file, e.g. File.read
  2. Open a connection, e.g. PG::Connection.open
  3. Call exec_params

Example from pg documentation:

require 'pg'
conn = PG::Connection.open(:dbname => 'test')
res = conn.exec_params('SELECT $1 AS a, $2 AS b, $3 AS c', [1, 2, nil])
# Equivalent to:
#  res  = conn.exec('SELECT 1 AS a, 2 AS b, NULL AS c')

http://deveiate.org/code/pg/PG/Connection.html

As you can see, the placeholders here are sequentially numbered, an improvement on simple question marks, I think.

Upvotes: 2

Related Questions