What's the Best Way to Talk to a Database While Using Sinatra

What's the best way to talk to a database while using Sinatra?

If you like ActiveRecord, use that. Or something else. Datamapper, for instance. For AR with SQLite, this works:

require 'rubygems' # may not be needed, depending on platform
require 'sinatra'
require 'active_record'

class Article < ActiveRecord::Base
end

get '/' do
  Article.establish_connection(
    :adapter => "sqlite3",
    :database => "hw.db"
  )
  Article.first.title
end

how do I read from a Database in ruby sinatra using active record

To read the data from the users table, you'd simply do:

class User < ActiveRecord::Base
end

get '/' do
  @users = User.all
  puts "Grabbed #{@users.size} user(s) from database"
end

Use existing database and tables with Sinatra and Datamapper?

Sinatra has no provision for talking to databases. You can use the mysql2 gem to talk to the database, but I'd highly recommend looking at Sequel, which is a very flexible and powerful ORM, which makes it very easy to talk to legacy databases.

Look through the README, and Cheat Sheet and you'll get a good idea of how easy it is to connect to an existing database, without needing to worry about modifying it.

This is untested of course, but it's the basic steps needed to make a connection and retrieve a record from an existing database:

require 'sequel'
DB = Sequel.connect('mysql2://your_DB:credentials@host/table')
foo = DB[:serverinfo].select(:DB, :SERVERNM).where(:STATUS => 'A').first

Wrap the code above inside a get or post handler in Sinatra and it'll retrieve the record. Something like this would get you close:

require 'sequel'
require 'json'

DB = Sequel.connect('mysql2://your_DB:credentials@host/table')

get '/' do
  content_type :json
  foo = DB[:serverinfo].select(:DB, :SERVERNM).where(:STATUS => 'A').first
  foo.to_json # would return the hash to the browser
end

As far as using Datamapper, read the quote at the top of Sequel's main page.

Is it possible for a sinatra app to use 2 databases?

Standard practice and common sense says that you should keep your production app separate from your staging app. I'm not sure what you have against deploying two different apps, but that's the only way to ensure problems in staging don't trip up your production app.

Separate Sinatra app's test database from development database?

In spec_helper.rb file you are requiring my_app.rb file which in turn requires db.rb to set up your database, but this happens before you call set :environment, :test, so the configure :test block isn't being run (the environment is the default development at that stage).

Of course you can't call set environment :test before you require Sinatra, but you can set the RACK_ENV environment variable, which Sinatra checks when setting its environment.

So to make sure you're running in the test environment, add this to the top of spec_helper.rb (or somewhere else at the start of your tests, but before you require Sinatra):

ENV['RACK_ENV'] = 'test'

You could then remove the set :environment, :test line as it's now redundant.

This is similar to the other question you reference, but rack apps use RACK_ENV instead of RAILS_ENV.

Best practices concerning SQLite queries, Sinatra, and erb?

The best practice consists in doing your SQL queries in the do block, and passing an object to your template.

It is considered a bad idea to write too much code in the templates, and it can be useful to isolate the fetching logic from the displaying logic if you ever want to use the same template from different routes.

By using Ruby's duck typing, you can make your template very flexible. For instance with Haml:

%ul
  - @dataset.each do |row|
    %li= row['name']

All you need to do is to provide the template with an Enumerable of objects which all respond to []. It can be the result of a database query (most gems return something compatible, I think), or it can be an hard-coded Array of Hashes for instance.

Read-only web apps with Rails/Sinatra

Both of those are workable and I have employed both in the past, but I'd go with the API approach.

Quick disclaimer: one thing that's not clear is how different these apps are in function. For example, I can imagine the old one being a CRUD app that works on individual records and the new one being a reporting app that does big complicated aggregation queries. That makes the shared DB (maybe) more attractive because the overlap in how you access the data is so small. I'm assuming below that's not the case.

Anyway, the API approach. First, the bad:

• One more dependency (the old app). When it breaks, it takes down both apps.
• One more hop to get data, so higher latency.
• Working with existing code is less fun than writing new code. Just is.

But on the other hand, the good:

• Much more resilient to schema changes. Your "old" app's API can have tests, and you can muck with the database to your heart's content (in the context of the old app) and just keep your API to its spec. Your new app won't know the difference, which is good. Abstraction FTW. This the opposite side of the "one more dependency" coin.

• Same point, but from different angle: in the we-share-the-database approach, your schema + all of SQL is effectively your API, and it has two clients, the old app and the new. Unless your two apps are doing very different things with the same data, there's no way that's the best API. It's too poorly defined.

• The DB admin/instrumentation is better. Let's say you mess up some query and hose your database. Which app was it? Where are these queries coming from? Basically, the fewer things that can interact with your DB, the better. Related: optimize your read queries in one place, not two.

• If you used RESTful routes in your existing app for the non-API actions, I'm guessing your API needs will have a huge overlap with your existing controller code. It may be a matter of just converting your data to JSON instead of passing it to a view. Rails makes it very easy to use an action to respond to both API and user-driven requests. So that's a big DRY win if it's applicable.

• What happens if you find out you do want some writability in your new app? Or at least access to some field your old app doesn't care about (maybe you added it with a script)? In the shared DB approach, it's just gross. With the other, it's just a matter of extending the API a bit.

Basically, the only way I'd go for the shared DB approach is that I hated the old code and wanted to start fresh. That's understandable (and I've done exactly that), but it's not the architecturally soundest option.

A third option to consider is sharing code between the two apps. For example, you could gem up the model code. Now your API is really some Ruby classes that know how to talk to your database. Going even further, you could write a Sinatra app and mount it inside of the existing Rails app and reuse big sections it. Then just work out the routing so that they look like separate apps to the outside world. Whether that's practical obviously depends on your specifics.

In terms of specific technologies, both Sinatra and Rails are fine choices. I tend towards Rails for bigger projects and Sinatra for smaller ones, but that's just me. Do what feels good.

Sinatra and Postgresql -- how to use pure Sql, perhaps without ActiveRecord

You can use pg gem directly.

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')

For specify more connection options check it out the PG::Connection constructor documentation.

---

Related Topics