Struct sqlx::Pool

pub struct Pool<DB>(_) 
where
    DB: Database;

An asynchronous pool of SQLx database connections.

Create a pool with Pool::connect or Pool::connect_with and then call Pool::acquire to get a connection from the pool; when the connection is dropped it will return to the pool so it can be reused.

You can also pass &Pool directly anywhere an Executor is required; this will automatically checkout a connection for you.

See the module documentation for examples.

The pool has a maximum connection limit that it will not exceed; if acquire() is called when at this limit and all connections are checked out, the task will be made to wait until a connection becomes available.

You can configure the connection limit, and other parameters, using PoolOptions.

Calls to acquire() are fair, i.e. fulfilled on a first-come, first-serve basis.

Pool is Send, Sync and Clone. It is intended to be created once at the start of your application/daemon/web server/etc. and then shared with all tasks throughout the process’ lifetime. How best to accomplish this depends on your program architecture.

In Actix-Web, for example, you can share a single pool with all request handlers using web::Data.

Cloning Pool is cheap as it is simply a reference-counted handle to the inner pool state. When the last remaining handle to the pool is dropped, the connections owned by the pool are immediately closed (also by dropping). PoolConnection returned by Pool::acquire and Transaction returned by Pool::begin both implicitly hold a reference to the pool for their lifetimes.

If you prefer to explicitly shutdown the pool and gracefully close its connections (which depending on the database type, may include sending a message to the database server that the connection is being closed), you can call Pool::close which causes all waiting and subsequent calls to Pool::acquire to return Error::PoolClosed, and waits until all connections have been returned to the pool and gracefully closed.

Type aliases are provided for each database to make it easier to sprinkle Pool through your codebase:

• MssqlPool (MSSQL)
• MySqlPool (MySQL)
• PgPool (PostgreSQL)
• SqlitePool (SQLite)

Why Use a Pool?

A single database connection (in general) cannot be used by multiple threads simultaneously for various reasons, but an application or web server will typically need to execute numerous queries or commands concurrently (think of concurrent requests against a web server; many or all of them will probably need to hit the database).

You could place the connection in a Mutex but this will make it a huge bottleneck.

Naively, you might also think to just open a new connection per request, but this has a number of other caveats, generally due to the high overhead involved in working with a fresh connection. Examples to follow.

Connection pools facilitate reuse of connections to amortize these costs, helping to ensure that you’re not paying for them each time you need a connection.

1. Overhead of Opening a Connection

Opening a database connection is not exactly a cheap operation.

For SQLite, it means numerous requests to the filesystem and memory allocations, while for server-based databases it involves performing DNS resolution, opening a new TCP connection and allocating buffers.

Each connection involves a nontrivial allocation of resources for the database server, usually including spawning a new thread or process specifically to handle the connection, both for concurrency and isolation of faults.

Additionally, database connections typically involve a complex handshake including authentication, negotiation regarding connection parameters (default character sets, timezones, locales, supported features) and upgrades to encrypted tunnels.

If acquire() is called on a pool with all connections checked out but it is not yet at its connection limit (see next section), then a new connection is immediately opened, so this pool does not automatically save you from the overhead of creating a new connection.

However, because this pool by design enforces reuse of connections, this overhead cost is not paid each and every time you need a connection. In fact, if you set the min_connections option in PoolOptions, the pool will create that many connections up-front so that they are ready to go when a request comes in, and maintain that number on a best-effort basis for consistent performance.

2. Connection Limits (MySQL, MSSQL, Postgres)

Database servers usually place hard limits on the number of connections that are allowed open at any given time, to maintain performance targets and prevent excessive allocation of resources, such as RAM, journal files, disk caches, etc.

These limits have different defaults per database flavor, and may vary between different distributions of the same database, but are typically configurable on server start; if you’re paying for managed database hosting then the connection limit will typically vary with your pricing tier.

