Database Concepts Summary

Local vs Remote Databases

Understanding the architectural differences between local and remote databases is crucial for choosing the right solution.

Local Databases
Remote Databases

SQLite and File-Based Systems

Architecture

Entire database stored in a single file on the same machine as the application

Access Method

Direct file system access - no separate server process needed

Concurrency

Single concurrent writer, multiple readers

Use Cases

Development, testing, mobile apps, small desktop applications

Characteristics

Zero configuration - works out of the box
Serverless - no daemon or background service
Portable - single file can be copied/moved easily
Lightweight - minimal resource consumption
Limited concurrency - not suitable for high-traffic applications

Comparison Table

Feature	Local (SQLite)	Remote (MySQL/PostgreSQL)
Setup Complexity	None	Moderate to High
Concurrent Writes	1	Hundreds/Thousands
Network Dependency	No	Yes
Data Size	Up to ~280TB (practical: < 100GB)	Terabytes+
ACID Compliance	Full	Full
User Management	File permissions	Database users & roles
Ideal For	Dev, testing, small apps	Production, large-scale apps

Start with SQLite for development and prototyping, then migrate to a remote database when you need concurrency, scaling, or network access.

Flask-SQLite Integration

Flask connects to SQLite through Python’s built-in library with a structured approach.

Connection Methods

Raw SQL (sqlite3)
ORM (Flask-SQLAlchemy)

import sqlite3
from flask import g

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect('database.db')
        g.db.row_factory = sqlite3.Row
    return g.db

@app.teardown_appcontext
def close_db(error):
    db = g.pop('db', None)
    if db is not None:
        db.close()

@app.route('/users')
def users():
    db = get_db()
    users = db.execute('SELECT * FROM users').fetchall()
    return {'users': [dict(user) for user in users]}

Advantages:

Direct control over SQL
No additional dependencies
Simple for small projects

Disadvantages:

Manual connection management
No abstraction layer
Prone to SQL injection if not careful

from flask_sqlalchemy import SQLAlchemy

app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///database.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    
    def to_dict(self):
        return {
            'id': self.id,
            'username': self.username,
            'email': self.email
        }

# Query using ORM
@app.route('/users')
def users():
    users = User.query.all()
    return {'users': [user.to_dict() for user in users]}

@app.route('/user/<int:user_id>')
def get_user(user_id):
    user = User.query.get_or_404(user_id)
    return user.to_dict()

Advantages:

Database abstraction
Easy to switch databases
Built-in query builder
Relationship management
Migration support

Disadvantages:

Additional dependency
Learning curve
Slight performance overhead

Connection Lifecycle

Connection establishment

conn = sqlite3.connect('database.db')

Creates or opens database file and establishes connection.

Row factory configuration

conn.row_factory = sqlite3.Row

Enables dictionary-like access to rows: row['column'] instead of row[0]

Cursor creation

cursor = conn.cursor()

Cursor object executes SQL commands and fetches results.

Query execution

cursor.execute('SELECT * FROM users')
results = cursor.fetchall()

Execute SQL and retrieve data.

Transaction management

conn.commit()  # Save changes
conn.rollback()  # Undo changes

Changes are not permanent until commit() is called.

Connection cleanup

conn.close()

Always close connections to release resources.

CRUD Operations Fundamentals

CRUD operations form the foundation of data management in any application.

What is CRUD?

CRUD is an acronym for the four basic functions of persistent storage:

Create

Purpose: Insert new data
HTTP: POST
SQL: INSERT

Read

Purpose: Retrieve existing data
HTTP: GET
SQL: SELECT

Update

Purpose: Modify existing data
HTTP: PUT/PATCH
SQL: UPDATE

Delete

Purpose: Remove data
HTTP: DELETE
SQL: DELETE

Why CRUD is Fundamental

Complete Data Lifecycle

CRUD operations represent the entire lifecycle of any piece of data:

Born - Created when user signs up, creates post, etc.
Lives - Read and displayed throughout the application
Evolves - Updated as information changes
Dies - Deleted when no longer needed

