What is CRUD and how does it help me with my application?
Tags: CRUD, sql, javascript
Published: 2020-04-29 07:16:37

(Originally published at [florianherlings.de](https://florianherlings.de/posts/2020-04-28-crud-create-read-update-delete/))

Some concepts are not really hard to understand, but so core to the work of a developer, that it is worth thinking and writing about them. CRUD is one of those concepts: You will find many developers talking about CRUD in all kinds of programming projects and tasks, as a way to express what they are thinking about a feature.

You might find somebody saying: „*Twitter is just CRUD without the U in front of a big tweets table with joined users.*” and even if this is an extreme simplification, it still expresses a lot of information in a small sentence. Many programmers love to speak like this. 😅


## Concept

CRUD is actually a pretty simple concept: It describes a set of actions you can do to your data. Whenever you are handling data (in a data base, on your hard drive, in the "*cloud*"), you always want to think about: What can a user do to this data.

It usually falls into one of these four actions that can be performed to a data point[^data-point]:
1. You can **create** a new data point
2. You can **read** existing data points
3. You can **update** an existing data point
4. You can **delete** an existing data point

And this is exactly where this acronym is coming from. CRUD is short for:

* **C**REATE
* **R**EAD
* **U**PDATE
* **D**ELETE

So any application you write, that is dealing with data, can do one of those four things: Create, Read, Update and Delete. Let's look at a few use cases like [SQL](#sql-like-postgresql-or-mysql), the [file system](#file-system) and [HTTP](#http).



### SQL (like PostgreSQL or MySQL)

SQL is the super powerful language, that will help you to "talk" to a relational database[^relational-db] like PostgreSQL. All you can do to your database's data is adding new entries, getting entries, updating and deleting entries. 

Here are some example SQL queries, and what their role in CRUD is:

| Action | SQL query |
|---|----|
| **C**reate | `INSERT INTO users (name) VALUES ('Alice');`   |
| **R**ead   | `SELECT * FROM users;` |
| **U**pdate | `UPDATE users SET name='Bob' WHERE id=1;`       |
| **D**elete | `DELETE FROM users WHERE id=2;`   |



### File system

The file system follows the same set of rules: You can create, read, update and delete files. Every action you can do to a file falls into one of those four categories.

| Action | Shell command |
|---|----|
| **C**reate | `touch hello.txt`       |
| **R**ead   | `cat hello.txt`         |
| **U**pdate | `echo "Hi" > hello.txt` |
| **D**elete | `rm hello.txt`          |

Even moving a file from one directory to another directory is just an update, as you only update it's path from one directory to another.

If you run: `mv hello.txt ~/Desktop/hello.txt` it will move the file *hello.txt* from its current location to the desktop, which only means that the file's path will be updated to the new directory (`~/Desktop`),

### HTTP

Even HTTP[^http] follows this concept. The main four HTTP actions, that a request can have are GET, PUT, POST and DELETE. Those can be easily categorized into the four CRUD actions as well:

| Action | HTTP Method |
|---|----|
| **C**reate | POST   |
| **R**ead   | GET    |
| **U**pdate | PUT    |
| **D**elete | DELETE |

There are numerous other examples of CRUD being used in all corners of software development. Those three ([SQL](#sql-like-postgresql-or-mysql), the [file system](#file-system) and [HTTP](#http)) are just simple examples to underline the point I am trying to make in this article: 

CRUD is everywhere. 😊


## Using CRUD as a thinking model in your project

The CRUD concept is – as we have seen – very simple and straight forward. Because of its simplicity we can use it as a foundation for thinking about how we design our software.

### Data layer design

Let's say we want to design a data layer for our new web application. The first thing we have to think about are the "Nouns", which means: What kind of "things" exist on our website.

If you were to re-create something like twitter, you would definitely come up with two things: Tweets and users.

The next step is to think about: What can you do with those Tweets and users. This is where CRUD comes in. For every "thing" that exists in your data layer (tweets and users), think about if you want to build and what kind of functionalities these would represent:

| Action | Tweet           | User                         |
|--------|-----------------|------------------------------|
| Create | Write a tweet.  | Register.                    |
| Read   | See tweets.     | Login and show user profile. |
| Update | Edit a tweet.   | Change user profile.         |
| Delete | Remove a tweet. | Delete account.              |

With this big list, you can think about which kind of functionality you want to provide to your users. In our example of Twitter, you can do almost all those things, except editing a tweet[^editing-tweets]. So the product team at Twitter sat down with this list and decided to not allow updating tweets.


### API design

The same model can be applied when you create an API for your application. Let's say you are working for Spotify and want to create an API for their app.

The "things" in their app that come to mind are: Users, Songs, Albums and Playlists.

For a user, the app API should pretty much allow everything:

|        | User                         | Allowed |
|--------|------------------------------|:-------:|
| Create | Register.                    |    ✅    |
| Read   | Login and show user profile. |    ✅    |
| Update | Change user profile.         |    ✅    |
| Delete | Delete account.              |    ✅    |

But what about a Song? It would not be helpful, if any user could create new songs, or delete existing ones.

|        | Song                   | Allowed |
|--------|------------------------|:-------:|
| Create | Upload new song.       |    ❌    |
| Read   | Listen to a song.      |    ✅    |
| Update | Change a song's title. |    ❌    |
| Delete | Delete a song.         |    ❌    |

With this simple "matrix", you can go through all the "things" in your application and decide which of the four CRUD actions you want to allow.

Thinking this through will give you a great foundation to talk to customers and users about your application as well as a great todo list. Even if you decide that a user should not be able to do some of the CRUD actions, this is still a valuable thing to know and a great thing to decide before you start implementing.



### Code structure

As we now know, almost all applications follow the CRUD concept. Even if you decide to not include one or more of the CRUD actions, the general concept is still the same.

This is why, it makes a lot of sense to structure your application in the same way: You probably have routes to create, read, update and delete the things in your database, and you might want to name those routes in the same way.

This means, that potentially, a route to add a new tweet might look like this:

```javascript
app.post('/tweets/create', async (request, response) => {

    const newTweet = await db.createTweet(request.body.text);
    response.json(newTweet);

});
```

Because this is such a well known pattern, many web frameworks even come with libraries to support creating CRUD actions for your data.

Ruby on Rails[^rails] is the most famous example. It has special commands you can run to generate everything you need to do CRUD for a certain "thing" on your website. The generated code will including routes, controllers, models and database migrations[^db-migration].

```bash
rails generate scaffold Tweet text:string
```

## Recap

What I hope you take from this article is this:
* CRUD stands for create, read, update and delete
* Those four actions can be found everywhere in software development.
* When you think about the application you want to build, you can use CRUD as a guideline to think through the different "things" and what a user should or should not be able to do with them.

I hope you enjoyed reading the article and were able to learn something from it.


[^data-point]: In this article, I am using the word "*data point*" as what you could think of as a single thing in your database. Like one user, one song or one Album.

[^relational-db]: Databases have existed for a very long time in the software development word. This is why many different kinds of databases exist to be provide a perfect solutoin for all the different use cases. The kind of database, that is used most often is called a relational database. If you allow me to simplify the matter a lot, you could think of a relational database as something like a fancy excel file. You have columns to define what kind of fields you want and rows to hold the data entries.

[^http]: HTTP is short for "*Hyper text transfer protocol*" and is one of the basic foundations of the internet that we know today. This protocol defines how computers (often a browser and a server) communicate with each other.

[^editing-tweets]: As a side note: On Twitter, you cannot actually edit a tweet, you can only delete it and write a new one. The developers and product people at Twitter certainly had/have their reasons to not allow editing a tweet, but many users still complain about the fact from time to time. 🤷‍♀️

[^rails]: [Rails](https://rubyonrails.org/) (actually *Ruby on Rails*) is a very popular web framework, written in the programming language Ruby. It is know for its ease of use. There are frameworks for other languages that try to achieve similar goals.

[^db-migration]: A database migration is a piece of code, that allows you to update the schema of the tables in your database. This means, that when you have an existing database with data in tables, you don't log into the server and change the table structure manually, but you have a well structured file with the code to do exactly that. Two of the biggest benefits are that you can make those changes on many different computers (like your own computer, your colleagues computer and the server). The other big benefit is that these migrations can often be undone, which helps a lot, if you find out later that you made a little mistake.