CRUD operations

Rest APIs CRUD
SQL CRUD
Django CRUD
MongoDB CRUD
Flask CRUD
Bootstrap CRUD
FastAPI CRUD
Angular CRUD
React CRUD
GraphQL CRUD
Firebase CRUD
JSON CRUD
Laravel CRUD
SQL CRUD

SQL CRUD

Learn how to perform Create, Read, Update, and Delete (CRUD) operations with SQL across various programming languages. Each section includes SQL queries and code examples for common programming environments.

1. Create (INSERT)

In this guide, we’ll walk you through building CRUD SQL with practical code examples.

SQL Query

The INSERT operation adds new records to a table. It can populate all columns or selectively insert data into specific columns. The general syntax for inserting data into all columns can look like:

INSERT INTO TABLE_NAME VALUES (VALUE1, VALUE2, …);


Here’s an example of adding two entries to a CUSTOMERS table:

INSERT INTO CUSTOMERS VALUES(1, 'A1', 'MAEGAN', 1);
INSERT INTO CUSTOMERS VALUES(2, 'A2', 'DENNIS', 0);


On successful execution, the database will confirm the addition of two rows. Using all-column INSERT statements is not common in production due to the need for updates when table structures change.

1. Inserting Values into Specific Columns

To insert data into selected columns, specify the column names and corresponding values:

INSERT INTO TABLE_NAME (COLUMN_1, COLUMN_2, …)
VALUES (VALUE1, VALUE2, …);


This ensures omitted columns use default values, calculated values, or NULL. Example:

INSERT INTO CUSTOMERS (CUSTOMER_ID, CUSTOMER_CODE, CONTACT_NAME)
VALUES (3, 'A3', 'LANA');


If a default value is defined (e.g., ACTIVE defaults to 1), it will automatically be assigned.

Examples:

1. Javascript(using Node.js and Knex.js)

const knex = require('knex')({ client: 'mysql' });

knex('users')
  .insert({ name: 'John Doe', email: 'john.doe@example.com', age: 30 })
  .then(() => console.log('Record inserted'))
  .catch(err => console.error(err));

2. Python (using SQLAlchemy)

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData

engine = create_engine('sqlite:///example.db')
metadata = MetaData()

users = Table('users', metadata,
              Column('id', Integer, primary_key=True),
              Column('name', String),
              Column('email', String),
              Column('age', Integer))

conn = engine.connect()
insert_query = users.insert().values(name='John Doe', email='john.doe@example.com', age=30)
conn.execute(insert_query)

3. PHP (PDO)

$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['John Doe', 'john.doe@example.com', 30]);

2. Read (SELECT)

SQL Query

The SELECT statement retrieves records. By default, it fetches all rows and columns but can be tailored to return specific data.

1. Selecting All Columns and Rows

To fetch all data, use:

SELECT * FROM TABLE_NAME;


Example:

SELECT * FROM CUSTOMERS;

‍
Output:

1 | A1 | MAEGAN | 1
2 | A2 | DENNIS | 0
3 | A3 | LANA   | 1

2. Selecting Specific Columns

To fetch specific columns, list them in the SELECT statement:

SELECT COLUMN_1, COLUMN_2 FROM TABLE_NAME;


Example:

SELECT CONTACT_NAME, ACTIVE FROM CUSTOMERS;


Output:

MAEGAN | 1
DENNIS | 0
LANA   | 1

3. Selecting Specific Rows

To filter rows, add a WHERE clause:

SELECT * FROM TABLE_NAME WHERE CONDITION;

‍
Example:

SELECT * FROM TABLE_NAME WHERE CONDITION;

‍
Output:

1 | A1 | MAEGAN | 1
3 | A3 | LANA   | 1

Examples:

1. Javascript(using Node.js and Knex.js)

knex('users')
  .where('age', '>', 25)
  .select('*')
  .then(rows => console.log(rows))
  .catch(err => console.error(err));

2. Python (using SQLAlchemy)

result = conn.execute(users.select().where(users.c.age > 25))
for row in result:
    print(row)

3. PHP (PDO)

$sql = "SELECT * FROM users WHERE age > ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([25]);
$rows = $stmt->fetchAll();
print_r($rows);

3. Update (UPDATE)

SQL Query

The UPDATE statement modifies existing records. Combine it with WHERE to specify the rows to update.

Syntax

UPDATE TABLE_NAME 
SET COLUMN1 = NEW_VALUE 
WHERE CONDITION;

‍
Example: Updating an inactive customer to active:

UPDATE CUSTOMERS 
SET ACTIVE = 1 
WHERE CUSTOMER_ID = 2;


Validate the update with:

SELECT * FROM CUSTOMERS WHERE ACTIVE = 1;

‍
Output:

1 | A1 | MAEGAN | 1
2 | A2 | DENNIS | 1
3 | A3 | LANA   | 1

Examples:

1. Javascript(using Node.js and Knex.js)

knex('users')
  .where({ id: 1 })
  .update({ email: 'new.email@example.com' })
  .then(() => console.log('Record updated'))
  .catch(err => console.error(err));

2. Python (using SQLAlchemy)

update_query = users.update().where(users.c.id == 1).values(email='new.email@example.com')
conn.execute(update_query)

3. PHP (PDO)

$sql = "UPDATE users SET email = ? WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(['new.email@example.com', 1]);

4. Delete (DELETE)

SQL Query

The DELETE statement removes records. Use it cautiously, especially without a WHERE clause, to avoid accidental deletion of all rows.

Syntax

DELETE FROM TABLE_NAME WHERE CONDITION;

‍
Example: Deleting a specific customer:

DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 2;


Confirm by fetching the remaining records:

SELECT * FROM CUSTOMERS;

‍
Output:

1 | A1 | MAEGAN | 1
3 | A3 | LANA   | 1

Examples:

1. Javascript(using Node.js and Knex.js)

knex('users')
  .where({ id: 1 })
  .del()
  .then(() => console.log('Record deleted'))
  .catch(err => console.error(err));

2. Python (using SQLAlchemy)

delete_query = users.delete().where(users.c.id == 1)
conn.execute(delete_query)

3. PHP (PDO)

$sql = "DELETE FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([1]);

5. Create Table

Please note that code examples mentioned in this article assume that you will create a data structure (table) in your database.

SQL Query

CREATE TABLE Customers (
   customer_id NOR NULL PRIMARY KEY,
    name VARCHAR(255) UNIQUE,
    Contact_name VARCHAR(255)
    Active TINYINT DEFAULT 1
);


This statement defines the following characteristics for the table:

  • CUSTOMER_ID: Serves as the primary key, ensuring each row is uniquely identifiable. The primary key requires unique, non-null values. Triggers can be used to auto-increment this column for convenience.
  • CUSTOMER_CODE: A column with a UNIQUE constraint, meaning it can only contain unique values. Unlike the primary key, it allows null values.
  • CONTACT_NAME: A standard column with no specific constraints, allowing duplicate and null values.
  • ACTIVE: A column defined with a default value of 0. It uses the TINYINT data type, which accommodates values from 0 to 255. In this case, TINYINT is employed as a substitute for a boolean type since databases like MySQL and SQL Server don't have native boolean support (unlike Postgres). If no value is specified during record insertion, the default value (0) will be assigned.

Useful tools & libraries

Node.js Libraries:

Python Libraries

Notable libraries and example projects

Build your CRUD App in minutes

Speed up the development  with UI Bakery’s drag-and-drop CRUD builder. Learn more!