Raw insert

Version available: v1.7

sqlite_orm has several ways of inserting data into database tables:

• storage.insert(const T &) - CRUD insert which requires an object, skips primary key columns and returns sqlite3_last_insert_rowid;
• storage.insert(const T &, columns(...)) - explicit CRUD insert where columns is a set of members to insert and also returns sqlite3_last_insert_rowid;
• storage.replace(const T &) - CRUD insert which requires an object and inserts all columns using REPLACE INTO ... query and does not returns anything.

These options cannot cover all cases of INSERT query and that is why raw insert feature exists in sqlite_orm. Raw insert allows specifying more details just like raw select.

Example of simple insert:

INSERT INTO users (id, name) 
VALUES(5, 'Little Mix')

will be

storage.insert(into<User>(), 
               columns(&User::id, &User::name), 
               values(std::make_tuple(5, "Little Mix")));

Also raw insert allows inserting two one more values within one query:

INSERT INTO singers (name) 
VALUES ('Sofia Reyes')('Kungs')

will be

storage.insert(into<Singer>(), 
               columns(&Singer::name), 
               values(std::make_tuple("Sofia Reyes"), std::make_tuple("Kungs")));

Also raw insert allows adding DEFAULT VALUES explicitly:

INSERT INTO users 
DEFAULT VALUES

will be

storage.insert(into<Singer>(), 
               default_values());

And one of the most powerful abilities of raw insert feature:

INSERT INTO artists_backup 
SELECT ArtistId, Name
FROM artists;

will be

storage.insert(into<ArtistBackup>(),
               select(columns(&Artist::id, &Artist::name)));

One can use raw insert prepared statement also:

auto statement = storage.prepare(insert(into<Singer>(), default_values()));
storage.execute(statement);

Note:

Return type of raw insert is void but it will be changed once RETURNING feature is merged. RETURNING is planned to be available within raw insert API and return std::vector<std::tuple<...>>. Right now RETURNING feature is not available.

External links

• INSERT on sqlite.org