How do I reference a tiny_tds connection in ruby/sinatra via database.yml (or other)?

Question

I'm trying to figure out how to pull data from a database without the need to place a connection string at the top of each ruby file.

I'm learning the basics of ruby via a little Sinatra app I'm putting together which pulls data from a MSSQL database.

So far I've managed to create various simple erb pages that display data from the MSSQL database by using the following code structure at the top of each file:-

<% client = TinyTds::Client.new(:username => 'name', :password => 'password', :dataserver => 'hostname', :database => 'database') %>

<% data = client.execute("SELECT * from tablename") %>

From the books, guides and online tutorials I've found based on lots of configs to do with PostgreSQL or MySQL databases it seems to me I need to be creating a central file to store my connection data (such as a database.yml file) and then referencing that somewhere/somehow in my app.

Would that be correct, and should I be doing that in my main.rb file so that each of my .erb files do not require the connection string or do I have to still refer to the database in each .erb file also?

I've noted references to creating database config variables such as:-

db_config = YAML.load(File.Open("/path_to_file/database.yml")) [ENV['RAILS_ENV']]

but that clearly seems suited to Rails apps.

Could I do something similar for my sinatra driven 'app'?

Thanks.

Answer 1

This should work:

require "sinatra"
require "sinatra/config_file"

config_file 'path/to/config.yml'

DB = TinyTds::Client.new(
  :username => settings.name, 
  :password => settings.password, 
  :dataserver => settings.hostname, 
  :database => settings.database
) 

get '/' do
  @result = DB.do_stuff
  haml :index
end

What I would suggest though, is that you look for an ORM that supports TinyTDS and use that to set up the database connection and run queries. I use Sequel a lot and I know it supports TinyTDS, but I'm sure others do too.

I'd also suggest not putting things like database settings in a file that gets checked in to source control, as it's sensitive information. I prefer to put this kind of thing into environment variables for production, and use a config file to load the environment variables in development (for convenience). Using the above example:

DB = TinyTds::Client.new(
  :username => ENV["name"],
  :password => ENV["password"],
  :dataserver => ENV["hostname"],
  :database => ENV["database"]
)

Those env variables are loaded into the production server's memory, which makes them a little bit more secure. For development, I load YAML files before starting up Sinatra, adding each value to an ENV var. I'm not suggesting this is a standard way, but it's how I do it.