What is CRUD

CRUD stands for “create, read, update, and delete” operations, what is a common set of operations that are performed on data in a database or in an application.

CRUD is the abbreviation that refers to the four basic operations on a software application, doesn’t matter if the application is a mobile app, a web application or a desktop software.

The four operations are: Create, Read, Update and Delete.

In short, the Create operation is similar an insertion of a nre record in a database, the Read is the operation of recover a record from database, Update operation is the update of values of a record and the Delete operation is the action of remove an intire record from database. We could say thjat in a database, the CRUD operations map to SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

The CRUD operations can also apply to higher level functions of an application such as soft deletes where data is not actually deleted but marked as deleted via a status. Or even to a LIST operation that uses the Read operation to read and list the result dataset. I like to call this as CRUD-L: Create, Read, Update, Delete, List.

Here is a table that lists the equivalent CRUD operations to SQL and HTTP methods:

CRUD Operation	SQL Condition	HTTP Method
Create	INSERT	POST
Read	SELECT	GET
Update	UPDATE	PUT/PATCH
Delete	DELETE	DELETE

The Create operation corresponds to an SQL INSERT statement and an HTTP POST method.
The Read operation corresponds to an SQL SELECT statement and an HTTP GET method.
The Update operation corresponds to an SQL UPDATE statement and an HTTP PUT/PATCH method.
The Delete operation corresponds to an SQL DELETE statement and an HTTP DELETE method.

Note that the choice of HTTP method should be based on the specific requirements of your application and the conventions of the RESTful API. For example, PUT method is usually used to replace a resource in its entirety, while the PATCH method is used to make partial updates to a resource.

Book catalog schema

To help us to understand the concepts in this post, let’s consider a simple application that manages a book collection. The fields of the book would be: title, author of the book, the date it was published, in wich language and its type.

The code samples in this post are meant to demonstrate single aspects of each CRUD operation and are likely not functional. They are intended solely for a conceptual understanding.

Please read the tutorials and watch the courses on this site to learn how to properly use the languages and frameworks used as examples here.

Let’s see examples of schemas for a “book catalog”:

Here is an example of a SQL schema for a “book” table:

CREATE TABLE book (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    published_at DATE NOT NULL,
    author VARCHAR(255) NOT NULL,
    language VARCHAR(255) NOT NULL,
    type ENUM('Novel', 'Essay', 'Poetry') NOT NULL
);

This creates a table named “book” with the following columns:

id: an auto-incrementing primary key
title: a string of up to 255 characters, not nullable
published_at: a date, not nullable
author: a string of up to 255 characters, not nullable
language: a string of up to 255 characters, not nullable
type: a enumeration of one of the values ‘Novel’, ‘Essay’, ‘Poetry’ not nullable

Here is an example of a JSON schema for a “book” resource in a REST API:

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "properties": {
    "id": {
      "type": "integer"
    },
    "title": {
      "type": "string"
    },
    "published_at": {
      "type": "string",
      "format": "date"
    },
    "author": {
      "type": "string"
    },
    "language": {
      "type": "string"
    },
    "type": {
      "type": "string"
    }
  },
  "required": [
    "title",
    "published_at",
    "author",
    "language",
    "type"
  ]
}

This defines a JSON schema with the following properties:

id: an integer
title: a string
published_at: a date in string format
author: a string
language: a string
type: a string and all are required

This schema will be used on all our examples for the rest of this lesson.

The CREATE operation in CRUD

The Create operation in CRUD is used to insert new data into a database or an application’s data store.

Imagine that we are developing the create feature for book to this application, considering the schemas presented, here is an example of a Create operation in some languages and stacks: NodeJS, PHP, Laravel, SQL.

In Node.js using the book table would be a RESTful endpoint that accepts a POST request with a JSON payload representing a new book:

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const mysql = require('mysql');

app.use(bodyParser.json());

// Connect to the database
const connection = mysql.createConnection({
  host: 'hostname',
  user: 'username',
  password: 'password',
  database: 'database'
});

connection.connect(function(err) {
  if (err) throw err;
  console.log('Connected to database');
});

