Library

LiteSQL

Overview

LiteSQL is an ORM framework consisting of a code generator and C++ library. It provides an object persistence layer through tight integration of C++ objects with relational databases, supporting SQLite3, PostgreSQL, MySQL, and Oracle. It automatically generates C++ classes from XML schema definitions, achieving type-safe database access.

Details

LiteSQL 2025 edition continues to be used as an open-source C++ ORM choice. It is adopted in multi-database environment projects leveraging its broad major database support. The code generator approach enhances maintainability against database schema changes and improves development efficiency. Despite its lightweight design, it covers basic ORM features including relationship management and transaction processing.

Key Features

• Code Generation: Automatic C++ class generation from XML schemas
• Multi-database: Support for SQLite3, PostgreSQL, MySQL, Oracle
• Lightweight Design: Minimal dependencies and small footprint
• Relationship Management: Support for one-to-one, one-to-many, many-to-many relations
• Type Safety: Safety through compile-time type checking
• BSD License: Open source allowing commercial use

Pros and Cons

Pros

• Declarative data model definition through XML schemas
• Unified interface to multiple databases
• Lightweight enough for embedded systems
• Flexible usage terms through BSD license
• Improved development efficiency through auto-generated code
• Simple and easy-to-understand API design

Cons

• Slow development and maintenance pace
• Limited support for modern C++ features
• Insufficient documentation and community support
• Lack of advanced ORM features (lazy loading, etc.)
• Limited performance optimization options

References

• LiteSQL GitHub Repository
• LiteSQL Documentation
• LiteSQL Wiki

Examples

XML Schema Definition

<!-- person.xml - Data model definition -->
<?xml version="1.0"?>
<database name="PersonDB" namespace="example">
  
  <object name="Person">
    <field name="name" type="string" length="256"/>
    <field name="age" type="integer"/>
    <field name="email" type="string" length="128" unique="true"/>
    <field name="created" type="datetime" default="now()"/>
  </object>
  
  <object name="Department">
    <field name="name" type="string" length="128"/>
    <field name="description" type="string" length="1024"/>
  </object>
  
  <object name="Project">
    <field name="title" type="string" length="256"/>
    <field name="deadline" type="date"/>
    <field name="budget" type="double"/>
  </object>
  
  <!-- Relation definitions -->
  <relation name="Employment">
    <relate object="Person" limit="many" handle="employees"/>
    <relate object="Department" limit="one" handle="department"/>
  </relation>
  
  <relation name="Assignment">
    <relate object="Person" limit="many" handle="members"/>
    <relate object="Project" limit="many" handle="projects"/>
    <field name="role" type="string" length="64"/>
    <field name="hours_per_week" type="integer"/>
  </relation>
  
</database>

Basic Usage

// main.cpp - Using generated code
#include "persondb.hpp"
#include <iostream>
#include <litesql.hpp>

using namespace litesql;
using namespace example;

int main()
{
  try
  {
    // Database connection
    PersonDB db("sqlite3", "database=person.db");
    
    // Create tables
    db.create();
    
    // CREATE - Create new record
    Person person(db);
    person.name = "John Doe";
    person.age = 30;
    person.email = "[email protected]";
    person.update();  // Save
    
    std::cout << "Created person with ID: " << person.id << std::endl;
    
    // Create department
    Department dept(db);
    dept.name = "Development";
    dept.description = "Software Development Department";
    dept.update();
    
    // Set up relation
    Employment(db, person, dept).link();
    
    // READ - Retrieve data
    // Get all records
    std::vector<Person> allPersons = select<Person>(db).all();
    for (const auto& p : allPersons)
    {
      std::cout << "Person: " << p.name << ", Age: " << p.age << std::endl;
    }
    
    // Conditional search
    std::vector<Person> adults = select<Person>(db, Person::Age > 18).all();
    
    // Get single record
    Person foundPerson = select<Person>(db, Person::Email == "[email protected]").one();
    
    // UPDATE - Update record
    foundPerson.age = 31;
    foundPerson.update();
    
    // DELETE - Delete record
    foundPerson.del();
    
  }
  catch (const Except& e)
  {
    std::cerr << "Database error: " << e << std::endl;
    return 1;
  }
  
  return 0;
}

