How to preserve random order of an SQLite table on ephemeral disks

In SQLite databases, generating random values with a seed is not possible as the random function of SQLite does not support a seed value. That's why it may not be possible to write an SQL query to get the same random order every time. But there are solutions to this so you can preserve the random order. This post is one of those solutions.

On ephemeral disks like Heroku's, the files you write to disk will not persist after your application is restarted. So having an SQLite database as a file is pointless. However, if you only want to keep unimportant runtime data in that sqlite database and you have another database as a service to keep actual data in, the simple and "dirty" solution in this article will help you a lot.

How?

Assuming the table name is questions, we add a new column to the table in order to store the random data. We can name it random_order:

ALTER TABLE questions ADD COLUMN random_order REAL;

Then we generate random values for each row:

UPDATE questions SET random_order = Random();

All the rows now have a permanent random values. Using these values, we can preserve the random order and get the data sorted by these random values.

SELECT * FROM questions ORDER BY random_order

You now have a randomly pre-ordered database that can be used even on ephemeral disks. That's all.