Primary and Foreign Keys

When mapping tables, we need a way to create incremental ids and use these values in other tables.

And it is very simple. Check this example, or you can see below:

const autoIncrement = new AutoIncrement();

autoIncrement
  .initialId('user.id', 100) // Here we are saying that the initial id willb e 101.
  .initialId('address.id', 200);

const tUser = database.addTable('user')
  .addColumn('id', 'int', autoIncrement.valueGen('user.id'))
  .setUniqueKeys('id');

const incrementBy = 2;
database.addTable('address')
  .addColumn('id', 'int', autoIncrement.valueGen('address.id', incrementBy))// the ids will increment +2 every time for this table
  .addColumn('user_id', 'int', LastValue(tUser.getColumn('id'))) // notice here that we are capturing the id of previous user!
  .setUniqueKeys('id');

Using the values:

database.insert('user');
database.insert('address', {});

database.insert('user');
database.insert('address', {});


console.log(database.toSQL().join('\n'));
console.log(database.rollback().join('\n'));

Result

INSERT INTO "user" ("id") VALUES (101);
INSERT INTO "address" ("id", "user_id") VALUES (202, 101);
INSERT INTO "user" ("id") VALUES (102);
INSERT INTO "address" ("id", "user_id") VALUES (204, 101);
/*  --- ROLLBACK */ 
DELETE FROM "address" WHERE "id"=204;
DELETE FROM "user" WHERE "id"=102;
DELETE FROM "address" WHERE "id"=202;
DELETE FROM "user" WHERE "id"=101;