In MySQL, the default limit is typically 150, plus 1 which is reserved for a user with the CONNECTION_ADMIN privilege so you can still access the server to diagnose problems even with all connections being used.

In MSSQL the only documentation for the default maximum limit is that it depends on the version and server configuration.

In Postgres, the default limit is typically 100, minus 3 which are reserved for superusers (putting the default limit for unprivileged users at 97 connections).

In any case, exceeding these limits results in an error when opening a new connection, which in a web server context will turn into a 500 Internal Server Error if not handled, but should be turned into either 403 Forbidden or 429 Too Many Requests depending on your rate-limiting scheme. However, in a web context, telling a client “go away, maybe try again later” results in a sub-optimal user experience.

Instead with a connection pool, clients are made to wait in a fair queue for a connection to become available; by using a single connection pool for your whole application, you can ensure that you don’t exceed the connection limit of your database server while allowing response time to degrade gracefully at high load.

Of course, if multiple applications are connecting to the same database server, then you should ensure that the connection limits for all applications add up to your server’s maximum connections or less.

3. Resource Reuse

The first time you execute a query against your database, the database engine must first turn the SQL into an actionable query plan which it may then execute against the database. This involves parsing the SQL query, validating and analyzing it, and in the case of Postgres 12+ and SQLite, generating code to execute the query plan (native or bytecode, respectively).

These database servers provide a way to amortize this overhead by preparing the query, associating it with an object ID and placing its query plan in a cache to be referenced when it is later executed.

Prepared statements have other features, like bind parameters, which make them safer and more ergonomic to use as well. By design, SQLx pushes you towards using prepared queries/statements via the Query API et al. and the query!() macro et al., for reasons of safety, ergonomics, and efficiency.

However, because database connections are typically isolated from each other in the database server (either by threads or separate processes entirely), they don’t typically share prepared statements between connections so this work must be redone for each connection.

As with section 1, by facilitating reuse of connections, Pool helps to ensure their prepared statements (and thus cached query plans) can be reused as much as possible, thus amortizing the overhead involved.

Depending on the database server, a connection will have caches for all kinds of other data as well and queries will generally benefit from these caches being “warm” (populated with data).

Implementations

impl<DB> Pool<DB> where
    DB: Database, 

