4 posts tagged with "postgres"

In the previous post, we implemented a simple Cloudflare Worker in Rust/WebAssembly connecting to a Neon Postgres database and measured end-to-end latency. Without any pooling, we got a mean response time of 345ms.

The two issues we suspected for the high latency were:

Establishing connection to the database: The worker creates a new connection for each request. Given that a secure connection, it takes 7+ round trips. Not surprisingly, latency is high.

Executing the query: The query method in our code causes the Rust Postgres driver to make two round trips: to prepare the statement and to bind/execute the query. It also sends a one-way message to close the prepared statement.

In this part, we will deal with connection establishment time by introducing a pool. We will fork the driver to deal with multiple round trips (which incidentally also helps with connection pooling). We will also learn a few things about Postgres's query protocol.

Introducing connection pooling​

If the problem is establishing a connection, the solution could be a pool. This way, we can reuse the existing connections instead of creating a new one for each request.

Application-level pooling​

Could we use a pooling crate such as deadpool?

While that would be a good option in a typical Rust environment (and Exograph uses it), it is not an option in the Cloudflare Worker environment. A worker is considered stateless and should not maintain any state between requests. Since a pool is a stateful object (holding the connections), it can't be used in a worker. If you try to use it, you will get the following runtime error on every other request:

Error: Cannot perform I/O on behalf of a different request. I/O objects (such as streams, request/response bodies, and others) created in the context of one request handler cannot be accessed from a different request's handler. This is a limitation of Cloudflare Workers which allows us to improve overall performance. (I/O type: WritableStreamSink)

When the client makes the first request, the worker creates a pool and successfully executes the query. For the second request, the worker tries to reuse the pool, but it fails due to the error above, leading to the eviction of the worker by the Cloudflare runtime. For the third request, a fresh worker creates another pool, and the cycle continues.

The error is clear: we cannot use application-level pooling in this environment.

External pooling​

Since application-level pooling won't work in this environment, could we try an external pool? Cloudflare provides Hyperdrive for connection pooling (and more, such as query caching). Let's try that.

#[event(fetch)]
async fn main(_req: Request, env: Env, _ctx: Context) -> Result<Response> {
    let hyperdrive = env.hyperdrive("todo-db-hyperdrive")?;

    let config = hyperdrive
        .connection_string()
        .parse::<tokio_postgres::Config>()
        .map_err(|e| worker::Error::RustError(format!("Failed to parse configuration: {:?}", e)))?;

    let host = hyperdrive.host();
    let port = hyperdrive.port();

    // Same as before
}

Besides how we get the host and port, the rest of the code (to connect to the database and execute the query) remains the same as the one in part 1.

You will need to create a Hyperdrive instance using the following command (replace the connection string with your own):

npx wrangler hyperdrive create todo-db-hyperdrive --caching-disabled --connection-string "postgres://..."

We disable query caching since that will cause skipping most database calls. Due to the empty cache, the first request will hit the database. For subsequent requests (which execute the same SQL query in our setup), Hyperdrive will likely serve them from its cache. We are interested in measuring the latency to include database calls. With caching turned on, the comparison to the baseline would be apples-to-oranges.

Next, you will need to put the Hyperdrive information in wrangler.toml:

[[hyperdrive]]
binding = "todo-db-hyperdrive"
id = "<your-hyperdrive-id>"

Let's test this worker.

curl <worker-url>
INTERNAL SERVER ERROR

Hmm... that failed.

What's going on? Postgres has two kinds of query protocols:

1. Simple query protocol: With this protocol, you must supply the SQL as a string and include any parameter values in the query (for example, SELECT * FROM todos WHERE id = 1). The driver makes one round trip to execute such a query.
2. Extended query protocol: With this protocol, you may have the SQL query with placeholders for parameters (for example, SELECT * FROM todos WHERE id = $1), and its execution requires a preparation step. We will go into detail in the next section.

