Connecting to PlanetScale securely (Node.js, Prisma)

Questions / Help
1

Hi,

I get an error when I deploy a simple node.js application using Planetscale and Prisma.

This is the error:
Error opening a TLS connection: error:16000069:STORE routines:func(0):unregistered scheme:…/deps/openssl/openssl/crypto/store/store_register.c:237:scheme=file, error:80000002:system library:func(0):reason(2):…/deps/openssl/openssl/providers/implementations/storemgmt/file_store.c:267:calling stat(/etc/ssl/certs), error:16000069:STORE routines:func(0):unregistered scheme:…/deps/openssl/openssl/crypto/store/store_register.c:237:scheme=file, error:80000002:system library:func(0):reason(2):…/deps/openssl/openssl/providers/implementations/storemgmt/file_store.c:267:calling stat(/etc/ssl/certs), error:16000069:STORE routines:func(0):unregistered scheme:…/deps/openssl/openssl/crypto/store/store_register.c:237:scheme=file, error:80000002:system library:func(0):reason(2):…/deps/openssl/openssl/providers/implementations/storemgmt/file_store.c:267:calling stat(/etc/ssl/certs), error:16000069:STORE routines:func(0):unregistered scheme:…/deps/openssl/openssl/crypto/store/store_register.c:237:scheme=file, error:80000002:system library:func(0):reason(2):…/deps/openssl/openssl/providers/implementations/storemgmt/file_store.c:267:calling stat(/etc/ssl/certs), error:0A000086:SSL routines:func(0):certificate verify failed:…/deps/openssl/openssl/ssl/statem/statem_clnt.c:1896: (unable to get local issuer certificate)

From what I could understand by logging in with “flyctl ssh console”, the path to the system CA roots

/etc/ssl/certs/ca-certificates.crt

does not exist and was not created by Fly and therefore Planetscale cannot open a TLS connection.

This is Planetscale’s guide to creating a secure connection:

I don’t know how to fix this error and get Planetscale working with Fly.io.

Can you help me? Thank you.

2

Hi,

You may not need to concern yourself with where the CA root it. It may simply be a case of adjusting the connection string you use to connect to it. For example with a base of node:18-slim I was able to successfully connect using mysql2 from Node.js with this:

DATABASE_URL='mysql://user:password@region/name?ssl={"rejectUnauthorized":true}'

If that form does not work for you, take a look at this page and scroll down. You’ll see people finding different variations they got working with mysql, mysql2 and sequelize. One of them should work!

If not, hmm. Next would be figuring out if you are using Fly’s provided buildpack or a Dockerfile? If you are not sure, if you don’t have a file called Dockerfile in the folder your node.js app is in, you’ll be using their buildpack.

I ask because the base OS will determine the path to the CA roots. It may not be where you suggest. It’s in a different place depending on what the Linux distribution is (Debian, Alpine etc). I’m not sure what the base of Fly’s buildpack is, whereas with a Dockerfile you are in control of what base image you use (check the first line). If you take a look at this section of the Planetscale docs, it lists the various places it may be:

But like I say, that shouldn’t matter. It’s more likely the connection string’s SSL bit that needs editing.

1 Like
3

Is there a way to install the system CA root in the right path “/etc/ssl/certs/ca-certificates.crt”? In a way that automatically handles the update of the certificates.

I am not a Docker user.

I am only deploying a node.js app with “fly deploy”.

thank you.

4

Hi,

Well … my point was that may not be the “right” path for the system CA. Where it should be varies.

In your DATABASE_URL what have you got for the XXX part here? Don’t paste the full string, of course, just that end bit. Since from those linked posts, it’s worth trying different values for that param to make SSL connections work:

DATABASE_URL='mysql://user:password@region/name?ssl=XXX'

Once you’ve tried all those and so eliminated the ?ssl param as your issue, it would then be worth looking for the CA stuff.

5

I already tried all those solutions but nothing has changed, I see the same error.

I am using the same connection string that Planetscale recommend in its Web UI through the usage of sslaccept=strict being present in the DATABASE_URL. Without any changes to the connection string.

Screenshot 2022-12-14 alle 17.20.50
Screenshot 2022-12-14 alle 17.20.502372×314 23.2 KB

6

Strange :thinking:

As those comments say, variations of ?ssl or ?sslmode worked for them (and me). The param at the end of the string shown in the Planetscale UI can’t account for all variations of Node/library so that’s why it’s worth changing it. But you say you have and none worked.

So … given I don’t know how the default Fly buildpack works or what Linux distribution it uses, all I can suggest is trying to use the alternative way to make a Fly app which is to use aDockerfile. It is easier than it sounds. Basically you just need a Dockerfile which tells Fly how to build the app’s image, and a sibling .dockerignore file (to tell it what files to, yep, ignore - like any .env file which you don’t want in there).

If you want to give that approach a try, take a look at this sample repo:

