Quickstart: Use Rust to interact with 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?.
In this article, you will learn how to use the PostgreSQL driver for Rust to connect and query data in Azure Database for PostgreSQL. You can explore CRUD (create, read, update, delete) operations implemented in sample code, and run the application locally to see it in action.
Prerequisites
For this quickstart, you need:
An Azure account with an active subscription. Create an account for free.
A recent version of Rust installed.
An Azure Database for PostgreSQL single server. Create one using Azure CLI.
Based on whether you are using public or private access, complete ONE of the actions below to enable connectivity.
Action Connectivity method How-to guide Configure firewall rules Public Portal
CLIConfigure service endpoint Public Portal
CLIConfigure private link Private Portal
CLIGit installed.
Get database connection information
Connecting to an Azure Database for PostgreSQL database requires a fully qualified server name and login credentials. You can get this information from the Azure portal.
- In the Azure portal, search for and select your Azure Database for PostgreSQL server name.
- On the server's Overview page, copy the fully qualified Server name and the Admin username. The fully qualified Server name is always of the form <my-server-name>.postgres.database.azure.com, and the Admin username is always of the form <my-admin-username>@<my-server-name>.
Review the code (optional)
If you're interested in learning how the code works, you can review the following snippets. Otherwise, feel free to skip ahead to Run the application.
Connect
The main
function starts by connecting to Azure Database for PostgreSQL and it depends on following environment variables for connectivity information POSTGRES_HOST
, POSTGRES_USER
, POSTGRES_PASSWORD
and, POSTGRES_DBNAME
. By default, the PostgreSQL database service is configured to require TLS
connection. You can choose to disable requiring TLS
if your client application does not support TLS
connectivity. For details, please refer Configure TLS connectivity in Azure Database for PostgreSQL - Single Server.
The sample application in this article uses TLS with the postgres-openssl crate. postgres::Client::connect function is used to initiate the connection and the program exits in case this fails.
fn main() {
let pg_host = std::env::var("POSTGRES_HOST").expect("missing environment variable POSTGRES_HOST");
let pg_user = std::env::var("POSTGRES_USER").expect("missing environment variable POSTGRES_USER");
let pg_password = std::env::var("POSTGRES_PASSWORD").expect("missing environment variable POSTGRES_PASSWORD");
let pg_dbname = std::env::var("POSTGRES_DBNAME").unwrap_or("postgres".to_string());
let builder = SslConnector::builder(SslMethod::tls()).unwrap();
let tls_connector = MakeTlsConnector::new(builder.build());
let url = format!(
"host={} port=5432 user={} password={} dbname={} sslmode=require",
pg_host, pg_user, pg_password, pg_dbname
);
let mut pg_client = postgres::Client::connect(&url, tls_connector).expect("failed to connect to postgres");
...
}
Drop and create table
The sample application uses a simple inventory
table to demonstrate the CRUD (create, read, update, delete) operations.
CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);
The drop_create_table
function initially tries to DROP
the inventory
table before creating a new one. This makes it easier for learning/experimentation, as you always start with a known (clean) state. The execute method is used for create and drop operations.
const CREATE_QUERY: &str =
"CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);";
const DROP_TABLE: &str = "DROP TABLE inventory";
fn drop_create_table(pg_client: &mut postgres::Client) {
let res = pg_client.execute(DROP_TABLE, &[]);
match res {
Ok(_) => println!("dropped table"),
Err(e) => println!("failed to drop table {}", e),
}
pg_client
.execute(CREATE_QUERY, &[])
.expect("failed to create 'inventory' table");
}
Insert data
insert_data
adds entries to the inventory
table. It creates a prepared statement with prepare function.
const INSERT_QUERY: &str = "INSERT INTO inventory (name, quantity) VALUES ($1, $2) RETURNING id;";
fn insert_data(pg_client: &mut postgres::Client) {
let prep_stmt = pg_client
.prepare(&INSERT_QUERY)
.expect("failed to create prepared statement");
let row = pg_client
.query_one(&prep_stmt, &[&"item-1", &42])
.expect("insert failed");
let id: i32 = row.get(0);
println!("inserted item with id {}", id);
...
}
Also note the usage of prepare_typed method, that allows the types of query parameters to be explicitly specified.
...
let typed_prep_stmt = pg_client
.prepare_typed(&INSERT_QUERY, &[Type::VARCHAR, Type::INT4])
.expect("failed to create prepared statement");
let row = pg_client
.query_one(&typed_prep_stmt, &[&"item-2", &43])
.expect("insert failed");
let id: i32 = row.get(0);
println!("inserted item with id {}", id);
...
Finally, a for
loop is used to add item-3
, item-4
and, item-5
with randomly generated quantity for each.
...
for n in 3..=5 {
let row = pg_client
.query_one(
&typed_prep_stmt,
&[
&("item-".to_owned() + &n.to_string()),
&rand::thread_rng().gen_range(10..=50),
],
)
.expect("insert failed");
let id: i32 = row.get(0);
println!("inserted item with id {} ", id);
}
...
Query data
query_data
function demonstrates how to retrieve data from the inventory
table. The query_one method is used to get an item by its id
.
const SELECT_ALL_QUERY: &str = "SELECT * FROM inventory;";
const SELECT_BY_ID: &str = "SELECT name, quantity FROM inventory where id=$1;";
fn query_data(pg_client: &mut postgres::Client) {
let prep_stmt = pg_client
.prepare_typed(&SELECT_BY_ID, &[Type::INT4])
.expect("failed to create prepared statement");
let item_id = 1;
let c = pg_client
.query_one(&prep_stmt, &[&item_id])
.expect("failed to query item");
let name: String = c.get(0);
let quantity: i32 = c.get(1);
println!("quantity for item {} = {}", name, quantity);
...
}
All rows in the inventory table are fetched using a select * from
query with the query method. The returned rows are iterated over to extract the value for each column using get.
Tip
Note how get
makes it possible to specify the column either by its numeric index in the row, or by its column name.
...
let items = pg_client
.query(SELECT_ALL_QUERY, &[])
.expect("select all failed");
println!("listing items...");
for item in items {
let id: i32 = item.get("id");
let name: String = item.get("name");
let quantity: i32 = item.get("quantity");
println!(
"item info: id = {}, name = {}, quantity = {} ",
id, name, quantity
);
}
...
Update data
The update_date
function randomly updates the quantity for all the items. Since the insert_data
function had added 5
rows, the same is taken into account in the for
loop - for n in 1..=5
Tip
Note that we use query
instead of execute
since we intend to get back the id
and the newly generated quantity
(using RETURNING clause).
const UPDATE_QUERY: &str = "UPDATE inventory SET quantity = $1 WHERE name = $2 RETURNING quantity;";
fn update_data(pg_client: &mut postgres::Client) {
let stmt = pg_client
.prepare_typed(&UPDATE_QUERY, &[Type::INT4, Type::VARCHAR])
.expect("failed to create prepared statement");
for id in 1..=5 {
let row = pg_client
.query_one(
&stmt,
&[
&rand::thread_rng().gen_range(10..=50),
&("item-".to_owned() + &id.to_string()),
],
)
.expect("update failed");
let quantity: i32 = row.get("quantity");
println!("updated item id {} to quantity = {}", id, quantity);
}
}
Delete data
Finally, the delete
function demonstrates how to remove an item from the inventory
table by its id
. The id
is chosen randomly - it's a random integer between 1
to 5
(5
inclusive) since the insert_data
function had added 5
rows to start with.
Tip
Note that we use query
instead of execute
since we intend to get back the info about the item we just deleted (using RETURNING clause).
const DELETE_QUERY: &str = "DELETE FROM inventory WHERE id = $1 RETURNING id, name, quantity;";
fn delete(pg_client: &mut postgres::Client) {
let stmt = pg_client
.prepare_typed(&DELETE_QUERY, &[Type::INT4])
.expect("failed to create prepared statement");
let item = pg_client
.query_one(&stmt, &[&rand::thread_rng().gen_range(1..=5)])
.expect("delete failed");
let id: i32 = item.get(0);
let name: String = item.get(1);
let quantity: i32 = item.get(2);
println!(
"deleted item info: id = {}, name = {}, quantity = {} ",
id, name, quantity
);
}
Run the application
To begin with, run the following command to clone the sample repository:
git clone https://github.com/Azure-Samples/azure-postgresql-rust-quickstart.git
Set the required environment variables with the values you copied from the Azure portal:
export POSTGRES_HOST=<server name e.g. my-server.postgres.database.azure.com> export POSTGRES_USER=<admin username e.g. my-admin-user@my-server> export POSTGRES_PASSWORD=<admin password> export POSTGRES_DBNAME=<database name. it is optional and defaults to postgres>
To run the application, change into the directory where you cloned it and execute
cargo run
:cd azure-postgresql-rust-quickstart cargo run
You should see an output similar to this:
dropped 'inventory' table inserted item with id 1 inserted item with id 2 inserted item with id 3 inserted item with id 4 inserted item with id 5 quantity for item item-1 = 42 listing items... item info: id = 1, name = item-1, quantity = 42 item info: id = 2, name = item-2, quantity = 43 item info: id = 3, name = item-3, quantity = 11 item info: id = 4, name = item-4, quantity = 32 item info: id = 5, name = item-5, quantity = 24 updated item id 1 to quantity = 27 updated item id 2 to quantity = 14 updated item id 3 to quantity = 31 updated item id 4 to quantity = 16 updated item id 5 to quantity = 10 deleted item info: id = 4, name = item-4, quantity = 16
To confirm, you can also connect to Azure Database for PostgreSQL using psql and run queries against the database, for example:
select * from inventory;
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
https://aka.ms/ContentUserFeedback.
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:Submit and view feedback for