What Are CRUD Operations?

What Are CRUD Operations?

by Philipp Acsany May 29, 2024 intermediate api databases web-dev

CRUD operations are at the heart of nearly every application you interact with. As a developer, you usually want to create data, read or retrieve data, update data, and delete data. Whether you access a database or interact with a REST API, only when all four operations are present are you able to make a complete data roundtrip in your app.

Creating, reading, updating, and deleting are so vital in software development that these methods are widely referred to as CRUD. Understanding CRUD will give you an actionable blueprint when you build applications and help you understand how the applications you use work behind the scenes. So, what exactly does CRUD mean?

Take the Quiz: Test your knowledge with our interactive “What Are CRUD Operations?” quiz. You’ll receive a score upon completion to help you track your learning progress:

Interactive Quiz

What Are CRUD Operations?

In this quiz, you'll revisit the key concepts and techniques related to CRUD operations. These operations are fundamental to any system that interacts with a database, and understanding them is crucial for effective data management.

In Short: CRUD Stands for Create, Read, Update, and Delete

CRUD operations are the cornerstone of application functionality, touching every aspect of how apps store, retrieve, and manage data. Here’s a brief overview of the four CRUD operations:

  • Create: This is about adding new entries to your database. But it’s also applicable to other types of persistent storage, such as files or networked services. When you perform a create operation, you’re initiating a journey for a new piece of data within your system.
  • Read: Through reading, you retrieve or view existing database entries. This operation is as basic as checking your email or reloading a website. Every piece of information you get has been received from a database, thanks to the read operation.
  • Update: Updating allows you to modify the details of data already in the database. For example, when you update a profile picture or edit a chat message. Each time, there’s an update operation at work, ensuring your new data is stored in the database.
  • Delete: Deleting removes existing entries from the database. Whether you’re closing an account or removing a post, delete operations ensure that unwanted or unnecessary data can be properly discarded.

CRUD operations describe the steps that data takes from creation to deletion, regardless of what programming language you use. Every time you interact with an application, you’re likely engaging in one of the four CRUD operations.

Why Are CRUD Operations Essential?

Whether you’re working on a basic task list app or a complex e-commerce platform, CRUD operations offer a universal language for designing and manipulating data models. Knowing about CRUD as a user helps you understand what’s happening behind the curtains. As a developer, understanding CRUD provides you with a structured framework for storing data in your application with persistence:

In computer science, persistence refers to the characteristic of state of a system that outlives (persists more than) the process that created it. This is achieved in practice by storing the state as data in computer data storage. (Source)

So even when a program crashes or a user disconnects, the data is safe and can be retrieved later. This also means that the order of the operations is important. You can only read, update, or delete items that were previously created.

It’s good practice to implement each CRUD operation separately in your applications. For example, when you retrieve items, then you shouldn’t update them at the same time.

While CRUD describes a concept that’s independent of specific programming languages, one could argue that CRUD operations are strongly connected to SQL commands and HTTP methods.

What Are CRUD Operations in SQL?

The idea of CRUD is strongly connected with databases. That’s why it’s no surprise that CRUD operations correspond almost one-to-one with SQL commands:

CRUD Operation SQL Command

When you create data, you’re using the INSERT command to add new records to a table. After creation, you may read data using SELECT. With a SELECT query, you’re asking the database to retrieve the specific pieces of information you need, whether it’s a single value, a set of records, or complex relationships between data points.

The update operation corresponds to the UPDATE command in SQL, which allows you to modify data. It lets you edit or change an existing item.

Lastly, the delete operation relates to the DELETE command. This is the digital equivalent of shredding a confidential document. With DELETE, you permanently remove an item from the database.

Writing CRUD Operations in Raw SQL

CRUD operations describe actions. That’s why it’s a good idea to pull up your sleeves and write some code to explore how CRUD operations translate into raw SQL commands.

In the examples below, you’ll use Python’s built-in sqlite3 package. SQLite is a convenient SQL library to try things out, as you’ll work with a single SQLite database file.

You’ll name the database birds.db. As the name suggests, you’ll use the database to store the names of birds you like. To keep the example small, you’ll only keep track of the bird names and give them an ID as a unique identifier.

