Insert, update, delete

insert_one#

The object form is recommended; each field name sits next to its value:

from('members').insert_one({name: 'Zeynep', surname: 'Kaya', email: 'zeynep@example.com', age: 27});

In the positional form the values come first and the field names are given with fields(...). The number of values must match the number of fields:

from('members')
    .insert_one('Can', 'Demir', 'can@example.com', 40, '01.05.1986')
    .fields('name', 'surname', 'email', 'age', 'birth_date')
    .get();

Fields that are not given are filled like this:

• autoincrement fields get the next number,
• guid fields get a new id,
• fields with a default get that value,
• the rest become null. If such a field is nullable: false, the insert fails.

.get() returns the inserted rows; use it to see generated ids.

insert_many#

from('members').insert_many([
    {name: 'Ece', email: 'ece@example.com'},
    {name: 'Mert', email: 'mert@example.com'}
]);

If a row is rejected, no rows are inserted; the error says at which row it stopped.

update#

from('members')
    .where({'email': 'osman@example.com'})
    .update({'name': 'Mehmet', 'age': 46})
    .get();

• Rules (check, max_length, uniqueness) apply to the new values too.
• When an indexed field changes, its index is updated.
• Password fields are hashed again.
• .commit() may be written but changes nothing: every statement is durable on its own.

An update without where is rejected, so that a whole table is not changed by accident. To change every row, write .all():

# error: update without where would change every row; add .where({...}) or .all()
from('members').update({'gender': 0});

from('members').all().update({'gender': 0});

delete#

from('members').where({'age': {lt: 30}}).delete().get();

.get() returns the deleted rows. A delete without where needs .all() too.

Atomicity#

Every statement is one transaction: it applies completely or not at all. If a multi-row insert_many, update or delete stops on an error, no row changes. A committed statement is first written to the write-ahead log (WAL) and flushed to disk, so it survives a sudden shutdown.

# error: row 2: email: value same@example.com already exists (unique index idx_email) (nothing was inserted)
from('members').insert_many([
    {name: 'One', email: 'same@example.com'},
    {name: 'Two', email: 'same@example.com'}
]);

Grouping several statements into one transaction (begin / commit) is not available yet.