Nov 26, 2024

How to Alter Table in Sqlite

110 Words | 30 Seconds

2024-11-26 07:49 +0800

Create temp table, then do migration

The old table

CREATE TABLE "users" (
	"id"	INTEGER NOT NULL,
	"created_at"	INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"password"	TEXT NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);

Rename table

ALTER TABLE "users" RENAME TO "users_old";

The new table

CREATE TABLE "users" (
	"id"	INTEGER NOT NULL,
	"created_at"	TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	"password"	TEXT NOT NULL,
	PRIMARY KEY("id" AUTOINCREMENT)
);

Migrate data from old table

INSERT INTO "users" (id, created_at, password)
SELECT
	id, created_at, password
FROM "users_old";

Delete old table

DROP TABLE "users_old";

Confirm data

Double check if the created_at type is TIMESTAMP

Notes

Backup data
Data check
If created_at is not timestamp style, need transform.