app.post('/books', function (req, res) {
  var newBook = req.body;
  // Validate and sanitize newBook here
  const sql = "INSERT INTO book (title, published_at, author, language, type) VALUES (?, ?, ?, ?, ?)";
  const values = [newBook.title, newBook.published_at, newBook.author, newBook.language, newBook.type];
  
  // Insert newBook into database
  connection.query(sql, values, function (err, result) {
    if (err) throw err;
    res.status(201).json({message: "Book created successfully"});
  });
});

In this example, we use the mysql library to connect to a database, and then use its query method to perform the INSERT operation. The bodyParser middleware is used to parse the incoming JSON data, which is used as the newBook object in the SQL query.

This endpoint listens for a POST request to the /books route. It retrieves the new book data from the request body, then validate and sanitize the data, then insert it into the database. The endpoint then sends back a JSON response with a “201 Created” status code and a message indicating that the book was created successfully.

An example of a SQL Create statement using the “book” table would be:

INSERT INTO book (title, published_at, author, language, type) VALUES ('The Great Gatsby', '2022-01-01', 'F. Scott Fitzgerald', 'English', 'Novel');

This statement would insert a new book with title “The Great Gatsby”, published on “2022-01-01”, written by “F. Scott Fitzgerald”, in “English” language, and with “Novel” type into the “book” table.

An example of a Create operation in PHP using ADODB and the book table would be:

<?php
require('adodb/adodb.inc.php');

$dsn = "mysql://username:password@localhost/myDB";
$db = ADONewConnection($dsn);

$title = $_POST["title"];
$published_at = $_POST["published_at"];
$author = $_POST["author"];
$language = $_POST["language"];
$type = $_POST["type"];

$query = "INSERT INTO book (title, published_at, author, language, type) VALUES ('$title', '$published_at', '$author', '$language', '$type')";
$db->Execute($query);

echo "New book created successfully";
?>

This script handle a form submission, create a new connection to the database using the ADONewConnection() method and pass the DSN string, then it creates the query and execute it using the Execute() method, which insert the data into the book table of the database.

In Laravel, the Create operation could be implemented using Eloquent, Laravel’s ORM (Object-Relational Mapping). Here is an example of a controller method that creates a new book and saves it to the database using the book table:

public function store(Request $request)
{
    $book = new Book;
    $book->title = $request->title;
    $book->published_at = $request->published_at;
    $book->author = $request->author;
    $book->language = $request->language;
    $book->type = $request->type;
    $book->save();

    return redirect()->route('books.index')->with('success', 'Book created successfully');
}

Please consider this examples as just simple demonstrations of the CRUD operations and that does not include any error handling or input validation. In a production environment, you should always validate and sanitize user input and handle any errors that may occur during the database or application operations.

So, to insert the new book, to create a book, in this application it should be necessary a client call, probably in a REST way, like from a REST API client as Postman, from the interface of the application. A client could create a new book by sending a POST request with a JSON payload representing the new book:

{
    "title": "The Great Gatsby",
    "published_at": "2022-01-01",
    "author": "F. Scott Fitzgerald",
    "language": "English",
    "type": "Novel"
}

Here is an example of how you could use curl to send a POST request to the “/books” endpoint and create a new book:

curl -X POST -H "Content-Type: application/json" -d '{
    "title": "The Great Gatsby",
    "published_at": "2022-01-01",
    "author": "F. Scott Fitzgerald",
    "language": "English",
    "type": "Novel"
}' http://localhost:3000/books

This command sends a POST request to the "http://localhost:3000/books" endpoint with a JSON payload representing the new book.

The -X option specifies the request method (in this case, POST)
The -H option sets the “Content-Type” header to “application/json” to indicate that the request body is in JSON format
The -d option sets the request body to the JSON payload representing the new book

You should get a response from the endpoint similar to this:

{
  "message": "Book created successfully"
}

This means that the book was created successfully

This example assumes that your endpoint is running locally on port 3000, you should replace this by the actual endpoint URL.

The READ operation in CRUD

The READ operation, also known as the Retrieve operation, is used to retrieve data from a database or other data storage system.

