Build an app / Next.js + Drizzle ORM

How to connect a Next.js app to SingleStore using Drizzle ORM

On this guide

This guide walks you through the steps to connect a Next.js application to a SingleStore database using Drizzle ORM. You’ll start by setting up a SingleStore deployment and workspace, then create a database and retrieve your connection credentials. Finally, you’ll configure your #Next.js project by installing the drizzle-orm and drizzle-kit packages, loading environment variables, and establishing a connection to handle queries against your SingleStore instance. By the end, you’ll have a working example that you can use in your application.

Prerequisites

SingleStore account
Node.js

Don’t have a SingleStore account yet?

Skip to next section

Create deployment

1. Log in to your SingleStore Portal account.

2. In the left-hand menu, click Create New → Deployment.

3. In the Create Workspace form, follow the on‑screen instructions to complete the form.

4. Click Create Workspace.

5. Wait for the workspace to finish deploying.

Create workspace

Note: If the required workspace already exists in the target deployment, you can skip this step.

1. Log in to your SingleStore Portal account.

2. In the left-hand menu, click Deployments.

3. From the deployments list, select the deployment where you want to create a workspace.

4. In the left‑hand pane, click + Create Workspace.

5. In the Create Workspace form, follow the on‑screen instructions to complete the form.

6. Сlick Create Workspace.

7. Wait for the workspace to finish deploying.

Create database

1. Log in to your SingleStore Portal account.

2. In the left-hand menu, click Deployments.

3. From the deployments list, select the deployment where you want to create a database.

4. In the right‑hand pane, click + Create Database.

5. In the Create Database form, enter a new database name and select the workspace to attach it to.

6. Click Create Database.

Retrieve database credentials

1. Log in to your SingleStore Portal account.

2. In the left-hand menu, click Deployments.

3. From the deployments list, select the deployment that contains your database.

4. From the workspaces list, select the workspace to which your database is attached.

5. In the selected workspace, click Connect.

6. In the Connect dropdown, choose SQL IDE.

7. In the SQL IDE tab, copy the connection parameters.

If you don’t know the password, click Reset Password, then copy the new password.

Prepare environment

1. Create a .env file in the root of your project.

2. In this file, define your connection string by adding a DB_URL variable, for example:

1
DB_URL="singlestore://<USER>:<PASSWORD>@<HOST>:<PORT>/<DATABASE_NAME>"

If you don’t know your connection string, see the Retrieve database credentials section above.

3. Now, in a terminal pointing to the root directory of your project, install the following NPM packages:

npm install drizzle-orm mysql2

npm install -D drizzle-kit

Establish database connection

In the root directory of your project, create a db.ts file with the following content to establish a connection:

1
import { drizzle } from "drizzle-orm/singlestore";
2

3
export const db = drizzle(process.env.DB_URL ?? "");

Create database schema using Drizzle ORM

1. Create a drizzle.config.ts file in the root of your project with the following content:

1
import { defineConfig } from "drizzle-kit";
2

3
export default defineConfig({
4
  out: "./drizzle",
5
  schema: "./schema.ts",
6
  dialect: "singlestore",
7
  dbCredentials: {
8
    url: process.env.DB_URL ?? "",
9
  },
10
});

2. Create a schema.ts file in the root of your project and describe your database schema. For example:

1
import { bigint, singlestoreTable, varchar } from "drizzle-orm/singlestore-core";
2

3
export const usersTable = singlestoreTable("users", {
4
  id: bigint({ mode: "number" }).autoincrement().primaryKey(),
5
  name: varchar({ length: 255 }).notNull(),
6
});

3. Push the schema into the database by running the following command:

npx drizzle-kit push

Once this is done, you should have a database with a table of users ready to use.

Query database

Once you’ve added the above code, let’s test to make sure that the connection is working as anticipated.

Next.js route handlers let you create custom request handlers for a given route using the Web Request and Response APIs. Follow these steps to implement an example app:

1. Create a route.ts file at ./app/api/users with the following content to handle requests for retrieving and creating users:

1
import { usersTable } from "../../../schema";
2
import { db } from "../../../db";
3

4
export async function GET() {
5
  const rows = await db.select().from(usersTable);
6
  return Response.json(rows);
7
}
8

9
export async function POST(request: Request) {
10
  const { name } = await request.json();
11
  const [rows] = await db.insert(usersTable).values({ name });
12
  return Response.json(rows);
13
}