You could then adjust accordingly for your app. Shouldn’t be needed as the buildpack should work. But you’ve found it doesn’t, so I’m out of ideas. It would be a case of someone else replying who has got Planetscale working with a buildpack-built app, and saying how :slight_smile:

1 Like
7

When I run fly launch, it automatically creates the files:

  • fly.toml
  • Dockerfiles
  • .dockerignore

I don’t know if it’s using the buldpack. These files are created automatically.

The linux distribution is this:

Screenshot 2022-12-15 alle 09.20.39
Screenshot 2022-12-15 alle 09.20.391302×632 72 KB

8

Ah, well I assume Fly have changed how their launch process works since I last used it. That’s fine, you already have a Dockerfile and .dockerignore then. Which suggests it’s not using the alternative (the buildpack).

Since you mention Debian bullseye … I wonder if the fix is as simple as changing the quotes around your URL :thinking: Only this person found that was the problem when they too could not connect to Planetscale (after experimenting with CA):

9

No, nothing changes. I always get the same error.

Is there a way to install the system CA root via the Dockerfile?

10

Weird!

Yep, you can do (almost) anything in a Dockerfile. Run whatever command you want in there :slight_smile:

For example this is a Debian Bullsye Dockerfile I found after a quick Google. I’m guessing this is what you are after. Check this line out:

If you like, you could copy your current Dockerfile here to see what it’s currently doing. Since I’m still surprised whatever it is Fly provides as default doesn’t already come with whatever base CA is needed. From what you’ve said it sounds like it doesn’t.

11

Sure, here is my current Dokerfile:

FROM debian:bullseye as builder

ARG NODE_VERSION=18.6.0

RUN apt-get update; apt install -y curl

RUN curl https://get.volta.sh | bash

ENV VOLTA_HOME /root/.volta

ENV PATH /root/.volta/bin:$PATH

RUN volta install node@${NODE_VERSION}

#######################################################################

RUN mkdir /app

WORKDIR /app

# NPM will not install any package listed in "devDependencies" when NODE_ENV is set to "production",

# to install all modules: "npm install --production=false".

# Ref: https://docs.npmjs.com/cli/v9/commands/npm-install#description

ENV NODE_ENV production

COPY . .

RUN npm install

FROM debian:bullseye

LABEL fly_launch_runtime="nodejs"

COPY --from=builder /root/.volta /root/.volta

COPY --from=builder /app /app

WORKDIR /app

ENV NODE_ENV production

ENV PATH /root/.volta/bin:$PATH

CMD [ "npm", "run", "start" ]

I’m not a Docker user. Could you tell me what should I add to this file to install the system CA?

12

Well it’s only a guess having not done this myself, but since it is a multi layer I’d try scrolling down and adding a line to add ca-certificates: RUN apt install -y ca-certificates. Maybe put that line in the second half between these two lines:

...
FROM debian:bullseye

RUN apt install -y ca-certificates

LABEL fly_launch_runtime="nodejs"
...

If the build fails as a result of adding that line, well my guess was wrong. But whatever error message it shows should show why not e.g use apt-get or no ca-certificates available or already installed etc.

13

It doesn’t work.

I see the same error.

Isn’t there someone from the Fly support who can fix this?

14

I didn’t think adding that line would help since like I say, the base image should come with any CA certificate already.

To investigate I actually tried myself just now, creating a brand new Node.js app with mysql2, with a Planetscale database.

I ran it locally. Worked fine, connected etc.

I ran fly launch. Sure enough that created a Dockerfile exactly the same as the one you have (the buildpack approach I previously mentioned must have now moved to legacy, as that used to be how Node.js worked - now they default to using a Dockerfile which is better). So that duplicates what you have.

I then ran fly secrets set DATABASE_URL='mysql://user:password@host/database-name?ssl={"rejectUnauthorized":true}' copied exactly from the Planetscale UI, using their suggested ssl param. That stages that secret, ready for deployment.

I then ran fly deploy and watched the process, and if you look very carefully you can see certs stuff being added as it whizzes past on screen from the builder output. As suspected. The deploy proceeded and worked. I checked the app using the name.fly.dev and sure enough, a sample query to the database worked.

app.get("/run_example_query", (req, res) => {
  connection.query(
    "SELECT * FROM table_name LIMIT 1",
    function (err, rows, fields) {
      if (err) throw err;

      res.send(rows);
    }
  );
});

That fetched the data from my Planetscale database:

Screenshot 2022-12-16 at 16.58.49

All works fine for me. Node.js, Fly, Planetscale.

So … the only remaining variable may be Prisma. Maybe try not using that, check it connects, then add that back in to the mix. If it breaks then, you will know it’s Prisma. If not, no idea!

Unfortunately you don’t officially get support without a paid plan (check the fly.io for options). Free support comes via this forum. You do see people from Fly on here regularly though so I’m sure someone will respond at some point.

1 Like
15