Start a new Python file named crud_sql.py with the code below:

Python crud_sql.py
 1import sqlite3
 3def connect_to_db(db_path):
 4    return sqlite3.connect(db_path)
 6if __name__ == "__main__":
 7    with connect_to_db("birds.db") as connection:
 8        connection.execute("""
 9          CREATE TABLE IF NOT EXISTS bird (
11            name TEXT NOT NULL
12          );
13        """)

After you import sqlite3 in line 1, you define a function named connect_to_db() in line 3. As the name suggests, you’ll use this function to connect to the database, which you pass in as an argument. If sqlite3.connect() in line 4 can’t find a database at the given path, then the database will be created.

In line 6, you’re using the name-main idiom to connect to birds.db and create a bird table in the database when you execute crud_sql.py directly. In line 8, you’re executing the raw SQL command you define in lines 9 to 11. Since you’re using a context manager you don’t need to worry about closing the database.

The SQL command that you define does two things conditionally. If the bird table doesn’t exist in the database, then you create it with two columns:

  1. id to uniquely identify database entries
  2. name to store a bird’s name

You can think of the bird database table like a spreadsheet. The columns id and name are the table headers. Later, you’ll add rows to the table, which will be your database entries.

Open a terminal window in the same working directory as crud_sql.py and run the Python script:

$ python crud_sql.py

Executing crud_sql.py for the first time creates birds.db with a bird table. Next, start a new Python REPL to perform CRUD operations with raw SQL:

>>> from crud_sql import connect_to_db
>>> connection = connect_to_db("birds.db")
>>> cursor = connection.cursor()
...   bird (name)
...   ('Humming Bird'),
...   ('Sugar Glider');
... """
>>> cursor.execute(CREATE_BIRDS_SQL)
<sqlite3.Cursor object at 0x105027bc0>

>>> connection.commit()

First, you establish the connection to the birds.db database and create a Cursor. Then, you prepare your SQL command to insert two flying animals into the bird table. Just like before, you use .execute() to perform the operation. This time, however, you open up a database transaction because you use the INSERT command.

At this stage, you could chain other queries to your transaction stack. To perform the operation, you must use .commit(). For now, you’ll use the connection open to perform the other CRUD operations. You’ll close the connection at the end of the section.

After you create the database records, it’s time to verify that they’re present in the database. For this, you use the retrieval CRUD operation:

>>> READ_BIRDS_SQL = "SELECT * from bird"
>>> cursor.execute(READ_BIRDS_SQL)
<sqlite3.Cursor object at 0x105027bc0>

>>> cursor.fetchall()
[(1, 'Humming Bird'), (2, 'Sugar Glider')]

With SELECT followed by an asterisk (*), you set up the READ_BIRDS_SQL query to retrieve all database entries of the bird table. If your database were bigger, then this would be a large transaction. For your small database, with only two small animals, this CRUD operation is fine.

The .fetchall() method returns a list of tuples. The list items are the database items. The tuple contents are the row values of id and name.

At this point, you may have spotted the incorrect spelling of the “Humming Bird” entry. The correct spelling of this tiny bird is “Hummingbird”. What a great opportunity to use another CRUD operation!

Go on and use the update CRUD operation on the “Humming Bird” entry by referencing its id:

...   bird
... SET
...   name = "Hummingbird"
...   id = 1
... """
>>> cursor.execute(UPDATE_HUMMINGBIRD_SQL)
<sqlite3.Cursor object at 0x105027bc0>

>>> connection.commit()

When performing the UPDATE command, you must provide the new content you want to overwrite the old one with. Here, you change the little bird’s name to “Hummingbird”.

Another important detail for the update CRUD operation is that you need to provide information about which data you want to update. If you want to update one specific database entry, then you need to provide a unique identifier like the ID.

The same thing is true when you want to perform a delete CRUD operation. Again, you must let the database know the exact entry or entries you want to delete to avoid deleting wanted entries.

Trying out the delete operation in SQL gives you the chance to fix another flaw in one of your database entries. A sugar glider is a cute, flying animal. Yet, the sugar glider is not a bird.

