Migrating from the old database (previous versions)

The early versions of rsslay and the base project by fiatjaf in github.com/fiatjaf/relayer/tree/master/rss-bridge, were using the "Pebble" database (a LevelDB/RocksDB inspired key-value store focused on performance and internal usage by CockroachDB).

Due to the nature of this database and being embedded in the own program and not allowing multiple connections at once, a decission was made to trasition to SQLite with LiteFS from fly.io (based on Litestream).

This decision was made considering its low costs and the easiness of managing SQLite.

Due to this breaking change, people running old versions of the relay will have either to lose all its data or migrate it to the new SQLite database.

If you are ok with losing the current data you just can update the instance and it will create an empty DB.

On the other hand, the process of migrating can be very simple... We will show in this document how to iterate all the entries in "Pebble" and perform one-by-one inserts (which may be slow but secure).

Generating a simple script to run the migrations

We will be iterating all entries in Pebble and making inserts in the SQLite DB, this sample script can be temporally exposed in an endpoint or executed at the start of the program:

// Put here your data
databaseDirectory := ""
sqliteConnection := ""

schemaSQL := "CREATE TABLE IF NOT EXISTS feeds (publickey VARCHAR(64) PRIMARY KEY, privatekey VARCHAR(64) NOT NULL, url TEXT NOT NULL);"

// Open Pebble DB
if db, err := pebble.Open(databaseDirectory, nil); err != nil {
	log.Fatalf("failed to open db: %v", err)
} else {
	r.db = db
}
iter := db.NewIter(nil)

// Open SQLite db
sqlDb, err := sql.Open("sqlite3", *sqliteConnection)
if err != nil {
	log.Fatalf("open db: %v", err)
}

log.Printf("database opened at %s", *sqliteConnection)

// Run migration.
if _, err := sqlDb.Exec(schemaSQL); err != nil {
	log.Fatalf("cannot migrate schema: %v", err)
}

var count uint64 = 0
var items []Entry
for iter.First(); iter.Valid(); iter.Next() {
	var entity Entity
	err := iter.Error()
	point, hasRange := iter.HasPointAndRange()
	if err != nil && point && hasRange {
		continue
	}

	entry, err := iter.ValueAndErr()
	if err != nil {
		continue
	}

	if err := json.Unmarshal(entry, &entity); err != nil {
		continue
	}

	if _, err := db.Exec(`INSERT INTO feeds (publickey, privatekey, url) VALUES (?, ?, ?)`, entity.PublicKey, entity.PrivateKey, entity.URL); err != nil {
		log.Printf("failure: " + err.Error())
	} else {
		log.Print("saved feed")
	}
}

Note: we will need to add github.com/cockroachdb/pebble and github.com/mattn/go-sqlite3 as dependencies to our project/script.