Let's explore both protocols.

Hyperdrive with the simple query protocol​

To explore the simple query protocol, we will use the simple_query method. Since it doesn't allow specifying parameters, we inline them.

#[event(fetch)]
async fn main(_req: Request, env: Env, _ctx: Context) -> Result<Response> {

    /// Hyperdrive setup as before

    let rows = client
        .simple_query("SELECT id, title, completed FROM todos WHERE completed = true")
        .await
        .map_err(|e| worker::Error::RustError(format!("Failed to query: {:?}", e)))?;

    ...
}

Does it work and how does this perform?

oha -c 1 -n 100 <worker-url>
  Slowest:      0.2871 secs
  Fastest:      0.0476 secs
  Average:      0.0633 secs

That's more like it! The mean response time is 63ms, a significant improvement over the previous 345ms.

Since the simple query protocol needed only one round trip, Hyperdrive was able to use an existing connection without too much additional logic, so it worked without any issues and performed well.

But... the simple query protocol forces us to use string interpolation to inline the parameters in the query, which is a big no-no in the world of databases due to the risk of SQL injection attacks. So let's not do that!

Hyperdrive with the extended query protocol​

Let's go back to the extended query protocol and figure out why Hyperdrive might be struggling with it. As it happens, all external pooling services deal with the same issue; for example, only recently did pgBouncer start to support it.

When using the extended query protocol through query, the driver executes the following steps:

