DEV Community

Margai Wangara(he/him)
Margai Wangara(he/him)

Posted on • Updated on

Back To Basics: CRUD Operations with SQL

I was recently going through my old projects trying to refactor them for inclusion into my portfolio website when I came across one of my first-ever full-stack projects. Back then, I mostly used structural PHP as my main back-end programming language. Being new to programming, I was excited, I mean, why not, programming is so cool. One of the best moments of my life was when I had my first programming 'aha' moment. It was amazing. Since then, I have created lots of projects, some finished, others unfinished, all with one goal in mind, building something.

Since I mainly used PHP (structural) for back-end, most of the time I wrote raw SQL queries, from database connections to creating tables and to be honest, it was quite refreshing. I gained a bunch of experience in SQL until I decided to shift to Node.js and the MERN stack for web development.

With the introduction of packages like Eloquent, it is easier to perform CRUD operation on a table without having any idea what happens under the hood. I decided to write this article to share my knowledge on the matter and in the process also remind myself how it actually works

For compatibility, I will use sqlfiddle to write the queries. Feel free to use any SQL editor. Possible options include XAMPP which comes with MySQL, Apache and PHP, VSCode's SQLite extension.

Creating a table


    CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
        name VARCHAR(50),
        surname VARCHAR(50),
        email VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        description TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

Enter fullscreen mode Exit fullscreen mode

The code above creates a table called users if it does not exist and then adds columns as specified

Data Types

INT - Accepts whole numbers only.
VARCHAR - Accepts strings with length specified in brackets
TEXT - Accepts long string such as descriptions
TIMESTAMP - Accepts timestamps

Attributes

PRIMARY_KEY - Sets column as primary key
AUTO_INCREMENT - Increases column value by specified increment value, 1 by default
UNIQUE - Column stores unique values, like email, values that are not the same
NOT NULL - Required values
DEFAULT - Default column value such as CURRENT_TIMESTAMP - For the current date and time

C - Adding data to users table


    INSERT INTO users (name, email, password, description) VALUES ('John', 'Doe', 'johndoe@app.com', 'JohnDoe1', 'The name is Doe. John Doe');

Enter fullscreen mode Exit fullscreen mode

Since we are using auto_increment, id is automatically added and incremented on addition of a new row, created_at and updated_at fields are also automatically filled because we set the default as current_timestamp.

R - Read data from users table


-- Select everything from users table
SELECT * FROM users;

-- Select some from users table
SELECT name, email FROM users;

-- Select from users table where
SELECT * FROM users WHERE email='johndoe@app.com';

-- Select from users table where ... and ...
SELECT * FROM users WHERE name='John' AND surname='Doe';

-- Select from users table where ... or ...
SELECT * FROM users WHERE name='John' OR surname='Doe';

Enter fullscreen mode Exit fullscreen mode

Select has a variety of options. Where is used to select and filter based on specification.

U - Update existing data in users table


-- Update name to jane doe using email[unique]
UPDATE users SET name='Jane' WHERE email='johndoe@app.com';

-- Update email to janedoe using id[assume = 1]
UPDATE users SET email='janedoe@app.com' WHERE id=1;

Enter fullscreen mode Exit fullscreen mode

D - Delete data from users table


-- Deleting entire table data[beware]
DELETE FROM users;

-- Deleting certain row data
DELETE FROM users WHERE email = 'janedoe@app.com';

Enter fullscreen mode Exit fullscreen mode

When writing the delete query be careful not to delete all data from the table by using where. I remember messing up a lot when I was starting out.

Incase I wrote anything wrongly, feel free to constructively criticize in the comments section.

Discussion (0)