Migrating Mastodon lists

If you move from one Mastodon server to another and you want to migrate the people on your lists, you have few options. Here’s a way to do it with Steampipe and SQL.

InfoWorld |

paper boat sailing 154996543 — Thinkstock

My original Mastodon account was on mastodon.social. I chose it because it’s the largest instance, it’s operated by the developers of the service, and it runs the reference implementation of the software. I wanted to experience the fediverse, initially, as most people do, then consider whether to migrate to another server, and if so, which one.

Last week I migrated to social.coop which, like cosocial.ca, is what Darius Kazemi calls a community-owned independent social media site. I don’t think the acronym COISMS will fly, but the business model is just what I’ve been looking for. I don’t want to use “free” services that make me the product they sell. I want to pay for a service that’s funded by my dollars, not by my data. That’s why I bailed on Gmail years ago, in favor of Fastmail, and it’s why I’m happy to spend $10/month—the price of a couple of cappucinos—to support the people who keep the lights on at social.coop and maintain civility.

The instructions for migrating from one server offer two paths: Profile redirect and Profile move. I chose the former, because I assumed I would retain API access to mastodon.social post-migration, and thus have the option to use Steampipe to query both accounts. Why do that? You can’t migrate your posts from the old server to the new one, only your followers and (optionally) your follows, bookmarks, blocks, and mutes. Steampipe can connect to two different Mastodon servers at the same time, so I figured I could run queries against both.

Lessons learned

That turned out to be a wrong assumption. I can reactivate my mastodon.social account by turning off the redirection, but I’m vague on the implications of doing that. Meanwhile it’s still possible to export my posts, so I can always reacquire them that way if needed.

Here was another wrong assumption. I think that if I’d chosen to move my account, instead of redirect it, my profile would have transferred to the new server. Instead it was on me to fill out the new profile. I’d created @judell@social.coop but, when I kicked off the migration, I hadn’t yet completed the profile. So when followers were notified that judell@social.coop was now following them, there was no picture or bio or verified website. That led some people to think it was a bogus account and block it. It wasn’t a catastrophe, and I’ve spoken with a few of them to resolve the matter, but it was an unforced error that would have been easy to avoid.

The view from here

Here’s a comparison of weekly activity on two servers, by way of the server dashboard.

mastodon social vs social coop weekly activity

What’s it like moving to a server with only 1% of the flow that happens on mastodon.social? Not too different! Because I’ve moved my followers and follows, and because I interact primarily with the home timeline and with lists, the experience is mostly the same. For example, here’s the graph of boost relationships among servers in my home timeline. It looks just like it did before.

It’s wonderful to have a portable social graph that you can bring along as you move around in the fediverse. The key differences are that I’m meeting new people, and spending more time on the local timeline. On a big server like mastodon.social the local timeline feels like the Twitter firehose, for those who remember when that was still a thing you could watch. I’d look at it occasionally, because it could be a source of useful serendipity, but mostly it was just too random. The local timeline on social.coop is still somewhat random, but I find it more useful—not only because it represents a far smaller population but also because the people who’ve signed up share a common interest in the coop model.

I want my lists

There was, however, one big obstacle. You can export lists from the old server and import them into the new server, but there’s no export/import for the accounts you’ve assigned to those lists. Since I rely heavily on lists to navigate Mastodon flow in a focused and intentional way, this was a problem I really needed to fix. I tried Eliot Nash’s masto-list-importer but when I tried to transfer my 460 person/list mappings I hit API rate limits. So instead I worked out a Steampipe-based solution which is a useful alternative, and also a good way to illustrate the steps involved.

Let’s assume that I want to migrate again, from social.coop to fosstodon.org. I’ve exported the names of my lists from social.coop, and imported them to fosstodon.org. Now I need to populate those lists. The SQL solution I came up with proceeds by steps, each being a common table expression (CTE) that accomplishes one part of a complex transformation, then passes results to the next step.

Step 1: Gather the accounts assigned to each `social.coop` list.

We start by joining two tables to enumerate accounts assigned to each list.

  with accounts_by_list as (
  select 
    a.acct as old_account,
    a.id as old_account_id,
    l.id as old_list_id,
    l.title as title
  from
    social_coop.mastodon_my_list l
  join
     social_coop.mastodon_list_account a
  on
    l.id = a.list_id
)
select * from accounts_by_list

The output, in accounts_by_list, is effectively a temporary table that you can review to make sure the results are as expected. The ability to verify the output of each phase of a CTE pipeline, before sending it to the next phase, is a key advantage of this approach. Nested subqueries are much harder to debug!

Step 2: Map the accounts on `social.coop` to accounts on `fosstodon.org`