pub async fn connect(url: &'_ str) -> Result<Pool<DB>, Error>

Create a new connection pool with a default pool configuration and the given connection URL, and immediately establish one connection.

Refer to the relevant ConnectOptions impl for your database for the expected URL format:

• Postgres: PgConnectOptions
• MySQL: MySqlConnectOptions
• SQLite: SqliteConnectOptions
• MSSQL: MssqlConnectOptions

The default configuration is mainly suited for testing and light-duty applications. For production applications, you’ll likely want to make at least few tweaks.

See PoolOptions::new() for details.

pub async fn connect_with(
    options: <<DB as Database>::Connection as Connection>::Options
) -> Result<Pool<DB>, Error>

Create a new connection pool with a default pool configuration and the given ConnectOptions, and immediately establish one connection.

The default configuration is mainly suited for testing and light-duty applications. For production applications, you’ll likely want to make at least few tweaks.

See PoolOptions::new() for details.

pub fn connect_lazy(url: &str) -> Result<Pool<DB>, Error>

Create a new connection pool with a default pool configuration and the given connection URL.

The pool will establish connections only as needed.

Refer to the relevant [ConnectOptions] impl for your database for the expected URL format:

• Postgres: PgConnectOptions
• MySQL: MySqlConnectOptions
• SQLite: SqliteConnectOptions
• MSSQL: MssqlConnectOptions

The default configuration is mainly suited for testing and light-duty applications. For production applications, you’ll likely want to make at least few tweaks.

See PoolOptions::new() for details.

pub fn connect_lazy_with(
    options: <<DB as Database>::Connection as Connection>::Options
) -> Pool<DB>

Create a new connection pool with a default pool configuration and the given ConnectOptions.

The pool will establish connections only as needed.

The default configuration is mainly suited for testing and light-duty applications. For production applications, you’ll likely want to make at least few tweaks.

See PoolOptions::new() for details.

pub fn acquire(
    &self
) -> impl Future<Output = Result<PoolConnection<DB>, Error>> + 'static

Retrieves a connection from the pool.

The total time this method is allowed to execute is capped by PoolOptions::acquire_timeout. If that timeout elapses, this will return Error::PoolClosed.

Note: Cancellation/Timeout May Drop Connections

If acquire is cancelled or times out after it acquires a connection from the idle queue or opens a new one, it will drop that connection because we don’t want to assume it is safe to return to the pool, and testing it to see if it’s safe to release could introduce subtle bugs if not implemented correctly. To avoid that entirely, we’ve decided to not gracefully handle cancellation here.

However, if your workload is sensitive to dropped connections such as using an in-memory SQLite database with a pool size of 1, you can pretty easily ensure that a cancelled acquire() call will never drop connections by tweaking your PoolOptions:

• Set test_before_acquire(false)
• Never set before_acquire or after_connect.

This should eliminate any potential .await points between acquiring a connection and returning it.

pub fn try_acquire(&self) -> Option<PoolConnection<DB>>

Attempts to retrieve a connection from the pool if there is one available.

Returns None immediately if there are no idle connections available in the pool or there are tasks waiting for a connection which have yet to wake.

pub async fn begin(&'_ self) -> Result<Transaction<'static, DB>, Error>

Retrieves a connection and immediately begins a new transaction.

pub async fn try_begin(
    &'_ self
) -> Result<Option<Transaction<'static, DB>>, Error>

Attempts to retrieve a connection and immediately begins a new transaction if successful.

pub fn close(&self) -> impl Future<Output = ()>

Shut down the connection pool, immediately waking all tasks waiting for a connection.

Upon calling this method, any currently waiting or subsequent calls to Pool::acquire and the like will immediately return Error::PoolClosed and no new connections will be opened. Checked-out connections are unaffected, but will be gracefully closed on-drop rather than being returned to the pool.

Returns a Future which can be .awaited to ensure all connections are gracefully closed. It will first close any idle connections currently waiting in the pool, then wait for all checked-out connections to be returned or closed.

Waiting for connections to be gracefully closed is optional, but will allow the database server to clean up the resources sooner rather than later. This is especially important for tests that create a new pool every time, otherwise you may see errors about connection limits being exhausted even when running tests in a single thread.

If the returned Future is not run to completion, any remaining connections will be dropped when the last handle for the given pool instance is dropped, which could happen in a task spawned by Pool internally and so may be unpredictable otherwise.

.close() may be safely called and .awaited on multiple handles concurrently.

pub fn is_closed(&self) -> bool

Returns true if .close() has been called on the pool, false otherwise.

pub fn close_event(&self) -> CloseEvent

Get a future that resolves when Pool::close() is called.

If the pool is already closed, the future resolves immediately.

This can be used to cancel long-running operations that hold onto a PoolConnection so they don’t prevent the pool from closing (which would otherwise wait until all connections are returned).

Examples

These examples use Postgres and Tokio, but should suffice to demonstrate the concept.

Do something when the pool is closed:

use sqlx::PgPool;

let pool = PgPool::connect("postgresql://...").await?;

let pool2 = pool.clone();

tokio::spawn(async move {
    // Demonstrates that `CloseEvent` is itself a `Future` you can wait on.
    // This lets you implement any kind of on-close event that you like.
    pool2.close_event().await;

    println!("Pool is closing!");

    // Imagine maybe recording application statistics or logging a report, etc.
});

// The rest of the application executes normally...

// Close the pool before the application exits...
pool.close().await;

Cancel a long-running operation:

use sqlx::{Executor, PgPool};

let pool = PgPool::connect("postgresql://...").await?;

let pool2 = pool.clone();

tokio::spawn(async move {
    pool2.close_event().do_until(async {
        // This statement normally won't return for 30 days!
        // (Assuming the connection doesn't time out first, of course.)
        pool2.execute("SELECT pg_sleep('30 days')").await;

        // If the pool is closed before the statement completes, this won't be printed.
        // This is because `.do_until()` cancels the future it's given if the
        // pool is closed first.
        println!("Waited!");
    }).await;
});

// This normally wouldn't return until the above statement completed and the connection
// was returned to the pool. However, thanks to `.do_until()`, the operation was
// cancelled as soon as we called `.close().await`.
pool.close().await;

pub fn size(&self) -> u32

Returns the number of connections currently active. This includes idle connections.

pub fn num_idle(&self) -> usize

Returns the number of connections active and idle (not in use).

As of 0.6.0, this has been fixed to use a separate atomic counter and so should be fine to call even at high load.

This previously called [crossbeam::queue::ArrayQueue::len()] which waits for the head and tail pointers to be in a consistent state, which may never happen at high levels of churn.

pub fn connect_options(
    &self
) -> &<<DB as Database>::Connection as Connection>::Options

Get the connection options for this pool

pub fn options(&self) -> &PoolOptions<DB>

Get the options for this pool

impl Pool<Any>

pub fn any_kind(&self) -> AnyKind

Returns the database driver currently in-use by this Pool.

Determined by the connection URL.

impl Pool<Postgres>

pub async fn copy_in_raw(
    &'_ self,
    statement: &'_ str
) -> Result<PgCopyIn<PoolConnection<Postgres>>, Error>

Issue a COPY FROM STDIN statement and begin streaming data to Postgres. This is a more efficient way to import data into Postgres as compared to INSERT but requires one of a few specific data formats (text/CSV/binary).

A single connection will be checked out for the duration.

If statement is anything other than a COPY ... FROM STDIN ... command, an error is returned.

Command examples and accepted formats for COPY data are shown here: https://www.postgresql.org/docs/current/sql-copy.html

Note

PgCopyIn::finish or PgCopyIn::abort must be called when finished or the connection will return an error the next time it is used.

pub async fn copy_out_raw(
    &'_ self,
    statement: &'_ str
) -> Result<Pin<Box<dyn Stream<Item = Result<Bytes, Error>> + Send + 'static, Global>>, Error>

Issue a COPY TO STDOUT statement and begin streaming data from Postgres. This is a more efficient way to export data from Postgres but arrives in chunks of one of a few data formats (text/CSV/binary).

If statement is anything other than a COPY ... TO STDOUT ... command, an error is returned.

Note that once this process has begun, unless you read the stream to completion, it can only be canceled in two ways:

1. by closing the connection, or:
2. by using another connection to kill the server process that is sending the data as shown in this StackOverflow answer.

If you don’t read the stream to completion, the next time the connection is used it will need to read and discard all the remaining queued data, which could take some time.

Command examples and accepted formats for COPY data are shown here: https://www.postgresql.org/docs/current/sql-copy.html

Trait Implementations

impl<'a, '_, DB> Acquire<'a> for &'_ Pool<DB> where
    DB: Database, 

type Database = DB

type Connection = PoolConnection<DB>

fn acquire(
    self
) -> Pin<Box<dyn Future<Output = Result<<&'_ Pool<DB> as Acquire<'a>>::Connection, Error>> + Send + 'static, Global>>

fn begin(
    self
) -> Pin<Box<dyn Future<Output = Result<Transaction<'a, DB>, Error>> + Send + 'static, Global>>

impl<DB> Clone for Pool<DB> where
    DB: Database, 

Returns a new Pool tied to the same shared connection pool.

fn clone(&self) -> Pool<DB>

Returns a copy of the value.

fn clone_from(&mut self, source: &Self)

Performs copy-assignment from source.

impl<DB> Debug for Pool<DB> where
    DB: Database, 

fn fmt(&self, fmt: &mut Formatter<'_>) -> Result<(), Error>

Formats the value using the given formatter.

impl<'p, '_, DB> Executor<'p> for &'_ Pool<DB> where
    DB: Database,
    &'c mut <DB as Database>::Connection: for<'c> Executor<'c>,
    <&'c mut <DB as Database>::Connection as Executor<'c>>::Database == DB, 

type Database = DB

fn fetch_many<'e, 'q, E>(
    self,
    query: E
) -> Pin<Box<dyn Stream<Item = Result<Either<<DB as Database>::QueryResult, <DB as Database>::Row>, Error>> + Send + 'e, Global>> where
    'q: 'e,
    E: 'q + Execute<'q, <&'_ Pool<DB> as Executor<'p>>::Database>, 

Execute multiple queries and return the generated results as a stream from each query, in a stream.

fn fetch_optional<'e, 'q, E>(
    self,
    query: E
) -> Pin<Box<dyn Future<Output = Result<Option<<DB as Database>::Row>, Error>> + Send + 'e, Global>> where
    'q: 'e,
    E: 'q + Execute<'q, <&'_ Pool<DB> as Executor<'p>>::Database>, 

Execute the query and returns at most one row.

fn prepare_with<'e, 'q>(
    self,
    sql: &'q str,
    parameters: &'e [<<&'_ Pool<DB> as Executor<'p>>::Database as Database>::TypeInfo]
) -> Pin<Box<dyn Future<Output = Result<<<&'_ Pool<DB> as Executor<'p>>::Database as HasStatement<'q>>::Statement, Error>> + Send + 'e, Global>> where
    'q: 'e, 

Prepare the SQL query, with parameter type information, to inspect the type information about its parameters and results.

fn execute<'e, 'q, E>(
    self,
    query: E
) -> Pin<Box<dyn Future<Output = Result<<Self::Database as Database>::QueryResult, Error>> + Send + 'e, Global>> where
    'q: 'e,
    'c: 'e,
    E: 'q + Execute<'q, Self::Database>, 

Execute the query and return the total number of rows affected.

fn execute_many<'e, 'q, E>(
    self,
    query: E
) -> Pin<Box<dyn Stream<Item = Result<<Self::Database as Database>::QueryResult, Error>> + Send + 'e, Global>> where
    'q: 'e,
    'c: 'e,
    E: 'q + Execute<'q, Self::Database>, 

Execute multiple queries and return the rows affected from each query, in a stream.

fn fetch<'e, 'q, E>(
    self,
    query: E
) -> Pin<Box<dyn Stream<Item = Result<<Self::Database as Database>::Row, Error>> + Send + 'e, Global>> where
    'q: 'e,
    'c: 'e,
    E: 'q + Execute<'q, Self::Database>, 

Execute the query and return the generated results as a stream.

fn fetch_all<'e, 'q, E>(
    self,
    query: E
) -> Pin<Box<dyn Future<Output = Result<Vec<<Self::Database as Database>::Row, Global>, Error>> + Send + 'e, Global>> where
    'q: 'e,
    'c: 'e,
    E: 'q + Execute<'q, Self::Database>, 

Execute the query and return all the generated results, collected into a Vec.

fn fetch_one<'e, 'q, E>(
    self,
    query: E
) -> Pin<Box<dyn Future<Output = Result<<Self::Database as Database>::Row, Error>> + Send + 'e, Global>> where
    'q: 'e,
    'c: 'e,
    E: 'q + Execute<'q, Self::Database>, 

Execute the query and returns exactly one row.

fn prepare<'e, 'q>(
    self,
    query: &'q str
) -> Pin<Box<dyn Future<Output = Result<<Self::Database as HasStatement<'q>>::Statement, Error>> + Send + 'e, Global>> where
    'q: 'e,
    'c: 'e, 

Prepare the SQL query to inspect the type information of its parameters and results.