CRUD

After the hello world application, the logical step for a beginner is to learn to build a CRUD. CRUD stands for Create Read Update and Delete. It refers to an application that can perform each of those tasks the name implies. The term is also often used to say a project is too simple, it's just a CRUD app. Most applications on the web do just that and after so many years of working as a web programmer, I can assure you that it is not so simple.

Create.

The first tutorial I learned on Ruby on Rails was a CRUD for a music player. It was fine and all but I had no intention to build a music listing website. The moment I tried to use that skill for my own needs, I realized that I didn't understand the first thing about it.

To create a record, you need a database. It did take some time to understand the concept of connecting to a database to save data. Why not just use a text file you can parse? Being told that something is bad is not the same as experiencing it first hand.

The bear asked, “the person who heard me, the one who saw me, and the one who escaped my claws, guess which ran the fastest?”

I had to struggle with write permissions on an XML file I created to save data for a while before I convinced myself to use MySQL.

Designing a table is easy. Designing a table that makes sense is not that simple. I made a the list fields that I thought were good enough for my application. Throughout the life of the program, I constantly had to come back to add and remove some fields.

To create data on the command line is easy, but on a website setting, you have to pass the data through the URL or POST data. The data coming from the user is unpredictable. Even if they don't write SQL injection, adding a form on a web page is target for spam bots. Every website get spammed, no exceptions.

User input needs to be validated, filtered, X-rayed, sometimes rejected. Creating means adding data to your server, consuming storage space. I was experimenting with a small project for parsing RSS feeds. Inserting analytic data for each request caused my database to fill up and crash. Along the way, it slowed down all my websites using the service.

Creating data is easy if you are following a simple tutorial. If you look at all the data inserted while you are editing a simple WordPress post, you will understand its complexity.

Read

Reading data is the easiest part of the group. Well at least it is supposed to be. Data on its own doesn't mean much. How you present it is what matters.

I assisted to a meeting once where we were to show the numbers on one of the dying websites we were working on. All the developers knew the numbers, but the manager managed to convince the business team that everything was still fine and dandy. He showed them that in our rate of decline, the last two months were declining at a slower rate. One month was 15% the other was 10%, which meant we slowed the rate of decline by +5%. They bought it.

Reading requires a good understanding of SQL. When you add complex JOINS and sub-queries, it is easy to lose track of the data you want to present. Sometimes a small mistake in a 50 lines query can completely change the meaning of the information.

When you are reading data from user input, just like it was sanitized before it was saved, it is a good idea to sanitize it before displaying it. Forgetting to escape HTML characters for example can lead to Cross-site scripting, a user could to run arbitrary code into other people machine.

Working with WordPress and many other CMSs thought me that reading data can be very complicated. When you have large databases with obscure field names and tables, it is easy to display the wrong data.

Update

Update is a combination of both create and read. All the precautions from the previous two have to be taken into consideration here. I can't tell you how many times I was trying to update and I end up modifying all the records on the table, losing data forever and ever.

When updating, an important thing to pay attention to is the condition for the update. Writing the wrong condition, or a typo can delete your data forever. Somethings you don't learn in beginner tutorials is that sometimes you never update a field, you create new record instead. I found myself using this feature more and more recently.

For example, I write a new article and publish it. If I find some grammatical errors, I can come back to edit it. If I simply overwrite the old version with the new corrected one, I can never go back to the old one. This happened to me when I had a full post selected, and instead of pressing CTRL-V I simply pressed V then saved it. My whole post was just the letter V, deleting all the text I had written previously. It's a good thing that my workflow includes having a backup version. But now I don't take the risk, I make copies on a draft table containing all versions of every posts.

Updating a single value on a table is easy, but when you need to have complex conditions to make an update, your SQL skills and years of experience will play a major role.

Delete

To delete an entry, all you have to do is match the right conditions and delete it. Only sometimes, you want that entry to come back but it is too late. After many years of screwing up, I decided that I don't need to delete anything again, ever.

At Facebook, they say that deleting something is a performance bog. Instead they mark entries as deleted. I do the same, if I have an entry I no longer need, I create an extra field called 'deleted' and give it the value (1,0) or (y,n), or (true,false).

There is almost never a good reason to delete a record entirely. Of course if you have a very active database and having too many entries can become a problem, you could have a scheduled cleaner service. For example, every hour, you can transfer all the data marked as deleted on an archive table before removing them from the main table. This way you have the luxury of recovering this data if you ever need to.

Delete, just like Update, can be a very dangerous command. It is not easy to use efficiently, at least it wasn't for me for many years. I had to screw up and learn the hard way. Delete is one of the reasons having backup of your full database is crucial.

---

Creating an application that do all four commands efficiently is not easy. It requires years of experience, dealing with the consequences of bad design, and a change of attitude. Most if not all websites are CRUD applications, and they are not easy to replicate. This reminds me of all the people saying that they could recreate stackoverflow.com over the weekend. I can't point you to their websites because they don't exist anymore.

Give cruds their true merits and understand that just because it is what you are working on and you are too comfortable with it, doesn't mean it is easy. You don't have to deal with pointers or low level kernel programming to call your self a true programmer.

If you are making a CRUD app, do your best to make it the best application.

---