In the context of a relational database, this typically corresponds to an SQL SELECT statement. The purpose of the SELECT statement is to retrieve data from one or more tables in the database, based on certain conditions or criteria.

In the context of a RESTful API, the READ operation is typically performed using the HTTP GET method, which is used to retrieve a representation of a resource.

In summary, the READ operation is used to retrieve data from a database or other data storage system and it is typically performed using an SQL SELECT statement in a relational database and an HTTP GET method in a RESTful API.

In an application this could be the LIST feature that show the last inserted records, in our examples it would the last books created. Or the result of a search or even the feature of showing the details of a record in a HTML page.

Here are examples of READ operation to retrieve all records from database:

A SQL READ operation using the “book” table:

SELECT * FROM book

An example of a READ operation in Node.js using the “book” table:

app.get('/books', function (req, res) {
  var bookId = req.params.id;
  // Validate and sanitize bookId here
  // Retrieve book from database using bookId
  var book = null;
  // Code to execute a SQL SELECT statement to retrieve the book with id = bookId  
  const books = database.query("SELECT * FROM book", function (err, result, fields) {
    if (err) throw err;
    res.status(200).json(result);
  });
});

This endpoint listens for a GET request to the "/books" route, retrieves all the books from the database, and sends them back as a JSON response. NOte that retrieveBooksFromDB() should be implemented by the developer.

Here is an example of a READ operation in PHP using the “book” table and ADODB:

<?php
require('adodb/adodb.inc.php');

$dsn = "mysql://username:password@localhost/myDB";
$db = ADONewConnection($dsn);

$query = "SELECT * FROM book";
$books = $db->getAll($query);

foreach($books as $book)
{
    echo $book['title'] . "<br>";
}

?>

This script creates a new connection to the database using the ADONewConnection() method and pass the DSN string, then it creates the query and execute it using the GetAll() method, which retrieves all the books from the “book” table of the database and loop through the results.

Here is an example of a READ operation in Laravel using the “book” table:

public function index()
{
    $books = Book::all();
    return view('books.index', ['books' => $books]);
}

This controller method retrieves all the books from the “book” table using the Eloquent ORM’s all() method, and then pass the result to the view ‘books.index’.

Off course we shouldn’t retrieve all records from database in all situations. This is just a simple example. If we want to show only one record, or retrieve only one record, we can use the id value to do it, we should do something like the following samples, receiving the value to be searched.

See some examples for READ operation based on an id value provided:

An example of a SQL READ operation using the “book” table to retrieve a book by its id, in this sample using a direct value:

This statement retrieves the row from the “book” table where the id is 1.

    SELECT * FROM book WHERE id = 1

Here is an example of a READ operation in Node.js using the “book” table to retrieve a book by its id, the id must be send with the API endpoint request:

app.get('/books/:id', function (req, res) {
  var bookId = req.params.id;
  // Validate and sanitize bookId here
  // Retrieve book from database using bookId
  var book = null;
  // Code to execute a SQL SELECT statement to retrieve the book with id = bookId  
  connection.query('SELECT * FROM books WHERE id = ?', [bookId], function (error, results, fields) {
    if (error) throw error;
    book = results[0];
    connection.end();
  });
  
  // Return the retrieved book
  res.status(200).json(book);
});

This endpoint listens for a GET request to the "/books/:id" route, retrieves the book with the id passed in the parameter from the database, and sends it back as a JSON response.

An example of a READ operation in PHP using the “book” table and ADODB to retrieve a book by its id:

<?php
require('adodb/adodb.inc.php');

$dsn = "mysql://username:password@localhost/myDB";
$db = ADONewConnection($dsn);

$book_id = $_GET["id"];
$query = "SELECT * FROM book WHERE id = ".$book_id;
$book = $db->getRow($query);

echo $book['title'] . "<br>";

?>

This script after the database connection, creates the query using the id passed by the $_GET variable, then it executes it using the GetRow() method, which retrieves the book based on its id value.

An example of a READ operation in Laravel using the “book” table to retrieve a book by its id:

public function show($id)
{
    $book = Book::find($id);
    return view('books.show', ['book' => $book]);
}