2. Create a route.ts file at ./app/api/users/[id] with the following content to handle requests for retrieving a user by ID, updating a user's name, and deleting a user:

1
import { usersTable } from "../../../../schema";
2
import { db } from "../../../../db";
3
import { eq } from "drizzle-orm";
4

5
export async function GET(request: Request, { params }: { params: Promise<{ id: string }> }) {
6
  const { id } = await params;
7

8
  const rows = await db
9
    .select()
10
    .from(usersTable)
11
    .where(eq(usersTable.id, Number(id)));
12

13
  return Response.json(rows[0]);
14
}
15

16
export async function PUT(request: Request, { params }: { params: Promise<{ id: string }> }) {
17
  const { id } = await params;
18
  const { name } = await request.json();
19

20
  const [rows] = await db
21
    .update(usersTable)
22
    .set({ name })
23
    .where(eq(usersTable.id, Number(id)));
24

25
  return Response.json(rows);
26
}
27

28
export async function DELETE(request: Request, { params }: { params: Promise<{ id: string }> }) {
29
  const { id } = await params;
30
  const [rows] = await db.delete(usersTable).where(eq(usersTable.id, Number(id)));
31
  return Response.json(rows);
32
}

3. Create a <DBTest /> client component in ./components/DBTest.tsx with the following content:

1
"use client";
2

3
import { usersTable } from "@/schema";
4
import { ComponentProps, useCallback, useEffect, useState } from "react";
5

6
type UserRecord = typeof usersTable.$inferSelect;
7

8
export function DBTest() {
9
  const [users, setUsers] = useState<UserRecord[]>([]);
10
  const [nameValue, setNameValue] = useState<UserRecord["name"]>("");
11

12
  const fetchUsers = useCallback(async () => {
13
    const response = await fetch("/api/users");
14
    const data = await response.json();
15
    setUsers(data);
16
  }, []);
17

18
  const createUser = async () => {
19
    await fetch("/api/users", {
20
      method: "POST",
21
      body: JSON.stringify({ name: nameValue }),
22
    });
23

24
    await fetchUsers();
25
  };
26

27
  const updateUser = async (user: UserRecord) => {
28
    const newName = `${user.name.split(" - ")[0]} - ${new Date().toISOString()}`;
29

30
    await fetch(`/api/users/${user.id}`, {
31
      method: "PUT",
32
      body: JSON.stringify({ name: newName }),
33
    });
34

35
    await fetchUsers();
36
  };
37

38
  const deleteUser = async (id: UserRecord["id"]) => {
39
    await fetch(`/api/users/${id}`, { method: "DELETE" });
40
    await fetchUsers();
41
  };
42

43
  const handleFormSubmit: ComponentProps<"form">["onSubmit"] = async (event) => {
44
    event.preventDefault();
45
    await createUser();
46
    setNameValue("");
47
  };
48

49
  useEffect(() => {
50
    fetchUsers();
51
  }, [fetchUsers]);
52

53
  return (
54
    <section>
55
      <h2>Users</h2>
56

57
      <form
58
        className="mt-4"
59
        onSubmit={handleFormSubmit}
60
      >
61
        <input
62
          type="text"
63
          name="name"
64
          placeholder="e.g. John Doe"
65
          className="bg-black border px-2 p-1"
66
          value={nameValue}
67
          onChange={(event) => setNameValue(event.target.value)}
68
        />
69
        <button
70
          type="submit"
71
          className="border px-2 ml-2 py-1"
72
        >
73
          Add
74
        </button>
75
      </form>
76

77
      <ul className="border p-4 mt-4 flex flex-col gap-2">
78
        {users.map((user) => (
79
          <li key={user.id}>
80
            <p>
81
              {user.id} - {user.name}
82
            </p>
83
            <button onClick={() => updateUser(user)}>Update</button>
84
            <button
85
              onClick={() => deleteUser(user.id)}
86
              className="ml-2"
87
            >
88
              Delete
89
            </button>
90
          </li>
91
        ))}
92
      </ul>
93
    </section>
94
  );
95
}

4. Add the following code at ./app/page.tsx:

1
import { DBTest } from "../components/DBTest";
2

3
export default function Home() {
4
  return (
5
    <main className="p-4">
6
      <DBTest />
7
    </main>
8
  );
9
}