Oat++ ORM | Oat++ (original) (raw)

# Object-Relational Mapping (ORM) Framework

Oat++ ORM framework is a set of generalized interfaces and their implementations to make it easy to work with databases.

It's based on an object-mapping framework and ensures data consistency when manipulating with data. Also, it integrates perfectly with other Oat++ components ensuring seamless data-flow in the application (example: from REST to database, from the database to REST).

• High-Level Overview
  • Declare DbClient
  • Create DbClient Component And Connect to Database
  • DbClient Usage Example
• Supported Databases
  • Available Database Adaptors
  • Libraries Hierarchy
• DbClient
  • Declare a Query
  • Query With Parameters
  • Query With DTO as a Parameter
  • Query With Prepared Statement
  • Execute An Arbitrary Query
  • Enable Type Interpretations
  • Transactions
• Executing Queries
  • Mapping Results
  • Managing Connections
• Connection Pool
• Schema Migration
  • Overview
  • Schema Name
• Examples projects

# High-Level Overview

# Declare DbClient

The main component you are going to work with is the DbClient. You may treat it as the main point interfacing with your data. Here you declare database queries and manage database schema migrations.

Database queries are declared with the help of code-gen macros.
DbClient code generation section must begin with#include OATPP_CODEGEN_BEGIN(DbClient) and must be closed with#include OATPP_CODEGEN_END(DbClient).
Do not forget to close the code generation section in order to avoid macro conflicts later in the code!

# Create DbClient Component And Connect to Database

DbClient is a heavy object - you want to instantiate it once and then inject it in whatever places you are going to use it.

Note:

• ConnectionProvider and ConnectionPool objects can be reused by multiple Executors unless it's prohibited by a database-specific implementation.
• Executor can be reused by multiple DbClients unless it's prohibited by a database-specific implementation.

# DbClient Usage Example

Output:

# Supported Databases

# Available Database Adaptors

# Libraries Hierarchy

The main oatpp module contains ORM interfaces only. In order to "plug" a specific database, you have to link the corresponding adaptor (ex.: oatpp-sqlite).

# DbClient

# Declare a Query

# Query With Parameters

During execution the expression username=:username will be changed to username='<username-parameter-value>' and parameter value will be properly escaped according to its type.

# Query With DTO as a Parameter

For complex queries, it's more convenient to use DTO objects as for parameters set. Thus you ensure the correct order of arguments.

Note:
The query template variable names are now starting with user, like user.username - where user is the name of the DTO parameter, and username is the name of DTO field.

• Yes, you can specify a path to nested DTO fields like :user.path.to.nested.field.
• Yes, you can have multiple DTO parameters in the query, and you can mix DTO parameters with regular parameters.

# Query With Prepared Statement

Note:
The database adapter may ignore this. For example:

• SQLite is always using prepared statements to execute queries thus oatpp-sqlite will ignore this parameter.
• PostgreSQL has a special method to execute prepared statements thus oatpp-postgresql will not ignore this parameter.

# Execute An Arbitrary Query

To execute an arbitrary query use DbClient::executeQuery() method.
Use this method when it's needed to dynamically build a query.

You can add parameters using parameters map:

When building parameters map dynamically you have to use std::unordered_map::insert() method.
The [] operator WON'T work.

To build a query string it's recommended to use oatpp::data::stream::BufferOutputStream.

# Enable Type Interpretations

When using custom or non-standard types as parameters in QUERY macro, as well as when reading query results to custom/non-standard structures, you have to explicitly enable corresponding type interpretations.

The recommended place to do it - is the constructor:

# Query With Custom Type Parameter

# Map Query Result To Custom Type

# Transactions

Use DbClient::beginTransaction() method to begin a transaction.
All queries MUST be executed on the same transaction connection.

Note:
Transaction will be automatically rollback if Transaction::commit() method was not called.

# Executing Queries

The queryResult here is the oatpp::orm::QueryResult object. All queries return oatpp::orm::QueryResult.

# Mapping Results

Available result mappings depend on the database adapter but here are some examples (that work for oatpp-sqlite and oatpp-postgresql)...

# Map everything using previously decalred UserDto and display results

For more info on how to declare a DTO - see oatpp::DTO

Output:

# Map everything using oatpp::Any and display results

Output:

# Managing Connections

All declared queries have an oatpp::orm::Connection as the last parameter.
If the connection is not specified(nullptr), then the new connection will be opened to execute that query.

Note:

The queryResult object holds a connection. The connection won't return to the connection pool until queryResult is destroyed.

# Connection Pool

It's always a good idea to use a connection pool when working with a database.

**Note:**SQLite is used as an example here. For other databases declaration is similar.

# Schema Migration

Use SchemaMigration to do schema migrations!
The recommended place to do schema migrations is the constructor of your DbClient.

# Overview

Note:

• Version MUST start from 1.
• Version MUST be incremented by 1.
• In case of an error changes will be rolled back to the last successfully applied version.

# Schema Name

If you have multiple Schemas in your database you can manage migrations of each one independently. For this specify a version control table suffix:

**Note:**It is recommended to have one DbClient per schema!

# Examples projects

• example-crud - Using oatpp ORM with SQLite.
• example-postgresql - Using oatpp ORM with PostgreSQL.