Two transformations happen in this phase. If an account is a bare username, say personA, then it’s a social.coop account. On fosstodon.org that same account will be represented as personA@social.coop. Conversely if personB@fosstodon.org exists on social.coop, that account becomes the bare username personB there. All other accounts (e.g. personC@hachyderm.io) pass through unchanged.

with accounts_by_list as (
  -- as above
),
adjusted_accounts_by_list as (
  select
    old_account,
    old_account_id,
    old_list_id,
    title,
    case 
      when old_account !~ '@' then old_account || '@social.coop' 
      when old_account ~ '@fosstodon.org' then replace(old_account, '@fosstodon.org', '')
      else old_account
    end as new_account
  from
     accounts_by_list
)
select * from adjusted_accounts_by_list

Step 3: Map the list titles to the new server.

The names of the lists are the same in both places, but their ids differ. For example, my Fediverse list is 1043 on social.coop and 6771 on fosstodon.org. To invoke the API call that adds someone to a list, we’ll need to use the latter id.

with accounts_by_list as (
  -- as above
),
adjusted_accounts_by_list as (
  -- as above
),
adjusted_list_ids as (
  select
    a.*,
    l.id as new_list_id
  from
    adjusted_accounts_by_list a
  join
    fosstodon.mastodon_my_list l
  on
    a.title = l.title
)
select * from adjusted_list_ids

Step 4: Map the account ids to the new server.

Like lists, accounts on the new server also have different ids, and those are also required for the API call. We can find the new ids by joining the new_account column from step one with the table fosstodon.mastodon_my_following.

with accounts_by_list as (
  -- as above
),
adjusted_accounts_by_list as (
  -- as above
),
adjusted_list_ids as (
  -- as above
,
),
adjusted_account_ids as (
  select 
    a.*,
    f.id as new_account_id
  from
    adjusted_list_ids a
  join
    fosstodon.mastodon_my_following f
  on
    f.acct = a.new_account
)
select * from adjusted_account_ids

Step 5: Build the API calls

There are lots of ways to skin this cat. Because you can use Steampipe as a component, you could use any programming language with a Postgres driver to run this query, and call the Mastodon API with the appropriate list and account ids.

And then, of course, there’s always curl.

with accounts_by_list as (
  -- as above
),
adjusted_accounts_by_list as (
  -- as above
),
adjusted_list_ids as (
  -- as above
),
adjusted_account_ids as (
  -- as above
)
select
  'curl -X POST -H "Authorization: Bearer ***" https://fosstodon.org/api/v1/lists/' ||
  new_list_id ||
  '/accounts/?' ||
  'account_ids[]=' ||
  new_account_id ||
  '; sleep 1;' as command

To avoid throttling, I appended sleep 1 to each line. I saved the query in migrate-lists.sql, and exported the output to a file.

steampipe query --output csv migrate-lists.sql > migrate.sh

That was close, but not quite right. The output looked like this:

command
"curl -X POST -H ""Authorization: Bearer ***"" https://fosstodon.org/api/v1/lists/6771/accounts/?account_ids[]=279462; sleep 1;"
"curl -X POST -H ""Authorization: Bearer ***"" https://fosstodon.org/api/v1/lists/6771/accounts/?account_ids[]=109283421559929728; sleep 1;"

I need to remove the header line, remove the double quotes on either end of each line, and deduplicate the pairs of double quotes. For that I turned to ChatGPT (v4). It took a few tries to get it right, but soon enough it produced a working bash script that I’m very glad I did not have to write.

#!/bin/bash

# read the original script from migrate.sh and store it in an array
readarray -t original_script < migrate.sh

# remove the first line of the original script
modified_script=("${original_script[@]:1}")

# loop through the modified script array and replace double double-quotes with a single double-quote and remove double quotes at the beginning and end of each line
for (( i=0; i<${#modified_script[@]}; i++ )); do
    modified_script[$i]=$(sed 's/""/"/g' <<< "${modified_script[$i]}")
    modified_script[$i]=$(sed 's/^"//' <<< "${modified_script[$i]}")
    modified_script[$i]=$(sed 's/"$//' <<< "${modified_script[$i]}")
done

# print out the modified script with newlines
printf '%s\n' "${modified_script[@]}"

And that did the trick. With my lists now fully populated on social.coop, I’m now reading Mastodon from my new home server in the same list-oriented way as I was on the old one.

If you move servers and want to migrate the people on your lists, you should definitely try masto-list-importer first, and consider the method here as fallback if that doesn’t work for you. I agree with Eliot Nash that list transfer ought to be baked into the Mastodon UI. People rightly complain that it can be hard to find the people you want to follow and topics you want to explore. You can follow hashtags but that can be too random. Assigning people to topical lists strikes a good balance. But only if those list assignments are portable!

This series:

Next read this:

Jon Udell was once "blogger in chief" for InfoWorld and is now the community lead for steampipe.io.