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.

Take a look at this: [Sinatra Config File](http://www.sinatrarb.com/contrib/config_file.html). It may be that you just want to try this yourself for the learning, just say and then I'm sure you'll get some more detailed answers below. BTW, `YAML` also has a `load_file` method, I'm not sure why all those Rails apps keep using `load` with `File.open`. — ian, May 20 '13 at 14:15
Thanks Iain, I've had a read through that link you provided and that is great to know. I'm not sure how it helps me though as I can't yet make the leap between setting up a config file and then pulling values from it to create the database connection... — lad33, May 21 '13 at 07:23
...I think what I'm trying to say is, ok, I can put my database connection settings into a config.yml file and then reference that in my main.rb file. How do I then create the client = TinyTds.Client.new(......) , and does that go in the main.rb app file too? — lad33, May 21 '13 at 07:27
How are you using those two lines in your erb files? I get an unitialized constant ActionView::CompiledTemplates::TinyTds error. — Alexey, Jun 13 '13 at 20:59

score 1 · Accepted Answer · answered May 21 '13 at 20:30

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.

Thanks Iain, this is excellent advice and using environment variables was the kind of thing I was hoping to be able to do! — lad33, May 22 '13 at 17:18
I've been reading about Sequel today too, looks great and is a fantastic suggestion, thanks again. — lad33, May 23 '13 at 18:53

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

1 Answers1