1. Prepare: Sends a prepare request. This contains a name for the statement (for example, "s1") and a query with the placeholders for parameters to be provided later (for example, $1, $2 etc.). The server sends back the expected parameter types.
2. Bind/execute: Sends the name of the prepared statement and the parameters serialized in the format appropriate for the types. The server looks up the prepared statement by name and executes it with the provided parameters. It sends back the rows.
3. Close: Closes the prepared statement to free up the resources on the server. In tokio-postgres, this is a fire-and-forget operation (doesn't wait for a response).

When you add in a connection pool, the driver must invoke the "bind/execute" and "close" with the same connection it used for "prepare". This requires some bookkeeping and is a source of complexity.

What if we combine all three steps into a single network package? This is what Exograph's fork of tokio-postgres (fork, PR) does. The client must specify the parameter values and their types (we no longer perform a round trip to discover parameter types). This way, the driver can serialize the parameters in the correct format in the same network package.

#[event(fetch)]
async fn main(_req: Request, env: Env, _ctx: Context) -> Result<Response> {

    /// Hyperdrive setup as before

    let rows: Vec<tokio_postgres::Row> = client
        .query_with_param_types(
            "SELECT id, title, completed FROM todos where completed <> $1",
            &[(&true, Type::BOOL)],
        )
        .await
        .map_err(|e| worker::Error::RustError(format!("query_with_param_types: {:?}", e)))?;

    ...
}

How does this perform?

oha -c 1 -n 100 <worker-url>
  Slowest:      0.2883 secs
  Fastest:      0.0466 secs
  Average:      0.0620 secs

Nice! The mean response time is now 62ms, which matches the simple query protocol (63ms).

Summary​

Let's summarize the mean response times for the various configurations:

With connection pooling through Hyperdrive, we have brought the mean response time by a factor of 5.5 (from 345ms to 62ms)!

The source code is available on GitHub, so you can check this yourself. If you find any improvements or issues, please let me know.

So what should you use? Assuming that the issue with Hyperdrive and query method has been fixed:

• If you don't want to use Hyperdrive, use the query_with_type_params method with the forked driver. It does the job in one roundtrip and gives you the best performance without any risk of SQL injection attacks.
• If you want to use Hyperdrive:
  • If you frequently make the same queries, the query method will likely do better. Hyperdrive may cache the "prepare" part of the step, making subsequent queries faster.
  • If you make a variety of queries, you can use the query_with_param_types method. Since you won't execute the same query frequently, Hyperdrive's prepare statement caching is unlikely to help. Instead, this method's fewer round trips will be beneficial.

Watch Exograph's blog for more explorations and insights as we ship its Cloudflare Worker support. You can reach us on Twitter or Discord. We would appreciate a star on GitHub!

We have been working on enabling Exograph on WebAssembly. Since we have implemented Exograph using Rust, it was natural to target WebAssembly. You can soon build secure, flexible, and efficient GraphQL backends using Exograph and run them at the edge.

During our journey towards WebAssembly support, we learned a few things to improve the latency of Rust-based programs targeting WebAssembly in Cloudflare Workers connecting to Postgres. This two-part series shares those learnings. In this first post, we will set up a simple Cloudflare Worker connecting to a Postgres database and get baseline latency measurements. In the next post, we will explore various ways to improve it.

Even though we experimented in the context of Exograph, the learnings should apply to anyone using WebAssembly in Cloudflare Workers (or other platforms that support WebAssembly) to connect to Postgres.

Rust Cloudflare Workers​

Cloudflare Workers is a serverless platform that allows you to run code at the edge. The V8 engine forms the underpinning of the Cloudflare Worker platform. Since V8 supports JavaScript, it is the primary language for writing Cloudflare Workers. However, JavaScript running in V8 can load WebAssembly modules. Therefore, you can write some parts of a worker in other languages, such as Rust, compile it to WebAssembly, and load that from JavaScript.

Cloudflare Worker's Rust tooling enables writing workers entirely in Rust. Behind the scenes, the tooling compiles the Rust code to WebAssembly and loads it in a JavaScript host. The Rust code you write must be able to compile to wasm32-unknown-unknown target. Consequently, it must follow the restrictions of WebAssembly. For example, it cannot access the filesystem or network directly. Instead, it must rely on the host-provided capabilities. Cloudflare provides such capabilities through the worker-rs crate. This crate, in turn, uses wasm-bindgen to export a few JavaScript functions to the Rust code. For example, it allows opening network sockets. We will use this capability later to integrate Postgres.

Here is a minimal Cloudflare Worker in Rust:

use worker::*;

#[event(fetch)]
async fn main(_req: Request, _env: Env, _ctx: Context) -> Result<Response> {
    Ok(Response::ok("Hello, Cloudflare!")?)
}

To deploy, you can use the npx wrangler deploy command, which compiles the Rust code to WebAssembly, generates the necessary JavaScript code, and deploys it to the Cloudflare network.

Before moving on, let's measure the latency of this worker. We will use Ohayou, an HTTP load generator written in Rust. We measure latency using a single concurrent client (-c 1) and one hundred requests (-n 100).

oha -c 1 -n 100 <worker-url>
...
  Slowest:      0.2806 secs
  Fastest:      0.0127 secs
  Average:      0.0214 secs
...

It takes an average of 21ms to respond to a request. This is a good baseline to compare when we add Postgres to the mix.

Focusing on latency​

We will focus on measuring the lower bound for latency of the roundtrip for a request to the worker who queries a Postgres database before responding. Here is our setup:

• Use a Neon Postgres database with the following table and no rows to focus on network latency (and not database processing time).

  CREATE TABLE todos (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    completed BOOLEAN NOT NULL
  );
  

• Implement a Cloudflare Worker that responds to GET by fetching all completed todos from the table and returning them as a JSON response (of course, since there is no data, the response will be an empty array, but the use of a predicate will allow us to explore some practical considerations where the queries will have a few parameters).

• Place the worker, database, and client in the same region. While, we can't control the worker placement, Cloudflare will place the worker close to either the client or the database (which we've put in the same region).

All right, let's get started!

Connecting to Postgres​

Let's implement a simple worker that fetches all completed todos from the Neon Postgres database. We will use the tokio-postgres crate to connect to the database.

