External SQL/YQL files

You may generate SQL queries or YQL queries (for YDB) from external .sql/.yql files. To do this, call the following cmake function in your CMakeLists.txt:

userver_add_sql_library(
    ${PROJECT_NAME}_sql
    NAMESPACE samples_postgres_service
    OUTPUT_DIR ${CMAKE_CURRENT_BINARY_DIR}
    SQL_FILES *.sql
)
target_link_libraries(${PROJECT_NAME} ${PROJECT_NAME}_sql)

It will generate the samples_postgres_service/sql_queries.hpp file with following variable:

namespace samples_postgres_service::sql {
 
extern const USERVER_NAMESPACE::storages::Query kDeleteValue;
 
extern const USERVER_NAMESPACE::storages::Query kInsertValue;
 
extern const USERVER_NAMESPACE::storages::Query kSelectValue;
 
}

And the definition for each statement in samples_postgres_service/sql_queries.cpp looks something like that:

const USERVER_NAMESPACE::storages::Query kSelectValue = {
    R"-(
    SELECT value FROM key_value_table WHERE key=$1
    )-",
    USERVER_NAMESPACE::storages::Query::Name("select_value"),
    USERVER_NAMESPACE::storages::Query::LogMode::kFull,
};

You may use it as usual by passing to storages::postgres::Cluster::Execute() for SQL files or ydb::TableClient::ExecuteDataQuery() for YQL files:

#include <samples_postgres_service/sql_queries.hpp>
 
namespace samples_postgres_service {
    ...
    auto result = trx->Execute(sql::kCreateTable);
    ...
}

While writing tests, you can check the coverage of your SQL/YQL queries using the sql_coverage plugin.

To use it, you need to pass the target with generated queries to the userver_testsuite_add_simple (or userver_testsuite_add) function in your CMakeLists.txt:

userver_testsuite_add_simple(SQL_LIBRARY ${PROJECT_NAME}_sql)

It will enable the sql_coverage plugin and add coverage test that will run with the other tests.

---

⇦ JSON schema codegen - the Chaotic | Unit Tests and Benchmarks ⇨