Database Migration (upgrade from sqlite)

[fnafox]

Feature Description

As far as I'm aware, there is currently no way to migrate away from sqlite to a bespoke database solution, at least not an officially supported one. Someone who starts with a small sqlite instance not expecting it to grow can find themselves in a situation where there's no clean upgrade path should the demand change.

Having an official migration tool or at least an outlined process would be a welcome addition.

Screenshots

No response

[KazzmanK]

ESF Migration toolkit may be a solution. Just used trial version to migrate Sonar db.

[eeyrjmr]

So one option is to actually use the gitea dump command since the golang db library gitea uses (xorm) can interact with 4 db dialects

1. sqlite3
2. mysql/mariadb
3. mssql
4. postgresSQL

by default the dump command with DUMP in the dialect of your app.ini but it can be chosen as per https://docs.gitea.com/next/administration/command-line#dump

Example outputs of the sql file within the dump

SQLITE3 -> SQLITE3

/*Generated by xorm 2023-09-03 18:00:20, from sqlite3 to sqlite3*/

CREATE TABLE IF NOT EXISTS `oauth2_application` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `uid` INTEGER NULL, `name` TEXT NULL, `client_id` TEXT NULL, `client_secret` TEXT NULL, `confidential_client` INTEGER DEFAULT TRUE NOT NULL, `redirect_uris` TEXT NULL, `created_unix` INTEGER NULL, `updated_unix` INTEGER NULL);

SQLITE3 -> MYSQL

/*Generated by xorm 2023-09-03 18:00:25, from sqlite3 to mysql*/

SET sql_mode='NO_BACKSLASH_ESCAPES';
CREATE TABLE IF NOT EXISTS `oauth2_application` (`id` BIGINT(20) PRIMARY KEY AUTO_INCREMENT NOT NULL, `uid` BIGINT(20) NULL, `name` VARCHAR(255) NULL, `client_id` VARCHAR(255) NULL, `client_secret` VARCHAR(255) NULL, `confidential_client` TINYINT(1) DEFAULT TRUE NOT NULL, `redirect_uris` TEXT NULL, `created_unix` BIGINT(20) NULL, `updated_unix` BIGINT(20) NULL);

SQLITE3 -> MSSQL

/*Generated by xorm 2023-09-03 18:00:29, from sqlite3 to mssql*/

IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = '[oauth2_application]' ) CREATE TABLE [oauth2_application] ([id] BIGINT PRIMARY KEY IDENTITY NOT NULL, [uid] BIGINT NULL, [name] VARCHAR(255) NULL, [client_id] VARCHAR(255) NULL, [client_secret] VARCHAR(255) NULL, [confidential_client] BIT DEFAULT 1 NOT NULL, [redirect_uris] VARCHAR(MAX) NULL, [created_unix] BIGINT NULL, [updated_unix] BIGINT NULL);
SET IDENTITY_INSERT [oauth2_application] ON;
CREATE INDEX [IDX_oauth2_application_uid] ON [oauth2_application] ([uid]);

SQLITE3 -> POSTGRES

/*Generated by xorm 2023-09-03 18:00:33, from sqlite3 to postgres*/

CREATE TABLE IF NOT EXISTS "public"."oauth2_application" ("id" BIGSERIAL PRIMARY KEY  NOT NULL, "uid" BIGINT NULL, "name" VARCHAR(255) NULL, "client_id" VARCHAR(255) NULL, "client_secret" VARCHAR(255) NULL, "confidential_client" BOOL DEFAULT TRUE NOT NULL, "redirect_uris" TEXT NULL, "created_unix" BIGINT NULL, "updated_unix" BIGINT NULL); ;
CREATE UNIQUE INDEX "UQE_oauth2_application_client_id" ON "oauth2_application" ("client_id");
CREATE INDEX "IDX_oauth2_application_created_unix" ON "oauth2_application" ("created_unix");

Once completed the sql file within the dump archive should be loadable into whatever db you intend to use now

NOTE: if you do use xorm as the translator please ensure you have a backup of the db in its natural format (ie copy the sqlite file, mysql dump... etc) "just in case" there is a xorm export bug.