#[event(fetch)]
async fn main(_req: Request, env: Env, _ctx: Context) -> Result<Response> {
    let config = tokio_postgres::config::Config::from_str(&env.secret("DATABASE_URL")?.to_string())
        .map_err(|e| worker::Error::RustError(format!("Failed to parse configuration: {:?}", e)))?;

    let host = match &config.get_hosts()[0] {
        Host::Tcp(host) => host,
        _ => {
            return Err(worker::Error::RustError("Could not parse host".to_string()));
        }
    };
    let port = config.get_ports()[0];

    let socket = Socket::builder()
        .secure_transport(SecureTransport::StartTls)
        .connect(host, port)?;

    let (client, connection) = config
        .connect_raw(socket, PassthroughTls)
        .await
        .map_err(|e| worker::Error::RustError(format!("Failed to connect: {:?}", e)))?;

    wasm_bindgen_futures::spawn_local(async move {
        if let Err(error) = connection.await {
            console_log!("connection error: {:?}", error);
        }
    });

    let rows: Vec<tokio_postgres::Row> = client
        .query(
            "SELECT id, title, completed FROM todos WHERE completed = $1",
            &[&true],
        )
        .await
        .map_err(|e| worker::Error::RustError(format!("Failed to query: {:?}", e)))?;

    Ok(Response::ok(format!("{:?}", rows))?)
}

There are several notable things (especially if you are new to WebAssembly):

• In a non-WebAssembly platform, you would get the client and connection directly using the database URL, which opens a socket to the database. For example, you would have done something like this:

  let (client, connection) = config.connect(tls).await?;
  

  However, that won't work in a WebAssembly environment since there is no way to connect to a server (or, for that matter, any other resources such as filesystem). This is the core characteristic of WebAssembly: it is a sandboxed environment that cannot access resources unless explicitly provided (thought functions exported to the WebAssembly module). Therefore, we use Socket::builder().connect() to create a socket (which, in turn, uses TCP Socket API provided by Cloudflare runtime). Then, we use config.connect_raw() to lay the Postgres protocol over that socket.

• We would have marked the main function with, for example, #[tokio::main] to bring in an async executor. However, here too, WebAssembly is different. Instead, we must rely on the host to provide the async runtime. In our case, Cloudflare worker provides a runtime (which uses JavaScript's event loop).

• In a typical Rust program, we would have used tokio::spawn to spawn a task. However, in WebAssembly, we use wasm_bindgen_futures::spawn_local, which runs in the context of JavaScript's event loop.

We will deploy it using npx wrangler deploy. You will need to create a database and add the DATABASE_URL secret to the worker.

You can test the worker using curl:

curl https://<worker-url>

And measure the latency:

oha -c 1 -n 100 <worker-url>
...
  Slowest:      0.8975 secs
  Fastest:      0.2795 secs
  Average:      0.3441 secs

So, our worker takes an average of 345ms to respond to a request. Depending on the use case, this can be between okay-ish and unacceptable. But why is it so slow?

We are dealing with two issues here:

1. Establishing connection to the database: The worker creates a new connection for each request. Given that a secure connection, it takes 7+ round trips. Not surprisingly, latency is high.
2. Executing the query: The query method in our code causes the Rust Postgres driver to make two round trips: to prepare the statement and to bind/execute the query. It also sends a one-way message to close the prepared statement.

How can we improve? We will address that in the next post by exploring connection pooling and possible changes to the driver. Stay tuned!

The driving principle behind Exograph is to make it easy for developers to build their backends by letting them focus only on inherent—not incidental—complexity; Exograph should handle everything else. With Exograph, you can create a GraphQL server with a rich domain model with authorization logic, Postgres persistence, and JavaScript/TypeScript business logic in just a few lines of code. But what about deploying to the cloud?

The new platform-as-a-service offerings make deploying Exograph apps easy, and we make it easier by providing specific integrations. In this blog, I will focus on Railway. With its Postgres support and GitHub integration, you can create an Exograph server from scratch and deploy it in under three minutes!

