CODEWITHSUNDEEP
rubydatabaseherokuhostingsinatra
user1626730
user1626730

Reputation: 4113

Where/how can I host my small, database-driven, Sinatra-powered Ruby app? Heroku limits database connections

Here is my app: a price inflation calculator. If you click 'Calculate,' you'll probably get a server-related error. When I tested the app out in Sinatra, I got an error saying 

PG::Error: FATAL: too many connections for role "********"

After looking into it, it turns out that Heroku limits its free databases to 20 connections.

I'd like to continue developing small apps (especially database-driven ones) on Heroku, but I may not be able to if I'm unable to get around this restriction. I would pay $50/month to get a database that allows more connections, but I do not yet know if it would be worth it.

My question is: Does anyone know if it's possible to get around this restriction for free, or if there are alternatives to Heroku that can host a database-driven Sinatra app?

Here's the code I use that adds inflation data to the database:

require 'rubygems'
require 'rest-client'
require 'nokogiri'
require 'sequel'

### MAKE CPI DATABASE ###
db_name = 'DATABASE_NAME_HERE'
DB = Sequel.postgres(db_name,:user=>'USER_NAME',:password=>'PASSWORD',:host=>'HOST',:port=>5432,:sslmode=>'require')
    DB.create_table! :cpi_nsa_annual do
        primary_key :id
        Integer :year
        Float :cpi
    end # DONE: DB.create_table :cpi_nsa_annual do
cpi_annual = DB[:cpi_nsa_annual]
### DONE MAKING CPI DATABASE ###

post_url = "http://data.bls.gov/pdq/SurveyOutputServlet"

post_params = {
    'delimiter'=>'comma',
    'output_format'=>'html',
    'output_type'=>'column',
    'periods_option'=>'all_periods',
    'series_id'=>'CUUR0000SA0',
    'years_option'=>'all_years'
}

if page = RestClient.post(post_url,post_params)
    npage = Nokogiri::HTML(page)
    data = npage.css('table.regular-data tbody tr')

    data.each{|row|
        month_prefix = (row.css('th')[2].text)[0]
        year = row.css('th')[1].text
        month = (row.css('th')[2].text)[1..2]
        cpi = row.css('td').text

        if month_prefix=='M' and month=='13'
                cpi_annual.insert(
                    :year=>year,
                    :cpi=>cpi
                ) # DONE: cpi_annual_insert
                p ["YEAR",year,cpi]
        end # DONE: month_prefix=='M' and month!='13'
    }
end # DONE: if page
p cpi_annual.each{|row| p row}

Upvotes: 2

Views: 535

Answers (2)

culix
culix

Reputation: 10626

meub's answer suggesting you store the data outside of a database is sound, but if you feel like you really want to stick with a database try looking into why your app is using so many connections.

If you run select * from pg_stat_database WHERE datname = 'yourdbname' the 'numbackends' field will tell you how many connections are being made to your database. If you do a fresh deploy to heroku and then visit your app a few times does the number of connections increase? Perhaps you need to close your connection.

If you add DB[:cpi_nsa_annual].disconnect at the end of your code does the number of connections stop going up with each page load?

Upvotes: 1

meub
meub

Reputation: 2318

It really doesn't seem like you need a database to accomplish what you want in that app. Can't you just store the annual rates in an array in your ruby code?

$inflation_rates = {"1999" => 2.19, "2000" => 2.97, "2001" => 3.73}

Or maybe I'm misunderstanding how your app works.

I've hosted small Sinatra apps with databases on Heroku without problems. The 20 connection limit has been enough. Are you sure its not a fault of your app? Maybe its using an excessive number of connections? Could you post your code?

Also, OpenKeyVal is an awesome free key/value data store with no connection limit (the only limit is the key must be under 64KB). It might force you to change your code around but its at least worth looking into. Because you can store data with JSONP calls you can build an app that uses the datastore in a single static html file.

Upvotes: 2

Related Questions