lovefield/simple.html

<!doctype html>
<html>
<head>
  <meta charset="utf-8" />
  <title>Simple Lovefield Example</title>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/lovefield/2.1.12/lovefield.js"></script>
</head>
<body>
<script>

  var schemaBuilder = lf.schema.create('users', 1);

  schemaBuilder.createTable('User').
  addColumn('id', lf.Type.INTEGER).
  addColumn('username', lf.Type.STRING).
  addColumn('email', lf.Type.STRING).
  addColumn('note', lf.Type.STRING).
  addColumn('age', lf.Type.INTEGER).
  addColumn('active', lf.Type.BOOLEAN).
  addColumn('created', lf.Type.DATE_TIME).
  addNullable(['note']).
  addPrimaryKey(['id'], true).
  addIndex('idxEmail', ['email'], false, lf.Order.ASC);

  let userDb;
  let user;
  
  const outFunc = rows => {
      console.log('=======================');
      rows.forEach(row => console.log(row));
  };
  
  const observerFunc = changes => {
     console.log(changes);
  };  

  schemaBuilder.connect()
  .then(db => {
    userDb = db;
    user = db.getSchema().table('User');

    return userDb.delete().from(user).exec();
  })
  .then(() => {

    const newRows = [];

    let row = user.createRow({
      username: 'john',
      email: 'john@test.com',
      age: 27,
      active: true,
      created: new Date()
    });
	newRows.push(row);

	row = user.createRow({
      username: 'ralph',
      email: 'ralph@test.com',
      age: 32,
      active: true,
      created: new Date(),
	  note: 'the admin'
    });
	newRows.push(row);

    row = user.createRow({
      username: 'jodie',
      email: 'jodie@test.com',
      age: 23,
      active: false,
      created: new Date()
    });
	newRows.push(row);

    return userDb.insertOrReplace().into(user).values(newRows).exec();
    //Constraint error: (202) Attempted to insert NULL value to non-nullable field User.created.

  })
  //select all users
  .then(() => userDb.select().from(user).exec())
  .then(outFunc)

  //select usernames
  .then(() => userDb.select(user.username)
                    .from(user)
					.orderBy(user.username, lf.Order.DESC)
					.exec())
  .then(outFunc)
  
  //select all active users
  .then(() => userDb.select()
                    .from(user)
					.where(user.active.eq(true))
					.exec())
  .then(outFunc)
  
  //aggregate functions
  .then(() => userDb.select(lf.fn.avg(user.age), lf.fn.count(user.id))
                   .from(user)
                   .exec())
  .then(outFunc)

  //group by
  .then(() => userDb.select(lf.fn.count(user.id), user.active)
                   .from(user)
				   .groupBy(user.active)
                   .exec())
  .then(outFunc)

  
  //update
  .then(() => userDb.update(user)
                    .set(user.active, true)
					.where(user.active.eq(false))
					.exec())
  .then(() => userDb.select().from(user).where(user.active.eq(true)).exec())
  .then(outFunc)
  
  //explain
  .then(() => console.log(userDb.select(user.id)
                    .from(user)
					.where(user.username.eq('john'))
					.explain()))
  .then(() => console.log(userDb.select(user.id)
                    .from(user)
					.where(user.email.eq('john@test.com'))
					.explain()));			
					

</script>
</body>
</html>