JSON serialization using Serde Rust crates in Amazon RDS for PostgreSQL

PostgreSQL distinguishes itself from other relational databases through its extensibility and support for object-oriented design. This allows you to create custom types, functions, and more. Amazon Relational Database Service (Amazon RDS) for PostgreSQL supports the PL/Rust extension, enabling developers to build safe and performant database functions in the Rust programming language. As an additional improvement to this process, Amazon RDS for PostgreSQL also supports PGRX, a framework that can map PostgreSQL types to their Rust counterparts, making it easier to work with PostgreSQL data in PL/Rust functions.

JSON objects use human-readable text to store and transmit data objects consisting of attribute–value pairs and arrays or other serializable values. Their versatility and readability make them widely used for exchanging data between clients and servers in web applications. However, PostgreSQL only supports four basic data types when working with JSON, and creating one with many attributes by hand, or converting them to text and back, can be greatly inefficient and tedious. Today, this can all be resolved in a single function with the newly added Serde crate support.

In this post, we showcase how to use PGRX and PL/Rust to efficiently access and manipulate all built-in PostgreSQL data types in Rust. We demonstrate how to write performant functions that create and serialize JSON objects that include these built-in types. These functions are directly usable in your database and use the newly supported serde and serde_json crates. We also walk through deploying an Amazon RDS for PostgreSQL instance with PL/Rust enabled, and how PGRX type mapping allows you to use all built-in PostgreSQL types in a JSON object.

Solutions overview

PGRX supports PostgreSQL major versions 12 through 17. This post demonstrates the solution using version 17.2.

Prerequisites

To run the example in this post, you need to provision an RDS for PostgreSQL instance or Multi-AZ DB cluster running PostgreSQL 13.12 or higher, 14.9 or higher, 15.2 or higher, 16.1 or higher, or 17.1 or higher. Additionally, you need to add plrust to the shared_preload_libraries parameter into a DB parameter group and assign the DB parameter group to your PostgreSQL database instance.

You can use the AWS Command Line Interface (AWS CLI) to create a DB parameter group:

REGION="us-east-1"

aws rds create-db-parameter-group
     --db-parameter-group-name pg17-plrust
     --db-parameter-group-family postgres17
     --description "Parameter group that contains PL/Rust settings for PostgreSQL 17"
     --region "${REGION}"

Modify your parameter group to add plrust to the shared_preload_libraries parameter:

aws rds modify-db-parameter-group 
     --db-parameter-group-name pg17-plrust
     --parameters "ParameterName='shared_preload_libraries',ParameterValue='plrust',ApplyMethod=pending-reboot"
     --region "${REGION}"

To create a PostgreSQL database instance with the parameter group:

aws rds create-db-instance 
     --db-instance-identifier example-instance
     --engine postgres --db-instance-class db.t3.small 
     --master-username example-username --master-user-password example-password 
     --db-parameter-group-name pg17-plrust --region us-west-2 --engine-version 17.2 
     --allocated-storage 100

If you modify the shared_preload_libraries parameter on an existing database instance, the changes will not take effect until the instance is rebooted. You can also modify the parameter group directly from the AWS Management Console. If you create an instance directly with your custom parameter group, you can start using plrust when the instance is available. For more information, see Working with DB parameter groups.

Install the PL/Rust extension

Connect to the database and verify that PL/Rust is enabled in shared_preload_libraries:

SHOW shared_preload_libraries;

Create the PL/Rust extension:

CREATE EXTENSION IF NOT EXISTS plrust;

You should see successful extension creation:

CREATE EXTENSION

PGRX PostgreSQL type mapping

In this section, we discuss PGRX PostgreSQL type mapping.

PostgreSQL built-in data types

PostgreSQL has built-in time functions. For example, run the following query:

SELECT now();

You should see the following output with a timestamp:

              now
-------------------------------
 2024-09-10 16:46:30.376207+00
(1 row)

You can also retrieve the underlying PostgreSQL type of this timestamp:

SELECT pg_typeof(now());

You should see that this is a timestamp with time zone type:

        pg_typeof
--------------------------
 timestamp with time zone
(1 row)

Run the following query to get the current year in SQL:

SELECT EXTRACT('Year' FROM now());

You should see the year:

 extract
---------
    2024
(1 row)

Access built-in data types in PL/Rust

PGRX provides a Rust mapping to PostgreSQL data types, allowing you to work with PostgreSQL types directly in your Rust functions. Let’s look at an example where we manipulate the timestamp with time zone type in PL/Rust using PGRX.

Create the following function:

CREATE OR REPLACE FUNCTION public.get_year(x timestamp with time zone)
RETURNS integer
LANGUAGE plrust
IMMUTABLE PARALLEL SAFE STRICT
AS $$
     let t: pgrx::TimestampWithTimeZone = x;
     let year: i32 = pgrx::TimestampWithTimeZone::year(&t);
     Ok(Some(year))
 $$;

You should see successful function creation:

CREATE FUNCTION

Provide this function with a timestamp and run the following query:

SELECT public.get_year(now());

You should see the year of the timestamp you provided:

 get_year
----------
     2024
(1 row)

We can see here that through PGRX type mapping pgrx::TimestampWithTimeZone, we were able to access a PostgreSQL type in PL/Rust. In the next section, we serialize this type into JSON, which was not possible previously, because PostgreSQL only supports string, number, boolean, and null. PGRX has a type mapping for nearly every PostgreSQL built-in type, as well as related functions. For more information, refer to the PGRX official documentation.

Custom types serialization

In this section, we demonstrate how to build a PL/Rust function to define a custom type, and serialize it into JSON using the Serde crates. This function uses TimestampWithTimeZone, which is a type unsupported by PostgreSQL JSON objects.

Create the following function in your database, public.my_serialize(), which returns text:

CREATE OR REPLACE FUNCTION public.my_serialize(name TEXT, idn INT, tz TIMESTAMP WITH TIME ZONE)
RETURNS text
LANGUAGE plrust
IMMUTABLE PARALLEL SAFE STRICT
AS $$
    [dependencies]
    serde = { version = "=1.0.199", features = [ "derive" ] }
    serde_json = "=1.0.117"
    
    [code]
    use serde::{Deserialize, Serialize};
    use serde_json::to_string;
    
    #[derive(Serialize, Deserialize)]
    struct MyDataType{
        name: String,
        id: i32,
        timetz: pgrx::TimestampWithTimeZone,
    }
    
    let my_data: MyDataType = MyDataType{name: name.to_string(), id: idn, timetz: tz};
    let my_data_serialized = to_string(&my_data);
    Ok(Some(my_data_serialized?))
$$;

You should see successful function creation:

CREATE FUNCTION

Execute the function with the following query:

SELECT public.my_serialize('one', 1, now());

You should see the following output:

                          my_serialize
------------------------------------------------------------------
 {"name":"one","id":1,"timetz":"2024-09-24T18:10:23.36879+00:00"}
(1 row)

timetz might be different depending on the current time of your AWS Region.

Breaking down the function

Run the following query to view all PL/Rust allowed dependencies. Though this post focuses on serde/serde_json, note that Amazon RDS for PostgreSQL supports many other crates:

SELECT * FROM plrust.allowed_dependencies();

You need to declare serde and serde_json as dependencies that you want use in the PL/Rust function:

[dependencies]
    serde = { version = "=1.0.199", features = [ "derive" ] }
    serde_json = "=1.0.117"

In the code section, import what you need from the crates:

[code]
    use serde::{Deserialize, Serialize};
    use serde_json::to_string;

Declare your custom type. Note the macro that makes this type visible to serde and serde_json. This custom type can be scaled to include more fields based on your needs:

#[derive(Serialize, Deserialize)]
struct MyDataType{
    name: String,
    id: i32,
    timetz: pgrx::TimestampWithTimeZone,
}

Create an instance of your custom type based on what was passed in, and return a JSON in the form of text:

let my_data: MyDataType = MyDataType{name: name.to_string(), id: idn, timetz: tz};
let my_data_serialized = to_string(&my_data);
Ok(Some(my_data_serialized?))

Cleanup

To clean up your resources, run the following query to remove anything PL/Rust related:

DROP EXTENSION plrust CASCADE;

See the following output, dropping the extension and function you created:

NOTICE:  drop cascades to function my_serialize(text,integer,timestamp with time zone)
DROP EXTENSION

You can delete your RDS for PostgreSQL instance if you no longer need to use it.

Conclusion

In this post, we covered how PGRX type mappings can help you access PostgreSQL built-in types when writing a PL/Rust function. We also demonstrated how crates supported by Amazon RDS, serde and serde_json, can be used to serialize Rust custom types into different forms, specifically a JSON string. We broke down the example associated with doing so, and explained how the example can be scaled to meet your needs.

Try this solution with your own use case, and let us know your thoughts in the comments.

About the author

Michael You is a Software Development Engineer on the Amazon RDS for PostgreSQL team. He focuses on bringing in open-source technologies to managed PostgreSQL at AWS.

Source: Read More