In a Ruby script, I want to connect to a Postgres DB via URL, and do a simple SELECT * FROM users hello world query.
What is a gem to do this? I haven't found any gem that lets me connect through URL.
The most promising I found was https://bitbucket.org/ged/ruby-pg/wiki/Home, but all of its doc links are broken and the Github repo makes it look like its not a popular choice.
Form the pg gem document you can set up a database connection by calling the new method with 3 styles by providing the parameter as a Hash, a string, and an array.
I realized that you can provide a URL string as a parameter string like this.
conn = PG::Connection.new("postgresql://postgres:mypassword@localhost:5432/mydb")
It works too.
The pg gem, which is the main PostgreSQL interface for Ruby, supports URLs for specifying the database to connect to. That's because it's just a wrapper around PostgreSQL's libpq, and libpq supports three ways to specify connections:
Individual settings for host, password, etc, passed to the library;
A PostgreSQL "connection string" like dbname=mydb user=fred password=d00rmat host=localhost port=5432 sslmode=require; or
A JDBC-style URL, like jdbc:postgresql://fred:d00rmat@localhost:5432/mydb?sslmode=require
so you don't need to do anything special. It's already supported by pg via libpq.
Now, if what you actually want is to query a PostgreSQL database like a web service API, e.g.
http://my-postgresql-server/q?query="SELECT * FROM users;"
and get responses like:
[
{"id":1, "name":"fred", ...},
...
]
... that's not supported directly by PostgreSQL. You'll need a mid-layer tool to expose the web-service API and broker for PostgreSQL. Think very hard about the security implications of allowing client apps to send arbitrary SQL; this is usually an extremely bad design, and you should instead write a proper web service API with request methods like:
http://my-appserver/api/1.0/fetchAllUsers
The 'pg' gem is the THE gem for Postgres support in Ruby (assuming you're not using JRuby, that is).
This is the gem that ultimately derives from https://bitbucket.org/ged/ruby-pg/wiki/Home. Not sure what problems you are seeing. Perhaps one gotcha is that if you are adding it to your Gemfile then you should use the name 'pg' rather than the obsolete name 'ruby-pg'.
What makes you think it's not popular?
The wiki looks okay to me (and the home page was last updated 2 months ago). If you have problems with the wiki, report it to the maintainers.
As for connecting via a 'URL', do you mean a JDBC-style URL?
