Many companies use SQL-compatible databases for their reliability and transactional support. However, SQL can prove difficult for the average non-technical user, especially when complex filters, sorting, or data merging are required. To combat this, organizations build internal tools that facilitate CRUD operations to interact with the underlying database:
- Creating the data
- Retrieving the data
- Updating the data
- Deleting the data
In this tutorial, we’ll explore how to use SQL statements to perform all four CRUD operations.
Quick Caveat. Keep in mind that SQL is a flavored language: popular databases such as MySQL, PostgreSQL, and Oracle each use a different dialect of SQL. Thankfully, these dialects don’t have enormous differences between them, and the syntax for basic CRUD operations is fairly consistent.
Want to follow along? If you’d like to try out these queries, you can use Retool to connect to an existing Postgres database or download Postgres locally and access it through the command line.
For our CRUD operations, we’ll create a table, CUSTOMER
, with four columns of varying data types. To be clear, this is not a CRUD operation (even though it’s called a CREATE
operation)—this is just provisioning the table to query from.
We’ll use the CREATE TABLE
statement to create the table.
1CREATE TABLE Customers (
2 customer_id int NOT NULL PRIMARY KEY,
3 customer_code varchar(255) UNIQUE,
4 contact_name varchar(255),
5 active TINYINT DEFAULT 1
6);
This statement creates the following:
CUSTOMER_ID
is the primary key. This column is used to uniquely identify a row in the table. Accordingly, the primary key column only accepts unique values and doesn't accept null values. We can also define triggers to auto-increment the values of the primary key column.CUSTOMER_CODE
is a column with aUNIQUE
constraint, which means the column can store only unique values. Unlike primary key columns, unique constraint columns will accept null values.CONTACT_NAME
is a column without any constraints. It will accept any type of value, including duplicate values and null values.ACTIVE
is a column without any constraints, but aDEFAULT
value is defined as0
. It is of typeTINYINT
, which spans values0
to255
. Here,TINYINT
is used as a hack-y boolean because mySQL and SQL Server lack a native boolean type (which is otherwise available in Postgres). If the user doesn't specify any explicit value for this column when inserting the record, the value 1 will be assigned by default.
Now, we’ll explore how different CRUD operations are performed on this table structure.
The INSERT
operation is used to add records to the table. INSERT
statements can pick and choose which columns to fill when adding a row; alternatively, developers can provide values for all the fields.
Let’s explore various INSERT
operations.
If we want to insert a new row with values for all columns, we can use the following syntax:
1INSERT INTO TABLE_NAME VALUES (VALUE1, VALUE2, …);
For the earlier syntax to work:
- The values must be in the same order as they are defined in the database table.
- Accordingly, the number of values must be equal to the number of columns in the table.
SQL is a very picky language when it comes to syntax. Ask any SQL developer: given the error messages are often vague, SQL errors can make a grown dev cry.
We’ll now add two entries to the database by using two INSERT
queries, separated by SQL’s favorite character: ;
.
1INSERT INTO CUSTOMERS VALUES(1, 'A1','MAEGAN', 1);
2INSERT INTO CUSTOMERS VALUES(2, 'A2','DENNIS', 0);
Upon success, any SQL database should inform us that two rows have been successfully added. Here, we’re using 1
to signify true and 0
to signify false.
While this format makes for a good example, this add-all-fields syntax isn’t typically used because the script would need to be updated whenever a column is added, removed, or modified. This creates a real hazard because databases are often changed; they are also typically maintained and accessed by various people spanning departments.
In order to insert values into specific columns, SQL expects a tuple—of the same length as the values—to identify the columns. Anything omitted will be filled by a static default value (like “Untitled”), an auto-calculated value (like automatically incrementing IDs), or NULL
.
The syntax’s general structure is defined as the following:
1INSERT INTO TABLE_NAME
2 (COLUMN_1, COLUMN_2, …)
3VALUES
4 (VALUE1, VALUE2, …);
SQL expects users to name the columns and state the values in the same respective order. If there is a mismatch of type or column/value length, SQL will return an error.
Let’s use this INSERT
pattern to add another row:
1INSERT INTO CUSTOMERS
2 (CUSTOMER_ID, CUSTOMER_CODE, CONTACT_NAME)
3VALUES
4 (3, 'A3','LANA');
When we execute the query, a single new record will be inserted into the CUSTOMERS
table. Though the value for the column ACTIVE
is not specified in the insert query, the value 1 will automatically be assigned as it’s the default value.
The SELECT
statement is used to retrieve records from the database table. While the SELECT
statement will return all data from all columns and rows by default, we can specify which rows and/or columns we need.
We can use the *
operator to select all columns and rows from a database table.
1SELECT * FROM TABLE_NAME;
Typically, this query is significantly slower than querying specific rows or columns; constraints limit the data that needs to be looked up and transmitted, thereby expediting results.
Right now, if we ran the following query:
1SELECT * FROM CUSTOMERS;
we would get an output that looks like this:
11|A1|MAEGAN|1
22|A2|DENNIS|0
33|A3|LANA|1
4
We can specify columns from a database table within the SELECT
statement:
1SELECT COLUMN_1, COLUMN_2, COLUMN_N FROM TABLE_NAME;
We will use the following query statement to select the CONTACT_NAME
and ACTIVE
columns of the CUSTOMERS
table. We can use this to check if the customer is an active customer or not.
1SELECT CONTACT_NAME, ACTIVE FROM CUSTOMERS;
The output will look like this:
1MAEGAN|1
2DENNIS|0
3LANA|1
4
To select specific rows from the database table, we will need to use the where
clause, which will filter the records based on the condition specified.
1SELECT COLUMN1, COLUMN2 FROM TABLE_NAME WHERE CONDITION;
Similarly, we can use the statement below to retrieve all columns from all active customers.
1SELECT * FROM CUSTOMERS WHERE ACTIVE = 1;
Accordingly, all the customers with the ACTIVE
value 1
will be retrieved:
11|A1|MAEGAN|1
23|A3|LANA|1
3
The UPDATE
statement is used to update existing records in the database table.
Using the UPDATE
statement, we can update one or more columns in specific row(s) by coupling it with a where clause. Predictably, the where specifies which rows need to be updated.
We will use the following syntax to update specific column(s) based on the specified condition.
1UPDATE TABLE_NAME SET COLUMN1 = NEW_VALUE WHERE CONDITION;
Previously, we inserted one customer with the Active
value 0
. Suppose this originally inactive customer is now an active customer, and we want to update them in the database accordingly.
We can use the statement below to update the Active
column to 1
specifically for the customer with the CUSTOMER_ID
that is 2
.
1UPDATE CUSTOMERS SET ACTIVE = 1 WHERE CUSTOMER_ID = 2;
To validate that the records have been updated correctly, we can retrieve the active customers using the following query:
1SELECT * FROM CUSTOMERS WHERE ACTIVE = 1;
Which returns the expected output:
11|A1|MAEGAN|1
22|A2|DENNIS|1
33|A3|LANA|1
4
The DELETE
statement is used to delete the records from the database table.
Just like UPDATE
, when using the DELETE
statement, we can specify one or more row(s) to be deleted by using the where clause. The where
clause is critical—otherwise, on some SQL databases, we will delete all of our data. (Yes, all three rows of it, the horror!)
We’ll use the following syntax to delete specific row(s) from the database table.
1DELETE FROM TABLE_NAME WHERE <CONDITION>;
For example, if we want to delete a customer whose CUSTOMER_ID
is 2
, we can use this statement:
1DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 2;
This will delete one row. Now when we select all rows from the table using the SELECT
statement, we only see two rows:
1SELECT * FROM CUSTOMERS;
with the output:
11|A1|MAEGAN|1
23|A3|LANA|1
3
In this tutorial, we explored how to use CRUD operations in SQL to insert, retrieve, update, and delete information from a database table. These CRUD operations help connect databases with user-facing applications, from external products to internal apps.
If you’re interested in building an internal application to work with relational data in any SQL database, Retool makes it easy to build and maintain internal tools. Retool provides a UI toolkit via a drag-and-drop interface for displaying and visualizing data retrieved via CRUD operations. Give it a try for free here.
Thanks to Vikram Aruchamy for writing and Mathew Pregasen for contributing to this post.
Reader