Databases

Most web servers store data in a database, and there are many databases to chose from. Examples include running PostgreSQL on fly.io’s infrastructure, embedding SQLite in your application, or accessing a MongoDB database hosted elsewhere.

For maximum flexibility, it generally is best if your application doesn’t hard code the path to the database, but rather extracts the path from the environment. We recommend, and will by default automatically set, DATABASE_URL to this path when we either provide or can detect the database.

For cases where an external database is used, you can set this yourself using fly secrets set.

Accessing the DATABASE_URL value

The way you access the DATABASE_URL value depends on the module or ORM you are using.

For Prisma, ensure your prisma/schema.prisma file contains:

const url = env("DATABASE_URL")

For drizzle/sqlite3:

const db = drizzle(new Database(new URL(process.env.DATABASE_URL).pathname));

For drizzle/postgres:

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool)

For knex/sqlite3:

  client: 'sqlite3',
  connection: { filename: new URL(process.env.DATABASE_URL).pathname },

For knex/pg:

  client: 'pg',
  connection: process.env.DATABASE_URL

for sqlite3:

const db = new sqlite3.Database(new URL(process.env.DATABASE_URL).pathname);

for postgres:

const client = new pg.Client({connectionString: process.env.DATABASE_URL});

for mongodb:

const client = new mongodb.MongoClient(process.env.DATABASE_URL);

SSL/TLS

If your database is hosted external to fly.io, you generally will want to connect to it securely. The way to do so varies by database and adapter. An example for postgres can be found at node-postgres features > ssl.

Equally important, if your database is hosted within the Fly.io private network, you will not want to connect with SSL/TLS connection options.

Migrations

In order to use a database we need to load and evolve a schema. This often involves running a tool like Prisma’s migrate tool.

For databases other than Sqlite3, this is best done as a one-off command run in a temporary VM. This can be accomplished by adding a release_command in your fly.toml file.

For sqlite3, a different approach is required as the migration will need to be run on each machine that has a volume mounted. This can be accomplished by creating a small script that first runs the migrate command then run the web server start script. You can place this script in your source tree, and then use the following command to replace the CMD in the Dockerfile:

npx dockerfile --cmd=file