仓库源文，站点原文

Run MySQL locally and query it with Express

This is a text version of Tejas Kumar's video, "How to run MySQL locally and query it with Express".

# allowing MySQL to start without a root password
docker run -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 3306:3306 mysql:latest
# docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

docker ps
# start an interactive bash shell session in the running Docker container
docker exec -it ba76bef03590 bash -l

# If you want to run PostgreSQL on Docker
# https://masteringpostgres.com/articles/how-to-install-postgresql
docker run --name my-postgres-name -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

[root@ba76bef03590 /]# mysql

mysql> SHOW DATABASES;
mysql> CREATE DATABASE todos;
mysql> USE todos;

mysql> CREATE TABLE todos (
  id INT NOT NULL AUTO_INCREMENT,
  label TEXT NOT NULL,
  is_done BOOLEAN DEFAULT FALSE,
  PRIMARY KEY (id)
);

mysql> SHOW TABLES;

mysql> INSERT INTO todos (label) VALUES ('Walk the dog');
mysql> INSERT INTO todos (label) VALUES ('Wash the car');

mysql> SELECT * FROM todos;

mkdir hello-prisma
cd hello-prisma

npm init -y
npm install prisma
npx prisma init

// Set the DATABASE_URL in the `.env` file to point to your existing database.
DATABASE_URL="mysql://root:@localhost:3306/todos"

// Set the provider of the datasource block in `prisma/schema.prisma` to match your database.
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

# Turn your database schema into a Prisma schema.
npx prisma db pull

# Generate Prisma Client. You can then start querying your database.
npx prisma generate

import express from "express";
import { PrismaClient } from "@prisma/client";

const app = express();
const client = new PrismaClient();

app.use(express.json());

app.get("/todos", async (req, res) => {
  const todos = await client.todos.findMany();
  res.json(todos);
});

app.get("/todos/:id", async (req, res) => {
  const { id } = req.params;
  const todo = await client.todos.findUnique({
    where: { id: parseInt(id) },
  });
  if (todo) {
    res.json(todo);
  } else {
    res.status(404).json({ error: "Todo not found" });
  }
});

app.post("/todos", async (req, res) => {
  const { label } = req.body;
  const newTodo = await client.todos.create({
    data: { label },
  });
  res.status(201).json(newTodo);
});

app.listen(3000, () => {
  console.log("Server started on http://localhost:3000");
});

Use Prisma in Next.js

Next.js Server(server components, server actions, API route) <--> ORM (Prisma) <--> Database

Running npx prisma init --datasource-provider sqlite creates a new prisma directory with a schema.prisma file. You're now ready to model your data.

# This is your Prisma schema file

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  updatedAt DateTime @updatedAt
  createdAt DateTime @default(now())
}

1. one-to-many relationship

model User {
  id     Int     @id @default(autoincrement())
  posts  Post[]
}

model Post {
  id        Int    @id @default(autoincrement())
  author    User   @relation(fields: [authorId], references: [id])
  authorId  Int
}

"author" will not become a column in the database. The way to read this is that "authorId" field references the "id" field on the User model.

2. many-to-many relationship

model User {
  id     Int     @id @default(autoincrement())
  posts  Post[]
}

model Post {
  id       Int    @id @default(autoincrement())
  authors  User[]
}

3. one-to-one relationship
model User {
  id     Int      @id @default(autoincrement())
  post   Post?
}

model Post {
  id        Int   @id @default(autoincrement())
  author    User  @relation(fields: [authorId], references: [id])
  authorId  Int   @unique
}

Prisma ORM is not your database. Running npx prisma db push first time will create SQLite database dev.db that in sync with your schema. npx prisma studio shows you a UI what's in the database, and you can manually add a record there.

Now that we have a database with some initial data, we can set up Prisma Client and connect it to our database. For Next.js integration, add a lib/prisma.ts file which creates a Prisma Client (@prisma/client) and attaches it to the global object.

import prisma from '@/lib/prisma'

export default async function Home() {
  const posts = await prisma.post.findMany();
  return (
    <ul>
      {posts.map((post) => (
        <li key={post.id}>{post.title}</li>
      ))}
    </ul>
  );
}

// more CRUD
const post = await prisma.post.findUnique({
  where: {
    id: params.id
  }
});

const posts = await prisma.post.findMany({
  where: {
    published: true,
    title: {
      contains: "First"
    }
  },
  orderBy: {
    createdAt: "desc"
  },
  select: {
    id: true,
    title: true,
  },
  // offset pagination (e.g. get page 2, each page has 10 posts)
  take: 10,
  skip: 10,
});

const user = await prisma.user.findUnique({
  where: {
    email: "test@gmail.com"
  },
  include: {
    posts: true
  }
})

export async function createPost(formData: FormData) {
  await prisma.post.create({
    data: {
      title: formData.get("title") as string,
      content: formData.get("content") as string,
      author: {
        connect: {
          email: "test@gmail.com"
        }
      }
    }
  });
  // rerender the view
  revalidatePath("/posts");
}

await prisma.post.update({
  where: { id },
  data: {
    title: formData.get("title") as string,
  }
})

await prisma.post.delete({
  where: { id },
})