Quickstart: Use Ruby to connect and query data in Azure Database for PostgreSQL - Single Server
APPLIES TO: 
Azure Database for PostgreSQL - Single Server
Important
Azure Database for PostgreSQL - Single Server is on the retirement path. We strongly recommend that you upgrade to Azure Database for PostgreSQL - Flexible Server. For more information about migrating to Azure Database for PostgreSQL - Flexible Server, see What's happening to Azure Database for PostgreSQL Single Server?.
This quickstart demonstrates how to connect to an Azure Database for PostgreSQL using a Ruby application. It shows how to use SQL statements to query, insert, update, and delete data in the database. The steps in this article assume that you are familiar with developing using Ruby, and are new to working with Azure Database for PostgreSQL.
Prerequisites
This quickstart uses the resources created in either of these guides as a starting point:
You also need to have installed:
Get connection information
Get the connection information needed to connect to the Azure Database for PostgreSQL. You need the fully qualified server name and login credentials.
- Log in to the Azure portal.
- From the left-hand menu in Azure portal, select All resources, and then search for the server you have created (such as mydemoserver).
- Select the server name.
- From the server's Overview panel, make a note of the Server name and Server admin login name. If you forget your password, you can also reset the password from this panel.
Note
The @ symbol in the Azure Postgres username has been url encoded as %40 in all the connection strings.
Connect and create a table
Use the following code to connect and create a table using CREATE TABLE SQL statement, followed by INSERT INTO SQL statements to add rows into the table.
The code uses a PG::Connection object with constructor new to connect to Azure Database for PostgreSQL. Then it calls method exec() to run the DROP, CREATE TABLE, and INSERT INTO commands. The code checks for errors using the PG::Error class. Then it calls method close() to close the connection before terminating. See Ruby Pg reference documentation for more information on these classes and methods.
Replace the host, database, user, and password strings with your own values.
require 'pg'
begin
# Initialize connection variables.
host = String('mydemoserver.postgres.database.azure.com')
database = String('postgres')
user = String('mylogin%40mydemoserver')
password = String('<server_admin_password>')
# Initialize connection object.
connection = PG::Connection.new(:host => host, :user => user, :dbname => database, :port => '5432', :password => password)
puts 'Successfully created connection to database'
# Drop previous table of same name if one exists
connection.exec('DROP TABLE IF EXISTS inventory;')
puts 'Finished dropping table (if existed).'
# Drop previous table of same name if one exists.
connection.exec('CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);')
puts 'Finished creating table.'
# Insert some data into table.
connection.exec("INSERT INTO inventory VALUES(1, 'banana', 150)")
connection.exec("INSERT INTO inventory VALUES(2, 'orange', 154)")
connection.exec("INSERT INTO inventory VALUES(3, 'apple', 100)")
puts 'Inserted 3 rows of data.'
rescue PG::Error => e
puts e.message
ensure
connection.close if connection
end
Read data
Use the following code to connect and read the data using a SELECT SQL statement.
The code uses a PG::Connection object with constructor new to connect to Azure Database for PostgreSQL. Then it calls method exec() to run the SELECT command, keeping the results in a result set. The result set collection is iterated over using the resultSet.each do loop, keeping the current row values in the row variable. The code checks for errors using the PG::Error class. Then it calls method close() to close the connection before terminating. See Ruby Pg reference documentation for more information on these classes and methods.
Replace the host, database, user, and password strings with your own values.
require 'pg'
begin
# Initialize connection variables.
host = String('mydemoserver.postgres.database.azure.com')
database = String('postgres')
user = String('mylogin%40mydemoserver')
password = String('<server_admin_password>')
# Initialize connection object.
connection = PG::Connection.new(:host => host, :user => user, :dbname => database, :port => '5432', :password => password)
puts 'Successfully created connection to database.'
resultSet = connection.exec('SELECT * from inventory;')
resultSet.each do |row|
puts 'Data row = (%s, %s, %s)' % [row['id'], row['name'], row['quantity']]
end
rescue PG::Error => e
puts e.message
ensure
connection.close if connection
end
Update data
Use the following code to connect and update the data using a UPDATE SQL statement.
The code uses a PG::Connection object with constructor new to connect to Azure Database for PostgreSQL. Then it calls method exec() to run the UPDATE command. The code checks for errors using the PG::Error class. Then it calls method close() to close the connection before terminating. See Ruby Pg reference documentation for more information on these classes and methods.
Replace the host, database, user, and password strings with your own values.
require 'pg'
begin
# Initialize connection variables.
host = String('mydemoserver.postgres.database.azure.com')
database = String('postgres')
user = String('mylogin%40mydemoserver')
password = String('<server_admin_password>')
# Initialize connection object.
connection = PG::Connection.new(:host => host, :user => user, :dbname => database, :port => '5432', :password => password)
puts 'Successfully created connection to database.'
# Modify some data in table.
connection.exec('UPDATE inventory SET quantity = %d WHERE name = %s;' % [200, '\'banana\''])
puts 'Updated 1 row of data.'
rescue PG::Error => e
puts e.message
ensure
connection.close if connection
end
Delete data
Use the following code to connect and read the data using a DELETE SQL statement.
The code uses a PG::Connection object with constructor new to connect to Azure Database for PostgreSQL. Then it calls method exec() to run the UPDATE command. The code checks for errors using the PG::Error class. Then it calls method close() to close the connection before terminating.
Replace the host, database, user, and password strings with your own values.
require 'pg'
begin
# Initialize connection variables.
host = String('mydemoserver.postgres.database.azure.com')
database = String('postgres')
user = String('mylogin%40mydemoserver')
password = String('<server_admin_password>')
# Initialize connection object.
connection = PG::Connection.new(:host => host, :user => user, :dbname => database, :port => '5432', :password => password)
puts 'Successfully created connection to database.'
# Modify some data in table.
connection.exec('DELETE FROM inventory WHERE name = %s;' % ['\'orange\''])
puts 'Deleted 1 row of data.'
rescue PG::Error => e
puts e.message
ensure
connection.close if connection
end
Clean up resources
To clean up all resources used during this quickstart, delete the resource group using the following command:
az group delete \
--name $AZ_RESOURCE_GROUP \
--yes
Next steps
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for