Question

Serverless functions and PostgreSQL

I have been banging my head against the wall trying to get a serverless function to successfully connect to a PostgreSQL dev instance inn the App Platform.

Locally, I have pgAdmin installed, and no problems there.

My case is just a simple read operation, but with all the function runtimes (node:14, node:18, node-lambda:18, python:3.9 and go:1.17) and postgres drivers I just end up getting a timeout when opening the connection (both in the serverless development environment and on App Platform).

In each case I have tried slightly modifying the functions to run locally, and that works with no problems. I am getting a feeling that the port (25060) might not be open or something.

Anyway, I am hearing and reading all about how seamlessly integrated these serverless functions are with DO’s managed databases, but as far as I can see, there aren’t any docs/examples/guides (for any language) that cover serverless/PostgreSQL.

I’ll attach my latest attempt (with the sql statement redacted, replace with anything…), hoping someone can point out what I am doing wrong:

package main

import (
    "context"
    "errors"
    "fmt"
    "log"
    "net/http"
    "os"

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

type In struct {
    Id string `json:"id"`
}

type Out struct {
    StatusCode int               `json:"statusCode,omitempty"`
    Headers    map[string]string `json:"headers,omitempty"`
    Body       string            `json:"body,omitempty"`
}

var (
    connStr string
    ErrNoId = errors.New("no id provided!")
)

func init() {
    connStr = os.Getenv("PG_CONN")
    if connStr == "" {
        panic("No connection string (PG_CONN) provided!")
    }
}

func Main(in In) (*Out, error) {
    if in.Id == "" {
        return &Out{StatusCode: http.StatusBadRequest}, ErrNoId
    }

    db, err := pgx.Connect(context.Background(), connStr)

    if err != nil {
        log.Println(err)
        panic("Could not open connection to database!")
    }

    defer db.Close(context.Background())

    var version string

    var sql = `
        SELECT ...
    `

    err = db.QueryRow(context.Background(), sql, in.Id).Scan(&version)

    if err != nil {
        fmt.Println("No rows were returned!")
        version = "1.0.0"
    }

    return &Out{
        StatusCode: http.StatusOK,
        Body:       version,
    }, nil
}


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

I think I essentially figured this out. For the purposes of using a local pgAdmin installation to configure the PostgreSQL development instance, I turned off the “Trusted sources” option for the database in App Platform. Both common sense and the info box that pops up states that this opens the database up to connections from anyone/anywhere with the right credentials, which is what I wanted.

However, as it turns out, when “Trusted sources” is turned off, serverless functions on both App Platform and in the development environment fail to connect to the database. I feel pretty confident that this is a DigitalOcean issue/bug, seeing as I could connect to the database from anywhere else.

Could it be that DO is using some sort of internal access logic for serverless functions that looks for an entry in “Trusted sources” regardless of whether it is actually turned on, and hence consistently declines connections if it is turned off?