Full Code Example

function doGet(e) {
  var Template = HtmlService.createTemplateFromFile("index")
    .evaluate()
    .setTitle("Test CRUD WebApp")
    .setFaviconUrl("https://cdn-icons-png.freepik.com/512/9850/9850812.png")
    .addMetaTag("viewport", "width=device-width, initial-scale=1")
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  return Template;
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

const db = DB.init("testing-DB", YOUR_ID);

const categoryTableConfig = {
  tableName: "CATEGORY",
  historyTableName: "DELETED_CATEGORY",
  fields: {
    name: { type: "string", treatEmptyStringAsMissing: true },
    created_at: { type: "date", default: "now" },
  },
};

const productTableConfig = {
  tableName: "PRODUCT",
  historyTableName: "DELETED_PRODUCT",
  fields: {
    name: { type: "string", treatEmptyStringAsMissing: true },
    price: "number",
    category_fk: "number",
    created_at: { type: "date", default: "now" },
  },
};

const customerTableConfig = {
  tableName: "CUSTOMER",
  historyTableName: "DELETED_CUSTOMER",
  fields: {
    first_name: { type: "string", treatEmptyStringAsMissing: true },
    last_name: { type: "string", treatEmptyStringAsMissing: true },
    email: "string",
    address: { type: "string", treatNullAsMissing: true },
    created_at: { type: "date", default: "now" },
  },
};

const orderTableConfig = {
  tableName: "ORDER",
  historyTableName: "DELETED_ORDER",
  fields: {
    customer_fk: "number",
    created_at: { type: "date", default: "now" },
  },
};

function createSchema() {
  console.log(db.createTable(categoryTableConfig));
  console.log(db.createTable(productTableConfig));
  console.log(db.createTable(customerTableConfig));
  console.log(db.createTable(orderTableConfig));
  console.log(db.createTable(orderDetailConfig));
}

console.log(db.putTableIntoDbContext(categoryTableConfig));
console.log(db.putTableIntoDbContext(productTableConfig));
console.log(db.putTableIntoDbContext(customerTableConfig));
console.log(db.putTableIntoDbContext(orderTableConfig));

const responseCreation = db.createManyToManyTableConfig({
  entity1TableName: orderTableConfig.tableName,
  entity2TableName: productTableConfig.tableName,
  fieldsRelatedToBothEntities: {
    quantity: "number",
  },
});

const orderDetailConfig = responseCreation.data;

console.log(db.putTableIntoDbContext(orderDetailConfig));

/**
 * ||=====================================================||
 * ||                   CRUD for CATEGORY                 ||
 * ||=====================================================||
 */

function getCategoryRelatedRecords(
  foreignKey,
  field = "category_fk",
  fieldIndex = 4,
  options = {},
  useCache = false
) {
  const response = db.getRelatedRecords(
    foreignKey,
    productTableConfig.tableName,
    field,
    fieldIndex,
    options,
    useCache
  );
  return JSON.stringify(response);
}

function createCategory(newCategory) {
  newCategory.created_at = new Date(newCategory.created_at);
  const response = db.create(
    categoryTableConfig.tableName,
    newCategory,
    Object.keys(categoryTableConfig.fields)
  );

  console.log(response);
  return JSON.stringify(response);
}

function readCategoryTable() {
  const response = db.getAll(
    categoryTableConfig.tableName,
    (options = {}),
    (useCache = false)
  );
  console.log(response.status);
  console.log(response.message);

  return JSON.stringify(response);
}

function updateCategory(updatedCategory, id) {
  const response = db.update(
    categoryTableConfig.tableName,
    id,
    updatedCategory,
    Object.keys(categoryTableConfig.fields)
  );

  console.log(response);

  return JSON.stringify(response);
}

function readCategoryById(id) {
  const response = db.read(categoryTableConfig.tableName, id);

  console.log(response);

  return JSON.stringify(response);
}

function removeCategory(id) {
  const response = db.remove(
    categoryTableConfig.tableName,
    categoryTableConfig.historyTableName,
    id
  );

  console.log(response);

  return JSON.stringify(response);
}

/**
 * ||=====================================================||
 * ||               CRUD for PRODUCT TABLE                ||
 * ||=====================================================||
 */
function createProduct(newProduct) {
  // Convert dates as needed
  if (newProduct.created_at) {
    newProduct.created_at = new Date(newProduct.created_at);
  }
  const response = db.create(
    productTableConfig.tableName,
    newProduct,
    Object.keys(productTableConfig.fields)
  );
  return JSON.stringify(response);
}

function readProductTable() {
  const response = db.getAll(
    productTableConfig.tableName,
    {}, // options = {}
    false // useCache = false
  );
  return JSON.stringify(response);
}

function readProductById(id) {
  const response = db.read(productTableConfig.tableName, id);
  return JSON.stringify(response);
}

function updateProduct(updatedProduct, id) {
  if (updatedProduct.created_at) {
    updatedProduct.created_at = new Date(updatedProduct.created_at);
  }
  const response = db.update(
    productTableConfig.tableName,
    id,
    updatedProduct,
    Object.keys(productTableConfig.fields)
  );
  return JSON.stringify(response);
}

function removeProduct(id) {
  const response = db.removeWithCascade(
    productTableConfig.tableName,
    productTableConfig.historyTableName,
    id
  );
  return JSON.stringify(response);
}

/**
 * ||=====================================================||
 * ||              CRUD for CUSTOMER TABLE                ||
 * ||=====================================================||
 */

function getRelatedCustomerRecords(
  foreignKey,
  field = "customer_fk",
  fieldIndex = 2,
  options = {},
  useCache = false
) {
  const response = db.getRelatedRecords(
    foreignKey,
    orderTableConfig.tableName,
    field,
    fieldIndex,
    options,
    useCache
  );

  return JSON.stringify(response);
}

function createCustomer(newCustomer) {
  if (newCustomer.created_at) {
    newCustomer.created_at = new Date(newCustomer.created_at);
  }
  const response = db.create(
    customerTableConfig.tableName,
    newCustomer,
    Object.keys(customerTableConfig.fields)
  );
  return JSON.stringify(response);
}

function readCustomerTable() {
  const response = db.getAll(customerTableConfig.tableName, {}, false);
  return JSON.stringify(response);
}

function readCustomerById(id) {
  const response = db.read(customerTableConfig.tableName, id);
  return JSON.stringify(response);
}

function updateCustomer(updatedCustomer, id) {
  if (updatedCustomer.created_at) {
    updatedCustomer.created_at = new Date(updatedCustomer.created_at);
  }
  const response = db.update(
    customerTableConfig.tableName,
    id,
    updatedCustomer,
    Object.keys(customerTableConfig.fields)
  );
  return JSON.stringify(response);
}

function removeCustomer(id) {
  const response = db.remove(
    customerTableConfig.tableName,
    customerTableConfig.historyTableName,
    id
  );
  return JSON.stringify(response);
}

/**
 * ||=====================================================||
 * ||                 CRUD for ORDER TABLE                ||
 * ||=====================================================||
 */
function createOrder(newOrder) {
  if (newOrder.created_at) {
    newOrder.created_at = new Date(newOrder.created_at);
  }
  const response = db.create(
    orderTableConfig.tableName,
    newOrder,
    Object.keys(orderTableConfig.fields)
  );
  return JSON.stringify(response);
}

function readOrderTable() {
  const response = db.getAll(orderTableConfig.tableName, {}, false);
  return JSON.stringify(response);
}

function readOrderById(id) {
  const response = db.read(orderTableConfig.tableName, id);
  return JSON.stringify(response);
}

function updateOrder(updatedOrder, id) {
  if (updatedOrder.created_at) {
    updatedOrder.created_at = new Date(updatedOrder.created_at);
  }
  const response = db.update(
    orderTableConfig.tableName,
    id,
    updatedOrder,
    Object.keys(orderTableConfig.fields)
  );
  return JSON.stringify(response);
}

function removeOrder(id) {
  const response = db.removeWithCascade(
    orderTableConfig.tableName,
    orderTableConfig.historyTableName,
    id
  );
  return JSON.stringify(response);
}

/**
 * ||=====================================================||
 * ||         CRUD for ORDER_DETAIL (Many-to-Many)        ||
 * ||=====================================================||
 * The 'orderDetailConfig' object was generated via:
 * const responseCreation = db.createManyToManyTableConfig({ ... });
 * const orderDetailConfig = responseCreation.data;
 */
function createOrderDetail(newOrderDetail) {
  if (newOrderDetail.created_at) {
    newOrderDetail.created_at = new Date(newOrderDetail.created_at);
  }
  // orderDetailConfig.fields => { created_at, order_id, product_id, quantity, ... }
  const response = db.create(
    orderDetailConfig.tableName,
    newOrderDetail,
    Object.keys(orderDetailConfig.fields)
  );
  return JSON.stringify(response);
}

function readOrderDetailTable() {
  const response = db.getAll(orderDetailConfig.tableName, {}, false);
  return JSON.stringify(response);
}

function readOrderDetailById(id) {
  const response = db.read(orderDetailConfig.tableName, id);
  return JSON.stringify(response);
}

function updateOrderDetail(updatedOrderDetail, id) {
  if (updatedOrderDetail.created_at) {
    updatedOrderDetail.created_at = new Date(updatedOrderDetail.created_at);
  }
  const response = db.update(
    orderDetailConfig.tableName,
    id,
    updatedOrderDetail,
    Object.keys(orderDetailConfig.fields)
  );
  return JSON.stringify(response);
}

function removeOrderDetail(id) {
  const response = db.remove(
    orderDetailConfig.tableName,
    orderDetailConfig.historyTableName,
    id
  );
  return JSON.stringify(response);
}

function readOrderDetailFromOrder(sourceId) {
  const response = db.getJunctionRecords(
    orderDetailConfig.tableName,
    orderTableConfig.tableName,
    productTableConfig.tableName,
    sourceId,
    (options = {})
  );

  console.log(response.status);
  console.log(response.message);
  console.log(response.metadata);

  for (record of response.data) {
    console.log(record);
  }

  return JSON.stringify(response);
}

function readOrderDetailFromProduct(sourceId) {
  const response = db.getJunctionRecords(
    orderDetailConfig.tableName,
    productTableConfig.tableName,
    orderTableConfig.tableName,
    sourceId,
    (options = {})
  );

  console.log(response.status);
  console.log(response.message);
  console.log(response.metadata);

  for (record of response.data) {
    console.log(record);
  }

  return JSON.stringify(response);
}