Library

CppDB

Overview

CppDB is a lightweight SQL connectivity library for C++ that provides platform and database independent connectivity API similar to what JDBC, ODBC and other connectivity libraries do. Developed as part of the CppCMS Project, it simplifies database operations in C++ applications. Supporting major databases including MySQL, PostgreSQL, SQLite3, and Oracle, it achieves high security and performance through prepared statements as a lightweight database access library.

Details

CppDB is designed as a lightweight bridge between raw SQL queries and C++ objects rather than a full ORM framework. It emphasizes simple and direct database access over complex object mapping, allowing developers to maintain control over SQL while enjoying type safety and memory safety. With prepared statement support, it prevents SQL injection attacks and improves performance. It comprehensively provides practical features necessary for database access including connection pooling, transaction management, and exception handling.

Key Features

• Lightweight Design: Achieves fast database access with minimal dependencies
• Cross-platform: Supports operation on Windows, Linux, and macOS
• Multi-database Support: Compatible with MySQL, PostgreSQL, SQLite3, and Oracle
• Prepared Statements: SQL injection prevention and performance improvement
• Connection Pooling: Efficient resource management and scalability
• Type Safety: Type-safe data access leveraging C++'s type system

Pros and Cons

Pros

• Simple with low learning curve, intuitive for C++ developers
• Lightweight with minimal overhead, suitable for embedded applications
• Can write raw SQL directly, enabling advanced query optimization
• High security level through prepared statements
• High portability with support for multiple database engines
• Powerful for web application development through CppCMS integration

Cons

• Not a complete ORM, requiring manual implementation for complex relationship mapping
• Limited automatic schema generation and migration capabilities
• Potentially insufficient features for large enterprise applications
• Fewer documentation and learning resources compared to other C++ ORMs
• No high-level abstractions like active record patterns provided
• Small community with limited third-party tools

Reference Pages

• CppDB Official Site
• CppDB GitHub
• CppDB Documentation

Code Examples

Basic Setup

#include <cppdb/frontend.h>
#include <iostream>

int main() {
    try {
        // Establish database connection
        cppdb::session sql("mysql:host=localhost;database=test;user=root;password=password");
        
        // Verify connection
        std::cout << "Successfully connected to database" << std::endl;
        
    } catch(std::exception const &e) {
        std::cerr << "Error: " << e.what() << std::endl;
        return 1;
    }
    return 0;
}

Model Definition and Basic Operations

#include <cppdb/frontend.h>
#include <string>

struct User {
    int id;
    std::string name;
    std::string email;
    int age;
};

class UserRepository {
private:
    cppdb::session sql;
    
public:
    UserRepository(const std::string& connection_string) 
        : sql(connection_string) {}
    
    // Create user
    bool createUser(const User& user) {
        try {
            cppdb::statement st = sql << 
                "INSERT INTO users (name, email, age) VALUES (?, ?, ?)"
                << user.name << user.email << user.age;
            st.exec();
            return true;
        } catch(std::exception const &e) {
            std::cerr << "User creation error: " << e.what() << std::endl;
            return false;
        }
    }
    
    // Get user
    bool getUser(int id, User& user) {
        try {
            cppdb::result res = sql << 
                "SELECT id, name, email, age FROM users WHERE id = ?" << id;
            
            if(res.next()) {
                res >> user.id >> user.name >> user.email >> user.age;
                return true;
            }
            return false;
        } catch(std::exception const &e) {
            std::cerr << "User retrieval error: " << e.what() << std::endl;
            return false;
        }
    }
    
    // Update user
    bool updateUser(const User& user) {
        try {
            cppdb::statement st = sql << 
                "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?"
                << user.name << user.email << user.age << user.id;
            st.exec();
            return st.affected() > 0;
        } catch(std::exception const &e) {
            std::cerr << "User update error: " << e.what() << std::endl;
            return false;
        }
    }
    
    // Delete user
    bool deleteUser(int id) {
        try {
            cppdb::statement st = sql << "DELETE FROM users WHERE id = ?" << id;
            st.exec();
            return st.affected() > 0;
        } catch(std::exception const &e) {
            std::cerr << "User deletion error: " << e.what() << std::endl;
            return false;
        }
    }
};

