Database Design and Migrations

This guide demonstrates migrating a small MariaDB (MySQL-compatible) table into a single-file SQLite database. The concrete goal is to move a contacts table (~1,000 rows) from a local MariaDB instance into contacts.db, preserving schema and data where practical and producing audit files plus a short verification report. This walkthrough covers:

Why naive shell-based exports can fail (e.g., unescaped apostrophes).
A robust, repeatable Python migration script using parameterized inserts.
How to produce companion .sql audit files and a simple verification summary.

This lesson focuses on a small, local migration (≈1,000 rows). For larger or production systems, plan for schema differences, transactionality, backups, and integrity checks before migrating.

Never paste production credentials into public transcripts or third-party tools. This demo uses a local test MariaDB instance and an example password. For production, use ephemeral credentials, environment variables, or an encrypted secrets store.

High-level plan

Inspect the MariaDB schema and sample data.
Attempted mysqldump / shell-based techniques to export/import (and why they can be brittle).
Use a small Python 3 script that:
- Introspects column metadata from INFORMATION_SCHEMA.
- Maps MySQL/MariaDB types to SQLite affinities.
- Creates an approximate SQLite schema.
- Inserts rows with parameterized statements inside a transaction.
- Writes audit .sql files for schema and inserts.
- Produces a verification summary (row counts).

Example prompts used to start the migration

Concise prompt:

I want to migrate my database from a local MariaDB server. Create a SQLite database and move the data from the MariaDB server to the SQLite database.

Detailed prompt (for production-quality code):

You are an expert migration engineer. Generate production-quality code (with clear comments) to migrate a MariaDB database to SQLite.

Goals:
- Read schema + data from MariaDB.
- Create a SQLite file that approximates schema and contains all rows (~1,000).
- Produce a Python script and companion .sql files (schema & inserts).
- Add logging, basic error handling, verification (row counts).

CLI args:
--mysql-host, --mysql-port, --mysql-user, --mysql-password, --mysql-db
--table
--sqlite-path
--output-dir

Both approaches work; the detailed prompt helps guide structure and testing.

Why simple shell pipelines can fail

Common pattern attempted in demos:

Inspect table:

mysql -h localhost -u jeremy -ppassword123 contactsdb -e "DESCRIBE contacts;"

Dump schema + inserts with mysqldump:

mysqldump -h localhost -u jeremy -ppassword123 --no-create-db --single-transaction contactsdb contacts > contacts_data.sql

Create an approximate SQLite table:

sqlite3 contacts.db "CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  job_title VARCHAR(100),
  email VARCHAR(255) NOT NULL UNIQUE
);"

Naive pipeline that reads rows and runs inline INSERTs (UNSAFE):

mysql -h localhost -u jeremy -ppassword123 contactsdb -e "SELECT * FROM contacts;" \
  | sed 's/\t/|/g' \
  | tail -n +2 \
  | while IFS='|' read -r id first_name last_name job_title email; do
    sqlite3 contacts.db "INSERT INTO contacts (id, first_name, last_name, job_title, email) VALUES ('$id', '$first_name', '$last_name', '$job_title', '$email');"
  done

Why this pattern is fragile:

Unescaped single quotes in data (e.g., O’Connolly) break inline SQL, causing syntax errors or creating injection risks.
Fields may contain separators, newlines, or binary data: naive splitting on tabs/pipes fails.
MySQL SQL dialect (backticks, engine definitions, collations) is not 1:1 compatible with SQLite.

In the demo, the shell import failed due to an apostrophe in a name. The safe resolution: use a connector library and parameterized queries.

Sample mysqldump excerpt

mysqldump outputs schema and INSERT statements. Example excerpt (trimmed):

-- Table structure for table `contacts`