A quick overview of Exograph​

Let's take a quick look at Exograph from a deployment perspective, which will make the Railway integration easy to understand. Exograph separates the build and execution steps and ships two corresponding binaries: exo and exo-server.

The exo binary Exograph's cli tool takes care of everything from building the app, migrating schema, and running integration tests to acting as a development server. It also simplifies deployment to cloud platforms, as we will see shortly.

Running an Exograph application requires that you build the app using exo build and then run the server using exo-server. The exo build command processes the index.exo file (and any files imported from it) and bundles JS/TS files. The result is an intermediate representation file: index.exo_ir.

exo build

The exo-server binary' is the runtime, whose sole purpose is to run Exograph apps. It processes the index.exo_ir file and runs the server.

exo-server

Please see the Exograph Architecture for more details.

These tools are available as Docker images, making it easy to integrate with cloud platforms and form the core of our Railway integration.

Railway Integration​

The exo deploy railway command generates a Docker file (Dockerfile.railway) and railway.toml to point to this file. The generated Docker file contains two stages:

1. Build stage: Build the app and another to run it. The build stage uses the exo Docker image to build the app and then copies the result to the runtime stage. The stage also runs database migrations.
2. Runtime stage: Runs the server. It uses the exo-server Docker image.

Deploying to Railway then involves:

• Pushing all the code to GitHub (or using railway up to push the code directly to Railway)
• Creating a Railway project
• Creating a Postgres service (unless you use an external Postgres)
• Creating a new service pointing to the GitHub repo (unless you use railway up)
• Binding environment variables for the Postgres database to the service

Let's see how this works in practice.

Creating a new Exograph project​

This is easy!

exo new todo
cd todo

This creates a new project with a simple todo app and initializes Git. If you are wondering, this doesn't generate much code. Here is the entire model!

@postgres
module TodoDatabase {
  @access(true)
  type Todo {
    @pk id: Int = autoIncrement()
    title: String
    completed: Boolean
  }
}

If you would like, you can run exo yolo to try it out locally.

Deploying to Railway​

We have a choice of using Postgres offered by Railway or an external one. Let's look at both options.

Using its Postgres​

Railway offers the Postgres database service, which has the advantage of keeping both the Exograph app and the database on the same platform.

exo deploy railway --use-railway-db=true

This will generate files necessary to deploy to Railway and provide step-by-step instructions. Here is a video of the entire process: we create an Exograph project from scratch and deploy it to Railway in under three minutes.

Using Neon's Postgres​

Using an external Postgres database is also easy and has the advantage of specialization offered by database providers. We will use Neon for this example.

Passing --use-railway-db=false to exo deploy railway will generate files for use with an external Postgres database.

exo deploy railway --use-railway-db=false

Other than using an external Postgres, the process is the same as using Railway's Postgres.

Using the playground​

Following the best practice, we do not enable introspection and playground in production. But that makes working with the GraphQL server challenging. Exograph's playground solves this problem by using schema from the local model (the app's source code) and sending requests to the remote server. This way, you can explore the API without enabling introspection and playground in production.

Let us know what you think of our Railway integration. You can reach us on Twitter or Discord.

We are excited to announce the release of Exograph 0.4! This release introduces several new features and enhancements, many in response to our users' feedback (thank you!). While we will explore some of these features in future blogs, here is a quick summary of what's new since the 0.3 version.

NPM modules support​

Exograph offers a Deno integration to write custom business logic in TypeScript or JavaScript. Until now, you could only use Deno modules. While Deno modules ecosystem continues to expand, it is not as rich as the Node ecosystem. Recognizing this, Deno added support for NPM modules to tap into the vast range of NPM packages, and Exograph 0.4 extends this support through our integration. You can now harness NPM modules to checkout with Stripe, send messages with Slack, interact with AWS, and so on.