Advanced Query Operations

#include "persondb.hpp"
#include <algorithm>

using namespace litesql;
using namespace example;

class PersonRepository
{
private:
  PersonDB& db_;
  
public:
  PersonRepository(PersonDB& db) : db_(db) {}
  
  // Complex conditional search
  std::vector<Person> findByAgeRange(int minAge, int maxAge)
  {
    return select<Person>(db_, 
      Person::Age >= minAge && Person::Age <= maxAge)
      .orderBy(Person::Age)
      .all();
  }
  
  // LIKE search
  std::vector<Person> searchByName(const std::string& keyword)
  {
    return select<Person>(db_, 
      Person::Name.like("%" + keyword + "%"))
      .all();
  }
  
  // JOIN operations
  void listEmployeesWithDepartment()
  {
    // List employees with departments
    auto persons = select<Person>(db_).all();
    
    for (const auto& person : persons)
    {
      // Get department through relation
      auto depts = person.department().get().all();
      if (!depts.empty())
      {
        std::cout << person.name << " - " << depts[0].name << std::endl;
      }
    }
  }
  
  // Aggregate functions
  int getAverageAge()
  {
    DataSource<Person> personDS = DataSource<Person>::get(db_);
    
    // Execute custom SQL
    std::string sql = "SELECT AVG(age) FROM " + personDS.table() + ";";
    Records recs = db_.query(sql);
    
    if (!recs.empty() && !recs[0].empty())
    {
      return atoi(recs[0][0].c_str());
    }
    return 0;
  }
  
  // Transaction processing
  bool transferToNewDepartment(int personId, int newDeptId)
  {
    db_.begin();
    
    try
    {
      Person person = select<Person>(db_, Person::Id == personId).one();
      Department newDept = select<Department>(db_, Department::Id == newDeptId).one();
      
      // Delete existing department relation
      Employment::del(db_, 
        Employment::PersonId == personId);
      
      // Create new department relation
      Employment(db_, person, newDept).link();
      
      db_.commit();
      return true;
    }
    catch (const NotFound&)
    {
      db_.rollback();
      return false;
    }
  }
  
  // Pagination
  struct PageResult
  {
    std::vector<Person> items;
    int totalCount;
    int currentPage;
    int pageSize;
  };
  
  PageResult getPaginatedPersons(int page, int pageSize)
  {
    int offset = (page - 1) * pageSize;
    
    // Get total count
    int totalCount = select<Person>(db_).count();
    
    // Get page data
    std::vector<Person> items = select<Person>(db_)
      .orderBy(Person::Name)
      .limit(pageSize)
      .offset(offset)
      .all();
    
    return {items, totalCount, page, pageSize};
  }
};

// Many-to-many relation operations
void projectAssignmentExample(PersonDB& db)
{
  // Create project
  Project project(db);
  project.title = "New System Development";
  project.deadline = Date(2025, 12, 31);
  project.budget = 10000000.0;
  project.update();
  
  // Assign personnel
  std::vector<Person> developers = select<Person>(db_, 
    Person::Name.like("%Developer%")).all();
  
  for (const auto& dev : developers)
  {
    Assignment assignment(db, dev, project);
    assignment.role = "Developer";
    assignment.hours_per_week = 40;
    assignment.link();
  }
  
  // List project members
  auto members = project.members().get().all();
  for (const auto& member : members)
  {
    std::cout << "Member: " << member.name << std::endl;
  }
}

Practical Application Example

// REST API server usage example
#include <httplib.h>
#include <json.hpp>
#include "persondb.hpp"

using namespace litesql;
using namespace example;
using json = nlohmann::json;

class PersonAPI
{
private:
  PersonDB db_;
  
public:
  PersonAPI() : db_("sqlite3", "database=api.db") 
  {
    db_.create();
  }
  
