App Connection Examples

These examples demonstrate connections from within the same Fly.io internal IPv6 private network . To adapt them for external connections, substitute the internal hostname for one that's reachable over the public Internet.

Psql

If you have an active WireGuard tunnel to your organization's private network, you can connect from a local machine to your Postgres cluster the same way you would from a Fly app within the same private network. For example, the following command would start an interactive terminal session on the cluster leader with psql:

psql postgres://postgres:secret123@appname.internal:5432

You can, of course, run psql from a shell on a Fly app as well (as long as that app has psql installed).

Connecting With Ruby

docs

Ruby apps use the pg gem to connect to postgres.

require 'pg'

# Output a table of current connections to the DB
conn = PG.connect("postgres://postgres:secret123@postgresapp.internal:5432/yourdb")
conn.exec( "SELECT * FROM pg_stat_activity" ) do |result|
  puts "     PID | User             | Query"
  result.each do |row|
    puts " %7d | %-16s | %s " %
      row.values_at('pid', 'usename', 'query')
  end
end

Connecting With Rails

docs

Rails apps automatically connect to the database specified in the DATABASE_URL environment variable.

You can set this variable manually with flyctl secrets set

flyctl secrets set DATABASE_URL=postgres://postgres:secret123@postgresapp.internal:5432/yourdb

or by attaching the Postgres database to your Fly app.

Connecting With Go

docs

pgx is the recommended driver for connecting to postgres. It supports the standard database/sql interface as well as directly exposing low level / high performance APIs.

First, add github.com/jackc/pgx/v4 as a module depepdency.

go get github.com/jackc/pgx/v4

The following program will connect to the database in DATABASE_URL and run a query.

package main

import (
  "database/sql"
  "fmt"
  "os"

  _ "github.com/jackc/pgx/v4/stdlib"
)

func main() {
  db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
  if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
    os.Exit(1)
  }
  defer db.Close()

  var greeting string
  err = db.QueryRow("select 'Hello, world!'").Scan(&greeting)
  if err != nil {
    fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
    os.Exit(1)
  }

  fmt.Println(greeting)
}

Connecting With Node.js

docs

You'll use the pg npm module to connect to Postgres from a Node.js app.

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

await client.connect()
const res = await client.query('SELECT $1::text as message', ['Hello world!'])
console.log(res.rows[0].message) // Hello world!
await client.end()

Connecting With Prisma – Node.js

docs

Prisma is an open-source object-relational mapper (ORM) for Node.js and works with both JavaScript and TypeScript. It consists of 3 components:

  • Prisma Client - a type-safe query builder
  • Prisma Migrate - a data modeling and migration tool
  • Prisma Studio - a modern intuitive GUI for interacting with your database
Set up Prisma in your project

Install the Prisma CLI and Prisma Client dependencies in your project

npm i --save-dev prisma
npm i @prisma/client

Initialize Prisma in your project:

npx prisma init

This command does the following:

  • Creates a folder called prisma at the root of your project
  • Creates a .env file at the root of your project if it doesn't exist
  • Creates a schema.prisma file inside the prisma folder. This is the file that you will use to model your data

Update the DATABASE_URL in the .env to your PostgreSQL database

DATABASE_URL="postgres://postgres:secret123@postgresapp.internal:5432/yourdb"

If you are working in a brownfield project, you can introspect your database to generate the models in your schema.prisma file:

npx prisma db pull

Assuming you have the following model in your schema.prisma file:

Add a model to your schema.prisma file:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider  = "prisma-client-js"
}

model Post {
  id       Int     @id @default(autoincrement())
  title    String
  content  String?
}

You can query your database using Prisma as follows:

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  const posts = await prisma.post.findMany()

  const newPost = await prisma.post.create({
    data: {
      title: 'PostgreSQL on Fly',
      content: 'https://fly.io/docs/reference/postgres'
    }
  })
}

main()
  .catch((e) => {
    throw e
  })
  .finally(async () => {
    await prisma.$disconnect()
  })