As an illustration, here is how you would send emails using Resend. First, you would define a mutation:

@deno("email.ts")
module Email {
  @access(true)
  mutation sendAnnouncement(): Boolean
}

And implement it using the resend NPM module:

import { Resend } from "npm:resend";

const resend = new Resend("re_...");

export async function sendAnnouncement(): Promise<boolean> {
  await resend.emails.send({
    from: "...",
    to: "...",
    subject: "Exograph 0.4 is out!",
    html: "<p>Exograph <strong>0.4</strong> is out with support for npm modules, playground mode, and more!</p>",
  });

  return true;
}

Compared to the example shown on the Resend site, the difference is the npm: prefix in the import statement. This prefix tells Exograph to look for the module in the npm registry.

Railway integration​

Exograph's deployment has always been easy since the server is just a simple binary with everything needed to run. However, we strive to make it easier for specific platforms. Exograph 0.4 now supports Railway as a deployment target! Here is a quick video where we create an Exograph project from scratch and deploy it to Railway in under three minutes.

To support this kind of integration, where the cloud platform can also run the build step, we now publish two Docker images: cli with the exo binary and server with the exo-server binary.

exo playground​

A recommended practice for GraphQL deployments is to turn off introspection in production, which is the default in Exograph. However, exploring the GraphQL API with such a server becomes difficult without code completion and other goodies. Exograph 0.4 introduces a new exo playground command that uses the schema from the local server and executes GraphQL operations against the specified remote endpoint.

exo playground --endpoint https://<server-url>/graphql
Starting playground server connected to the endpoint at: https://<server-url>/graphql
- Playground hosted at:
	http://localhost:9876/playground

You will see a UI element in the playground showing the specified endpoint. Besides this difference, the playground will behave identically to the local playground, including autocomplete, schema documentation, query history, and integrated authentication.

See the video with the Railway integration above for a quick demo of the exo playground command.

Access control improvements​

Exograph offers to express access control rules precisely. In version 0.4, we enhanced this expressive power through higher-order functions. Consider a document management system where users can read documents if they have read permissions and mutate if they have written permissions. The following access control rules express this requirement.

context AuthContext {
  @jwt("sub") id: Int
}

@postgres
module DocsDatabase {
  @access(
    query = self.permissions.some(permission => permission.user.id == AuthContext.id && permission.read),
    mutation = self.permissions.some(permission => permission.user.id == AuthContext.id && permission.write)
  )
  type Document {
    @pk id: Int = autoIncrement()
    ...
    permissions: Set<Permission>
  }

  @access(...)
  type Permission {
    @pk id: Int = autoIncrement()
    document: Document
    user: User
    read: Boolean
    write: Boolean
  }

  @access(...)
  type User {
    @pk id: Int = autoIncrement()
    ...
    permissions: Set<Permission>
  }
}

With this setup, no user can read or write a document without the appropriate permission. The some function allows you to express this requirement in a single line. Internally, it lowers it down to an SQL predicate for efficient execution.

Currently, we support the some higher-order function, which matches the Array.prototype.some function in Javascript. This function takes a predicate function and returns true if the predicate function returns true for any element in the array.

Other improvements​

Besides these major features, we continue to improve Exograph to fit more use cases and simplify the developer experience.

For the Postgres plugin, for example, you can now specify that the tables could be in a non-public schema and specify deeply nested creating and updates in a single mutation. It also allows clients to supply the primary key value for UUID fields when creating an entity.

In version 0.3, we introduced a friction-free integration with Clerk for authentication. Since then, we have extended this support to Auth0 as an authentication provider! While Exograph generically supports all compliant OIDC providers, the playground integration for Clerk and Auth0 makes it easy to try out APIs that require authentication. Along the way, we updated key rotation for the OIDC authentication mechanism.

Let us know what you think of these new features and what you would like to see in the future. You can reach us on Twitter or Discord.