Connect Your Serverless Functions to a PlanetScale MySQL DB

PlanetScale is a serverless MySQL platform that comes with modern features such as data branching, a serverless connection driver, multi-region support, a sleek CLI, and much more.

In particular, PlanetScale's Serverless Driver for JavaScript makes it a great fit to use with the endpts Serverless Functions.

In this article, we will build a simple API to list users stored in our PlanetScale DB to illustrate how to connect to it and query it from your endpts Functions.

Creating a PlanetScale database

If you don't already have an account with PlanetScale, head over to the PlanetScale Dashboard to create one.

Once you're in, create a new Database giving it a name and select a region:

In a couple of minutes, the database will be initialized. Let's head over to the console and connect to our database to create a table we can query:

Now let's create a users table:

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `email` varchar(255) NOT NULL,
  `name` varchar(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

and add a few records to it:

INSERT INTO users (name, email)
VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com'),
  ('Dave', 'dave@example.com'),
  ('Eve', 'eve@example.com');

The final step is to grab our connection string. From the Dashboard, click the Connect button:

In the Connect with dropdown, select @planetscale/database and copy your connection details as we will need them later when connecting to the database from our serverless functions:

Creating our serverless functions

Now that we have our database set up and the connection details handy, let's go ahead and create our API route that will query the users table.

Let's bootstrap an endpts project locally:

npm create endpts@latest planetscale-db

Change into the project directory and install the PlanetScale Serverless Driver:

cd planetscale-db/
npm install @planetscale/database

In the routes/ directory, we will create a new route that will list the users:

// routes/get_users.ts
import { connect } from '@planetscale/database'
import type { Route } from '@endpts/types'

const db = await connect({
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD,
})

export default {
  method: 'GET',
  path: '/users',
  async handler(req) {
    const { rows } = await db.execute('SELECT * FROM users')

    return Response.json(rows)
  },
} satisfies Route

Next, let's create a .env file in in the root of our project directory and paste the database connection details we got from the PlanetScale Dashboard. It should look like so:

Let's test our changes locally by firing up the development server:

npm run dev

and making a request to our API route:

curl -s http://localhost:3000/users

Deploying our API to endpts

The final step is to deploy our API to endpts. Head over to the Dashboard and create a new project.

You can connect a GitHub repository which will allow endpts to automatically deploy your API whenever you push changes to your repository as well as generating unique preview deployments for every pull request and branch:

Alternatively, you can deploy your project manually by giving your project a name and pasting the link to a Git repository (https://github.com/endpts-samples/planetscale-db), Gist, or tarball:

To allow the endpts deployments to connect to the database, you will need to add the PlanetScale DB connection details as environment variables.

This can be done by navigating to the project's Settings tab and selecting the Environment Variables menu item:

Once you've added the environment variables, you will need to redeploy the project to pick up the changes. In a couple of minutes, the deployment will be Ready and you can grab the deployment URL from the Dashboard:

Finally, we can test out our deployed API: