// databases

What Is SQLite?

SQLite is a lightweight relational database system that stores an entire database in a file. Unlike many database systems, it does not require a separate database server running in the background.

// definition

SQLite is an embedded relational database

SQLite is a relational database management system designed to be embedded directly inside applications.

A complete SQLite database is commonly stored in a single file on disk.

Applications interact with that file using SQL commands such as SELECT, INSERT, UPDATE and DELETE.

// simple idea

A database can be just one file

A SQLite database might look like this:

customers.db

Inside that file could be several tables:

customers orders products payments

The application can open the database file and query those tables directly.

// serverless

SQLite does not need a separate database server

Systems such as MySQL and PostgreSQL normally run as separate database server processes.

Applications connect to those servers over a local connection or network connection.

SQLite works differently.

Application ↓ SQLite library ↓ database.db

The application accesses the database through the SQLite library rather than contacting a separate database server.

// sql

SQLite uses SQL

SQL stands for Structured Query Language.

SQL is used to create, read, update and delete data stored in relational databases.

SELECT * FROM customers;

This query asks SQLite to return all rows from the customers table.

// tables

SQLite stores data in tables

A customer table might contain:

id	name	email
1	Alice	alice@example.com
2	Ben	ben@example.com
3	Carla	carla@example.com

Each row represents a record, while each column represents a particular type of information.

// create table

Tables can be created with SQL

CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT );

This creates a table containing three columns:

id name email

// insert

Data can be inserted

INSERT INTO customers ( name, email ) VALUES ( 'Alice', 'alice@example.com' );

This adds a new row to the customers table.

// select

Data can be retrieved

SELECT name, email FROM customers;

A condition can also be added:

SELECT * FROM customers WHERE id = 1;

This retrieves the customer whose ID is 1.

// update

Existing data can be changed

UPDATE customers SET email = 'alice.new@example.com' WHERE id = 1;

This changes the email address associated with the selected row.

// delete

Rows can be deleted

DELETE FROM customers WHERE id = 1;

The matching row is removed from the table.

// crud

These operations are often called CRUD

Create

Add new records to the database.

Read

Retrieve existing information.

Update

Change existing records.

Delete

Remove records from the database.

// relational

SQLite is a relational database

Relational databases can connect information stored across different tables.

For example:

customers id | name 1 | Alice orders id | customer_id | total 1001 | 1 | 49.99 1002 | 1 | 18.50

The customer_id value connects the orders to Alice's customer record.

// joins

SQL can combine related tables

SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id;

The database can use the relationship between the tables to combine the relevant information.

// files

SQLite databases can use different file extensions

You may encounter files such as:

data.db database.sqlite app.sqlite3 customers.db

The extension does not fundamentally determine whether the file is a SQLite database. It is mainly a naming convention.

// storage classes

SQLite can store several kinds of values

Storage class	Purpose
NULL	No value
INTEGER	Whole numbers
REAL	Floating-point numbers
TEXT	Text strings
BLOB	Binary data

// embedded

SQLite is often embedded inside applications

Software can include SQLite as part of the application itself.

The user may never even realise that a SQLite database is being used.

Desktop Apps Mobile Apps Browsers Local Tools Development Testing

// portable

SQLite databases are highly portable

Because a database can be stored in a single file, it can be straightforward to copy, back up or move.

project/ │ ├── app.py ├── database.db └── settings.json

The database can sit directly alongside the application files.

// sqlite vs csv

SQLite is very different from CSV

CSV	SQLite
Plain-text file	Database file
Usually one table	Can contain many tables
No SQL engine	Supports SQL queries
Limited relationships	Relational structure
Simple data exchange	Application data storage

// sqlite vs mysql

SQLite and MySQL use different architectures

SQLite	MySQL
Embedded database	Database server
Database commonly stored in one file	Server manages database storage
No separate server required	Requires database server software
Excellent for local and embedded use	Common for networked applications

// sqlite vs server database

SQLite is not designed for every database workload

SQLite is extremely useful when simplicity and local storage are important.

A dedicated database server such as PostgreSQL or MySQL may be more appropriate when an application requires many simultaneous writers, centralised access, extensive server administration or large multi-user workloads.

SQLite is not simply a smaller MySQL. Its architecture is deliberately different. It is designed to provide a database engine directly inside an application.

// transactions

SQLite supports transactions

A transaction allows several database operations to be treated as one logical unit.

BEGIN; UPDATE accounts SET balance = balance - 50 WHERE id = 1; UPDATE accounts SET balance = balance + 50 WHERE id = 2; COMMIT;

