SQL CRUD: Create, Read, Update, and Delete Data

SQL CRUD is the set of operations you use to create, read, update, and delete data in a relational database. In SQL, those operations usually map to INSERT, SELECT, UPDATE, and DELETE.

You'll see how each operation works, how WHERE targets rows, and how to run SQL safely from application code. The examples use small users and employees tables, but the same statements work for orders, posts, transactions, and most other backend records.

This article covers the "CRUD" chapter of Learn SQL, where you can run each query against a real database.

CRUD in SQL: Create, Read, Update, and Delete

CRUD is an acronym that stands for CREATE, READ, UPDATE, and DELETE. These four operations are the bread and butter of nearly every database you will create.

The CRUD operations correlate nicely with the HTTP methods used in backend web development:

It's important to understand how data flows through a typical web application.

1. The frontend processes some data from user input – maybe a form is submitted.
2. The frontend sends that data to the server through an HTTP request – maybe a POST.
3. The server makes a SQL query to its database to create an associated record – probably using an INSERT statement.
4. Once the server has determined that the DB query was successful, it responds to the frontend with a status code. Hopefully a 200-level code (success)!

SQL CRUD statements change or retrieve the rows in existing tables. Creating the table itself uses CREATE TABLE and is usually managed with database migrations.

Creating Data With INSERT

Tables are pretty useless without data in them! In SQL we can add records to a table using an INSERT INTO statement. When using an INSERT statement we must first specify the table we are inserting the record into, followed by the fields within that table we want to add VALUES to.

Example INSERT INTO statement:

INSERT INTO employees(id, name, title)
VALUES (1, 'Allan', 'Engineer');

Listing the columns explicitly makes the query easier to review and prevents the statement from depending on the table's declared column order.

Many dialects of SQL support an AUTO INCREMENT feature. When inserting records into a table with AUTO INCREMENT enabled, the database will assign the next value automatically. Different dialects of SQL will implement this feature differently, but in SQLite any column that has the INTEGER PRIMARY KEY constraint will auto-increment! So we can omit the id field within the INSERT statement and allow the database to automatically add that field for us. PostgreSQL and MySQL offer similar behavior with different syntax.

Depending on how your database is set up, you may be using traditional ids or you may be using UUIDs. SQL doesn't support auto-incrementing a uuid, so if your database is using them your server will have to handle the changing uuids for each record.

Run INSERT Queries Safely in Applications

Manually INSERTing every single record in a database would be an extremely time-consuming task! Working with raw SQL by hand is not super common when designing backend systems.

When working with SQL within a software system, like a backend web application, you'll typically have access to a programming language such as Go or Python. For example, a backend server written in Go can use string concatenation to dynamically create SQL statements:

sqlQuery := fmt.Sprintf(`
INSERT INTO users(name, age, country_code)
VALUES ('%s', %v, '%s');
`, user.Name, user.Age, user.CountryCode)

The example above is an oversimplification of what really happens when you access a database using Go code, and it's dangerous as written. Interpolating user input into SQL like this can turn that input into executable query text, creating an SQL injection vulnerability.

Use a parameterized query instead. Placeholders keep the SQL statement separate from its values, and the database driver binds those values safely. Go's database/sql package supports parameterized queries through methods such as ExecContext:

_, err := db.ExecContext(
	ctx,
	`INSERT INTO users (name, age, country_code) VALUES (?, ?, ?)`,
	user.Name,
	user.Age,
	user.CountryCode,
)

The placeholder syntax depends on the driver. SQLite and MySQL commonly accept ?; PostgreSQL drivers commonly use $1, $2, and $3. The safety rule doesn't change: bind data as arguments instead of building SQL with fmt.Sprintf or string concatenation.

An Object-Relational Mapping or an ORM for short, is a tool that allows you to perform CRUD operations on a database using a traditional programming language. These typically come in the form of a library or framework that you would use in your backend code.

The primary benefit an ORM provides is that it maps your database records to in-memory objects. For example, in Go we might have a struct that we use in our code:

type User struct {
    ID int
    Name string
    IsAdmin bool
}

This struct definition conveniently represents a database table called users, and an instance of the struct represents a row in the table.

Using an ORM we might be able to write simple code like this:

user := User{
    ID: 10,
    Name: "Lane",
    IsAdmin: false,
}

// generates a SQL statement and runs it,
// creating a new record in the users table
db.Create(user)

Using straight SQL we typically need to write more code and handle things a bit more manually:

user := User{
    ID: 10,
    Name: "Lane",
    IsAdmin: false,
}

db.Exec("INSERT INTO users (id, name, is_admin) VALUES (?, ?, ?);",
    user.ID, user.Name, user.IsAdmin)

Should you use an ORM? That depends! An ORM typically trades control for simplicity.

Using straight SQL you can take full advantage of the power of the SQL language. Using an ORM, you're limited by whatever functionality the ORM has. If you run into issues with a specific query, it can be harder to debug with an ORM because you have to dig through the framework's code and documentation to figure out how the underlying queries are being generated. Whichever you choose, both should use bound parameters.

