nextjs初探之使用drizzle-orm连接mysql

使用mysql2直接连接数据库
改为通过drizzle-orm来连接
drizzle-orm文档
大家可以翻文档了解下，这里引用里面的概述：
使用 Drizzle，您可以在 TypeScript 中定义和管理数据库模式，以类似 SQL 或关系的方式访问您的数据，并利用选择加入工具来推动您的开发人员体验。

添加dizz.config.ts

import type { Config } from 'drizzle-kit';

export default {
  schema: './db/schema.ts',
  out: './db/migrations',
  driver: 'mysql2',
  dbCredentials: {
    host: '127.0.0.1',
    port: 3306,
    user: 'root',
    password: '123456',
    database: 'knowledge',
  },
} satisfies Config;

drizzle-kit更新后的配置文件修改

export default {
  schema: './db/schema.ts',
  out: './db/migrations',
  dialect: "mysql",
  dbCredentials: {
    host: process.env.MYSQL_HOST,
    port: Number(process.env.MYSQL_POST),
    user: process.env.MYSQL_USER,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE,
  },
} satisfies Config;

更多的文档：orm.drizzle.team/docs/get-st…

但是现在在推送schema的时候提示校验信息不过，github.com/drizzle-tea…

暂时也没找到解决办法，所以还是用 "drizzle-kit": "0.20.18", "drizzle-orm": "0.30.10"的版本

连接数据库及schema

db/index.ts

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

// 创建数据库连接池
const poolConnection = mysql.createPool({
  host: '127.0.0.1',
  port: 3306,
  user: 'root',
  password: '123456',
  database: 'knowledge',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

export const db = drizzle(poolConnection);

db/schema.ts

import { index, int, mysqlTable, text, varchar } from 'drizzle-orm/mysql-core';

export const employees = mysqlTable(
  'employees',
  {
    id: varchar('id', { length: 255 }).primaryKey().notNull(),
    name: text('name'),
    salary: int('salary'),
  },
  (employees) => ({
    idIndex: index('employees__id__idx').on(employees.id),
  })
);

编写查询语句

lib/employees.ts

import { eq } from 'drizzle-orm';
import { db } from '~/db';
import { employees } from '~/db/schema';

export const getAll = async () => {
  return await db
    .select({
      id: employees.id,
      name: employees.name,
      salary: employees.salary,
    })
    .from(employees);
};

export const getOne = async (id: string) => {
  const [currentUser] = await db
    .select({
      id: employees.id,
      name: employees.name,
      salary: employees.salary,
    })
    .from(employees)
    .where(eq(employees.id, id));

  return currentUser;
};

在页面里的使用

app/employees/page.tsx(获取全部的)

import Link from 'next/link';
import { getAll } from '~lib/employees';

export default async function Page() {
  const req = await getAll();
  return (
    <>
      {req?.map((item) => {
        return (
          <Link key={item?.id} href={`/employees/${item?.id}`}>
            {item?.name}
          </Link>
        );
      })}
    </>
  );
}

app/employees/[id]/page.tsx（获取单条的数据）

import { getOne } from '~lib/employees';

export default async function Page({ params }: { params: { id: string } }) {
  const req = await getOne(params.id);

  return (
    <>
      <p>{req?.name}</p>
      <p>{req?.salary}</p>
    </>
  );
}