  // GET /api/persons
  std::string getAllPersons()
  {
    json result = json::array();
    
    auto persons = select<Person>(db_).all();
    for (const auto& person : persons)
    {
      result.push_back({
        {"id", person.id},
        {"name", person.name.value()},
        {"age", person.age.value()},
        {"email", person.email.value()},
        {"created", person.created.value().asString()}
      });
    }
    
    return result.dump();
  }
  
  // GET /api/persons/:id
  std::string getPersonById(int id)
  {
    try
    {
      Person person = select<Person>(db_, Person::Id == id).one();
      
      json result = {
        {"id", person.id},
        {"name", person.name.value()},
        {"age", person.age.value()},
        {"email", person.email.value()},
        {"created", person.created.value().asString()}
      };
      
      // Include department info
      auto depts = person.department().get().all();
      if (!depts.empty())
      {
        result["department"] = {
          {"id", depts[0].id},
          {"name", depts[0].name.value()}
        };
      }
      
      return result.dump();
    }
    catch (const NotFound&)
    {
      return json({{"error", "Person not found"}}).dump();
    }
  }
  
  // POST /api/persons
  std::string createPerson(const json& data)
  {
    try
    {
      db_.begin();
      
      Person person(db_);
      person.name = data["name"].get<std::string>();
      person.age = data["age"].get<int>();
      person.email = data["email"].get<std::string>();
      person.update();
      
      // Department assignment
      if (data.contains("department_id"))
      {
        int deptId = data["department_id"].get<int>();
        Department dept = select<Department>(db_, 
          Department::Id == deptId).one();
        Employment(db_, person, dept).link();
      }
      
      db_.commit();
      
      return json({
        {"success", true},
        {"id", person.id}
      }).dump();
    }
    catch (const std::exception& e)
    {
      db_.rollback();
      return json({
        {"success", false},
        {"error", e.what()}
      }).dump();
    }
  }
  
  // PUT /api/persons/:id
  std::string updatePerson(int id, const json& data)
  {
    try
    {
      Person person = select<Person>(db_, Person::Id == id).one();
      
      if (data.contains("name"))
        person.name = data["name"].get<std::string>();
      if (data.contains("age"))
        person.age = data["age"].get<int>();
      if (data.contains("email"))
        person.email = data["email"].get<std::string>();
      
      person.update();
      
      return json({{"success", true}}).dump();
    }
    catch (const NotFound&)
    {
      return json({
        {"success", false},
        {"error", "Person not found"}
      }).dump();
    }
  }
  
  // DELETE /api/persons/:id
  std::string deletePerson(int id)
  {
    try
    {
      Person person = select<Person>(db_, Person::Id == id).one();
      person.del();
      
      return json({{"success", true}}).dump();
    }
    catch (const NotFound&)
    {
      return json({
        {"success", false},
        {"error", "Person not found"}
      }).dump();
    }
  }
};

// Server implementation
int main()
{
  httplib::Server svr;
  PersonAPI api;
  
  svr.Get("/api/persons", [&](const httplib::Request&, httplib::Response& res) {
    res.set_content(api.getAllPersons(), "application/json");
  });
  
  svr.Get(R"(/api/persons/(\d+))", [&](const httplib::Request& req, httplib::Response& res) {
    int id = std::stoi(req.matches[1]);
    res.set_content(api.getPersonById(id), "application/json");
  });
  
  svr.Post("/api/persons", [&](const httplib::Request& req, httplib::Response& res) {
    auto data = json::parse(req.body);
    res.set_content(api.createPerson(data), "application/json");
  });
  
  svr.Put(R"(/api/persons/(\d+))", [&](const httplib::Request& req, httplib::Response& res) {
    int id = std::stoi(req.matches[1]);
    auto data = json::parse(req.body);
    res.set_content(api.updatePerson(id, data), "application/json");
  });
  
  svr.Delete(R"(/api/persons/(\d+))", [&](const httplib::Request& req, httplib::Response& res) {
    int id = std::stoi(req.matches[1]);
    res.set_content(api.deletePerson(id), "application/json");
  });
  
  std::cout << "Server running on http://localhost:8080" << std::endl;
  svr.listen("localhost", 8080);
  
  return 0;
}