JSON API for the MetroFuture project database.
Read the blog post about this project to understand what this app does and why we set it up the way it is.
This project depends on a connection to Microsoft SQL Server 2008. (The ActiveRecord adapter must stay at version 4.1.*, because 4.2 drops support for Microsoft SQL Server 2008.)
On a *nix machine, you'll need FreeTDS installed in your development environment in order to connect to the SQL Server.
- Install the latest FreeTDS by running
brew update && brew install freetdson a Mac with Homebrew. - Follow the instructions in the blog post about this project to set up your connection to the SQL database.
Deploying this application can be challenging, because it depends on Unix machines talking to non-Unix machines.
Heroku
We use multiple Heroku buildpacks to deploy our app. They are set as follows.
$ heroku buildpacks
1. https://github.com/foraker/heroku-buildpack-freetds
2. heroku/ruby
Dokku / Dokku-Alt
We previously deployed to our self-hosted PaaS (Dokku Alt) using a Docker-based deployment. See the Dockerfile for more.
If your SQL Server only allows connections from explicitly allowed IPs, you will need to set up static IPs on Heroku, because by default Heroku IPs are not deterministic.
-
Add the QuotaGuard Static add-on to your app. (Fixie, another add-on, only provides Static IPs for HTTP requests, but your database does connect via HTTP.)
-
Open the QuotaGuard Static dashboard, and send the IPs to whoever administers your SQL Server, so you can allow traffic from your app.
-
Forward traffic from your app to the database.
Set up QuotaGuard Static's SOCKS5 proxy, either via qgtunnel or qgsocksify, which we use.
To test your connection via the Rails console, make sure to run either bin/qgtunnel rails c or bin/qgsocksify rails c to forward traffic -- otherwise the Rails console won't use your QuotaGuard IPs.
If you use the qgsocksify method, remember to set it up so that it ONLY forwards traffic to your database -- otherwise every request your app makes will be forwarded through this proxy, and you'll incur a cost.
The SQL Server occasionally runs out of memory, so it can't run SQL queries. This causes the API to freeze. Usually, the issue is resolved when the SQL Server is rebooted, but you may need to heroku restart after that, as well.