Advanced Query Operations

#include <cppdb/frontend.h>
#include <vector>

class AdvancedUserOperations {
private:
    cppdb::session sql;
    
public:
    AdvancedUserOperations(const std::string& connection_string) 
        : sql(connection_string) {}
    
    // Get multiple users with conditions
    std::vector<User> getUsersByAge(int min_age, int max_age) {
        std::vector<User> users;
        try {
            cppdb::result res = sql << 
                "SELECT id, name, email, age FROM users WHERE age BETWEEN ? AND ? ORDER BY age"
                << min_age << max_age;
            
            while(res.next()) {
                User user;
                res >> user.id >> user.name >> user.email >> user.age;
                users.push_back(user);
            }
        } catch(std::exception const &e) {
            std::cerr << "User search error: " << e.what() << std::endl;
        }
        return users;
    }
    
    // Paginated query
    std::vector<User> getUsersWithPagination(int page, int limit) {
        std::vector<User> users;
        int offset = (page - 1) * limit;
        
        try {
            cppdb::result res = sql << 
                "SELECT id, name, email, age FROM users ORDER BY id LIMIT ? OFFSET ?"
                << limit << offset;
            
            while(res.next()) {
                User user;
                res >> user.id >> user.name >> user.email >> user.age;
                users.push_back(user);
            }
        } catch(std::exception const &e) {
            std::cerr << "Pagination error: " << e.what() << std::endl;
        }
        return users;
    }
    
    // Aggregate query
    struct UserStatistics {
        int total_users;
        double average_age;
        int min_age;
        int max_age;
    };
    
    UserStatistics getUserStatistics() {
        UserStatistics stats = {0, 0.0, 0, 0};
        try {
            cppdb::result res = sql << 
                "SELECT COUNT(*), AVG(age), MIN(age), MAX(age) FROM users";
            
            if(res.next()) {
                res >> stats.total_users >> stats.average_age >> stats.min_age >> stats.max_age;
            }
        } catch(std::exception const &e) {
            std::cerr << "Statistics retrieval error: " << e.what() << std::endl;
        }
        return stats;
    }
};

Relationship Operations

#include <cppdb/frontend.h>

struct Post {
    int id;
    std::string title;
    std::string content;
    int user_id;
    std::string created_at;
};

struct UserWithPosts {
    User user;
    std::vector<Post> posts;
};

class RelationOperations {
private:
    cppdb::session sql;
    
public:
    RelationOperations(const std::string& connection_string) 
        : sql(connection_string) {}
    
    // Get users with posts using JOIN
    std::vector<UserWithPosts> getUsersWithPosts() {
        std::map<int, UserWithPosts> user_map;
        
        try {
            cppdb::result res = sql << 
                "SELECT u.id, u.name, u.email, u.age, "
                "       p.id, p.title, p.content, p.created_at "
                "FROM users u "
                "LEFT JOIN posts p ON u.id = p.user_id "
                "ORDER BY u.id, p.id";
            
            while(res.next()) {
                int user_id;
                User user;
                res >> user.id >> user.name >> user.email >> user.age;
                
                // Add user to map if first time
                if(user_map.find(user.id) == user_map.end()) {
                    UserWithPosts uwp;
                    uwp.user = user;
                    user_map[user.id] = uwp;
                }
                
                // Add post if exists
                Post post;
                if(!res.is_null(4)) { // if post.id is not null
                    res >> post.id >> post.title >> post.content >> post.created_at;
                    post.user_id = user.id;
                    user_map[user.id].posts.push_back(post);
                }
            }
        } catch(std::exception const &e) {
            std::cerr << "Related data retrieval error: " << e.what() << std::endl;
        }
        
        // Convert map to vector
        std::vector<UserWithPosts> result;
        for(auto& pair : user_map) {
            result.push_back(pair.second);
        }
        return result;
    }
    
    // Get posts by specific user ID
    std::vector<Post> getPostsByUserId(int user_id) {
        std::vector<Post> posts;
        try {
            cppdb::result res = sql << 
                "SELECT id, title, content, user_id, created_at FROM posts WHERE user_id = ? ORDER BY created_at DESC"
                << user_id;
            
            while(res.next()) {
                Post post;
                res >> post.id >> post.title >> post.content >> post.user_id >> post.created_at;
                posts.push_back(post);
            }
        } catch(std::exception const &e) {
            std::cerr << "Post retrieval error: " << e.what() << std::endl;
        }
        return posts;
    }
};

