PostgreSQL service

Before you start

Make sure that you can compile and run core tests and read a basic example Writing your first HTTP server.

Note that there is a ready to use opensource pg service template to ease the development of your userver based services that use PostgreSQL database. The template already has a preconfigured CI, build and install scripts, testsuite and unit-tests setups.

Step by step guide

Microservices that have state often work with database to store their data and replicate that state across instances of the microservice. In this tutorial we will write a service that is a simple key-value storage on top of PostgreSQL database. The service would have the following Rest API:

• HTTP POST by path '/v1/key-value' with query parameters 'key' and 'value' stores the provided key and value or return an existing value for the key
• HTTP GET by path '/v1/key-value' with query parameter 'key' returns the value if it exists
• HTTP DELETE by path '/v1/key-value' with query parameter 'key' deletes the key if it exists and returns number of deleted keys

HTTP handler component

Like in Writing your first HTTP server we create a component for handling HTTP requests:

#include <userver/components/component.hpp>
#include <userver/components/minimal_server_component_list.hpp>
#include <userver/server/handlers/http_handler_base.hpp>
#include <userver/utils/daemon_run.hpp>
 
#include <userver/storages/postgres/cluster.hpp>
#include <userver/storages/postgres/component.hpp>
 
#include <samples_postgres_service/sql_queries.hpp>
 
namespace samples_postgres_service::pg {
 
class KeyValue final : public server::handlers::HttpHandlerBase {
public:
    static constexpr std::string_view kName = "handler-key-value";
 
    KeyValue(const components::ComponentConfig& config, const components::ComponentContext& context);
 
    std::string HandleRequest(server::http::HttpRequest& request, server::request::RequestContext&) const override;
 
private:
    std::string GetValue(std::string_view key, const server::http::HttpRequest& request) const;
    std::string PostValue(std::string_view key, const server::http::HttpRequest& request) const;
    std::string DeleteValue(std::string_view key) const;
 
    storages::postgres::ClusterPtr pg_cluster_;
};
 
}  // namespace samples_postgres_service::pg

Note that the component holds a storages::postgres::ClusterPtr - a client to the PostgreSQL DB. That client is thread safe, you can use it concurrently from different threads and tasks.

Initializing the database

To access the database from our new component we need to find the PostgreSQL component and request a client to the DB. After that we may create the required tables.

KeyValue::KeyValue(const components::ComponentConfig& config, const components::ComponentContext& context)
    : HttpHandlerBase(config, context),
      pg_cluster_(context.FindComponent<components::Postgres>("key-value-database").GetCluster()) {
    using storages::postgres::ClusterHostType;
    pg_cluster_->Execute(ClusterHostType::kMaster, sql::kCreateTable);
}

To create a table we just execute an SQL statement, mentioning that it should go to the master instance. After that, our component is ready to process incoming requests in the KeyValue::HandleRequestThrow function.

KeyValue::HandleRequestThrow

In this sample we use a single handler to deal with all the HTTP methods. The KeyValue::HandleRequestThrow member function mostly dispatches the request to one of the member functions that actually implement the key-value storage logic:

std::string KeyValue::HandleRequest(server::http::HttpRequest& request, server::request::RequestContext&) const {
    const auto& key = request.GetArg("key");
    if (key.empty()) {
        throw server::handlers::ClientError(server::handlers::ExternalBody{"No 'key' query argument"});
    }
 
    request.GetHttpResponse().SetContentType(http::content_type::kTextPlain);
    switch (request.GetMethod()) {
        case server::http::HttpMethod::kGet:
            return GetValue(key, request);
        case server::http::HttpMethod::kPost:
            return PostValue(key, request);
        case server::http::HttpMethod::kDelete:
            return DeleteValue(key);
        default:
            throw server::handlers::ClientError(server::handlers::ExternalBody{
                fmt::format("Unsupported method {}", request.GetMethod())});
    }
}

Warning

Handle* functions are invoked concurrently on the same instance of the handler class. In this sample the KeyValue component only uses the thread safe DB client. In more complex cases synchronization primitives should be used or data must not be mutated.

KeyValue::GetValue

Postgres driver in userver implicitly works with prepared statements. For the first time you execute the query a prepared statement is created. Executing the query next time would result in only sending the arguments for the already created prepared statement.

You could pass strings as queries, just like we done in constructor of KeyValue. Also queries could be stored in variables along with query names and reused across the functions. Name of the query could be used in dynamic configs to set the execution timeouts (see POSTGRES_QUERIES_COMMAND_CONTROL). You can ease query definition by using External SQL/YQL files.