I recommend doing projects both ways so that you can learn about the trade-offs. At the end of the day, when you're working on a team of developers it will be a team decision.

Read Data With SELECT

Use SELECT to retrieve rows. We talked about how a "create" operation flows through a web application. Let's talk about a "read."

We'll use an example scenario from CashPal, an imaginary payments app. Our product manager wants to show profile data on a user's settings page. Here's how we could engineer that feature request:

1. First, the front-end webpage loads.
2. The front-end sends an HTTP GET request to a /users endpoint on the back-end server.
3. The server receives the request.
4. The server uses a SELECT statement to retrieve the user's record from the users table in the database.
5. The server converts the row of SQL data into a JSON object and sends it back to the front-end.

Select only the columns the application needs:

SELECT id, name, username
FROM users;

SELECT * is useful for quick exploration, but explicit columns make application queries more stable and avoid transferring data you don't need.

We can also use a SELECT statement to get a count of the records within a table. This can be very useful when we need to know how many records there are, but we don't particularly care what's in them. COUNT is one of the common SQL aggregate functions.

Here's an example in SQLite:

SELECT COUNT(*) FROM employees;

The * in this case refers to a column name. We don't care about the count of a specific column – we want to know the number of total records, so we can use the wildcard (*).

If you need to know how many users your app has, use COUNT(*) — you can't use the id number to calculate the count because user accounts can be deleted!

How Does WHERE Target Rows?

The WHERE clause limits a statement to rows whose condition is true. It works with SELECT, UPDATE, and DELETE:

SELECT id, name, username
FROM users
WHERE is_admin = true;

Without the condition, the query would return every user. If a filter value comes from a request, bind it to a placeholder rather than inserting it into the SQL string.

Update Existing Rows

Whenever you update your profile picture or change your password online, you are changing the data in a field on a table in a database! Imagine if every time you accidentally messed up a tweet on Twitter, you had to delete the entire tweet and post a new one instead of just editing it...

... well, that's a bad example.

The UPDATE statement in SQL allows us to update the fields of a record. We can even update many records depending on how we write the statement.

An UPDATE statement specifies the table that needs to be updated, followed by the fields and their new values by using the SET keyword. Lastly a WHERE clause indicates the record(s) to update.

UPDATE employees
SET job_title = 'Backend Engineer', salary = 150000
WHERE id = 251;

Updates aren't always single-record. Say we've noticed that some of the records in a users table were incorrectly saved with a country_code value of USA instead of US:

UPDATE users
SET country_code = 'US'
WHERE country_code = 'USA';

This statement changes every USA row. Bulk fixes are useful, and a loose filter can wreck a lot of data. Review it carefully.

If you omit WHERE, SQL updates every row in the table:

UPDATE users
SET is_admin = false;

That query is valid SQL and changes every row. Run it only when that's intentional. For a single-record update, filter by a unique primary key and bind both the new value and the ID as parameters.

Delete Rows Safely

When a user deletes their account on Twitter, or deletes a comment on a YouTube video, that data needs to be removed from its respective database.

A DELETE statement removes all records from a table that match the WHERE clause. As an example:

DELETE FROM employees
WHERE id = 251;

This DELETE statement removes all records from the employees table that have an id of 251!

Deleting data can be a dangerous operation. Once removed, data can be really hard if not impossible to restore! When writing a manual DELETE, first run a SELECT with the same WHERE clause to preview the affected rows:

SELECT id, name, title
FROM employees
WHERE id = 251;

Check the result, then run the delete inside a transaction when your tooling allows it. You can inspect the change and roll it back before committing. Let's talk about a couple of common ways back-end engineers protect against losing valuable customer data.

If you're using a cloud-service like GCP's Cloud SQL or AWS's RDS you should always turn on automated backups. They take an automatic snapshot of your entire database on some interval, and keep it around for some length of time. You should have a backup strategy for production databases.

The Boot.dev database has a backup snapshot taken daily, and we retain those backups for 30 days. If we ever accidentally run a query that deletes valuable data, we can restore it from the backup.

A "soft delete" is when you don't actually delete data from your database, but instead just "mark" the data as deleted. For example, you might set a deleted_at date on the row you want to delete:

UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = 251;

Then, in your queries you ignore anything that has a deleted_at date set. The idea is that this allows your application to behave as if it's deleting data, but you can always go back and restore any data that's been removed.

Soft deletes help when the product must restore records or retain an audit trail, but they complicate every query. You should probably only soft-delete if you have a specific reason to do so. Automated backups should be "good enough" for most applications that are just interested in protecting against developer mistakes.

A Safety Checklist for UPDATE and DELETE

Treat every data-changing query as a precise description of its target set.

1. Write a SELECT with the planned WHERE clause.
2. Verify that it returns every intended row and no others.
3. Run the parameterized UPDATE or DELETE in a transaction when practical.
4. Check the affected-row count before committing.
5. Keep tested backups for recovery after mistakes.