DROP TABLE IF EXISTS `contacts`;
CREATE TABLE `contacts` (
  `id` int(11) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `job_title` varchar(100) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table `contacts`
LOCK TABLES `contacts` WRITE;
set autocommit=0;
INSERT INTO `contacts` VALUES
(1,'Britt','Hyne','Senior Editor','bhyne0@lulu.com'),
(2,'Merrel','Cornew','VP Product Management','mcornew1@indiegogo.com'),
(3,'Emmit','Glasard','Senior Editor','eglasard2@behance.net'),
...
;
UNLOCK TABLES;

Note: mysqldump is useful for capturing schema and data, but direct import of MySQL’s SQL into SQLite typically needs manual adjustments. For robust, repeatable migrations, a small script that introspects schema and performs parameterized inserts is usually preferable.

Type mapping overview

The migration script uses a conservative mapping from MySQL/MariaDB column types to SQLite affinity types. Example mapping:

MySQL / MariaDB type (prefix)	SQLite affinity
int, tinyint, smallint, bigint, mediumint, bit	INTEGER
decimal, numeric	NUMERIC
float, double	REAL
char, varchar, text, enum, set	TEXT
blob, binary, varbinary	BLOB
datetime, timestamp, date	TEXT (or INTEGER if you convert to epoch)

Tip: If you prefer storing timestamps as integers (epoch), map datetime/timestamp to INTEGER and convert the values during the insert loop.

Safer approach: Python migration script

The following production-oriented Python script performs an end-to-end migration of one table. It:

Connects to MariaDB using mysql-connector-python.
Reads column metadata from INFORMATION_SCHEMA.
Maps MySQL types to SQLite affinities.
Creates an approximate SQLite schema.
Inserts rows with parameterized statements inside a transaction.
Writes two audit files per table: <table>_schema.sql and <table>_inserts.sql.
Prints a brief verification summary.

Save as mariadb_to_sqlite.py.

#!/usr/bin/env python3
"""
mariadb_to_sqlite.py
Simple migration of one table from MariaDB to SQLite with basic type mapping,
parameterized inserts, .sql audit files, and a verification summary.

Dependencies:
  pip install mysql-connector-python
"""

import argparse
import logging
import os
import sqlite3
import sys
from typing import Dict, List, Tuple

import mysql.connector

# Basic type mapping from MariaDB -> SQLite affinity
TYPE_MAP = {
    "int": "INTEGER",
    "tinyint": "INTEGER",
    "smallint": "INTEGER",
    "mediumint": "INTEGER",
    "bigint": "INTEGER",
    "bit": "INTEGER",
    "decimal": "NUMERIC",
    "numeric": "NUMERIC",
    "float": "REAL",
    "double": "REAL",
    "char": "TEXT",
    "varchar": "TEXT",
    "text": "TEXT",
    "enum": "TEXT",
    "set": "TEXT",
    "blob": "BLOB",
    "binary": "BLOB",
    "varbinary": "BLOB",
    "datetime": "TEXT",
    "timestamp": "TEXT",
    "date": "TEXT",
    # Add more as needed
}


def map_type(mysql_type: str) -> str:
    t = mysql_type.lower()
    for k in TYPE_MAP:
        if t.startswith(k):
            return TYPE_MAP[k]
    return "TEXT"


def fetch_columns(cursor, db_name: str, table: str) -> List[Tuple]:
    q = """
    SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY, EXTRA
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
    ORDER BY ORDINAL_POSITION
    """
    cursor.execute(q, (db_name, table))
    return cursor.fetchall()


def build_create_table_sql(table: str, columns: List[Tuple]) -> str:
    """
    Build a CREATE TABLE statement for SQLite based on INFORMATION_SCHEMA metadata.
    This function maps types to SQLite affinities and preserves NOT NULL and DEFAULT
    where it is safe to do so. It also adds a PRIMARY KEY clause if one or more
    columns are marked as PRIMARY in the source.
    """
    cols_sql = []
    pk_cols = []
    for col_name, col_type, is_nullable, default, col_key, extra in columns:
        affinity = map_type(col_type)
        # Preserve NOT NULL when appropriate
        null_sql = " NOT NULL" if is_nullable == "NO" else ""
        # Handle default values conservatively:
        default_sql = ""
        if default is not None:
            d = str(default)
            # Allow CURRENT_TIMESTAMP without quotes, numeric defaults without quotes
            if d.upper() == "CURRENT_TIMESTAMP":
                default_sql = f" DEFAULT {d}"
            else:
                # Try to detect numeric defaults (simple heuristic)
                try:
                    float(d)
                    default_sql = f" DEFAULT {d}"
                except ValueError:
                    esc = d.replace("'", "''")
                    default_sql = f" DEFAULT '{esc}'"
        col_def = f'"{col_name}" {affinity}{default_sql}{null_sql}'
        cols_sql.append(col_def)
        if col_key == "PRI":
            pk_cols.append(col_name)

    pk_sql = ""
    if pk_cols:
        pk_sql = ", PRIMARY KEY (" + ", ".join(f'"{c}"' for c in pk_cols) + ")"
    return f'CREATE TABLE IF NOT EXISTS "{table}" (\n  ' + ",\n  ".join(cols_sql) + pk_sql + "\n);"


def write_audit_file(path: str, content: str):
    with open(path, "w", encoding="utf-8") as f:
        f.write(content)


def migrate_table(mysql_cfg: Dict, sqlite_path: str, table: str, output_dir: str) -> Tuple[int, int]:
    # Connect to MariaDB
    conn = mysql.connector.connect(**mysql_cfg)
    cur = conn.cursor()
    db_name = mysql_cfg["database"]

    # Fetch columns metadata
    cols = fetch_columns(cur, db_name, table)
    if not cols:
        raise RuntimeError(f"Table {table} not found in {db_name}")

    create_sql = build_create_table_sql(table, cols)
    os.makedirs(output_dir, exist_ok=True)
    write_audit_file(os.path.join(output_dir, f"{table}_schema.sql"), create_sql + "\n")

    # Open SQLite and create table
    sq = sqlite3.connect(sqlite_path)
    sq.row_factory = sqlite3.Row
    s_cur = sq.cursor()
    s_cur.execute(create_sql)
    sq.commit()

    # Read data from MariaDB and insert into SQLite
    col_names = [c[0] for c in cols]
    select_q = f"SELECT {', '.join('`' + c + '`' for c in col_names)} FROM `{table}`"
    cur.execute(select_q)

    placeholders = ", ".join(["?"] * len(col_names))
    # Build a quoted column list for INSERT
    columns_quoted = ", ".join(f'"{c}"' for c in col_names)
    insert_q = f'INSERT INTO "{table}" ({columns_quoted}) VALUES ({placeholders})'
    inserted = 0

    # Write inserts to audit file (safe, quoted representation)
    inserts_audit_path = os.path.join(output_dir, f"{table}_inserts.sql")
    with open(inserts_audit_path, "w", encoding="utf-8") as inserts_file:
        inserts_file.write(f"-- Inserts for {table}\nBEGIN TRANSACTION;\n")

        # Use a transaction for SQLite inserts
        s_cur.execute("BEGIN")
        for row in cur:
            values = []
            for val in row:
                values.append(val)
            s_cur.execute(insert_q, values)
            inserted += 1

            # Write a safe representation for the audit file (escaping single quotes)
            esc_vals = []
            for v in values:
                if v is None:
                    esc_vals.append("NULL")
                elif isinstance(v, (int, float)):
                    esc_vals.append(str(v))
                else:
                    esc = str(v).replace("'", "''")
                    esc_vals.append(f"'{esc}'")
            inserts_file.write(f"INSERT INTO \"{table}\" VALUES ({', '.join(esc_vals)});\n")

        s_cur.execute("COMMIT")
        inserts_file.write("COMMIT;\n")

    # Close connections
    cur.close()
    conn.close()
    sq.commit()
    sq.close()

    return inserted, len(col_names)


def parse_args():
    p = argparse.ArgumentParser(description="Migrate one table from MariaDB to SQLite")
    p.add_argument("--mysql-host", default="127.0.0.1")
    p.add_argument("--mysql-port", type=int, default=3306)
    p.add_argument("--mysql-user", required=True)
    p.add_argument("--mysql-password", required=True)
    p.add_argument("--mysql-db", required=True)
    p.add_argument("--table", required=True)
    p.add_argument("--sqlite-path", required=True)
    p.add_argument("--output-dir", default="out")
    return p.parse_args()


def main():
    args = parse_args()
    logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")

    mysql_cfg = {
        "host": args.mysql_host,
        "port": args.mysql_port,
        "user": args.mysql_user,
        "password": args.mysql_password,
        "database": args.mysql_db,
    }

    try:
        inserted, cols = migrate_table(mysql_cfg, args.sqlite_path, args.table, args.output_dir)
        logging.info("Migration summary:")
        logging.info(" Source: MariaDB %s/%s", args.mysql_host, args.mysql_db)
        logging.info(" Table: %s", args.table)
        logging.info(" Columns: %d", cols)
        logging.info(" Rows migrated: %d", inserted)
        logging.info(" SQLite file: %s", args.sqlite_path)
        logging.info(" Audit files: %s", args.output_dir)
        print(0)
    except Exception as e:
        logging.error("Migration failed: %s", e)
        sys.exit(1)


if __name__ == "__main__":
    main()

Notes about the script

Uses INFORMATION_SCHEMA to fetch column metadata and applies a conservative mapping to SQLite affinities.
Parameterized inserts (? placeholders) avoid quoting issues and SQL injection.
Wraps SQLite writes in a transaction for performance and atomicity.
Produces table_schema.sql and table_inserts.sql for auditing and replay.
To migrate multiple tables, iterate over a table list and call migrate_table for each, resolving dependency order if foreign keys exist.

Example usage (README style)

# Create and activate a Python venv (recommended)
python3 -m venv migration_env
source migration_env/bin/activate

# Install connector
pip install mysql-connector-python

# Run the migration for the contacts table
python mariadb_to_sqlite.py \
  --mysql-host=127.0.0.1 --mysql-port=3306 \
  --mysql-user=jeremy --mysql-password=password123 --mysql-db=contactsdb \
  --table=contacts \
  --sqlite-path=contacts.db \
  --output-dir=out

After success you should have:

contacts.db — the SQLite database file with the migrated table.
out/contacts_schema.sql — the CREATE TABLE used.
out/contacts_inserts.sql — INSERT statements for auditing/replay.

Verification

Compare row counts to ensure basic verification:

# Count in MariaDB
mysql -h localhost -u jeremy -ppassword123 contactsdb -e "SELECT COUNT(*) AS total_records FROM contacts;"

# Count in SQLite
sqlite3 contacts.db "SELECT COUNT(*) AS total_records FROM contacts;"

In the demo both counts returned 1000. You can also sample rows in SQLite:

sqlite3 contacts.db "SELECT id, first_name, last_name, email FROM contacts ORDER BY id LIMIT 10;"

Handling special type conversions

The TYPE_MAP is conservative: most temporal types are mapped to TEXT. If you prefer epoch integers:

Map datetime and timestamp to INTEGER in map_type.
Convert values when iterating over rows (e.g., parse strings or datetime objects to .timestamp()).

This approach gives you control over how to store and index time-based columns.

What happened in the demo

A concise assistant prompt was used to begin the migration.
A shell-based import failed because of apostrophes in names (e.g., O'Connolly).
The assistant produced the Python migration script above, created a virtual environment, installed mysql-connector-python, and ran the migration.
The script successfully migrated ~1,000 rows from the MariaDB contacts table into contacts.db.
Verification included counting rows in both databases and sampling a few rows in SQLite.

Below are screenshots from DB Browser for SQLite showing the resulting contacts.db and the contacts table (these images are from the original demo).

A dark-themed screenshot of DB Browser for SQLite with a macOS file-open dialog overlaid, showing a "Contacts" folder and files like contacts_data.sql, contacts.csv and contacts.db. A mouse cursor is pointing at a file named "migration_env."

A dark-themed database interface displaying a "contacts" table with columns like id, first_name, last_name, job_title, and email populated with many rows. The left sidebar shows a localhost connection and database objects (tables, collations, users).

Final thoughts and next steps

For small, local migrations, a compact, well-instrumented script is practical and repeatable.
Always prefer connector libraries + parameterized queries to ad-hoc shell pipelines to avoid quoting and encoding pitfalls.
Preserve audit/replay SQL files for review and reproducibility.
For production or larger databases, consider:
- Migrating in dependency order to respect foreign keys.
- Preserving or approximating indexes and constraints.
- Exporting views, triggers, and stored procedures (or at least documenting them).
- Testing end-to-end with data validation checks (row counts, checksums, sampling).

Links and references

Introduction

Getting Started with Claude Code

Working with Claude Code

Code Review with Claude Code

Security Auditing with Claude Code

Advanced Features

Database Design and Migrations

High-level plan

Example prompts used to start the migration

Why simple shell pipelines can fail

Sample mysqldump excerpt

Type mapping overview

Safer approach: Python migration script

Example usage (README style)

Verification

Handling special type conversions

What happened in the demo

Final thoughts and next steps

Watch Video

​High-level plan

​Example prompts used to start the migration

​Why simple shell pipelines can fail

​Sample mysqldump excerpt

​Type mapping overview

​Safer approach: Python migration script

​Example usage (README style)

​Verification

​Handling special type conversions

​What happened in the demo

​Final thoughts and next steps

Watch Video

High-level plan

Example prompts used to start the migration

Why simple shell pipelines can fail

Sample mysqldump excerpt

Type mapping overview

Safer approach: Python migration script

Example usage (README style)

Verification

Handling special type conversions

What happened in the demo

Final thoughts and next steps