Without any one operation, the application cannot fully manage its data.

Maps to User Actions

Every user interaction translates to a CRUD operation:

User Action	CRUD Operation	Example
Sign up	Create	New user account
View profile	Read	Display user information
Edit settings	Update	Change password
Close account	Delete	Remove user data
Post comment	Create	New comment record
View posts	Read	Display all posts
Edit comment	Update	Modify comment text
Remove comment	Delete	Delete comment

Universal Pattern

CRUD operations are universal across all applications:

E-commerce: Create orders, Read products, Update cart, Delete items
Social Media: Create posts, Read feed, Update profile, Delete messages
CMS: Create pages, Read content, Update articles, Delete drafts
Task Manager: Create tasks, Read list, Update status, Delete completed

Understanding CRUD helps you design any data-driven application systematically.

CRUD in Practice

import sqlite3
from flask import Flask, request, jsonify

app = Flask(__name__)

def get_db():
    conn = sqlite3.connect('app.db')
    conn.row_factory = sqlite3.Row
    return conn

# CREATE
@app.route('/users', methods=['POST'])
def create_user():
    data = request.get_json()
    conn = get_db()
    cursor = conn.execute(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        (data['name'], data['email'])
    )
    conn.commit()
    return jsonify({'id': cursor.lastrowid}), 201

# READ (all)
@app.route('/users', methods=['GET'])
def read_users():
    conn = get_db()
    users = conn.execute('SELECT * FROM users').fetchall()
    return jsonify([dict(user) for user in users])

# READ (one)
@app.route('/users/<int:user_id>', methods=['GET'])
def read_user(user_id):
    conn = get_db()
    user = conn.execute(
        'SELECT * FROM users WHERE id = ?', 
        (user_id,)
    ).fetchone()
    if user is None:
        return jsonify({'error': 'Not found'}), 404
    return jsonify(dict(user))

# UPDATE
@app.route('/users/<int:user_id>', methods=['PUT'])
def update_user(user_id):
    data = request.get_json()
    conn = get_db()
    conn.execute(
        'UPDATE users SET name = ?, email = ? WHERE id = ?',
        (data['name'], data['email'], user_id)
    )
    conn.commit()
    if conn.total_changes == 0:
        return jsonify({'error': 'Not found'}), 404
    return jsonify({'message': 'Updated'})

# DELETE
@app.route('/users/<int:user_id>', methods=['DELETE'])
def delete_user(user_id):
    conn = get_db()
    conn.execute('DELETE FROM users WHERE id = ?', (user_id,))
    conn.commit()
    if conn.total_changes == 0:
        return jsonify({'error': 'Not found'}), 404
    return jsonify({'message': 'Deleted'})

Migrating to Remote Database

When your application outgrows SQLite, migration to a remote DBMS involves two main steps.

Step 1: Database Migration

Set up remote database server

Install and configure MySQL or PostgreSQL:

# PostgreSQL
sudo apt-get install postgresql

# MySQL
sudo apt-get install mysql-server

Create new database

-- PostgreSQL
CREATE DATABASE myapp;

-- MySQL
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Export schema from SQLite

sqlite3 database.db .dump > schema.sql

Import into remote database

# PostgreSQL
psql -d myapp -f schema.sql

# MySQL
mysql -u root -p myapp < schema.sql

Schema syntax may need adjustments - SQLite SQL is not 100% compatible with PostgreSQL/MySQL.

Step 2: Application Refactoring

Changes required in your Flask application:

Connection String
Driver Installation
Connection Pooling
SQL Dialect Differences

import sqlite3
conn = sqlite3.connect('database.db')

# PostgreSQL
pip install psycopg2-binary

# MySQL
pip install mysql-connector-python

# SQLAlchemy (supports all)
pip install Flask-SQLAlchemy

from flask_sqlalchemy import SQLAlchemy