I tried, as you suggested, not using Prisma and using mysql2 instead.

I created and deployed a node.js app with express.js and mysql2 connected to a Planetscale database. And as in your case it works without problems.

However, I did not see the installation of certificates in the output of the build process. Maybe it’s working because it’s not using a TLS connection?

I do not know. I won’t be using Prisma with Planetscale on Fly.io for now.

1 Like
16

Seems I’ve ran into the same issue here.
I have a very basic Next.js app(with App Router) and I’m using prisma + planetscale for data persistance. Works like a charm on my local machine(MacOS) - I run pnpm dev it runs on localhost:3000 and I can do CRUD as expected; I run pnpm build it builds successfully.
Now I want to deploy it on flyio. I first ran fly launch, it automatically creates following file:

  1. fly.toml
  2. Dockerfile
  3. .dockerignore

Then I ran fly deploy, seems it failed at pnpm build. I see the following error:

15.55 Error opening a TLS connection: error:0A000086:SSL routines:tls_post_process_server_certificate:certificate verify failed:../ssl/statem/statem_clnt.c:1889: (unable to get local issuer certificate)
15.55     at _n.handleRequestError (/app/node_modules/.pnpm/@prisma+client@5.10.2_prisma@5.10.2/node_modules/@prisma/client/runtime/library.js:123:7154)
15.55     at _n.handleAndLogRequestError (/app/node_modules/.pnpm/@prisma+client@5.10.2_prisma@5.10.2/node_modules/@prisma/client/runtime/library.js:123:6188)
15.55     at _n.request (/app/node_modules/.pnpm/@prisma+client@5.10.2_prisma@5.10.2/node_modules/@prisma/client/runtime/library.js:123:5896)
15.55     at async l (/app/node_modules/.pnpm/@prisma+client@5.10.2_prisma@5.10.2/node_modules/@prisma/client/runtime/library.js:128:10871)
15.55     at async s (/app/.next/server/app/page.js:1:3312)
 ✓ Generating static pages (5/5)
15.66
15.66 > Export encountered errors on following paths:
15.66 	/page: /
15.69  ELIFECYCLE  Command failed with exit code 1.
------
Error: failed to fetch an image or build from source: error building: failed to solve: executor failed running [/bin/sh -c pnpm run build]: exit code: 1

I’ve searched for solutions, none of them works so far. I’ve tried:

  1. replace the ending query of the connection string from sslaccept=strict to ssl={“rejectUnauthorized”:true} or ssl={"rejectUnauthorized":true}&&sslaccept=strict or some other string
  2. remove .env from .dockerignore
  3. add previewFeatures = ["driverAdapters"] in prisma.schema under generator section
  4. set fly secrets DATABASE_URL with actually value in double quote(“mysql://…”), single quote(‘mysql://…’) or without quote
  5. add following in Dockerfile
ENV NODE_TLS_REJECT_UNAUTHORIZED=0
RUN pnpm config set strict-ssl false
  1. change Node version from 20 to 18

No luck at all.
Can someone help? Or if someone successfully deployed prisma + planetscale app, please share.

Some additional info:

  1. When I try to manually find out where my certs files sits, fly ssh console results in an error telling me that I haven’t deployed anything yet.
  2. Following is my generated Dockerfile
# syntax = docker/dockerfile:1

# Adjust NODE_VERSION as desired
ARG NODE_VERSION=20.11.0
FROM node:${NODE_VERSION}-slim as base

LABEL fly_launch_runtime="Next.js/Prisma"

# Next.js/Prisma app lives here
WORKDIR /app

# Set production environment
ENV NODE_ENV="production"

# Install pnpm
ARG PNPM_VERSION=8.15.3
RUN npm install -g pnpm@$PNPM_VERSION


# Throw-away build stage to reduce size of final image
FROM base as build

# Install packages needed to build node modules
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y build-essential node-gyp openssl pkg-config python-is-python3

# Install node modules
COPY --link package.json pnpm-lock.yaml ./
RUN pnpm install --frozen-lockfile --prod=false

# Generate Prisma Client
COPY --link prisma .
RUN npx prisma generate

# Copy application code
COPY --link . .

# Build application
RUN pnpm run build

# Remove development dependencies
RUN pnpm prune --prod


# Final stage for app image
FROM base

# Install packages needed for deployment
RUN apt-get update -qq && \
    apt-get install --no-install-recommends -y openssl && \
    rm -rf /var/lib/apt/lists /var/cache/apt/archives

# Copy built application
COPY --from=build /app /app

# Start the server by default, this can be overwritten at runtime
EXPOSE 3000
CMD [ "pnpm", "run", "start" ]
17

I followed the guidance in this post, and by utilizing the fly.toml and Dockerfile shared by @edmbn, I successfully resolved my problem. If anyone else is facing a similar issue, you might find the mentioned post helpful.

P.S. I still need to remove .env in .dockerignore to make it work. I recon there is a better way to handle this?