You may diversify your database at some point in the future to be a habitat of any cute animal. But for now, it’s time to remove the sugar glider from the birds.db:

>>> cursor.execute(DELETE_SUGAR_GLIDER_SQL)
<sqlite3.Cursor object at 0x105027bc0>

>>> connection.commit()
>>> connection.close()

With DELETE, you don’t write new data. That’s why providing an id is sufficient to let the database know which entry you want to delete.

Again, make sure not to miss the WHERE clause. Otherwise, you’d delete all entries of the bird table. If you want to learn more about the SQL commands you can use with SQLite, then you can check out the overview on SQL As Understood By SQLite.

Now that you’ve finished all SQL CRUD operations, you also call .close() to close the database connection.

Translating CRUD concepts into SQL helps you to construct queries to manipulate data in your database. As you’ve seen in the examples above, the CRUD operations are closely related to SQL commands. However, writing pure SQL can be cumbersome. That’s where a package like SQLAlchemy comes into play to help you perform CRUD operations more conveniently.

Executing CRUD Operations With SQLAlchemy

SQLAlchemy is a popular Object-Relational Mapper (ORM) for Python. An ORM allows you to interact with a database using Python objects instead of writing raw SQL queries like you did in the previous section. This makes writing code more convenient for you as a Python developer.

Another convenience of using ORMs is that you don’t need to bother about the exact SQL implementation:

Unfortunately, despite SQL being standardized since 1986, a lot of different implementations exist. They deviate more or less from each other, making developing applications that would work with a range of different SQL servers particularly difficult. (Source)

With an ORM like SQLAlchemy, you can switch the SQL library in the background and keep your database queries unchanged.

SQLAlchemy is an external Python package that you can find on the Python Package Index (PyPI).

As with other external packages, it’s a good idea to install SQLAlchemy inside of a virtual environment. That way, you’re installing any project dependencies not system-wide but only in your project’s virtual environment.

Select your operating system below and use your platform-specific command to set up a virtual environment:

Windows PowerShell
PS> python -m venv venv
PS> .\venv\Scripts\activate
(venv) PS>
$ python -m venv venv
$ source venv/bin/activate
(venv) $

With the commands shown above, you create and activate a virtual environment named venv by using Python’s built-in venv module. The parenthesized (venv) in front of the prompt indicates that you’ve successfully activated the virtual environment.

After you’ve created and activated your virtual environment, you can go ahead and install SQLAlchemy:

(venv) $ python -m pip install SQLAlchemy

Now that you’ve installed SQLAlchemy, you can go on and explore CRUD operations with the SQLAlchemy ORM.

In the previous section, you performed CRUD operations with pure SQL. Even when using an ORM like SQLAlchemy, the concept of using CRUD operations holds true.

Again, start by creating a Python file that helps you to connect to the birds.db database:

Python crud_sql_alchemy.py
 1from sqlalchemy import create_engine, Column, Integer, String
 2from sqlalchemy.orm import DeclarativeBase, sessionmaker
 4class Base(DeclarativeBase):
 5    pass
 7class Bird(Base):
 8    __tablename__ = "bird"
 9    id = Column(Integer, primary_key=True)
10    name = Column(String)
12    def __repr__(self):
13        return f"Bird(id={self.id}, name={self.name!r})"
15engine = create_engine("sqlite:///birds.db")
16Session = sessionmaker(bind=engine)
18def init_db():
19    Base.metadata.create_all(engine)

One important advantage of using SQLAlchemy is that you can declare models. In line 7, you create a Bird class, which both describes a bird database table and the Python object model that will interact with table items later.

With init_db() in line 18, you either create the birds.db database if it doesn’t exist or connect to it if it’s present. That means you can continue working with the existing birds.db database from the former section to investigate the CRUD operations with SQLAlchemy.

Start another Python REPL session from within the same folder that you saved crud_sql_alchemy.py in. Then, perform your first CRUD operations with SQLAlchemy:

>>> from crud_sql_alchemy import Session, Bird, init_db
>>> init_db()
>>> session = Session()
>>> new_bird = Bird(name="Test Bird")
>>> session.add(new_bird)
>>> session.commit()

