You've got a database full of customer records, and your team needs to add, view, edit, and remove entries without pinging engineering for every change. The operations behind all of that are deceptively simple. They go by the acronym CRUD: Create, Read, Update, and Delete.
In this guide, we'll walk through each CRUD operation in SQL with concrete examples you can run against a real database. We'll also cover modern patterns like UPSERT, touch on SQL injection prevention, and show how CRUD app generators like Retool help you build data interfaces in hours instead of weeks.
A note on SQL dialects. Databases like MySQL, PostgreSQL, and Oracle each use a slightly different SQL flavor. The core CRUD syntax in this guide is consistent across all of them, but data types, constraints, and upsert behavior vary by engine.
Want to follow along? Use Retool to connect to an existing Postgres database, or download PostgreSQL locally and run queries from the command line.
A CRUD application is any piece of software that lets users create, read, update, and delete records in a database. If your app has a form for adding data, a table for viewing it, an edit button, and a delete button, you've built a CRUD app.
The term was popularized by James Martin in his 1983 book Managing the Data-Base Environment, and it's stuck around because it maps cleanly to both SQL and REST APIs. CRUD operations correspond to four SQL statements:
- Create →
INSERT - Read →
SELECT - Update →
UPDATE - Delete →
DELETE
They also map to HTTP methods (POST, GET, PUT/PATCH, DELETE), which means the same pattern works whether you're querying a database directly or building CRUD API endpoints.
Most internal software is, at its core, a CRUD app. Customer dashboards, inventory panels, user admin tools, order tracking systems. The underlying data operations are always the same. The SQL isn't the hard part. It's building the interface around it so non-technical teammates can work without filing a ticket every time they need to edit a record.
For our CRUD operations, we'll set up a CUSTOMERS table with four columns. This uses the CREATE TABLE statement, which provisions the table structure we'll query against. (This isn't itself a CRUD operation, even though it's called CREATE.)
1 2 3 4 5 6 7 8 9 10 11CREATE TABLE Customers ( customer_id INT NOT NULL PRIMARY KEY, customer_code VARCHAR(255) UNIQUE, contact_name VARCHAR(255), active TINYINT DEFAULT 1 );
Here's what each column does.
customer_id is the primary key. It uniquely identifies each row and accepts only unique, non-null values. You can also set up triggers to auto-increment it.
customer_code has a UNIQUE constraint, meaning no duplicate values are allowed. Unlike primary keys, unique columns can accept nulls.
contact_name has no constraints. It accepts any value, including duplicates and nulls.
active defaults to 1 and uses TINYINT (values 0 through 255). This is a common workaround in MySQL and SQL Server, which lack a native boolean type. (PostgreSQL has a native BOOLEAN type if you're working there instead.) When you don't specify a value during an insert, the default 1 gets assigned automatically.
Now let's work through each CRUD operation on this table.
The INSERT statement adds new rows to a table. You can provide values for every column or target a specific subset.
To insert a row with values for every column:
1INSERT INTO TABLE_NAME VALUES (VALUE1, VALUE2, ...);
The values must match the column order and count exactly. Get either wrong and SQL throws an error. (Ask any SQL developer: debugging vague SQL error messages can ruin an afternoon.)
Let's add two customers:
1 2 3INSERT INTO CUSTOMERS VALUES(1, 'A1', 'MAEGAN', 1); INSERT INTO CUSTOMERS VALUES(2, 'A2', 'DENNIS', 0);
Both rows are now in the database. Here, 1 means active and 0 means inactive.
This all-columns syntax works for quick examples, but it's fragile in production. If someone adds, removes, or reorders a column, the query breaks. Since databases are typically maintained by multiple people spanning departments, that creates real risk.
The safer approach names the columns explicitly:
1 2 3 4 5 6 7INSERT INTO TABLE_NAME (COLUMN_1, COLUMN_2, ...) VALUES (VALUE1, VALUE2, ...);
SQL matches values to the named columns in order. A type mismatch or length mismatch returns an error.
Let's add a third customer without specifying the active column:
1 2 3 4 5 6 7INSERT INTO CUSTOMERS (CUSTOMER_ID, CUSTOMER_CODE, CONTACT_NAME) VALUES (3, 'A3', 'LANA');
The active column gets its default value of 1 automatically. This pattern is more resilient to schema changes and is what you'll see in production code.
The SELECT statement pulls records from the database. You control exactly which rows and columns come back.
The * operator selects everything:
1SELECT * FROM TABLE_NAME;
This is convenient but slower than targeting specific columns, especially on large tables. Constraining the columns and rows reduces the data the database needs to scan and transmit.
Running:
1SELECT * FROM CUSTOMERS;
returns:
1 2 3 4 51|A1|MAEGAN|1 2|A2|DENNIS|0 3|A3|LANA|1
Name the columns you need:
1SELECT COLUMN_1, COLUMN_2, COLUMN_N FROM TABLE_NAME;
To check which customers are active:
1SELECT CONTACT_NAME, ACTIVE FROM CUSTOMERS;
Output:
1 2 3 4 5MAEGAN|1 DENNIS|0 LANA|1
The WHERE clause filters rows based on a condition:
1SELECT COLUMN1, COLUMN2 FROM TABLE_NAME WHERE CONDITION;
To retrieve only active customers:
1SELECT * FROM CUSTOMERS WHERE ACTIVE = 1;
Output:
1 2 31|A1|MAEGAN|1 3|A3|LANA|1
The UPDATE statement changes values in existing rows. Pair it with a WHERE clause to target specific records.
1UPDATE TABLE_NAME SET COLUMN1 = NEW_VALUE WHERE CONDITION;
Say customer Dennis (ID 2) was inactive but just renewed. Update the active flag:
1UPDATE CUSTOMERS SET ACTIVE = 1 WHERE CUSTOMER_ID = 2;
Verify the change:
1SELECT * FROM CUSTOMERS WHERE ACTIVE = 1;
Output:
1 2 3 4 51|A1|MAEGAN|1 2|A2|DENNIS|1 3|A3|LANA|1
Modern databases support UPSERT operations that insert a row if it doesn't exist, or update it if it does. In PostgreSQL, this looks like:
1 2 3 4 5 6 7INSERT INTO CUSTOMERS (CUSTOMER_ID, CUSTOMER_CODE, CONTACT_NAME, ACTIVE) VALUES (2, 'A2', 'DENNIS', 1) ON CONFLICT (CUSTOMER_ID) DO UPDATE SET ACTIVE = EXCLUDED.ACTIVE;
MySQL uses ON DUPLICATE KEY UPDATE for the same purpose. UPSERT eliminates the need for separate "check if exists, then insert or update" logic, which reduces race conditions and simplifies your application code. If you're syncing data from an external source or handling user submissions where records may or may not already exist, UPSERT saves you from writing conditional branching that's easy to get wrong under concurrency.
The DELETE statement removes rows from a table. Always use a WHERE clause. Without one, some databases will delete every row in the table. (Yes, all three rows of it. The horror.)
1DELETE FROM TABLE_NAME WHERE CONDITION;
To delete the customer with ID 2:
1DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 2;
Confirm with a SELECT:
1SELECT * FROM CUSTOMERS;
Output:
1 2 31|A1|MAEGAN|1 3|A3|LANA|1
Many production applications use soft deletes instead of hard deletes. Rather than removing a row, you set a flag (like active = 0 or deleted_at = NOW()). This preserves data for audit trails and makes accidental deletions recoverable. Our CUSTOMERS table already supports this pattern through the active column.
Any time your application passes user input into a SQL query, you open the door to SQL injection, one of the most common web vulnerabilities. The OWASP Top 10 (2025) ranks injection as a top-five threat, with over 14,000 CVEs attributed to SQL injection alone.
The fix is straightforward: use prepared statements with parameterized queries. Instead of concatenating user input directly into your SQL string, you pass values as parameters that the database treats as data, not executable code.
A vulnerable query builds the string directly:
1 2 3-- Don't do this query = "SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID = " + user_input;
A parameterized query separates the structure from the data:
1 2 3-- Do this instead SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID = \$1;
When you write queries in Retool, the platform uses prepared statements by default. User inputs from form components and table components are always parameterized, reducing risk—but injection is still a design concern when integrating external systems.
Beyond injection prevention, a few practices keep your CRUD operations reliable at scale. Use transactions when multiple operations need to succeed or fail as a group. Apply the principle of least privilege so database users only have the permissions they need. Add indexes to columns you frequently filter with WHERE clauses, and prefer SELECT with named columns over SELECT * in production. Log and audit your data changes, especially for UPDATE and DELETE operations.
Writing SQL is only half the job. The other half is building the interface that lets your team actually use it: tables to browse records, forms to create and edit them, confirmation dialogs for deletes, role-based permissions, and authentication.
That's why CRUD app generators exist. These tools connect directly to your database and give you pre-built components for the repetitive parts so you can focus on the logic specific to your use case.
The Stack Overflow Developer Survey (2024) found that SQL is the second most-used language among professional developers at 54.1%, and PostgreSQL is now the most popular database for the second consecutive year. The JetBrains Developer Ecosystem survey corroborates this, with open-source relational databases dominating the landscape. The demand for tools that put a usable interface on top of SQL data continues to grow.
CRUD app generators generally fall into a few categories. Code-generation tools scaffold source code from your database schema, giving you a codebase to own and customize. Full-stack frameworks include CRUD conventions out of the box, with scaffolding commands that generate models, views, and controllers. And low-code platforms provide drag-and-drop builders with pre-built UI components, database connectors, and deployment options so you can ship an internal tool in hours instead of weeks.
Retool is purpose-built for that last category. You connect your database (PostgreSQL, MySQL, SQL Server, MongoDB, and 70+ other sources), drag table and form components onto a canvas, and wire them to SQL queries. The platform handles prepared statements, role-based access, audit logging, and deployment out of the box. You can self-host in your own VPC or run on Retool's cloud.
Retool's AI features make this faster still. Describe the tool you need in plain language and get a working app scaffold in seconds. From there, customize in the drag-and-drop builder or drop into raw SQL and JavaScript when you need full control. The platform includes over 100 pre-built UI components, so you're assembling interfaces rather than coding them from scratch. The free plan supports up to five users with unlimited apps, so you can try it without a procurement cycle.
CRUD operations are the foundation of how applications interact with databases. The SQL itself is the straightforward part: INSERT, SELECT, UPDATE, DELETE. The real challenge is everything around those queries, including the UI, the security layer, the permissions model, and the deployment pipeline.
If you're writing SQL for a one-off analysis, the examples in this guide have you covered. If you're building an internal tool that non-technical teammates will use every day, give Retool a try. You'll spend your time on the problems that matter instead of reinventing table components and login screens.
Originally written by Vikram Aruchamy, with contributions from Mathew Pregasen. Updated March 2026.
Reader