Transactions help protect data from being left in an incomplete state if an operation fails.

// reliability

SQLite supports ACID transactions

Atomicity

A transaction is completed as a unit or rolled back.

Consistency

Database rules can help preserve valid states.

Isolation

Transactions are managed to avoid unsafe interference.

Durability

Committed changes are designed to survive failures.

// indexes

Indexes can improve query performance

CREATE INDEX idx_customer_email ON customers(email);

An index can allow the database to find certain values more efficiently instead of examining every row.

// programming

Many programming languages can use SQLite

Python PHP JavaScript C C++ Java C# Swift

// example with python

SQLite can be used from application code

For example, Python includes support for SQLite:

import sqlite3 connection = sqlite3.connect("customers.db") cursor = connection.cursor() cursor.execute( "SELECT * FROM customers" ) rows = cursor.fetchall() connection.close()

The program opens the database file, executes a query and retrieves the results.

// uses

What is SQLite used for?

Mobile Apps Desktop Software Local Data Application Settings Prototypes Testing Small Websites Data Analysis Embedded Systems Offline Applications

// advantages

Why is SQLite useful?

Simple

No separate database server needs to be configured.

Portable

An entire database can commonly be stored in one file.

Lightweight

The database engine can be embedded directly inside an application.

Powerful

It still provides SQL, transactions, indexes, relationships and many database features.

// important distinction

A SQLite database is more than an ordinary data file

A CSV file stores rows of text.

A JSON file stores structured text.

A SQLite file contains a database managed by an actual database engine.

CSV → data file JSON → structured data file SQLite → database stored in a file

That distinction is important.

// summary

A database inside one file.

SQLite is an embedded relational database system that provides SQL, tables, relationships, transactions and indexes without requiring a separate database server. Its simplicity and portability make it useful for mobile apps, desktop software, local tools, prototypes and many other applications.

// server-side programming

What Is PHP?

PHP is a programming language widely used for server-side web development. It can generate web pages, process forms, communicate with databases, manage sessions and build dynamic websites and applications.

// definition

PHP is a server-side scripting language

PHP is a general-purpose programming language particularly suited to web development.

PHP code commonly runs on a web server rather than directly inside the user's browser.

The server executes the PHP code and sends the resulting output, often HTML, back to the browser.

// name

What does PHP stand for?

PHP is officially described using the recursive acronym:

PHP: Hypertext Preprocessor

The name reflects PHP's long association with generating and processing web content.

// example

What does PHP look like?

<?php echo "Hello, world!"; ?>

The echo statement sends text to the output.

In a web application, that output can become part of the HTML returned to the browser.

// server

PHP usually runs before the page reaches the browser

Browser requests page ↓ Web server receives request ↓ PHP code executes ↓ PHP generates output ↓ Server sends response ↓ Browser displays page

The browser usually receives the output generated by PHP rather than the original PHP source code.

// php + html

PHP can be embedded inside HTML

<h1>Welcome</h1> <p> Today is <?php echo date("d F Y"); ?> </p>

PHP executes on the server and inserts the generated value into the document before it is sent to the user.

// variables

PHP can store information in variables

<?php $name = "Alice"; $orders = 4; echo $name; echo $orders; ?>

PHP variable names normally begin with a $ symbol.

// data types

PHP supports different kinds of data

Type	Example
String	"Freelance Coder"
Integer	42
Float	19.99
Boolean	true
Array	["HTML", "CSS", "PHP"]
Null	null
Object	new Customer()

// arrays

PHP arrays can store collections of data

<?php $skills = [ "HTML", "CSS", "JavaScript", "PHP" ]; echo $skills[0]; ?>

This outputs:

HTML

// associative arrays

PHP arrays can also use named keys

<?php $customer = [ "name" => "Alice", "orders" => 4, "active" => true ]; echo $customer["name"]; ?>

Associative arrays are useful for representing structured information using named keys and values.

// conditions

PHP can make decisions

<?php $loggedIn = true; if ($loggedIn) { echo "Welcome back."; } else { echo "Please sign in."; } ?>

Conditional statements allow different code to run depending on the state of the application.

// functions

PHP functions package reusable logic

<?php function greet($name) { return "Hello, " . $name; } echo greet("Alice"); ?>

The full stop operator joins strings together in PHP.

// loops

PHP can repeat tasks

<?php for ($i = 1; $i <= 5; $i++) { echo $i; } ?>

Loops are useful when the same operation must be repeated several times.

// foreach

foreach is useful for collections