This controller method retrieves a book by its id from the “book” table using the Eloquent ORM’s find() method, and then pass the result to the view books.show.

Here is an example of how you could use curl to send a GET request to the /books/3 endpoint and retrieve a book with id 3:

curl http://localhost:3000/books/3

This command sends a GET request to the http://localhost:3000/books/3 endpoint and retrieve the book with id 3.

You should get a response from the endpoint similar to this:

{
  "id": 3,
  "title": "The Catcher in the Rye",
  "published_at": "2021-01-01",
  "author": "J.D. Salinger",
  "language": "English",
  "type": "Novel"
}

The UPDATE operation in CRUD

The UPDATE operation is used to modify or update existing data in a database or other data storage system.

In the context of a relational database, the UPDATE operation is performed using an SQL UPDATE statement. The syntax of an SQL UPDATE statement is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE some_column = some_value;

This statement updates the data in the specified table by setting the values of one or more columns to new values, based on certain conditions or criteria specified in the WHERE clause. For example, using our “book” table scenario, the following SQL statement updates the name of the author of a book where the id is 3:

UPDATE book
SET author = 'J.D. Salinger'
WHERE id = 3;

In the context of a RESTful API, the UPDATE operation is typically performed using the HTTP PUT or PATCH method.

The PUT method is used to replace a resource in its entirety
The PATCH method is used to make partial updates to a resource.

For example, the following PUT request should be performed by the server to updates the whole “book” record that has the id = 3:

PUT /books/1
Content-Type: application/json
{
    "title": "The Catcher in the Rye",
    "published_at": "2021-01-01",
    "author": "J.D. Salinger",
    "language": "English",
    "type": "Novel"
}

Some examples of UPDATE operation considering the modification of the whole record, even if only one field would be modified, web should set all the record values:

An SQL UPDATE operation using the “book” table to update the book with id = 3:

UPDATE book
SET title = 'The Catcher in the Rye 2.0', published_at = '2022-01-01', author = 'J.D. Salinger', language = 'English', type = 'Novel'
WHERE id = 3;

This statement updates all the fields of the book with id = 3.

An UPDATE operation in Node.js using the “book” table to update the book with id = 3:

app.put('/books/:id', function (req, res) {
  var bookId = req.params.id;
  var updatedBook = req.body;
  // Validate and sanitize updatedBook here
  // Update book with id = bookId in database
  // For example using an SQL query:
  connection.query("UPDATE book SET title = ?, published_at = ?, author = ?, language = ?, type = ? WHERE id = ?", [updatedBook.title, updatedBook.published_at, updatedBook.author, updatedBook.language, updatedBook.type, bookId], function (error, results, fields) {
    if (error) {
      res.status(500).json({message: "Error updating book"});
    } else {
      res.status(200).json({message: "Book updated successfully"});
    }
  });
});

This endpoint listens for a PUT request to the “/books/:id” route, updates the book with the id passed in the parameter from the request with the data in the request’s body in the database, and sends back a JSON response indicating that the book was updated successfully.

An example of an UPDATE operation in PHP using the “book” table and ADODB to update the book with id = 3, considering that the values are sent by request:

<?php
require('adodb/adodb.inc.php');

$dsn = "mysql://username:password@localhost/myDB";
$db = ADONewConnection($dsn);

$book_id = $_POST["id"];
$title = $_POST["title"];
$published_at = $_POST["published_at"];
$author = $_POST["author"];
$language = $_POST["language"];
$type = $_POST["type"];
$query = "UPDATE book SET title = '$title', published_at = '$published_at', author = '$author', language = '$language', type = '$type' WHERE id = ".$book_id;
$db->execute($query);

echo "Book updated successfully";

?>

This script creates the query using the id passed by the $_POST variable and the values of title, published_at, author, language and type, then it executes it using the Execute() method.

An example of an UPDATE operation in Laravel using the “book” table to update the book with id = 3:

public function update(Request $request, $id)
{
    $book = Book::find($id);
    $book->title = $request->title;
    $book->published_at = $request->published_at;
    $book->author = $request->author;
    $book->language = $request->language;
    $book->type = $request->type;
    $book->save();

    return redirect()->route('books.index')->with('success', 'Book updated successfully');
}