const storages::postgres::Query kSelectValue{
    "SELECT value FROM key_value_table WHERE key=$1",
    storages::postgres::Query::Name{"sample_select_value"},
};
 
std::string KeyValue::GetValue(std::string_view key, const server::http::HttpRequest& request) const {
    storages::postgres::ResultSet res =
        pg_cluster_->Execute(storages::postgres::ClusterHostType::kSlave, kSelectValue, key);
    if (res.IsEmpty()) {
        request.SetResponseStatus(server::http::HttpStatus::kNotFound);
        return {};
    }
 
    return res.AsSingleRow<std::string>();
}

KeyValue::PostValue

You can start a transaction by calling storages::postgres::Cluster::Begin(). Transactions are automatically rolled back, if you do not commit them. To execute a query in transaction, just call Execute member function of a transaction. Just like with non-transactional Execute, you can pass string or storages::postgres::Query, you could reuse the same query in different functions. Transactions also could be named, and those names could be used in POSTGRES_QUERIES_COMMAND_CONTROL. You can ease query definition by using External SQL/YQL files.

const storages::postgres::Query kInsertValue{
    "INSERT INTO key_value_table (key, value) "
    "VALUES ($1, $2) "
    "ON CONFLICT DO NOTHING",
    storages::postgres::Query::Name{"sample_insert_value"},
};
 
std::string KeyValue::PostValue(std::string_view key, const server::http::HttpRequest& request) const {
    const auto& value = request.GetArg("value");
 
    storages::postgres::Transaction transaction =
        pg_cluster_->Begin("sample_transaction_insert_key_value", storages::postgres::ClusterHostType::kMaster, {});
 
    auto res = transaction.Execute(kInsertValue, key, value);
    if (res.RowsAffected()) {
        transaction.Commit();
        request.SetResponseStatus(server::http::HttpStatus::kCreated);
        return std::string{value};
    }
 
    res = transaction.Execute(kSelectValue, key);
    transaction.Rollback();
 
    auto result = res.AsSingleRow<std::string>();
    if (result != value) {
        request.SetResponseStatus(server::http::HttpStatus::kConflict);
    }
 
    return res.AsSingleRow<std::string>();
}

KeyValue::DeleteValue

Note that mutating queries should be executed on a master instance.

std::string KeyValue::DeleteValue(std::string_view key) const {
    auto res = pg_cluster_->Execute(
        storages::postgres::ClusterHostType::kMaster, "DELETE FROM key_value_table WHERE key=$1", key
    );
    return std::to_string(res.RowsAffected());
}

Static config

Static configuration of service is quite close to the configuration from Writing your first HTTP server except for the handler and DB:

# yaml
components_manager:
    components:                       # Configuring components that were registered via component_list
        handler-key-value:
            path: /v1/key-value                  # Registering handler by URL '/v1/key-value'.
            task_processor: main-task-processor  # Run it on CPU bound task processor
            method: GET,DELETE,POST
 
        key-value-database:
            dbconnection: 'postgresql://testsuite@localhost:15433/postgres'
            blocking_task_processor: fs-task-processor
            dns_resolver: async
 
        dynamic-config:                      # Dynamic config storage options
            defaults:
                POSTGRES_CONNECTION_POOL_SETTINGS:
                    key-value-database:
                        max_pool_size: 15
                        max_queue_size: 200
                        min_pool_size: 8
                POSTGRES_HANDLERS_COMMAND_CONTROL:
                    /v1/key-value:
                        DELETE:
                            network_timeout_ms: 500
                            statement_timeout_ms: 250
                POSTGRES_QUERIES_COMMAND_CONTROL:
                    sample_select_value:
                        network_timeout_ms: 70
                        statement_timeout_ms: 40
                    sample_transaction_insert_key_value:
                        network_timeout_ms: 200
                        statement_timeout_ms: 150
                POSTGRES_STATEMENT_METRICS_SETTINGS:
                    key-value-database:
                        max_statement_metrics: 5
 
        server:
            # ...

Note the dynamic-config.defaults usage. In production ready service those values are usually retrieved from remote server, so that they could be changed at runtime without any need to restart the service. See Dynamic config schemas for more info.

int main()

Finally, we add our component to the components::MinimalServerComponentList(), and start the server with static config kStaticConfig.

int main(int argc, char* argv[]) {
    const auto component_list = components::MinimalServerComponentList()
                                    .Append<samples_postgres_service::pg::KeyValue>()
                                    .Append<components::Postgres>("key-value-database")
                                    .Append<components::TestsuiteSupport>()
                                    .Append<clients::dns::Component>();
    return utils::DaemonMain(argc, argv, component_list);
}

