Uh oh!
There was an error while loading.Please reload this page.
- Notifications
You must be signed in to change notification settings - Fork341
❤️ SQLite ORM light header only library for modern C++
License
AGPL-3.0, MIT licenses found
Licenses found
fnc12/sqlite_orm
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
SQLite ORM light header only library for modern C++. Please read the license precisely. The project has AGPL license for open source project and MIT license after purchasing it for 50$ (usingPayPal or any different way (contact using emailfnc12@me.com)).
| Branch | Travis | Appveyor |
|---|---|---|
master |  | |
dev |  |
- No raw string queries
- Intuitive syntax
- Comfortable interface - one code line per single query
- Built with modern C++14/C++17/C++20 features (no macros and external scripts)
- CRUD support
- Pure select query support
- Prepared statements support
- UNION, EXCEPT and INTERSECT support
- STL compatible
- Custom types binding support
- BLOB support - maps to
std::vector<char>or one can bind your custom type - FOREIGN KEY support
- Composite key support
- JOIN support
- Transactions support
- Migrations functionality
- Powerful conditions
- ORDER BY and LIMIT, OFFSET support
- GROUP BY / DISTINCT support
- INDEX support
- Follows single responsibility principle - no need write code inside your data model classes
- Easy integration - single header only lib.
- The only dependency - libsqlite3
- C++ standard code style
- In memory database support - provide
:memory:or empty filename - COLLATE support
- Limits setting/getting support
- User defined functions support
sqlite_orm library allows to create easy data model mappings to your database schema. It is built to manage (CRUD) objects with a primary key and without it. It also allows you to specify table names and column names explicitly no matter how your classes actually named. Take a look at example:
structUser{int id; std::string firstName; std::string lastName;int birthDate; std::unique_ptr<std::string> imageUrl;int typeId;};structUserType {int id; std::string name;};
So we have database with predefined schema like
CREATE TABLE users (id integer primary key autoincrement, first_name text not null, last_name text not null, birth_date integer not null, image_url text, type_id integer not null)
CREATE TABLE user_types (id integer primary key autoincrement, name text not null DEFAULT 'name_placeholder')
Now we tellsqlite_orm library about our schema and provide database filename. We createstorage service object that has CRUD interface. Also we create every table and every column. All code is intuitive and minimalistic.
usingnamespacesqlite_orm;auto storage = make_storage("db.sqlite",make_table("users",make_column("id", &User::id, primary_key().autoincrement()), make_column("first_name", &User::firstName), make_column("last_name", &User::lastName), make_column("birth_date", &User::birthDate), make_column("image_url", &User::imageUrl), make_column("type_id", &User::typeId)), make_table("user_types",make_column("id", &UserType::id, primary_key().autoincrement()), make_column("name", &UserType::name, default_value("name_placeholder"))));
Too easy isn't it? You do not have to specify mapped type explicitly - it is deduced from your member pointers you pass during making a column (for example:&User::id). To create a column you have to pass two arguments at least: its name in the table and your mapped class member pointer. You can also add extra arguments to tell your storage about column's constraints likeprimary_key,autoincrement,default_value,unique orgenerated_always_as (order isn't important;not_null/null are deduced from type automatically but can be added manually if you wish withnull() andnot_null()).
More details about making storage can be found intutorial.
If your datamodel classes have private or protected members to map to sqlite then you can make a storage with setter and getter functions. More info in theexample.
Let's create and insert newUser into our database. First we need to create aUser object with any id and callinsert function. It will return id of just created user or throw exception if something goes wrong.
User user{-1,"Jonh","Doe",664416000, std::make_unique<std::string>("url_to_heaven"),3 };auto insertedId = storage.insert(user);cout <<"insertedId =" << insertedId << endl;// insertedId = 8user.id = insertedId;User secondUser{-1,"Alice","Inwonder",831168000, {} ,2};insertedId = storage.insert(secondUser);secondUser.id = insertedId;Note: if we need to insert a new user with specified id callstorage.replace(user); instead ofinsert.
Next let's get our user by id.
try{auto user = storage.get<User>(insertedId); cout <<"user =" << user.firstName <<"" << user.lastName << endl;}catch(std::system_error e) { cout << e.what() << endl;}catch(...){ cout <<"unknown exeption" << endl;}
Probably you may not like throwing exceptions. Me too. Exceptionstd::system_error is thrown because return type inget function is not nullable. You can use alternative versionget_pointer which returnsstd::unique_ptr and doesn't thrownot_found_exception if nothing found - just returnsnullptr.
if(auto user = storage.get_pointer<User>(insertedId)){ cout <<"user =" << user->firstName <<"" << user->lastName << endl;}else{ cout <<"no user with id" << insertedId << endl;}
std::unique_ptr is used as optional insqlite_orm. Of course there is class optional in C++14 located atstd::experimental::optional. But we don't want to use it until it isexperimental.
We can also update our user. It updates row by id provided inuser object and sets all other nonprimary_key fields to values stored in the passeduser object. So you can just assign members touser object you want and callupdate
user.firstName ="Nicholas";user.imageUrl ="https://cdn1.iconfinder.com/data/icons/man-icon-set/100/man_icon-21-512.png"storage.update(user);
Also there is a non-CRUD update versionupdate_all:
storage.update_all(set(c(&User::lastName) ="Hardey",c(&User::typeId) = 2), where(c(&User::firstName) == "Tom"));
And delete. To delete you have to pass id only, not whole object. Also we need to explicitly tell which class of object we want to delete. Function name isremove notdelete causedelete is a reserved word in C++.
storage.remove<User>(insertedId)
Also we can extract all objects intostd::vector.
auto allUsers = storage.get_all<User>();cout <<"allUsers (" << allUsers.size() <<"):" << endl;for(auto &user : allUsers) { cout << storage.dump(user) << endl;// dump returns std::string with json-like style object info. For example: { id : '1', first_name : 'Jonh', last_name : 'Doe', birth_date : '664416000', image_url : 'https://cdn1.iconfinder.com/data/icons/man-icon-set/100/man_icon-21-512.png', type_id : '3' }}
And one can specify return container type explicitly: let's get all users instd::list, notstd::vector:
auto allUsersList = storage.get_all<User, std::list<User>>();Container must be STL compatible (must havepush_back(T&&) function in this case).
get_all can be too heavy for memory so you can iterate row by row (i.e. object by object):
for(auto &user : storage.iterate<User>()) { cout << storage.dump(user) << endl;}
iterate member function returns adapter object that hasbegin andend member functions returning iterators that fetch object on dereference operator call.
CRUD functionsget,get_pointer,remove,update (notinsert) work only if your type has a primary key column. If you try toget an object that is mapped to your storage but has no primary key column astd::system_error will be thrown causesqlite_orm cannot detect an id. If you want to know how to perform a storage without primary key take a look atdate_time.cpp example inexamples folder.
Prepared statements are strongly typed.
// SELECT doctor_id// FROM visits// WHERE LENGTH(patient_name) > 8auto selectStatement = storage.prepare(select(&Visit::doctor_id, where(length(&Visit::patient_name) >8)));cout <<"selectStatement =" << selectStatement.sql() << endl;// prints "SELECT doctor_id FROM ..."auto rows = storage.execute(selectStatement);// rows is std::vector<decltype(Visit::doctor_id)>// SELECT doctor_id// FROM visits// WHERE LENGTH(patient_name) > 11get<0>(selectStatement) =11;auto rows2 = storage.execute(selectStatement);
get<N>(statement) function call allows you to access fields to bind them to your statement.
// SELECT AVG(id) FROM usersauto averageId = storage.avg(&User::id); cout <<"averageId =" << averageId << endl;// averageId = 4.5// SELECT AVG(birth_date) FROM usersauto averageBirthDate = storage.avg(&User::birthDate); cout <<"averageBirthDate =" << averageBirthDate << endl;// averageBirthDate = 6.64416e+08// SELECT COUNT(*) FROM usersauto usersCount = storage.count<User>(); cout <<"users count =" << usersCount << endl;// users count = 8// SELECT COUNT(id) FROM usersauto countId = storage.count(&User::id); cout <<"countId =" << countId << endl;// countId = 8// SELECT COUNT(image_url) FROM usersauto countImageUrl = storage.count(&User::imageUrl); cout <<"countImageUrl =" << countImageUrl << endl;// countImageUrl = 5// SELECT GROUP_CONCAT(id) FROM usersauto concatedUserId = storage.group_concat(&User::id); cout <<"concatedUserId =" << concatedUserId << endl;// concatedUserId = 1,2,3,4,5,6,7,8// SELECT GROUP_CONCAT(id, "---") FROM usersauto concatedUserIdWithDashes = storage.group_concat(&User::id,"---"); cout <<"concatedUserIdWithDashes =" << concatedUserIdWithDashes << endl;// concatedUserIdWithDashes = 1---2---3---4---5---6---7---8// SELECT MAX(id) FROM usersif(auto maxId = storage.max(&User::id)){ cout <<"maxId =" << *maxId <<endl;// maxId = 12 (maxId is std::unique_ptr<int>)}else{ cout <<"maxId is null" << endl;}// SELECT MAX(first_name) FROM usersif(auto maxFirstName = storage.max(&User::firstName)){ cout <<"maxFirstName =" << *maxFirstName << endl;// maxFirstName = Jonh (maxFirstName is std::unique_ptr<std::string>)}else{ cout <<"maxFirstName is null" << endl;}// SELECT MIN(id) FROM usersif(auto minId = storage.min(&User::id)){ cout <<"minId =" << *minId << endl;// minId = 1 (minId is std::unique_ptr<int>)}else{ cout <<"minId is null" << endl;}// SELECT MIN(last_name) FROM usersif(auto minLastName = storage.min(&User::lastName)){ cout <<"minLastName =" << *minLastName << endl;// minLastName = Doe}else{ cout <<"minLastName is null" << endl;}// SELECT SUM(id) FROM usersif(auto sumId = storage.sum(&User::id)){// sumId is std::unique_ptr<int> cout <<"sumId =" << *sumId << endl;}else{ cout <<"sumId is null" << endl;}// SELECT TOTAL(id) FROM usersauto totalId = storage.total(&User::id);cout <<"totalId =" << totalId << endl;// totalId is double (always)
You also can select objects with custom where conditions with=,!=,>,>=,<,<=,IN,BETWEEN andLIKE.
For example: let's select users with id lesser than 10:
// SELECT * FROM users WHERE id < 10auto idLesserThan10 = storage.get_all<User>(where(c(&User::id) <10));cout <<"idLesserThan10 count =" << idLesserThan10.size() << endl;for(auto &user : idLesserThan10) { cout << storage.dump(user) << endl;}
Or select all users who's first name is not equal "John":
// SELECT * FROM users WHERE first_name != 'John'auto notJohn = storage.get_all<User>(where(c(&User::firstName) !="John"));cout <<"notJohn count =" << notJohn.size() << endl;for(auto &user : notJohn) { cout << storage.dump(user) << endl;}
By the way one can implement not equal in a different way using C++ negation operator:
auto notJohn2 = storage.get_all<User>(where(not (c(&User::firstName) =="John")));
You can use! andnot in this case cause they are equal. Also you can chain several conditions withand andor operators. Let's try to get users with query with conditions likewhere id >= 5 and id <= 7 and not id = 6:
auto id5and7 = storage.get_all<User>(where(c(&User::id) <=7andc(&User::id) >= 5 and not (c(&User::id) == 6)));cout <<"id5and7 count =" << id5and7.size() << endl;for(auto &user : id5and7) { cout << storage.dump(user) << endl;}
Or let's just export two users with id 10 or id 16 (of course if these users exist):
auto id10or16 = storage.get_all<User>(where(c(&User::id) ==10orc(&User::id) == 16));cout <<"id10or16 count =" << id10or16.size() << endl;for(auto &user : id10or16) { cout << storage.dump(user) << endl;}
In fact you can chain together any number of different conditions with any operator fromand,or andnot. All conditions are templated so there is no runtime overhead. And this makessqlite_orm the most powerfulsqlite C++ ORM library!
Moreover you can use parentheses to set the priority of query conditions:
auto cuteConditions = storage.get_all<User>(where((c(&User::firstName) =="John"orc(&User::firstName) == "Alex") and c(&User::id) == 4));// where (first_name = 'John' or first_name = 'Alex') and id = 4cout <<"cuteConditions count =" << cuteConditions.size() << endl;// cuteConditions count = 1cuteConditions = storage.get_all<User>(where(c(&User::firstName) =="John"or (c(&User::firstName) =="Alex"andc(&User::id) == 4)));// where first_name = 'John' or (first_name = 'Alex' and id = 4)cout <<"cuteConditions count =" << cuteConditions.size() << endl;// cuteConditions count = 2
Also we can implementget by id withget_all andwhere like this:
// SELECT * FROM users WHERE ( 2 = id )auto idEquals2 = storage.get_all<User>(where(2 == c(&User::id)));cout <<"idEquals2 count =" << idEquals2.size() << endl;if(idEquals2.size()){ cout << storage.dump(idEquals2.front()) << endl;}else{ cout <<"user with id 2 doesn't exist" << endl;}
Lets try theIN operator:
// SELECT * FROM users WHERE id IN (2, 4, 6, 8, 10)auto evenLesserTen10 = storage.get_all<User>(where(in(&User::id, {2,4,6,8,10})));cout <<"evenLesserTen10 count =" << evenLesserTen10.size() << endl;for(auto &user : evenLesserTen10) { cout << storage.dump(user) << endl;}// SELECT * FROM users WHERE last_name IN ("Doe", "White")auto doesAndWhites = storage.get_all<User>(where(in(&User::lastName, {"Doe","White"})));cout <<"doesAndWhites count =" << doesAndWhites.size() << endl;for(auto &user : doesAndWhites) { cout << storage.dump(user) << endl;}
AndBETWEEN:
// SELECT * FROM users WHERE id BETWEEN 66 AND 68auto betweenId = storage.get_all<User>(where(between(&User::id,66,68)));cout <<"betweenId =" << betweenId.size() << endl;for(auto &user : betweenId) { cout << storage.dump(user) << endl;}
And evenLIKE:
// SELECT * FROM users WHERE last_name LIKE 'D%'auto whereNameLike = storage.get_all<User>(where(like(&User::lastName,"D%")));cout <<"whereNameLike =" << whereNameLike.size() << endl;for(auto &user : whereNameLike) { cout << storage.dump(user) << endl;}
Looks like magic but it works very simple. Cute functionc (column) takes a class member pointer and returns a special expression middle object that can be used with operators overloaded in::sqlite_orm namespace. Operator overloads act just like functions
- is_equal
- is_not_equal
- greater_than
- greater_or_equal
- lesser_than
- lesser_or_equal
- is_null
- is_not_null
that simulate binary comparison operator so they take 2 arguments: left hand side and right hand side. Arguments may be either member pointer of mapped class or any other expression (core/aggregate function, literal or subexpression). Binary comparison functions map arguments to text to be passed to sqlite engine to process query. Member pointers are being mapped to column names and literals/variables/constants to '?' and then are bound automatically. Nextwhere function places brackets around condition and adds "WHERE" keyword before condition text. Next resulted string appends to a query string and is being processed further.
If you omitwhere function inget_all it will return all objects from a table:
auto allUsers = storage.get_all<User>();Also you can useremove_all function to performDELETE FROM ... WHERE query with the same type of conditions.
storage.remove_all<User>(where(c(&User::id) <100));If you need to extract only a single column (SELECT %column_name% FROM %table_name% WHERE %conditions%) you can use a non-CRUDselect function:
// SELECT id FROM usersauto allIds = storage.select(&User::id); cout <<"allIds count =" << allIds.size() << endl;// allIds is std::vector<int>for(auto &id : allIds) { cout << id <<"";}cout << endl;// SELECT id FROM users WHERE last_name = 'Doe'auto doeIds = storage.select(&User::id, where(c(&User::lastName) =="Doe"));cout <<"doeIds count =" << doeIds.size() << endl;// doeIds is std::vector<int>for(auto &doeId : doeIds) { cout << doeId <<"";}cout << endl;// SELECT last_name FROM users WHERE id < 300auto allLastNames = storage.select(&User::lastName, where(c(&User::id) <300)); cout <<"allLastNames count =" << allLastNames.size() << endl;// allLastNames is std::vector<std::string>for(auto &lastName : allLastNames) { cout << lastName <<"";}cout << endl;// SELECT id FROM users WHERE image_url IS NULLauto idsWithoutUrls = storage.select(&User::id, where(is_null(&User::imageUrl)));for(auto id : idsWithoutUrls) { cout <<"id without image url" << id << endl;}// SELECT id FROM users WHERE image_url IS NOT NULLauto idsWithUrl = storage.select(&User::id, where(is_not_null(&User::imageUrl)));for(auto id : idsWithUrl) { cout <<"id with image url" << id << endl;}auto idsWithUrl2 = storage.select(&User::id, where(notis_null(&User::imageUrl)));assert(std::equal(idsWithUrl2.begin(), idsWithUrl2.end(), idsWithUrl.begin()));
Also you're able to select several column in a vector of tuples. Example:
// `SELECT first_name, last_name FROM users WHERE id > 250 ORDER BY id`auto partialSelect = storage.select(columns(&User::firstName, &User::lastName),where(c(&User::id) > 250), order_by(&User::id));cout <<"partialSelect count =" << partialSelect.size() << endl;for(auto &t : partialSelect) {auto &firstName = std::get<0>(t);auto &lastName = std::get<1>(t); cout << firstName <<"" << lastName << endl;}
ORDER BY query option can be applied toget_all andselect functions just likewhere but withorder_by function. It can be mixed with WHERE in a single query. Examples:
// `SELECT * FROM users ORDER BY id`auto orderedUsers = storage.get_all<User>(order_by(&User::id));cout <<"orderedUsers count =" << orderedUsers.size() << endl;for(auto &user : orderedUsers) { cout << storage.dump(user) << endl;}// `SELECT * FROM users WHERE id < 250 ORDER BY first_name`auto orderedUsers2 = storage.get_all<User>(where(c(&User::id) <250), order_by(&User::firstName));cout <<"orderedUsers2 count =" << orderedUsers2.size() << endl;for(auto &user : orderedUsers2) { cout << storage.dump(user) << endl;}// `SELECT * FROM users WHERE id > 100 ORDER BY first_name ASC`auto orderedUsers3 = storage.get_all<User>(where(c(&User::id) >100), order_by(&User::firstName).asc());cout <<"orderedUsers3 count =" << orderedUsers3.size() << endl;for(auto &user : orderedUsers3) { cout << storage.dump(user) << endl;}// `SELECT * FROM users ORDER BY id DESC`auto orderedUsers4 = storage.get_all<User>(order_by(&User::id).desc());cout <<"orderedUsers4 count =" << orderedUsers4.size() << endl;for(auto &user : orderedUsers4) { cout << storage.dump(user) << endl;}// `SELECT first_name FROM users ORDER BY ID DESC`auto orderedFirstNames = storage.select(&User::firstName, order_by(&User::id).desc());cout <<"orderedFirstNames count =" << orderedFirstNames.size() << endl;for(auto &firstName : orderedFirstNames) { cout <<"firstName =" << firstName << endl;}
There are three available versions ofLIMIT/OFFSET options:
- LIMIT %limit%
- LIMIT %limit% OFFSET %offset%
- LIMIT %offset%, %limit%
All these versions available with the same interface:
// `SELECT * FROM users WHERE id > 250 ORDER BY id LIMIT 5`auto limited5 = storage.get_all<User>(where(c(&User::id) >250),order_by(&User::id), limit(5));cout <<"limited5 count =" << limited5.size() << endl;for(auto &user : limited5) { cout << storage.dump(user) << endl;}// `SELECT * FROM users WHERE id > 250 ORDER BY id LIMIT 5, 10`auto limited5comma10 = storage.get_all<User>(where(c(&User::id) >250),order_by(&User::id), limit(5,10));cout <<"limited5comma10 count =" << limited5comma10.size() << endl;for(auto &user : limited5comma10) { cout << storage.dump(user) << endl;}// `SELECT * FROM users WHERE id > 250 ORDER BY id LIMIT 5 OFFSET 10`auto limit5offset10 = storage.get_all<User>(where(c(&User::id) >250),order_by(&User::id), limit(5, offset(10)));cout <<"limit5offset10 count =" << limit5offset10.size() << endl;for(auto &user : limit5offset10) { cout << storage.dump(user) << endl;}
Please beware that queriesLIMIT 5, 10 andLIMIT 5 OFFSET 10 mean different.LIMIT 5, 10 meansLIMIT 10 OFFSET 5.
You can perform simpleJOIN,CROSS JOIN,INNER JOIN,LEFT JOIN orLEFT OUTER JOIN in your query. Instead of joined table specify mapped type. Example for doctors and visits:
// SELECT a.doctor_id, a.doctor_name,// c.patient_name, c.vdate// FROM doctors a// LEFT JOIN visits c// ON a.doctor_id=c.doctor_id;auto rows = storage2.select(columns(&Doctor::id, &Doctor::name, &Visit::patientName, &Visit::vdate), left_join<Visit>(on(c(&Doctor::id) == &Visit::doctorId)));// one `c` call is enough cause operator overloads are templatedfor(auto &row : rows) { cout << std::get<0>(row) <<'\t' << std::get<1>(row) <<'\t' << std::get<2>(row) <<'\t' << std::get<3>(row) << endl;}cout << endl;
SimpleJOIN:
// SELECT a.doctor_id,a.doctor_name,// c.patient_name,c.vdate// FROM doctors a// JOIN visits c// ON a.doctor_id=c.doctor_id;rows = storage2.select(columns(&Doctor::id, &Doctor::name, &Visit::patientName, &Visit::vdate), join<Visit>(on(c(&Doctor::id) == &Visit::doctorId)));for(auto &row : rows) { cout << std::get<0>(row) <<'\t' << std::get<1>(row) <<'\t' << std::get<2>(row) <<'\t' << std::get<3>(row) << endl;}cout << endl;
TwoINNER JOINs in one query:
// SELECT// trackid,// tracks.name AS Track,// albums.title AS Album,// artists.name AS Artist// FROM// tracks// INNER JOIN albums ON albums.albumid = tracks.albumid// INNER JOIN artists ON artists.artistid = albums.artistid;auto innerJoinRows2 = storage.select(columns(&Track::trackId, &Track::name, &Album::title, &Artist::name), inner_join<Album>(on(c(&Album::albumId) == &Track::albumId)), inner_join<Artist>(on(c(&Artist::artistId) == &Album::artistId)));// innerJoinRows2 is std::vector<std::tuple<decltype(Track::trackId), decltype(Track::name), decltype(Album::title), decltype(Artist::name)>>
More join examples can be found inexamples folder.
There are no explicitup anddown functions that are used to be used in migrations. Insteadsqlite_orm offerssync_schema function that takes responsibility of comparing actual db file schema with one you specified inmake_storage call and if something is not equal it alters or drops/creates schema.
storage.sync_schema();// orstorage.sync_schema(true);
Please beware thatsync_schema doesn't guarantee that data will be saved. Ittries to save it only. Below you can see rules list thatsync_schema follows during call:
- if there are excess tables exist in db they are ignored (not dropped)
- every table from storage is compared with it's db analog and
- if table doesn't exist it is created
- if table exists its colums are being compared with table_info from db and
- if there are columns in db that do not exist in storage (excess) table will be dropped and recreated if
preserveisfalse, and table will be copied into temporary table without excess columns, source table will be dropped, copied table will be renamed to source table (sqlite remove column technique) ifpreserveistrue.preserveis the first argument insync_schemafunction. It's default value isfalse. Beware that setting it totruemay take time for copying table rows. - if there are columns in storage that do not exist in db they will be added using 'ALTER TABLE ... ADD COLUMN ...' command and table data will not be dropped but if any of added columns is null but has not default value table will be dropped and recreated
- if there is any column existing in both db and storage but differs by any of properties (type, pk, notnull) table will be dropped and recreated (dflt_value isn't checked cause there can be ambiguity in default values, please beware).
- if there are columns in db that do not exist in storage (excess) table will be dropped and recreated if
The best practice is to call this function right after storage creation.
There are three ways to begin and commit/rollback transactions:
- explicitly call
begin_transaction();,rollback();orcommit();functions - use
transactionfunction which begins transaction implicitly and takes a lambda argument which returns true for commit and false for rollback. All storage calls performed in lambda can be commited or rollbacked by returningtrueorfalse. - use
transaction_guardfunction which returns a guard object which works just likelock_guardforstd::mutex.
Example for explicit call:
auto secondUser = storage.get<User>(2);storage.begin_transaction();secondUser.typeId =3;storage.update(secondUser);storage.rollback();// or storage.commit();secondUser = storage.get<decltype(secondUser)>(secondUser.id);assert(secondUser.typeId !=3);
Example for implicit call:
storage.transaction([&] ()mutable {// mutable keyword allows make non-const function callsauto secondUser = storage.get<User>(2); secondUser.typeId =1; storage.update(secondUser);auto gottaRollback =bool(rand() %2);if(gottaRollback){// dummy condition for testreturnfalse;// exits lambda and calls ROLLBACK }returntrue;// exits lambda and calls COMMIT});
The second way guarantees thatcommit orrollback will be called. You can use either way.
Transactions are useful withchanges sqlite function that returns number of rows modified.
storage.transaction([&] ()mutable { storage.remove_all<User>(where(c(&User::id) <100));auto usersRemoved = storage.changes(); cout <<"usersRemoved =" << usersRemoved << endl;returntrue;});
It will print a number of deleted users (rows). But if you callchanges without a transaction and your database is located in file not in RAM the result will be 0 always causesqlite_orm opens and closes connection every time you call a function without a transaction.
Also atransaction function returnstrue if transaction is commited andfalse if it is rollbacked. It can be useful if your next actions depend on transaction result:
auto commited = storage.transaction([&] ()mutable {auto secondUser = storage.get<User>(2); secondUser.typeId =1; storage.update(secondUser);auto gottaRollback =bool(rand() %2);if(gottaRollback){// dummy condition for testreturnfalse;// exits lambda and calls ROLLBACK }returntrue;// exits lambda and calls COMMIT});if(commited){ cout <<"Commited successfully, go on." << endl;}else{ cerr <<"Commit failed, process an error" << endl;}
Example fortransaction_guard function:
try{auto guard = storage.transaction_guard();// calls BEGIN TRANSACTION and returns guard object user.name ="Paul";auto notExisting = storage.get<User>(-1);// exception is thrown here, guard calls ROLLBACK in its destructor guard.commit();}catch(...){ cerr <<"exception" << endl;}
To manage in memory database just provide:memory: or"" instead as filename tomake_storage.
| sqlite_orm | SQLiteCpp | hiberlite | ODB | |
|---|---|---|---|---|
| Schema sync | yes | no | yes | no |
| Single responsibility principle | yes | yes | no | no |
| STL compatible | yes | no | no | no |
| No raw string queries | yes | no | yes | yes |
| Transactions | yes | yes | no | yes |
| Custom types binding | yes | no | yes | yes |
| Doesn't use macros and/or external codegen scripts | yes | yes | no | no |
| Aggregate functions | yes | yes | no | yes |
| Prepared statements | yes | yes | no | no |
To work well your data model class must be default constructable and must not have const fields mapped to database cause they are assigned during queries. Otherwise code won't compile on line with member assignment operator.
For more details please check the projectwiki.
Note: Installation is not necessary if you plan to use the fetchContent method, see below in Usage.
Use a popular package manager likevcpkg and just install it with thevcpkg install sqlite-orm command.
Or you build it from source:
git clone https://github.com/fnc12/sqlite_orm.git sqlite_ormcd sqlite_ormcmake -B buildcmake --build build --target installYou might need admin rights for the last command.
If you use cmake, there are two supported ways how to use it with cmake (if another works as well or should be supported, open an issue).
Either way you choose, the include path as well as the dependency sqlite3 will be set automatically on your target. So usage is straight forward, but you need to have installed sqlite3 on your system (see Requirements below)
If you have installed the lib system wide and it's in your PATH, you can use find_package to include it in cmake. It will make a targetsqlite_orm::sqlite_orm available which you can link against. Have a look at examples/find_package for a full example.
find_package(SqliteOrm REQUIRED)target_link_libraries(mainPRIVATE sqlite_orm::sqlite_orm)
Alternatively, cmake can download the project directly from github during configure stage and therefore you don't need to install the lib before.Againt a targetsqlite_orm::sqlite_orm will be available which you can link against. Have a look at examples/fetch_content for a full example.
If you want to use the lib directly with Make or something else, just set the inlcude path correctly (should be correct on Linux already), sosqlite_orm/sqlite_orm.h is found. As this is a header only lib, there is nothing more you have to do.
- C++14 compatible compiler (not C++11 cause of templated lambdas in the lib).
- Sqlite3 installed on your system and in the path, so cmake can find it (or linked to you project if you don't use cmake)
In case you need a native SQLite client for macOS or Windows 10 you can use SqliteManhttps://sqliteman.dev. It is not a commercial. It is a free native client being developed by the maintainer of this repo.
About
❤️ SQLite ORM light header only library for modern C++
Topics
Resources
License
AGPL-3.0, MIT licenses found
Licenses found
Contributing
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Sponsor this project
Uh oh!
There was an error while loading.Please reload this page.
Packages0
Uh oh!
There was an error while loading.Please reload this page.