5) The delete method

deleteFrom

The deleteFrom method is called to delete datas in the database. It receives as a parameter an object containing the data to be deleted 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.

deleteFrom parameters

The deleteFrom method receives as a parameter an object contains 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 delete will be made;
• using(optional): string with the value of the name of the table that will be used during the deletion;
• 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 deletion. If returning is not specified, nothing will be returned.

Structure

See below an generic example of this structure:

{
    beginTransaction: false || true,
    table: "table_name",
    using: "table_name",
    where: {
        column_name: {
            operator: "operator",
            value: ["value"] || 1 || “value",
            percent: “start" || “end" || “both",
        }
    },
    logicalOperators: [],
    returning: ["*"],
}

Practical example

Below is an example of using the deleteFrom method:

const deleting = {
    beginTransaction: true,
    table: “users”,
    where: {
        id: {
            operator: “=“,
            value: 134
        }
    },
    logicalOperators = [],
    returning: [“*"],
}

// async await
 const deleteResult = await query.deleteFrom(deleting);

// promise
query.deleteFrom(deleting)
    .then( (result) => console.log(result.data))
    .catch( (error) => console.log(error.error))

Delete return

If everything occurs well during the delete, 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 delete using begin transaction, and erros in passing parameters. Success stores only booleans for commit and rollback. See below the return of the delete 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.