<?php $skills = [ "HTML", "CSS", "PHP" ]; foreach ($skills as $skill) { echo $skill; } ?>

The loop processes each item in the array one at a time.

// forms

PHP can process form submissions

A website might contain a form:

<form method="post"> <input type="text" name="name" > <button type="submit"> Send </button> </form>

PHP can receive the submitted data:

<?php if ($_SERVER["REQUEST_METHOD"] === "POST") { $name = $_POST["name"] ?? ""; } ?>

User input should be validated and handled safely. Real applications should never assume that submitted information is automatically trustworthy.

// databases

PHP can communicate with databases

PHP is commonly used with relational databases such as MySQL and PostgreSQL.

An application might use a database to store:

Users Products Orders Blog Posts Settings Transactions

// database connection

PHP provides database interfaces

One common approach is PHP Data Objects, or PDO.

<?php $pdo = new PDO( "mysql:host=localhost;dbname=shop;charset=utf8mb4", $username, $password ); ?>

Applications can then execute database queries through the connection.

// sql safety

Prepared statements are important

<?php $stmt = $pdo->prepare( "SELECT * FROM users WHERE email = ?" ); $stmt->execute([$email]); $user = $stmt->fetch(); ?>

Prepared statements allow values to be supplied separately from the SQL structure and are an important defence against SQL injection.

// sessions

PHP can maintain session data

<?php session_start(); $_SESSION["user_id"] = 123; ?>

Sessions allow a server to associate information with a user across multiple requests.

They are commonly used for login systems and other stateful website features.

// cookies

PHP can also work with cookies

<?php setcookie( "theme", "light", time() + 3600 ); ?>

Cookies are stored by the browser and can later be sent back to the server with requests.

// json

PHP works with JSON

PHP can convert arrays and objects into JSON:

<?php $data = [ "name" => "Alice", "orders" => 4 ]; echo json_encode($data); ?>

It can also convert JSON into PHP data:

<?php $data = json_decode( $json, true ); ?>

This makes PHP useful for building and consuming APIs.

// APIs

PHP can power APIs

Instead of returning an HTML page, a PHP application can return structured data.

<?php header( "Content-Type: application/json" ); echo json_encode([ "status" => "success", "message" => "Data received" ]); ?>

Another application can then consume the JSON response.

// php vs javascript

PHP and JavaScript often work together

PHP

Commonly runs on the server and handles server-side application logic.

JavaScript

Commonly runs in the browser and handles interactive client-side behaviour.

PHP Output

Can generate HTML or JSON that is sent to the browser.

JavaScript Requests

Can send requests to PHP-powered APIs and process their responses.

// web application

A PHP web application might work like this

User ↓ Browser ↓ HTTP request ↓ Web server ↓ PHP application ↓ Database ↓ PHP generates response ↓ HTML or JSON ↓ Browser

PHP often acts as the server-side layer connecting users, application logic and stored data.

// wordpress

WordPress is built with PHP

PHP has played a major role in the development of content management systems.

WordPress uses PHP extensively for themes, plugins, templates, database interaction and server-side logic.

<?php the_title(); the_content(); ?>

WordPress-specific PHP functions can dynamically retrieve and display website content.

// .php files

PHP code is commonly stored in .php files

index.php contact.php login.php api.php functions.php

A web server configured for PHP can execute the PHP code when one of these files is requested.

// security

Server-side code must handle data carefully

PHP applications often process passwords, form inputs, database queries, cookies and uploaded files.

Secure applications therefore need appropriate validation, escaping, authentication and database practices.

Never trust input simply because it came from your own website. Server-side code should validate data and apply the correct security controls for how that data will be used.

// frameworks

PHP has a large software ecosystem

Developers can use frameworks and content management systems rather than building every component from scratch.

Laravel Symfony WordPress Composer PHPUnit

// uses

What is PHP used for?

Dynamic Websites WordPress APIs Databases Forms Authentication E-commerce Web Applications Server-Side Logic Automation

// summary

The server does the work.

PHP is a programming language widely used for server-side web development. It can generate HTML, process forms, manage sessions, communicate with databases, work with JSON and APIs, and power dynamic websites and applications such as WordPress.

// file formats

What Is a .csv File?

A .csv file is a simple way of storing tabular data as plain text. It is commonly used to move information between spreadsheets, databases, websites and software applications.

// definition

CSV means Comma-Separated Values

CSV stands for Comma-Separated Values.

A .csv file stores data as plain text, usually with each line representing a row and commas separating the individual values within that row.