app.config['SQLALCHEMY_DATABASE_URI'] = \
    'postgresql://user:pass@localhost/myapp'
    
# Connection pool settings
app.config['SQLALCHEMY_POOL_SIZE'] = 10
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 30
app.config['SQLALCHEMY_POOL_RECYCLE'] = 3600

db = SQLAlchemy(app)

Connection pooling is essential for remote databases to efficiently manage multiple concurrent connections.

Some SQL features differ between databases:

Feature	SQLite	PostgreSQL	MySQL
Auto-increment	`AUTOINCREMENT`	`SERIAL`	`AUTO_INCREMENT`
Boolean	`INTEGER (0/1)`	`BOOLEAN`	`TINYINT(1)`
Datetime	`TEXT`	`TIMESTAMP`	`DATETIME`
String concat	`\|\|`	`\|\|`	`CONCAT()`
Limit	`LIMIT n`	`LIMIT n`	`LIMIT n`
Case sensitive	No	Yes	Depends on collation

Test thoroughly after migration - subtle differences can cause bugs.

Migration Checklist

Backup data

Create complete backup before migration

Test schema conversion

Verify all tables, indexes, and constraints

Update connection code

Install drivers and change connection strings

Add connection pooling

Configure pool size and timeouts

Update SQL queries

Fix dialect-specific syntax differences

Test thoroughly

Verify all CRUD operations work correctly

Monitor performance

Track query performance and connection usage

Using an ORM like SQLAlchemy from the beginning makes migration significantly easier, as most code remains unchanged - only the connection string needs to be updated.

Key Takeaways

Choose Wisely

Use SQLite for development and small apps, remote databases for production and scale

ORM Benefits

Consider using SQLAlchemy for database abstraction and easier migration

CRUD is Universal

Master CRUD operations - they apply to every data-driven application

Plan for Growth

Design with migration in mind - keep database logic separate and testable

Database Design

Web Development with Databases

Database Security

Programming & Computing

Architecture & Tools

Database Concepts Summary

Local vs Remote Databases

SQLite and File-Based Systems

Architecture

Access Method

Concurrency

Use Cases

Characteristics

Client-Server Systems (MySQL, PostgreSQL)

Architecture

Access Method

Concurrency

Use Cases

Characteristics

Comparison Table

Flask-SQLite Integration

Connection Methods

Connection Lifecycle

CRUD Operations Fundamentals

What is CRUD?

Create

Read

Update

Delete

Why CRUD is Fundamental

CRUD in Practice

Migrating to Remote Database

Step 1: Database Migration

Step 2: Application Refactoring

Migration Checklist

Key Takeaways

Choose Wisely

ORM Benefits

CRUD is Universal

Plan for Growth

Database Design

Web Development with Databases

Database Security

Programming & Computing

Architecture & Tools

​Local vs Remote Databases

​SQLite and File-Based Systems

Architecture

Access Method

Concurrency

Use Cases

​Characteristics

​Client-Server Systems (MySQL, PostgreSQL)

Architecture

Access Method

Concurrency

Use Cases

​Characteristics

​Comparison Table

​Flask-SQLite Integration

​Connection Methods

​Connection Lifecycle

​CRUD Operations Fundamentals

​What is CRUD?

Create

Read

Update

Delete

​Why CRUD is Fundamental

​CRUD in Practice

​Migrating to Remote Database

​Step 1: Database Migration

​Step 2: Application Refactoring

​Migration Checklist

​Key Takeaways

Choose Wisely

ORM Benefits

CRUD is Universal

Plan for Growth

Local vs Remote Databases

SQLite and File-Based Systems

Characteristics

Client-Server Systems (MySQL, PostgreSQL)

Characteristics

Comparison Table

Flask-SQLite Integration

Connection Methods

Connection Lifecycle

CRUD Operations Fundamentals

What is CRUD?

Why CRUD is Fundamental

CRUD in Practice

Migrating to Remote Database

Step 1: Database Migration

Step 2: Application Refactoring

Migration Checklist

Key Takeaways