MySQL - Drizzle ORM 中文文档

PlanetScale

根据其官方网站描述， PlanetScale 是世界上最先进的无服务器 MySQL 平台。

有了 Drizzle ORM，您就可以使用我们的 drizzle-orm/planetscale-serverless 软件包，通过其官方 database-js 驱动程序，在无服务器和有服务器环境中通过 http 访问 PlanetScale。

你还可以通过 mysql2 驱动通过 TCP 连接到 PlanetScale，请参阅 MySQL 2 文档。

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);

⚠️

以前，我们建议使用 database-js 驱动的 connect() 函数。然而，在接收到 PlanetScale 团队和 database-js 的作者的建议后，我们现在强制使用 Client 实例来处理与 PlanetScale 的连接。

请注意，你将收到一个警告，要求从 connect() 切换到 new Client()。从版本 0.30.0 开始，你将遇到错误，并需要使用 Client 连接。确保从 connect() 迁移到 Client。

请务必查看 PlanetScale 官方的 MySQL 课程，我们认为它们非常出色 🙌

mysql2

根据其官方网站描述，mysql2 是一个专注于性能的 Node.js MySQL 客户端。

Drizzle ORM 使用 drizzle-orm/mysql2 包原生支持 mysql2。

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

你可以使用 mysql2 驱动连接到 MySQL，有两种方式，单个 client 连接或者一个 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);

⚙️

对于带有 DDL 迁移的内置 migrate() 函数，我们和驱动程序强烈建议使用单个 client 连接连接到 MySQL。

对于查询目的，请根据业务需求随意使用 client 或 pool。

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 代理

驱动实现示例

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: [] };
  }
});

服务器实现示例

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