This makes CSV particularly useful for information that naturally fits into rows and columns.

// example

What does CSV data look like?

Imagine a spreadsheet containing customer information:

Name,Email,Orders Alice,alice@example.com,4 Ben,ben@example.com,7 Carla,carla@example.com,2

The first line contains the column headings. Each line after that represents one record.

Name	Email	Orders
Alice	alice@example.com	4
Ben	ben@example.com	7
Carla	carla@example.com	2

The CSV file and the table contain essentially the same information. The difference is how that information is represented.

// structure

Rows, columns and values

Rows

Each line normally represents one record, such as one customer, one product or one transaction.

Columns

Each position within a row represents a particular field, such as a name, price or date.

Delimiter

Commas normally separate the fields from one another, giving the format its name.

Header row

Many CSV files begin with a row containing the names of the columns, although this is not compulsory.

// plain text

A CSV file is still a text file

CSV is a structured text format.

If you open a CSV file in a plain-text editor, you can normally read its contents directly.

However, spreadsheet software such as Microsoft Excel or Google Sheets can interpret the separators and display the information as rows and columns.

Plain Text Rows Columns Records Fields

// quoted values

What happens if the data itself contains a comma?

Sometimes a value needs to contain a comma of its own. In that situation, the value can be enclosed in double quotation marks.

Name,Location Alice,"Birmingham, England" Ben,"London, England"

The quotation marks tell software that the comma inside the location belongs to the value rather than separating two different columns.

// escaping

What about quotation marks inside a value?

If a quoted field itself contains a double quotation mark, CSV commonly represents it by doubling the quotation mark.

Product,Description Book,"The ""Advanced"" Edition"

This allows the quotation mark to remain part of the actual data.

// csv vs spreadsheet

A CSV file is not an Excel workbook

Excel can open CSV files, but that does not make CSV an Excel format.

A normal Excel workbook can store much more than simple tabular values. It can contain formatting, formulas, multiple worksheets, charts and other spreadsheet features.

Feature	CSV	Excel workbook
Rows and columns	Yes	Yes
Plain text	Yes	No
Multiple worksheets	No	Yes
Cell formatting	No	Yes
Charts	No	Yes

Important: when an Excel workbook is exported to CSV, features such as colours, fonts, charts and multiple worksheets are not preserved as part of the CSV format.

// why csv?

Why is CSV so widely used?

Simple

CSV has a straightforward structure that is relatively easy for both people and software to understand.

Portable

CSV files can move data between many different applications and operating systems.

Lightweight

There is very little additional information surrounding the actual data.

Programmable

Programming languages and data tools can easily read, create and transform CSV files.

// uses

What are CSV files used for?

CSV files are common anywhere structured data needs to move from one system to another.

Customer Data Product Lists Transactions Website Data Analytics Database Exports Affiliate Data Inventory Spreadsheets Data Migration

// encoding

CSV and UTF-8

Like other text files, a CSV file must use a character encoding.

UTF-8 is a particularly useful choice because it supports a huge range of characters and languages.

Name,City,Amount João,Lisboa,£25 Élodie,Paris,€40 Sofia,Athens,€32

Important: the .csv extension does not itself guarantee UTF-8. The character encoding is a separate property of the file.

// delimiters

Is a comma always used?

CSV literally means Comma-Separated Values, and commas are the standard delimiter associated with the format.

In practice, however, data files may also use other separators. For example, semicolons are sometimes used because of regional settings or software conventions.

Name;City;Orders Alice;Birmingham;4 Ben;London;7

Files using tabs as separators are commonly called TSV, meaning Tab-Separated Values.

// data management

Why CSV matters in data management

CSV is particularly useful because it provides a simple bridge between different systems.

A database can export records to CSV. A spreadsheet can open them. A script can transform them. Another application can then import the resulting file.

Database ↓ CSV ↓ Spreadsheet ↓ Script / Automation ↓ Another System

This makes CSV extremely useful for data migration, cleaning, imports, exports and automation.

// txt vs csv

How is CSV different from TXT?

Both .txt and .csv files can contain plain text.

The key difference is that CSV normally follows a tabular structure: values are separated into fields and records so that software can interpret them as rows and columns.

A general .txt file does not necessarily have that structure.

Format	Main purpose
.txt	General plain-text information
.csv	Structured tabular data

// summary

Simple text. Structured data.

A .csv file stores tabular information as plain text, normally using commas to separate values and new lines to separate records. Its simplicity makes CSV one of the most useful formats for exchanging data between spreadsheets, databases, websites and software systems.