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
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.
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:
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 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.
All the values are described in a separate section Dynamic configs .
A production ready service would dynamically retrieve the above options at runtime from a configuration service. See Writing your own configs server for insights on how to change the above options on the fly, without restarting the service.
int main()
Finally, after writing down the dynamic config values into file at dynamic-config-fallbacks.fallback-path, we add our component to the components::MinimalServerComponentList(), and start the server with static config kStaticConfig.
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> (do not forget to prepare the configuration files!).
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