With SQLAlchemy, you can create new database entries using the .add() method of Session. In the example above, you’re creating a new entry with the name “Test Bird”.

To check if “Test Bird” is present in the database, you can query the database:

>>> from sqlalchemy import select
>>> query = select(Bird).where(Bird.name == "Test Bird")
>>> bird = session.execute(query).scalar_one()
>>> bird
<Bird(id=3, name='Test Bird')>

To retrieve database entries with SQLAlchemy, you use select() combined with the .where() method. The query resembles the raw SQL statement, but the Python code looks arguably way more familiar. Here, you want to select all Bird objects whose names are equal to "Test Bird".

In return, you get a Select object that you save in a query variable.

When you pass query into .execute(), you perform the query. Although there is only one “Test Bird” in your database that matches your query, you need to use .scalar_one() to save this exact database entry as bird.

Now that you’ve caught the “Test Bird” and saved the object in the bird variable, you can go on and update its name:

>>> bird.name = "Example Bird"
>>> session.commit()
>>> session.execute(select(Bird)).scalars().all()
[<Bird(id=1, name='Hummingbird')>, <Bird(id=3, name='Example Bird')>]

In SQLAlchemy, the update operation is as straightforward as changing an object’s property and then committing the change. To verify that everything worked, you could query the database again and check out all database entries.

After creating, reading, and updating your database with SQLAlchemy, only one operation of your CRUD routine remains missing: deleting an entry.

As long as you stay in the session, you can continue to work with bird:

>>> session.delete(bird)
>>> session.commit()
>>> session.close()

You can prepare to delete a database entry with SQLAlchemy by calling the session’s .delete() method. Again, you need to call .commit() to perform the delete CRUD operation.

Finally, it’s good practice to reset your session. You can think of session.close() as cleaning up after you.

Using SQLAlchemy can lower the complexities of writing raw SQL by adding a layer of abstraction to perform CRUD operations. When working with web applications, there can be an even further layer of abstraction when performing CRUD operations.

Next, you’ll explore the role of CRUD operations on the web and then move on to build your own CRUD-powered REST API that uses HTTP request methods and SQLAlchemy to interact with your database.

What Are CRUD Operations in HTTP Request Methods?

When you surf the web, you’re performing CRUD operations all the time in the form of HTTP request methods. Before you investigate HTTP requests further, have a look at how CRUD operations correspond to HTTP request methods:

CRUD Operation HTTP Request Method
Create POST
Read GET
Update PUT or PATCH

The most common HTTP request method by far is the GET request method. When you visit a website, you fetch data from the server. In other words, you perform a read CRUD operation with each request.

To explore how all four CRUD operations interact in the context of HTTP request methods, think of a website where you have a user account.

When you signed up, you created a new account with a POST HTTP request method. When you visit your profile, you send a GET request to the server, asking to receive your data. In other words, you read data from the server.

If you update your username, then you’re probably using a PATCH HTTP request method. If you replace your avatar image, you may be using the PUT HTTP method. In other words, PUT replaces an entire database entry with new content while PATCH only updates its individual fields.

To delete a post you made on your feed, you might trigger a DELETE HTTP method. When you work with DELETE, it’s important to actually delete the targeted resource. After the DELETE operation, a GET on the deleted resource should return a 404 response code.

Which exact HTTP request method you’re performing depends on how the web interface was built. If you’re interacting with a REST API, then the HTTP methods are more visible. And what better way to explore how HTTP methods and CRUD operations work in a REST API than to build a REST API yourself?

Performing CRUD Operations With a REST API

REST is an acronym for Representational State Transfer. With a REST API, you define a set of paths and rules for HTTP request methods to interact with your web service.

CRUD operations in REST APIs are HTTP methods in the first place. Similarly to interacting with websites in your browser, REST API calls may trigger database CRUD operations in the back end.

In the next section, you’ll create your own REST API with FastAPI to better understand how a REST API, HTTP request methods, and databases work together using CRUD operations.

Executing CRUD Operations With FastAPI

FastAPI is a web framework for building APIs with Python. Go ahead and install FastAPI into the same virtual environment you created before and installed SQLAlchemy in:

(venv) $ python -m pip install fastapi "uvicorn[standard]"

Besides FastAPI, you also install Uvicorn with the command above. Uvicorn will be the server on which you’ll run FastAPI.

Also, FastAPI installs Pydantic as its dependency. Pydantic will help you to model your data. If you want to learn more about Pydantic, then you can read the tutorial on simplifying data validation in Python.

You’ll use FastAPI to create endpoints that you send HTTP requests to. Once an endpoint receives a request, you perform a CRUD operation on your birds.db database. To streamline the process, you’ll leverage the crud_sql_alchemy.py file from before.

If you haven’t followed along with the tutorial so far, then you can download the Python scripts by clicking the link below:

Create a new file named crud_fastapi.py next to crud_sql_alchemy.py and start with the code below:

Python crud_fastapi.py
 1from fastapi import FastAPI, HTTPException, Depends
 2from sqlalchemy import select
 3from sqlalchemy.orm import Session
 4from pydantic import BaseModel, ConfigDict
 6from crud_sql_alchemy import Bird, init_db
 7from crud_sql_alchemy import Session as SessionLocal
 9app = FastAPI()
12class BirdCreate(BaseModel):
13    name: str
15class BirdUpdate(BaseModel):
16    name: str
18class BirdResponse(BaseModel):
19    model_config = ConfigDict(from_attributes=True)
21    id: int
22    name: str
24def get_db():
25    db = SessionLocal()
26    try:
27        yield db
28    finally:
29        db.close()

First, you import FastAPI to create your web application and HTTPException to handle potential errors. You use Pydantic’s BaseModel for data validation and settings management.

Before you can try out your API, you need to define the endpoints for your CRUD operations. Continue editing crud_fastapi.py and add the code below to define your first endpoint:

Python crud_fastapi.py
# ...

@app.post("/birds/", response_model=BirdResponse)
def create_bird(bird: BirdCreate, db: Session = Depends(get_db)):
    new_bird = Bird(name=bird.name)
    return new_bird

You use the create_bird() function to create a new bird to the bird table. This function responds to POST requests at the /birds/ endpoint. Since you’re working with SQLAlchemy, the code looks very similar to the example in the previous section.

The next endpoint you’ll implement is the endpoint to read all birds that are currently stored in the database:

Python crud_fastapi.py
# ...

@app.get("/birds/", response_model=list[BirdResponse])
def read_birds(db: Session = Depends(get_db)):
    birds = db.execute(select(Bird)).scalars().all()
    return birds

The read_birds() function returns all birds stored in the bird table of your database. This endpoint responds to GET requests, allowing you to retrieve a list of all birds.

If you only want to receive a specific bird, you need to tell the API which bird you want to get in the response. To do so, add another endpoint that accepts the id of a bird as a parameter:

Python crud_fastapi.py
# ...

@app.get("/birds/{bird_id}", response_model=BirdResponse)
def read_bird(bird_id: int, db: Session = Depends(get_db)):
    query = select(Bird).where(Bird.id == bird_id)
    found_bird = db.execute(query).scalar_one()
    if found_bird is None:
        raise HTTPException(status_code=404, detail="Bird not found")
    return found_bird

When you pass in bird_id as part of the endpoint URL of your GET request, then the read_bird() function tries to return the bird with the given ID.

The SQLAlchemy code above looks similar to the steps you performed in the Python REPL before. One minor difference is that here you cover the situation when no bird with the given ID is found. In this case, you’re raising an HTTPException to inform the API user that the bird wasn’t found.

You’ll use a similar pattern for the update CRUD operation. You only go on to change the bird’s name if a bird with the requested ID exists:

Python crud_fastapi.py
# ...

@app.put("/birds/{bird_id}", response_model=BirdResponse)
def update_bird(bird_id: int, bird: BirdUpdate, db: Session = Depends(get_db)):
    query = select(Bird).where(Bird.id == bird_id)
    found_bird = db.execute(query).scalar_one()
    if found_bird is None:
        raise HTTPException(status_code=404, detail="Bird not found")
    found_bird.name = bird.name
    return found_bird

