CRUD (Create, Read, Update, Delete)

Software applications usually store data in a database. There are different types of databases; the most common type is the relational one. A relational database has tables with rows and columns. Each row of a table is called a record. Each column represents an attribute. We don’t want to lose data, so most applications store data in one form or another. Usually, they use persistent storage. This means that the storage retains data even when it is powered off—for example, hard disks or SSDs.

CRUD is an acronym for four operations that every software application should be able to do:

• Create
• Read
• Update
• Delete

The concept of CRUD dates back to early computer programming and database management systems in the 1960s and 1970s. It became more popular in the 1980s when it was used in books or articles about software development and database management systems. James Martin’s “Managing the Database Environment” was one such book in 1983.

CRUD has been around for a while and is as relevant today as it was back then. We also use these operations in modern software applications. For example, when we use REST APIs to interact with a network device or MySQL to create and select data.

In this lesson, I’ll explain the four operations and give you examples of REST API and MySQL. These two are probably the most common that you’ll use as a network engineer. Many network devices and tools use REST APIs to interact with them.

Operations

Let’s take a closer look at those four CRUD operations.

I’ll give you examples of an imaginary system that keeps track of network devices. Each network device is an object, and this is the data we want to store:

"network-device": {
  "id":,
  "hostname": ,
  "ip_address": , 
  "model": , 
  "software_version": 
}

The fields above are self-explanatory. Many databases use an ID for unique records. When you create new objects, it’s possible to create an ID automatically.

Create

The create operation means we insert new data into a database. In our fictional system, that means we have to supply the values for the hostname, IP address, model, and software version of a network device. After using the create operation, we’ll have created a new object.

REST API

Here’s how to accomplish this with a REST API. You would use the HTTP POST method. This creates a new resource. Here’s an example:

POST /api/network-devices HTTP/1.1
Content-Type: application/json

{
    "hostname": "R1",
    "ip_address": "192.168.1.1",
    "model": "Cisco ISR 4451",
    "software_version": "16.9.3"
}

This would add a device with the hostname “R1” to our system. HTTP uses status code to tell if it was successful or not:

• 201 Created: This indicates that the network device was successfully created. The server might also include a Location header pointing to the newly created resource.
• 400 Bad Request: This indicates that the server could not understand the request due to invalid syntax, such as missing required fields.
• 409 Conflict: This could be used if the resource already exists and can’t be recreated with the same identifier, like a unique hostname in the database.

MySQL

With SQL, we use the INSERT clause (or statement) , and we supply the column names and the values to insert:

INSERT INTO network_devices 
    (hostname, ip_address, model, software_version) 
VALUES 
    ('R1', '192.168.1.1', 'Cisco ISR 4451', '16.9.3');

We specify the table (network_devices) and the column names. Under values, we specify the values we want to insert.

Read

The read operation is used to fetch and display data from a database. It’s similar to a search function. You can specify what objects you want to retrieve by filtering data based on criteria. For example, you could search for all network devices that use software version 16.9.3 or all router models “Cisco ISR 4451”. This operation does not make any changes to data.

REST API

With a REST API, we have to use the HTTP GET method. For example, let’s say we want to retrieve the first stored network device that uses ID 1:

GET /api/network-devices/1

Would return us:

{
  "id": 1,
  "hostname": "R1",
  "ip_address": "192.168.1.1",
  "model": "Cisco ISR 4451",
  "software_version": "16.9.3"
}

Here’s what it could look like if you search for devices that use a specific software version:

GET /api/network-devices?software_version=16.9.3

Which returns:

[
  {
    "id": 1,
    "hostname": "R1",
    "ip_address": "192.168.1.1",
    "model": "Cisco ISR 4451",
    "software_version": "16.9.3"
  },
  {
    "id": 3,
    "hostname": "R3",
    "ip_address": "192.168.1.3",
    "model": "Cisco ISR 4331",
    "software_version": "16.9.3"
  },
  {
    "id": 5,
    "hostname": "SW2",
    "ip_address": "192.168.1.12",
    "model": "Cisco Catalyst 3850",
    "software_version": "16.9.3"
  }
]

REST APIs also return a HTTP status code to tell you whether it was successful or not. You most likely would see one of the two HTTP status codes:

• 200 OK: This indicates that the request was successful and the server has returned the requested data.
• 404 Not Found: This means that a network device with the specified ID does not exist.

MySQL

SQL uses the SELECT clause to retrieve data from the database. For example:

SELECT id, hostname, ip_address, model, software_version FROM network_devices WHERE id = 1;

This gives us the following output:

+----+----------+---------------+----------------+-----------------+
| id | hostname |  ip_address   |     model      | software_version|
+----+----------+---------------+----------------+-----------------+
|  1 |    R1    | 192.168.1.1   | Cisco ISR 4451 |      16.9.3     |
+----+----------+---------------+----------------+-----------------+

multiple:

SELECT id, hostname, ip_address, model, software_version 
FROM network_devices 
WHERE model="Cisco ISR 4451";

with:

+----+----------+---------------+----------------+-----------------+
| id | hostname |  ip_address   |     model      | software_version|
+----+----------+---------------+----------------+-----------------+
|  1 |    R1    | 192.168.1.1   | Cisco ISR 4451 |      16.9.3     |
|  3 |    R3    | 192.168.1.3   | Cisco ISR 4451 |      16.9.5     |
|  5 |    SW2   | 192.168.1.12  | Cisco ISR 4451 |      16.9.3     |
+----+----------+---------------+----------------+-----------------+

Update

The update operation allows us to modify existing records in a database.  It’s usually possible to modify a single or multiple records with a single operation. For example, if we upgrade the software on all our Cisco ISR 4451 routers from 16.9.3 to 17.3.1, we probably want to do update all router records in our database.

REST API

We use the HTTP PUT or PATCH method. For example, here’s how to change the software version for the router with ID 1:

PUT /api/network-devices/1
Content-Type: application/json

{
  "software_version": "17.3.1"
}

You’ll probably see one of the following HTTP status codes after making this request:

• 200 OK: This indicates that the update was successful and the resource has been modified.
• 204 No Content: This can also be used to indicate that the update was successful, but no content is returned in the response.
• 400 Bad Request: This indicates invalid syntax in the request.
• 404 Not Found: This is used if the device with the specified ID was not found.

MySQL

SQL uses the UPDATE clause to modify data. Here’s an example:

UPDATE network_devices SET software_version = '17.3.1' WHERE id = 1;

This changes the software version to 17.3.1 for the network device with ID 1.

Delete

Last but not least, the delete operation removes data from a database. Let’s say we want to delete a network device with ID 1 from our database.

REST API

REST APIs use the HTTP delete method to remove something. Here’s what it looks like:

DELETE /api/network-devices/1

You’ll likely see one of these HTTP status codes:

• 204 No Content: This indicates that the deletion was successful, and no content is returned.
• 404 Not Found: This means that a network device with the specified ID doesn’t exist.
• 403 Forbidden: This could be used if the user does not have permission to delete the resource.

MySQL

SQL uses the DELETE clause to achieve the same thing:

DELETE FROM network_devices WHERE id = 1;

This removes the object where the ID matches 1.

Conclusion

In this lesson, you learned the concept of CRUD (Create, Read, Update, Delete) operations in software development and database management. I hope you enjoyed this lesson. If you have any questions, please leave a comment.