Delete Query Tutorial

Deleting entities based on specific criteria is a common operation in database management. Onyx Database allows you to efficiently delete entities that match a given condition. This tutorial demonstrates how to bulk delete all Player entities where the active attribute is set to false.

Steps to Perform a Delete Query (Onyx Cloud Database)

1

Define the Schema

Use the JSON schema below to define the Player entity in the Onyx Cloud Database.
1{
2  "tables": [
3    {
4      "name": "Player",
5      "identifier": {
6        "name": "id",
7        "type": "Long",
8        "generator": "Sequence"
9      },
10      "partition": "",
11      "attributes": [
12        {
13          "name": "id",
14          "type": "Long",
15          "isNullable": false
16        },
17        {
18          "name": "firstName",
19          "type": "String",
20          "isNullable": false,
21          "maxSize": 100
22        },
23        {
24          "name": "lastName",
25          "type": "String",
26          "isNullable": false,
27          "maxSize": 100
28        },
29        {
30          "name": "active",
31          "type": "Boolean",
32          "isNullable": false
33        }
34      ],
35      "relationships": [],
36      "indexes": []
37    }
38  ],
39  "revisionDescription": "Schema for Player entity"
40}
2

Create a Query with Criteria

Utilize the query builder syntax to create a query that specifies the criteria for deletion. In this case, we are deleting all Player entities where active is false.
1// Using Query Builder Syntax
2const query = db.from('Player')
3    .where(eq('active', false));
3

Confirm Entities to Delete

Before executing the delete operation, confirm that there are entities matching the criteria.
1const inactivePlayers = await query.list();
2
3// There should only be one inactivePlayer: Calvin Johnson
4for (const player of inactivePlayers) {
5    console.log(`${player.firstName} ${player.lastName} is not active.`);
6}
4

Execute the Delete Operation

Invoke the delete() method on the query to perform the delete action.
1// Execute the delete action
2const deletedCount = await query.delete();
3console.log(`Deleted ${deletedCount} inactive players.`);
5

Verify the Deletion

Re-execute the query to confirm that the entities have been deleted.
1const remainingInactivePlayers = await query.list();
2console.log(`There are ${remainingInactivePlayers.length} inactive players now.`); // should be zero
Next Tutorial: Update Query

Important Notes

  • The delete() method operates on all entities that match the specified criteria.
  • Ensure that your criteria are accurate to avoid unintended data loss.
  • Always back up your data before performing bulk delete operations in a production environment.

Troubleshooting

  • No Entities Deleted: Confirm that entities matching the criteria exist in the database.
  • Criteria Not Working: Verify that the attribute names and values in your criteria match the entity definitions.
  • Errors During Deletion: Check for any constraints or relationships that might prevent deletion, such as foreign key constraints.

Next Steps

Now that you have learned how to perform delete queries, you can explore more advanced operations: