Drizzle ORM - MySQL

With Drizzle ORM you can access PlanetScale over http through their official database-js driver from serverless and serverfull environments with our drizzle-orm/planetscale-serverless package.

You can also access PlanetScale through TCP with mysql2 driver — see here.

npm

yarn

pnpm

bun

npm i drizzle-orm @planetscale/database
npm i -D drizzle-kit

yarn add drizzle-orm @planetscale/database
yarn add -D drizzle-kit

pnpm add drizzle-orm @planetscale/database
pnpm add -D drizzle-kit

bun add drizzle-orm @planetscale/database
bun add -D drizzle-kit

import { drizzle } from "drizzle-orm/planetscale-serverless";
import { Client } from "@planetscale/database";

const client = new Client({
  host: process.env["DATABASE_HOST"],
  username: process.env["DATABASE_USERNAME"],
  password: process.env["DATABASE_PASSWORD"],
});

const db = drizzle(client);

⚠️

Previously, we were suggesting the use of the connect() function from the database-js driver. However, after receiving a suggestion from the PlanetScale Team and the authors of database-js, we are now enforcing the use of the Client instance to handle connections to PlanetScale.

Please note that you will receive a warning to switch from connect() to new Client(), and starting from version 0.30.0, you will encounter an error and be required to use the Client connection. Make sure to migrate from connect() to Client

Make sure to checkout the PlanetScale official MySQL courses, we think they’re outstanding 🙌

mysql2

According to the official website, mysql2 is a MySQL client for Node.js with focus on performance.

Drizzle ORM natively supports mysql2 with drizzle-orm/mysql2 package.

npm

yarn

pnpm

bun

npm i drizzle-orm mysql2
npm i -D drizzle-kit

yarn add drizzle-orm mysql2
yarn add -D drizzle-kit

pnpm add drizzle-orm mysql2
pnpm add -D drizzle-kit

bun add drizzle-orm mysql2
bun add -D drizzle-kit

There’re two ways you can connect to the MySQL with mysql2 driver, either single client connection or a pool.

Client connection

Pool connection

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "host",
  user: "user",
  database: "database",
  ...
});

const db = drizzle(connection);

import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";

const poolConnection = mysql.createPool({
  host: "host",
  user: "user",
  database: "database",
  ...
});

const db = drizzle(poolConnection);

⚙️

For the built in migrate function with DDL migrations we and drivers strongly encourage you to use single client connection.

For querying purposes feel free to use either client or pool based on your business demands.

TiDB Serverless

According to the official website, TiDB Serverless is a fully-managed, autonomous DBaaS with split-second cluster provisioning and consumption-based pricing.

ℹ️

TiDB Serverless is compatible with MySQL, so you can use drizzle-orm/mysql2 to connect to it.

TiDB Serverless provides an HTTP driver for edge environments. It is natively supported by Drizzle ORM via drizzle-orm/tidb-serverless package.

npm

yarn

pnpm

bun

npm i drizzle-orm @tidbcloud/serverless
npm i -D drizzle-kit

yarn add drizzle-orm @tidbcloud/serverless
yarn add -D drizzle-kit

pnpm add drizzle-orm @tidbcloud/serverless
pnpm add -D drizzle-kit

bun add drizzle-orm @tidbcloud/serverless
bun add -D drizzle-kit

import { connect } from '@tidbcloud/serverless';
import { drizzle } from 'drizzle-orm/tidb-serverless';

const client = connect({ url: '...' });
const db = drizzle(client);

HTTP proxy

Example of driver implementation

import { drizzle } from 'drizzle-orm/mysql-proxy';

const db = drizzle(async (sql, params, method) => {
  try {
    const rows = await axios.post('http://localhost:3000/query', { sql, params, method });

    return { rows: rows.data };
  } catch (e: any) {
    console.error('Error from mysql proxy server: ', e.response.data)
    return { rows: [] };
  }
});

Example of server implementation

import * as mysql from 'mysql2/promise';
import express from 'express';

const app = express();

app.use(express.json());
const port = 3000;

const main = async () => {
    const connection = await mysql.createConnection('mysql://root:mysql@127.0.0.1:5432/drizzle');

    app.post('/query', async (req, res) => {
      const { sql, params, method } = req.body;

      // prevent multiple queries
      const sqlBody = sql.replace(/;/g, '');

      try {
            const result = await connection.query({
                sql: sqlBody,
                values: params,
                rowsAsArray: method === 'all',
                typeCast: function(field: any, next: any) {
                    if (field.type === 'TIMESTAMP' || field.type === 'DATETIME' || field.type === 'DATE') {
                        return field.string();
                    }
                    return next();
                },
            });
      } catch (e: any) {
        res.status(500).json({ error: e });
      }

      if (method === 'all') {
        res.send(result[0]);
      } else if (method === 'execute') {
        res.send(result);
      }
      res.status(500).json({ error: 'Unknown method value' });
    });

    app.listen(port, () => {
      console.log(`Example app listening on port ${port}`);
    });
}

main();

Become a Gold Sponsor