This controller method retrieves a book by its id from the “book” table using the Eloquent ORM’s find() method, updates its all the fields with the values passed by the request, and saves the changes using the save() method. This way teh read record is updated by the new values.

The method redirects the user back to the index view with a success message.

Here is an example of how you could use curl to send a PUT request to the /books/3 endpoint and update the book with id 3 with all the fields:

curl -X PUT -H "Content-Type: application/json" -d '{"title": "The Catcher in the Rye 2.0", "published_at": "2022-01-01", "author": "J.D. Salinger", "language": "English", "type": "Novel"}' http://localhost:3000/books/3

This command sends a PUT request to the http://localhost:3000/books/3 endpoint and updates the book with id 3 with the data that is sent on the body of the request.

You should get a response from the endpoint similar to this:

{
  "message": "Book updated successfully"
}

The DELETE operation in CRUD

The DELETE operation is one of the four basic CRUD (Create, Read, Update, Delete) operations in software development. It is used to delete or remove existing data from a database or other data storage system.

In the context of a relational database, the DELETE operation is performed using an SQL DELETE statement. The syntax of an SQL DELETE statement is as follows:

DELETE FROM table_name
WHERE some_column = some_value;

This statement deletes the rows in the specified table that match the conditions or criteria specified in the WHERE clause. For example, the following SQL statement deletes the book where the id is 3:

DELETE FROM book
WHERE id = 3;

In the context of a RESTful API, the DELETE operation is typically performed using the HTTP DELETE method. For example, the following DELETE request deletes the book with id 3:

DELETE /books/1

It’s important to understand that a DELETE operation is an action of desctruction. Data will be removed from a persistent storage. So it’s allways necessary to implement some kind of confirmation with the user to perform the operation. It’s a good practice to build a DELETE operation in more than one steps:

the request of deletion by the user
the confirmation of the action
the action of deletion

Let’s see some examples of DELETE operation:

Here is an example of an SQL DELETE operation using the “book” table to delete the book with id = 3:

DELETE FROM book
WHERE id = 3;

This statement deletes the book with id = 3 from the “book” table.

Here is an example of a DELETE operation in Node.js using the “book” table to delete the book with id = 3:

app.delete('/books/:id', function (req, res) {
  var bookId = req.params.id;
  // Validate bookId here
  // Delete the book with the specified id from the database
  var sql = "DELETE FROM book WHERE id = ?";
  connection.query(sql, [bookId], function (error, result) {
    if (error) {
      res.status(500).json({message: "Error deleting book"});
    } else {
      res.status(200).json({message: "Book deleted successfully"});
    }
  });
});

This endpoint listens for a DELETE request to the “/books/:id” route, deletes the book with the id passed in the parameter from the request, and sends back a JSON response indicating that the book was deleted successfully.

Here is an example of an DELETE operation in PHP using the “book” table and ADODB to delete the book with id = 3 and the values passed by request:

<?php
require('adodb/adodb.inc.php');

$dsn = "mysql://username:password@localhost/myDB";
$db = ADONewConnection($dsn);

$book_id = $_POST["id"];
$query = "DELETE FROM book WHERE id = ".$book_id;
$db->execute($query);

echo "Book deleted successfully";

?>

This script creates the query using the id passed by the $_POST variable, then it executes it using the Execute() method.

And here is an example of an DELETE operation in Laravel using the “book” table to delete the book with id = 3, using the values passed by request:

public function destroy($id)
{
    $book = Book::find($id);
    $book->delete();

    return redirect()->route('books.index')->with('success','Book deleted successfully');
}

This controller method retrieves a book by its id from the “book” table using the Eloquent ORM’s find() method, and deletes the record using the delete() method. The method redirects the user back to the index view with a success message.

Conclusions

Overall, CRUD is a simple and powerful set of operations that are widely used in software engineering, and it is considered a good practice because it helps to simplify and standardize the way data is stored and accessed, making the development process more efficient, maintainable and scalable.