The update_bird() function replaces an existing bird’s details with the provided new name. Just like before, the delete CRUD operation looks very much like the one you use to update a database record. Instead of renaming a bird, you call the .delete() method this time:

Python crud_fastapi.py
# ...

@app.delete("/birds/{bird_id}", response_model=dict)
def delete_bird(bird_id: int, db: Session = Depends(get_db)):
    query = select(Bird).where(Bird.id == bird_id)
    found_bird = db.execute(query).scalar_one()
    if found_bird is None:
        raise HTTPException(status_code=404, detail="Bird not found")
    return {"message": "Bird deleted successfully"}

Now that all your CRUD API endpoints are present, it’s time to try out your API! Start the Uvicorn server by running the uvicorn command. As an argument, you need to provide the filename without the .py extension followed by a colon (:) and the name of the FastAPI app:

(venv) $ uvicorn crud_fastapi:app
INFO:     Will watch for changes in these directories: [...]
INFO:     Uvicorn running on (Press CTRL+C to quit)
INFO:     Started reloader process [29889] using WatchFiles
INFO:     Started server process [29891]
INFO:     Waiting for application startup.
INFO:     Application startup complete.

One cool thing about FastAPI is that it comes with interactive API documentation out of the box. That means you can test out the REST API endpoints directly in the browser. Visit and perform some CRUD operations on your REST API:

Interactive REST API Documentation showing CRUD operations

CRUD operations in REST APIs provide a standardized framework for building web services that can create, read, update, and delete resources over the web.

FastAPI is a great package for connecting CRUD operations with HTTP request methods. With your endpoints, you mapped the CRUD operations to functions that interact with your database. Again, SQLAlchemy is a great helper for performing CRUD operations on your database intuitively.


CRUD stands for create, read, update, and delete. These four operations are fundamental to the functionality and interactivity of modern applications. Whether it’s managing user data in a social media app, updating inventory in an e-commerce platform, or simply maintaining a database of birds, the principles of CRUD are universally applicable.

In this tutorial, you explored CRUD operations in databases and REST APIs. After writing CRUD operations in raw SQL, you leveraged SQLAlchemy for a more intuitive way of interacting with your database. Then, you used FastAPI to create a REST API to help you understand how CRUD operations connect to HTTP request methods.

CRUD operations are at the heart of nearly every application you interact with. In the digital ecosystem, CRUD operations are as essential as wings to a bird, enabling applications to navigate the skies of data management with agility and grace.

Take the Quiz: Test your knowledge with our interactive “What Are CRUD Operations?” quiz. You’ll receive a score upon completion to help you track your learning progress:

Interactive Quiz

What Are CRUD Operations?

In this quiz, you'll revisit the key concepts and techniques related to CRUD operations. These operations are fundamental to any system that interacts with a database, and understanding them is crucial for effective data management.

🐍 Python Tricks 💌

Get a short & sweet Python Trick delivered to your inbox every couple of days. No spam ever. Unsubscribe any time. Curated by the Real Python team.

Python Tricks Dictionary Merge

About Philipp Acsany

Philipp is a Berlin-based software engineer with a graphic design background and a passion for full-stack web development.

» More about Philipp

Each tutorial at Real Python is created by a team of developers so that it meets our high quality standards. The team members who worked on this tutorial are:

Master Real-World Python Skills With Unlimited Access to Real Python

Locked learning resources

Join us and get access to thousands of tutorials, hands-on video courses, and a community of expert Pythonistas:

Level Up Your Python Skills »

Master Real-World Python Skills
With Unlimited Access to Real Python

Locked learning resources

Join us and get access to thousands of tutorials, hands-on video courses, and a community of expert Pythonistas:

Level Up Your Python Skills »

What Do You Think?

Rate this article:

What’s your #1 takeaway or favorite thing you learned? How are you going to put your newfound skills to use? Leave a comment below and let us know.

Commenting Tips: The most useful comments are those written with the goal of learning from or helping out other students. Get tips for asking good questions and get answers to common questions in our support portal.

Looking for a real-time conversation? Visit the Real Python Community Chat or join the next “Office Hours” Live Q&A Session. Happy Pythoning!

Keep Learning

Related Topics: intermediate api databases web-dev