Build an app / Next.js + HTTP

How to connect a Next.js app to SingleStore via HTTP

On this guide

In this guide, you’ll learn how to connect a Next.js application to SingleStore using its HTTP Data API. We’ll walk you through creating a deployment, workspace, and database in the SingleStore Portal, retrieving your connection credentials, and configuring your Next.js project with environment variables. Finally, you’ll implement a reusable helper function to execute and query SQL statements over HTTP, giving you a solid foundation for building applications on SingleStore.

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 details by adding the following variables:

1
DB_USER="<USER>"
2
DB_PASSWORD="<PASSWORD>"
3
DB_HOST="<HOST>"
4
DB_PORT="<PORT>"
5
DB_NAME="<DATABASE_NAME>"

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

3. Then, you’ll add helper functions to query your database at ./db.js:

1
import { Buffer } from "buffer";
2

3
const { DB_USER, DB_PASSWORD, DB_HOST, DB_NAME } = process.env ?? {};
4
const CREDENTIALS = Buffer.from(`${DB_USER}:${DB_PASSWORD}`).toString("base64");
5

6
export async function request(url, body) {
7
  const response = await fetch(`https://${DB_HOST}/api/v2${url}`, {
8
    body,
9
    method: "POST",
10
    headers: {
11
      "Content-Type": "application/json",
12
      Authorization: `Basic ${CREDENTIALS}`,
13
    },
14
  });
15

16
  if (!response.ok) {
17
    const text = await response.text();
18
    throw new Error(`${response.status} ${response.statusText}\n${text}`);
19
  }
20

21
  return response.json();
22
}
23

24
export async function query(sql, database = DB_NAME) {
25
  return request("/query/rows", JSON.stringify({ sql, database }));
26
}
27

28
export async function execute(sql, database = DB_NAME) {
29
  return request("/exec", JSON.stringify({ sql, database }));
30
}

execute (/api/v2/exec)Executes SQL statements without returning result sets, typically used for DDL and DML operations (e.g., CREATE TABLE, INSERT, UPDATE, DELETE).

query (/api/v2/query/rows)Executes SQL statements and returns result sets typically used for SELECT queries. Each row in the result set is represented as an object mapping column names to their corresponding values.

Create database schema

1. Create a db-init.js file in the root of your project with the following script for creating a database schema:

1
import { execute } from "./db";
2

3
async function main() {
4
  try {
5
    console.log("Create users table");
6

7
    const createTableSQL = `
8
      CREATE TABLE IF NOT EXISTS users (
9
        id BIGINT AUTO_INCREMENT PRIMARY KEY,
10
        name VARCHAR(255) NOT NULL
11
      )
12
    `;
13

14
    await execute(createTableSQL);
15
  } catch (error) {
16
    console.error(error);
17
  }
18
}
19

20
main();

2. Execute the script by running the following command in your terminal:

npx dotenv-cli -- npx tsx ./db-setup.js

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.js file at ./app/api/users with the following content to handle requests for retrieving and creating users:

1
import { query, execute } from "../../../db";
2

3
export async function GET() {
4
  const sql = "SELECT id, name FROM users";
5
  const rows = (await query(sql)).results[0].rows ?? [];
6
  return Response.json(rows);
7
}
8

9
export async function POST(request) {
10
  const { name } = await request.json();
11
  const sql = `INSERT INTO users (name) VALUES ('${name}')`;
12
  const result = await execute(sql);
13
  return Response.json(result);
14
}

2. Create a route.js 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 { query, execute } from "../../../../db";
2

3
export async function GET(request, { params }) {
4
  const { id } = await params;
5
  const sql = `SELECT id, name FROM users WHERE id = ${id}`;
6
  const result = (await query(sql)).results[0].rows[0];
7
  return Response.json(result);
8
}
9

10
export async function PUT(request, { params }) {
11
  const { id } = await params;
12
  const { name } = await request.json();
13
  const sql = `UPDATE users SET name = '${name}' WHERE id = ${id}`;
14
  const result = await execute(sql);
15
  return Response.json(result);
16
}
17

18
export async function DELETE(request, { params }) {
19
  const { id } = await params;
20
  const sql = `DELETE FROM users WHERE id = ${id}`;
21
  const result = await execute(sql);
22
  return Response.json(result);
23
}

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

1
"use client";
2

3
import { useCallback, useEffect, useState } from "react";
4

5
export function DBTest() {
6
  const [users, setUsers] = useState([]);
7
  const [nameValue, setNameValue] = useState("");
8

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

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

21
    await fetchUsers();
22
  };
23

24
  const updateUser = async (user) => {
25
    const newName = `${user.name.split(" - ")[0]} - ${new Date().toISOString()}`;
26

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

32
    await fetchUsers();
33
  };
34

35
  const deleteUser = async (id) => {
36
    await fetch(`/api/users/${id}`, { method: "DELETE" });
37
    await fetchUsers();
38
  };
39

40
  const handleFormSubmit = async (event) => {
41
    event.preventDefault();
42
    await createUser();
43
    setNameValue("");
44
  };
45

46
  useEffect(() => {
47
    fetchUsers();
48
  }, [fetchUsers]);
49

50
  return (
51
    <section>
52
      <h2>Users</h2>
53

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

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

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

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
}