The Great Migration

Around a month ago, Frontpage.fyi migrated away from Vercel Postgres aka Neon Serverless Postgres to Turso. Since then, we have read almost 7 million rows of data and written a little over 3000.

We managed to complete the migration with 0 downtime and with 0 data loss and our users were none the wiser. This for us, was an accomplishment we were proud of but why did we want to move away from Vercel to begin with? Well let me tell you.

On the hobby tier, Vercel has the following offering:

• Compute time: 60 hours
• Data transfer: N/A
• Databases: First Database (What does this mean Vercel?)
• Storage: 256MB
• Written data: N/A

Now you may think that the the issue was going to be that we had burned through our copious storage limit. However this was not the case, we were blowing though the compute time and they offered zero tooling to see why. We were also struggling with cold starts, switching to Vercel's paid tier helped a bit but it was still a cause of slow page loads.

I was already messing around with Turso for a different project (that is likely not going to see the light of day) so I put it forward as a possible alternitive. Lets take a quick look at the equivelent hobby tier on Turso.

• Compute time: Doesn't matter
• Data transfer: 1 Billion rows read
• Databases: 500 Total databases
• Storage: 9GB Total storage
• Written data: 25 Million rows written per month

Plus a few other nice to haves, such as embedded replicas, database branching, database groups and up to 3 locations. However if you ask Tom Sherman what his favourite feature is, he will probably tell you it is the database analytics you get for free (They are super cool).

Disclaimer: I am not 100% certain that Data transer and Written data map exactly over to rows written and rows read, this is just my assumption that seems to make the most sense.

So with a little bit of backstory out of the way, lets look at the technical implementation and how we achieved the migration.

The Technical Details

A key point to this migration and a bit of a shout out goes to Drizzle, I imagine it would have been a much more painful experience if it was not for this fantastic ORM!

Babies First Steps

Probably the easiest step, we created a new database in Turso via their web interface and then we utilised their CLI tool to grab our database credentials and put them into our environment variables.

From there, we deleted all of our old migration files as we knew we would not be needing them any more and we set our eyes to our schema.ts so we could start rewriting our schema. This was necessary as there are some nuances that needed tackling when moving from Postgres to SQLite.

Learning To Walk (again)

Rewriting our schema was not all that bad, again, thanks to Drizzle, the process was fairly easy (as I write this, I wonder if it was fate considering Drizzle has two libaries, postgres and sqlite).

Most of the column defitions could remain as they were but there were some that needed some tweaking and further consideration, lets look at a couple of them below.

We have a column on our posts for the post status, this was enum originally and something that Postgres handled natively.

Postgres version:

const submissionStatus = pgEnum("submission_status", [
    "live",
    "deleted",
    "moderator_hidden",
]);

const Post = pgTable("posts", {
    // ... other columns
    status: submissionStatus("status").default("live"),
});

Since SQLite does not handle enums natively, we had to make some changes to our code in order to get a similar behaviour but working within our new constraints.

SQLite version:

const createStatusColumn = (col: string) =>
    text(col, { enum: ["live", "deleted", "moderator_hidden"] }).default(
        "live",
    );

const Post = sqliteTable("posts", {
    // ... other columns
    status: createStatusColumn("status"),
});

This was a fairly trivial fix for the migration, one that I am sure was made easier by the flexibility of Drizzle. Another issue we ran into and that needed tackling was working with dates. We were storing them as a Date which is something that we would be unable to do as we were moving over to SQLite.

Postgres version:

const Post = pgTable("posts", {
    // ... other columns
    createdAt: timestamp("created_at").notNull().defaultNow(),
});

As you can see, in Postgres it is fairly easy to store a date inside of the database but for SQLite we had to get a little more creative. The reason for doing so is we wanted to maintain a good level of type safety for our schema which we would have lost if we had simply just stored the value as a string or integer.

SQLite version:

const dateIsoText = customType<{ data: Date; driverData: string }>({
    dataType() {
        return "text";
    },
    toDriver: (value) => value.toISOString(),
    fromDriver: (value) => new Date(value),
});

const Post = sqliteTable("posts", {
    // ... other columns
    createdAt: dateIsoText("created_at")
        .default(sql`(CURRENT_DATE)`)
        .notNull(),
});

What the above enabled use to do was to use drizzle for the heavy lifting and cast the date values to and from a Date object / string and vice versa.

The rest of the migration changes were even less trivial, changing varchar to text and so on. So now that we had a shiny new schema, it was time to generate a migration and push it up to Turso.

And it worked! We had all of our empty tables ready to go.

Running All The Way

Now I may have gave this final step the heading of Running all the way but it reality it was much more like a slow jog. There was no easy way to pull of the data down from Vercel and simply dump it into Turso. Luckily, Will found a pretty decent guide on some of the steps that would be required, which we followed and got us most of the way there.

These steps mostly revolved around removing reduntant SQL commands and public. prefixes. After we had followed the steps, we first tried executing the migration as one big SQL query, however this failed with what I can only describe as, the most unhelpful error messages ever.

After fiddling around with the SQL dump for quite some time, I decided to break out each table into it's own SQL file to help me find the problematic areas. This plan worked and aside from Tim and his rather huge and annoying post (testing out new lines with a 10k character limit) everything was going well.

The issue (for those of you interested) was due to references of database rows that no longer existed or needed to exist being being referenced. So it needed a bit of manually wizardy to set these values as null initially, and then backfill in the correct values.

Going Live

Finally, we had our new database, all of our data and we were ready to migrate! Tom approved the pull request, I merged it and Tim added the new environemtn variables to Vercel and everything was went off without an hitch.

If you are interested in learning more about Frontpage.fyi you can head on over and join the beta, we look forward to seeing you on there!

We Still Love You Postgres

If we are being completely honest, it is not all sunshine and rainbows. There are still bits we love and miss from Postgres. Such as migrations, these are much more involved with SQlite because it can involve copying tables, adding / removing columns, copying the data and deleting a table. We also miss some of the types and constraints that Postgres gave us out of the box.

Thankfully, as I mentioned above, Drizzle does a fantastic job of mitigating the types and contrainstraint shortcomings of SQLite but it could do a little bit better when it comes to migrations.