With Drizzle ORM you can access PlanetScale over http through their official database-js driver from serverless and serverfull environments with the drizzle-orm/planetscale-serverless package
You can also access PlanetScale through TCP with mysql2 driver.
Official docs here: https://orm.drizzle.team/docs/quick-mysql/planetscale
npm i drizzle-orm @planetscale/database
npm i -D drizzle-kit
Prototyping with db push & introspecting with db pull
In order to create tables and make changes to our database schema we use 'drizzle-kit'. The `drizzle-kit push` command in Drizzle allows you to rapidly prototype and alter your database schema directly, which is handy during local development or when using external providers that manage migrations. On the other hand, `drizzle-kit introspect` pulls the DDL from an existing database and generates a Drizzle TypeScript schema file, which helps in obtaining a schema from an existing database swiftly. Both commands serve to synchronize your database schema with your code, but in opposite directions: `push` applies your local schema changes to the database, while `introspect` reflects the database schema in your local code.
Add the scripts to your 'package.json' so you can run the with 'npm run introspect' and 'npm run push':
"scripts": {
...
"introspect": "drizzle-kit introspect:mysql",
"push": "drizzle-kit push:mysql"
}
In Drizzle, you don't write the SQL file manually for the `db push` operation. Instead, you define your schema using Drizzle ORM's TypeScript schema, and then the `drizzle-kit push` command generates and executes the necessary SQL statements to update your database schema based on the TypeScript schema definitions. The db push command in Drizzle takes your TypeScript schema and generates the necessary SQL statements to create or update your database schema to match the TypeScript schema.
With introspection (e.g., drizzle-kit introspect), Drizzle retrieves the schema from your existing database, which is defined using SQL, and generates a corresponding TypeScript schema automatically.
If you prefer writing SQL, introspection might be more comfortable, allowing you to generate a TypeScript schema from an existing database. On the other hand, if you prefer working with TypeScript, db push allows you to define your schema in TypeScript and have Drizzle generate and execute the necessary SQL. Choosing one over the other depends on your workflow preferences, the existing state of your project, and whether you are starting from an existing database or building a new one from scratch.
Config
Create a config file called 'drizzle.config.ts' and place it in the root of your project. Read the official docs on the config file here: https://orm.drizzle.team/kit-docs/conf. For the 'dbCredentials' use the Node.js connection string given to you on planetscale.
import type { Config } from "drizzle-kit";
import dotenv from "dotenv";
dotenv.config({ path: ".env" });
export default {
out: "./src/db/migrations",
schema: "./src/db/schema.ts",
driver: "mysql2",
breakpoints: true,
dbCredentials: { connectionString: process.env.DATABASE_URL || "" },
} satisfies Config;
Connecting to the db
Create a 'db' directory in the 'src' folder and create a file named 'db.ts'
In this file, we will export a function called 'getDb' and return our database connection so that we can use it in our other files. Types from the @builder.io/qwik-city package are being imported and utilized. Specifically, RequestEventAction and RequestEventLoader are imported, and their respective nested types env and fail are aliased as Env and Fail. These types are then used to type the parameters of the getDb function which sets up a connection to a PlanetScale database using the drizzle-orm/planetscale-serverless and @planetscale/database packages, utilizing an imported schema.
//src/db/db.ts
import { drizzle } from "drizzle-orm/planetscale-serverless";
import { connect } from "@planetscale/database";
import type {
RequestEventAction,
RequestEventLoader,
} from "@builder.io/qwik-city";
import * as schema from "./schema";
type Fail = RequestEventLoader["fail"];
type Env = RequestEventAction["env"];
export const getDb = ({ env, fail }: { env: Env; fail?: Fail }) => {
// create the connection
const connection = connect({
host: env.get("DATABASE_HOST"),
username: env.get("DATABASE_USERNAME"),
password: env.get("DATABASE_PASSWORD"),
});
return drizzle(connection, { schema });
};
We can then import the 'getDb' function and make queries. In this snippet for a Qwik city app, an API for user queries is being created. It exports a `usersApi` function that takes a `RequestEventLoader` or `RequestEventBase` object, and returns an object containing a query method `getByUsername`. This method, when called with an `email` parameter, interacts with a database to find a user with that email. If found, it returns the user data; if not, it returns a 404 error. In case of any other error, it returns a 500 error. The `getDb` function is imported to establish a database connection, and zod (`z`) is used for schema validation of the `email` parameter.
import {
type RequestEventBase,
type RequestEventLoader,
z,
} from "@builder.io/qwik-city";
import { getDb } from "../db/db";
import { users } from "../db/schema";
const getByIdParams = z.object({
email: z.string(),
});
type GetByIdParams = z.infer<typeof getByIdParams>;
export const usersApi = ({ env }: RequestEventLoader | RequestEventBase) => {
return {
query: {
getByUsername: async ({ email }: GetByIdParams) => {
try {
const db = getDb({ env });
const user = await db.query.users.findFirst({
where: eq(users.email, email.toLowerCase()),
});
if (!user)
return { code: 404, message: "User not found", data: null };
return { code: 200, message: "success", data: { user } };
} catch (error) {
let message = "Oops, something went wrong. Please try again later.";
if (error instanceof Error) message = error.message;
return { code: 500, message, data: null };
}
},
},
};