Practical Examples

#include <cppdb/frontend.h>
#include <cppdb/pool.h>
#include <thread>
#include <memory>

class DatabaseManager {
private:
    std::unique_ptr<cppdb::pool> db_pool;
    
public:
    DatabaseManager(const std::string& connection_string, int pool_size = 10) {
        // Connection pool setup
        db_pool = std::make_unique<cppdb::pool>(connection_string, pool_size);
    }
    
    // Complex operation with transaction
    bool transferUserData(int from_user_id, int to_user_id, const std::string& data_type) {
        try {
            cppdb::session sql = db_pool->open();
            
            // Begin transaction
            cppdb::transaction tr(sql);
            
            // Execute data migration
            cppdb::statement move_data = sql << 
                "UPDATE user_data SET user_id = ? WHERE user_id = ? AND data_type = ?"
                << to_user_id << from_user_id << data_type;
            move_data.exec();
            
            // Log the operation
            cppdb::statement log_entry = sql << 
                "INSERT INTO data_transfer_log (from_user, to_user, data_type, transfer_time) "
                "VALUES (?, ?, ?, NOW())"
                << from_user_id << to_user_id << data_type;
            log_entry.exec();
            
            // Commit transaction
            tr.commit();
            return true;
            
        } catch(std::exception const &e) {
            std::cerr << "Data migration error: " << e.what() << std::endl;
            return false;
        }
    }
    
    // Batch processing example
    bool batchInsertUsers(const std::vector<User>& users) {
        try {
            cppdb::session sql = db_pool->open();
            cppdb::transaction tr(sql);
            
            cppdb::statement st = sql << 
                "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
            
            for(const auto& user : users) {
                st.reset();
                st << user.name << user.email << user.age;
                st.exec();
            }
            
            tr.commit();
            return true;
            
        } catch(std::exception const &e) {
            std::cerr << "Batch insert error: " << e.what() << std::endl;
            return false;
        }
    }
    
    // Asynchronous processing support (multi-threaded environment)
    void processUsersAsync(const std::function<void(const User&)>& processor) {
        std::vector<std::thread> workers;
        
        try {
            cppdb::session sql = db_pool->open();
            cppdb::result res = sql << "SELECT id, name, email, age FROM users";
            
            while(res.next()) {
                User user;
                res >> user.id >> user.name >> user.email >> user.age;
                
                // Execute user processing asynchronously
                workers.emplace_back([user, processor]() {
                    processor(user);
                });
            }
            
            // Wait for all threads to complete
            for(auto& worker : workers) {
                worker.join();
            }
            
        } catch(std::exception const &e) {
            std::cerr << "Async processing error: " << e.what() << std::endl;
            
            // Wait for remaining threads
            for(auto& worker : workers) {
                if(worker.joinable()) {
                    worker.join();
                }
            }
        }
    }
};

// Usage example
int main() {
    try {
        DatabaseManager db_manager("mysql:host=localhost;database=test;user=root;password=password");
        
        // User data migration
        bool success = db_manager.transferUserData(1, 2, "profile_data");
        if(success) {
            std::cout << "Data migration completed" << std::endl;
        }
        
        // Batch processing
        std::vector<User> new_users = {
            {0, "John Doe", "[email protected]", 30},
            {0, "Jane Smith", "[email protected]", 25},
            {0, "Bob Johnson", "[email protected]", 35}
        };
        
        if(db_manager.batchInsertUsers(new_users)) {
            std::cout << "Batch insertion completed" << std::endl;
        }
        
        // Asynchronous processing
        db_manager.processUsersAsync([](const User& user) {
            std::cout << "Processing user: " << user.name << std::endl;
            // Some time-consuming processing...
            std::this_thread::sleep_for(std::chrono::milliseconds(100));
        });
        
    } catch(std::exception const &e) {
        std::cerr << "Application error: " << e.what() << std::endl;
        return 1;
    }
    
    return 0;
}