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.gitSet 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 runYou 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 = 16To 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
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