Writing in a database means adding or inserting data to the database. In this Web Note, I will talk about Create, Read, Update, Delete (CRUD), INSERT INTO, CSVs, .import, Triggers, and Soft Deletions using SQLite. Let’s start 😊
To create a database for Nawabshah, the city I live in (located in Sindh, Pakistan), we will add a table for popular places.
-- Create a table to store Nawabshah details
CREATE TABLE nawabshah (
id INTEGER PRIMARY KEY, -- Unique ID for each entry
name TEXT, -- Name of the place
popular_places TEXT -- Description of popular places
);
Reading data means retrieving it from the database. Here’s how to read data from the table:
-- Simple SELECT query to read data
SELECT * FROM nawabshah; -- Fetch all rows from the Nawabshah table
Updating data allows modifying existing entries, and deleting removes entries.
-- Update an entry
UPDATE nawabshah
SET name = 'Updated Name'
WHERE id = 1; -- Change name for the entry with ID 1
-- Delete an entry
DELETE FROM nawabshah
WHERE id = 2; -- Remove the entry with ID 2
INSERT INTO is used to add data to a table. Data can also be imported from a CSV file.
-- Insert data into the table
INSERT INTO nawabshah (id, name, popular_places)
VALUES (1, 'Quest University', 'The University i study in.');
-- Import data from a CSV file
.mode csv -- Set mode to CSV
.import file.csv nawabshah -- Import data from file.csv into the Nawabshah table
Triggers automate actions based on database events.
-- Create a trigger to log deletions
CREATE TRIGGER log_deletion
AFTER DELETE ON nawabshah
BEGIN
INSERT INTO deletion_log (deleted_id) VALUES (OLD.id); -- Log the deleted ID
END;
Soft deletions mark entries as inactive instead of removing them.
-- Add an "is_active" column for soft deletions
ALTER TABLE nawabshah ADD COLUMN is_active BOOLEAN DEFAULT 1; -- 1 for active, 0 for inactive
-- Soft delete an entry
UPDATE nawabshah
SET is_active = 0
WHERE id = 3; -- Mark the entry with ID 3 as inactive