3) The update method

Update

The update method is called to update datas in the database. It receives as a parameter an object containing the data to be updated and always return an object with the fields data, error and success. The method is asynchronous and its use must be done using async await or promises.

Update parameters

The update method receives as a parameter an object with the following keys:

• beginTransaction: boolean, if true, the transaction will be performed using begin transaction, if false, the transaction will not use begin transaction;
• table: string with the value of the name of the table in which the update will be made;
• columns: object, each key of this object is the name of the column to be updated, this key in turn will have as value an object with the keys:
  • value: storing the new column value, can be numeric or string;
  • type: indicating the new value type, the accepted values for type are string and number, date, time and boolean values can be passed with both values;
• where(optional): object. Each key of the where object will be the name of the column that will compose the conditions of the where clause, this key that take the name of the column will be an object containing the following keys:
  • operator: string with the name of the operator that will be used. Accepted values: =, !=, <, >, <=. >=, is, is not, in, not in, like, ilike, not like, not ilike, between and not between.
  • value: integer, string, or array containing the value the operator references. Pass values as arrays only when making use of the in, between, not in and not between operators.
  • percent(optional): when making use of the operator LIKE and derivatives, the percent key can be included. It indicates where the wild card % will be. Accepts the values start, for LIKE ‘%foo’, end for LIKE ‘foo%’ and both for LIKE ‘%foo%’. For LIKE ‘foo’, the percent key can be omitted.
• logicalOperators(optional): array containing the logical operators of the where clause in the order they should be put according to the order of the columns in the where object. If where has only one column, logicalOperators is not needed.
• returning(optional): array containing the name of the columns that you want to be returned after the update. If returning is not specified, nothing will be returned.

Structure

See a generic example of this structure:

{
    beginTransaction: false || true,
    table: "table_name",
    columns: {
        columnName: {
            value: “value" || 1,
            type: “string" || "number",
        },
    },
    where: {
        coulumn_name: {
            operator: "operator",
            value: ["value"] || 1 || “value",
            percent: “start" || “end" || “both",
        },
    },
    logicalOperators: ["AND"],
    returning: ["*"],
}

Practical example

Below is an example of using the update method:

const update = {
        beginTransaction: true,
        table: "users",
        columns: {
            age: {
                value: "21",
                type: "number",
            },
        },
        where: {
            id: {
                operator: "=",
                value: 131,
            }
        },
        logicalOperators: [],
        returning: ["*"],
    }

// async await
const updateResult = await query.update(update);

// promise
query.update(update)
    .then( (result) => console.log(result.data))
    .catch( (error) => console.log(error));

Update return

If everything occurs well during the update, data will have an array with all the values specified on the returning, if no value was specified, data will have an empty array. If something goes wrong, error will be responsible for storing the error messages of the erros found. The error field stores errors that happened during the transaction, commit and rollback errors in the case of an update using begin transaction, and erros in passing parameters. Success stores only booleans for commit and rollback. See below the return of the update mede earlier:

{
  error: { 
    transaction: false,
    commit: false,
    rollback: false
    params: false
  },
  success: {
    commit: true,
    rollback: false
   },
  data: [
    {
      id: 131,
      name: 'Maria Flor’,
      age: 21
    }
  ]
}

It is verified that there was no error and the commit was successful.