Schema Migration Script for SQLite to Rename and Change Column Type

Database schema changes are a common part of evolving applications. In this guide, we’ll focus on writing a migration script for SQLite, specifically to rename a column and change its data type. This process is crucial when you need to modify your database structure while preserving existing data.

Introduction
Prerequisites
Creating a Migration Script
Running the Migration
Testing
Alternate method that allows you to keep the original table until you’re ready to drop it
Conclusion

Introduction

SQLite, being a lightweight and serverless database, requires special attention when it comes to schema changes. In this guide, we’ll explore the process of writing a migration script to rename a column and change its data type in an SQLite database.

Prerequisites

Before starting, ensure you have the following:

SQLite installed on your machine. A SQLite database with the table and column you want to modify.

Creating a Migration Script

Let’s assume you have a table named users with a column named old_column of type INTEGER, and you want to rename it to new_column and change its type to TEXT. Create a migration script (e.g., migration.sql) with the following content:

-- Save this as migration.sql

-- Step 1: Create a temporary table
CREATE TABLE users_backup AS
SELECT
id,
other_columns, -- list other columns excluding the one you're changing
CAST(old_column AS TEXT) AS new_column
FROM users;

-- Step 2: Drop the original table
DROP TABLE users;

-- Step 3: Rename the temporary table to the original table name
ALTER TABLE users_backup RENAME TO users;

In this script:

We create a temporary table (users_backup) with the new column name and the desired data type.
Drop the original table (users).
Rename the temporary table to the original table name.

Running the Migration

Open your terminal, navigate to the directory containing your SQLite database, and run the following command:

sqlite3 your_database.db < migration.sql

Replace your_database.db with the actual name of your SQLite database.

Testing

To verify the changes, open the SQLite command-line interface:

sqlite3 your_database.db

Run the following command:

PRAGMA table_info(users);

This should display the columns of the users table, and you should see the renamed and modified column.

Alternate method that allows you to keep the original table until you’re ready to drop it

-- Step 1: Create a backup of your table
CREATE TABLE my_table_backup AS SELECT * FROM my_table;

-- Step 2: Rename the existing table
ALTER TABLE my_table RENAME TO my_table_old;

-- Step 3: Create a new table with the desired changes
CREATE TABLE my_table (
    id INTEGER PRIMARY KEY,
    bucketname TEXT NOT NULL,
    date_time_created_at_utc INTEGER DEFAULT (CAST(strftime('%s', 'now') AS INTEGER)) NOT NULL
);

-- Step 4: Copy data from the backup to the new table
INSERT INTO my_table (id, bucketname, date_time_created_at_utc)
SELECT id, bucketname, strftime('%s', date_time_utc) FROM my_table_old;

-- Step 5: Drop the old table
DROP TABLE my_table_old;

Conclusion

You’ve successfully written and executed a migration script to rename a column and change its data type in SQLite. Remember to backup your database before making significant changes, and always test the migration on a copy of your database first. This ensures the integrity of your data during the migration process. Happy coding!

Table of contents