Welcome to the boring but essential world of Sqlite3 CRUD where tiny files hold your data and your mistakes can be permanent unless you back them up. This short guide shows how to create a database file run basic create insert update and delete commands and stay sane using transactions and a couple of practical tips

Start a file database and open the CLI

If you like single file databases that behave predictably the sqlite3 CLI is your friend. Run the command below in a shell to open or create a file called mydb.sqlite

sqlite3 mydb.sqlite

Once inside the sqlite prompt you can run SQL directly or use dot commands for housekeeping

Create a simple users table

Define a compact table for demos or small apps. Primary key is an integer that autoincrements in Sqlite3 so you do not need to worry about assigning ids

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
)

Insert records and check your work

Populating the table is gratifying and harmless until you forget to backup. Run a few inserts and then select to verify

INSERT INTO users (name, email) VALUES ('Alice','alice@example.com')
INSERT INTO users (name, email) VALUES ('Bob','bob@example.com')

SELECT id, name, email FROM users

If you see your rows you have succeeded at SQL today

Update rows without drama

Target rows with a WHERE clause and use transactions when changing many rows to avoid half finished states and awkward bug reports

BEGIN TRANSACTION
UPDATE users SET email = 'alice.new@example.com' WHERE id = 1
COMMIT

Delete rows when they earn it

Delete removes data permanently from the file unless you have a backup. Be deliberate

DELETE FROM users WHERE id = 1

Quick safety and performance tips

• Enable foreign key support when using related tables with PRAGMA foreign_keys = ON
• Wrap bulk changes in a transaction using BEGIN TRANSACTION and COMMIT to avoid slow row by row writes
• Make a file copy for a quick backup with cp mydb.sqlite mydb.sqlite.backup on unix like systems
• Use indexes for large tables to speed up SELECT queries on filtered columns

Cli housekeeping

At the sqlite prompt use dot commands for control. Type .exit to quit and close the file cleanly

.exit

Wrapping up without false promises

That is the core of Sqlite3 CRUD Create your file create a table insert some rows query them update what needs updating and delete what you do not need. Remember backups and transactions when things matter and enable foreign keys when your schema needs referential integrity

Now go build something small and useful or at least something that teaches you a lesson without taking your production data hostage