Share This Article
If you want to connect PostgreSQL In Rails, you first need to install the pg gem. You can do this by adding the following line to your Gemfile:
gem 'pg'Then run bundle install from the command line to install the gem.
Once the gem is installed, you can require it in your project.
Example:
require "pg"The pg gem defines the PG class, which you can use to connect to a Postgres database.
Before connecting to a database, you need to have a database server running and a database created.
Once you have that setup, you can use the PG#connect method to connect to the database.
Example:
conn = PG.connect(dbname: "myapp_development")The PG#connect method accepts a hash with connection options.
The most common option is dbname, which specifies the database name.
The default one will be used if you don’t specify a database name.
Other options you may want to use are user, password, host and port.
Once you have a connection, you can run SQL queries using the PG#exec method.
Example:
conn.exec("SELECT * FROM users") # SELECT queryIf you want to run an INSERT, UPDATE or DELETE query, you can use the PG#exec_params method.
This method accepts an SQL query and an array of parameters.
Example:
conn.exec_params("INSERT INTO users (name, email) VALUES ($1, $2)", ["John Doe", "[email protected]"]) # INSERT queryNote: The $1 and $2 are placeholders for the parameters in the array.
If you want to get the results of a query as an array of hashes, you can use the PG#exec_params method with the :as option set to :hash.
Example:
conn.exec_params("SELECT * FROM users", [], as: :hash)Getting a Single Result
If your query only returns a single row, you can use the PG#exec_params method with the :single_value option set to true.
Example:
conn.exec_params("SELECT name FROM users WHERE id = $1", [1], single_value: true)Opening & Closing Connections
It’s important to remember to close the connection when you are done using it.
You can do this by calling the PG#close method.
conn.close
If you forget to close the connection, your database may run out of connections and start refusing new connections.
You can use the PG#finish method to ensure the connection is permanently closed.
This method will close the connection if it’s open and do nothing if it’s already closed.
Using a Connection Pool
You may want to use a connection pool if you have many concurrent connections to the database.
This will make sure that you don’t run out of database connections.
The pg gem comes with a connection pool implementation called PG::ConnectionPool.
You can use the PG::ConnectionPool#new method to create a new connection pool.
Example:
require "pg"
pool = PG::ConnectionPool.new(dbname: "myapp_development")Once you have a connection pool, you can use the PG::ConnectionPool#with method to borrow a connection from the pool.
Example:
pool.with do |conn|
conn.exec_params("INSERT INTO users (name, email) VALUES ($1, $2)", ["John Doe", "[email protected]"])
endWhen you are done with the connection, it will be returned to the pool and available for other parts of your code.