Build and Run

To build the sample, execute the following build steps at the userver root directory:

mkdir build_release
cd build_release
cmake -DCMAKE_BUILD_TYPE=Release ..
make userver-samples-postgres_service

The sample could be started by running make start-userver-samples-postgres_service. The command would invoke testsuite start target that sets proper paths in the configuration files, prepares and starts the DB, and starts the service.

To start the service manually start the DB server and run ./samples/postgres_service/userver-samples-postgres_service -c </path/to/static_config.yaml>.

Now you can send a request to your service from another terminal:

bash
$ curl -X POST 'localhost:8085/v1/key-value?key=hello&value=world' -i
HTTP/1.1 201 Created
Date: Wed, 27 Oct 2021 16:45:13 UTC
Content-Type: text/html
X-YaSpanId: 015fb0becd2926ef
X-YaRequestId: 7830671d7dd2462ba9043db532c2b82a
Server: userver/2.0 (20211027123413; rv:c1879aa03)
X-YaTraceId: d7422d7bcdc9493997fc687f8be24883
Connection: keep-alive
Content-Length: 5
 
world
$ curl -X POST 'localhost:8085/v1/key-value?key=hello&value=nope' -i
HTTP/1.1 409 Conflict
Date: Wed, 27 Oct 2021 16:45:56 UTC
Content-Type: text/html
X-YaSpanId: e1e2702b87ceeede
X-YaRequestId: 4f677a7cd405418ea412fd4ec540676a
Server: userver/2.0 (20211027123413; rv:c1879aa03)
X-YaTraceId: 203870322f704b308c4322bd44b354ed
Connection: keep-alive
Content-Length: 5
 
world
$ curl -X DELETE 'localhost:8085/v1/key-value?key=hello&value=world' -i
HTTP/1.1 200 OK
Date: Wed, 27 Oct 2021 16:46:35 UTC
Content-Type: text/html
X-YaSpanId: e83698e2ef8cc729
X-YaRequestId: ffbaacae38e64bb588affa10b928b759
Server: userver/2.0 (20211027123413; rv:c1879aa03)
X-YaTraceId: cd3e6acc299742739bb22c795b6ef3a7
Connection: keep-alive
Content-Length: 1
 
1

Functional testing

Functional tests for the service could be implemented using the testsuite. To do that you have to:

• Turn on the pytest_userver.plugins.postgresql plugin and provide PostgreSQL schema to start the database:

  import pytest
   
  from testsuite.databases.pgsql import discover
   
  pytest_plugins = ['pytest_userver.plugins.postgresql']
   
   
  @pytest.fixture(scope='session')
  def pgsql_local(service_source_dir, pgsql_local_create):
      databases = discover.find_schemas(
          'admin', [service_source_dir.joinpath('schemas/postgresql')],
      )
      return pgsql_local_create(list(databases.values()))

  The auto_client_deps fixture already knows about the pgsql fixture, so there's no need to override the extra_client_deps fixture.
• Write the test:

  async def test_postgres(service_client, pgsql):
      # POSTing 'key=hello&value=world'
      response = await service_client.post('/v1/key-value?key=hello&value=world')
      assert response.status == 201
      assert 'text/plain' in response.headers['Content-Type']
      assert response.text == 'world'
   
      # Checking content of the database via direct access.
      # `pgsql.keys()` outputs all the databases
      cursor = pgsql['admin'].cursor()  # 'admin.sql' file contains the schema.
      cursor.execute("SELECT value FROM key_value_table WHERE key='hello'")
      result = cursor.fetchall()
      assert len(result) == 1
      assert result[0][0] == 'world'
   
      # Getting key=hello
      response = await service_client.get('/v1/key-value?key=hello')
      assert response.status == 200
      assert 'text/plain' in response.headers['Content-Type']
      assert response.text == 'world'
   
      # Deleting key=hello
      response = await service_client.delete('/v1/key-value?key=hello')
      assert response.status == 200
   
      # Make sure database is does not store key=hello
      cursor.execute("SELECT value FROM key_value_table WHERE key='hello'")
      assert len(cursor.fetchall()) == 0

Full sources

See the full example:

• samples/postgres_service/postgres_service.cpp
• samples/postgres_service/static_config.yaml
• samples/postgres_service/CMakeLists.txt
• samples/postgres_service/tests/conftest.py
• samples/postgres_service/tests/test_postgres.py

---

⇦ gRPC middleware | MongoDB service ⇨