Add a SQLite database

Open the app in your IDE.

Navigate to the app’s source code in your IDE and open the wrangler.json file.

Add a SQLite database binding to the d1_databases array in wrangler.json.

Add the d1_databases array to the wrangler.json file, and declare a new database binding.

1
"d1_databases": [
2
  {
3
    "binding": "DB", // A valid JavaScript variable name
4
    "database_name": "db",
5
    "database_id": "1234", // Webflow Cloud generates this when you deploy the app
6
    "migrations_dir": "drizzle" // You'll create this directory later
7
  }
8
]

This configuration tells Webflow Cloud to create a SQLite database for your app and to apply any migrations found in the drizzle directory.

Generate TypeScript types for your binding.

$
wrangler types

This command updates the app’s type definitions so you can safely access the database in your code.

Install Drizzle ORM and Drizzle Kit.

Install the required packages for working with Drizzle ORM and managing migrations:

$
npm i drizzle-orm
$
npm i -D drizzle-kit tsx better-sqlite3

• drizzle-orm: The ORM for interacting with the database.
• drizzle-kit: CLI for migrations and schema generation.
• tsx and better-sqlite3: Required for local development and SQLite support.

Create the Drizzle config file.

In the root of your app, create a file named drizzle.config.ts. Configure the schema path and output path as in this example:

1
import { defineConfig } from "drizzle-kit";
2
3
export default defineConfig({
4
    schema: "./src/db/schema/index.ts",
5
    out: "./drizzle",
6
    dialect: "sqlite",
7
});

Add local migration scripts to your package.json.

Add the following scripts to the package.json file to manage local migrations:

1
"scripts": {
2
  // ... existing scripts ...
3
  "db:generate": "drizzle-kit generate",
4
  "db:apply:local": "wrangler d1 migrations apply DB --local",
5
}

• db:apply:local: Applies migrations to the local database.
• db:generate: Generates migration files from the schema.

Create the schema directory.

To keep the schema organized, create a db/schema folder inside your src directory to hold all your schema files.

$
cd src
$
mkdir -p db/schema

Define your first table.

Create an index.ts file in src/db/schema and import the necessary Drizzle ORM methods and use them to define a table of users named usersTable:

1
// Import the necessary Drizzle ORM functions
2
import { sqliteTable, text, int } from "drizzle-orm/sqlite-core";
3
4
// Define the users table with the following fields:
5
// - id: The primary key of the table
6
// - name: The name of the user
7
// - age: The age of the user
8
// - email: The email of the user
9
10
export const usersTable = sqliteTable("users_table", {
11
    id: int().primaryKey({ autoIncrement: true }),
12
    name: text().notNull(),
13
    age: int().notNull(),
14
    email: text().notNull().unique(),
15
});

By defining the schema in code, you ensure that the database structure is always documented and version-controlled. To learn more about defining schemas with Drizzle ORM, see the Drizzle ORM documentation.

Initialize the database.

Whenever you change the schema, you must generate a migration. A migration is a set of instructions for updating the database structure. Migrations let you evolve your database structure safely over time, without losing data or breaking the app.

Because you just created a new table, you must generate a migration file to update the database. In the terminal, run the following commands to generate a new migration file in the drizzle directory from the schema defined in src/db/schema/index.ts and apply it to the local database:

$
npm run db:generate
$
npm run db:apply:local

Create a helper function to get the database instance.

To avoid repeating connection logic, create a reusable helper that returns a Drizzle ORM instance. This instance ensures that you always use the correct schema and environment, and improves performance by reusing connections.

In src/db, create a getDb.ts file and add the following code:

1
import { drizzle } from "drizzle-orm/d1";
2
import * as schema from "./schema";
3
4
// For dynamic routes
5
export const getDb = (locals: App.Locals) => {
6
  const { env } = locals.runtime;
7
  return drizzle(env.DB, { schema });
8
};
9
10
// For static routes
11
export const getDbAsync = async (locals: App.Locals) => {
12
  const { env } = locals.runtime;
13
  return drizzle(env.DB, { schema });
14
};

Create a route to add users to the users table.

To allow the app to add new users, create an API route that accepts user details and inserts them into the usersTable table. This pattern is common for building RESTful APIs and helps keep your data layer organized.

1
import { APIRoute } from "astro";
2
import { getDb } from "../../db/getDb";
3
import { usersTable } from "../../db/schema";
4
5
// Define the expected shape of the request body
6
type UserInput = {
7
  name: string;
8
  age: number;
9
  email: string;
10
};
11
12
// Define CORS headers
13
const corsHeaders = {
14
  "Access-Control-Allow-Origin": "*", // Or specify your domain instead of *
15
  "Access-Control-Allow-Methods": "GET,POST,OPTIONS",
16
  "Access-Control-Allow-Headers": "Content-Type",
17
};
18
19
// Handle OPTIONS requests for CORS preflight
20
export const OPTIONS: APIRoute = async ({ request }) => {
21
  return new Response(null, {
22
    headers: corsHeaders,
23
  });
24
};
25
26
// Handle POST requests to add a new user
27
export const POST: APIRoute = async ({ request, locals }) => {
28
  const db = getDb(locals); // Get the database instance
29
  const { name, age, email }: UserInput = await request.json();
30
  try {
31
    // Insert the new user into the usersTable
32
    const newUser = await db
33
      .insert(usersTable)
34
      .values({ name, age, email })
35
      .returning();
36
    // Return the created user
37
    return Response.json(newUser[0], { status: 201 });
38
  } catch (error) {
39
    // Handle errors (e.g., duplicate email)
40
    return Response.json({ error: "Failed to create user" }, { status: 500 });
41
  }
42
};

This code runs these main steps:

1. The route receives a POST request with user data.
2. It inserts the data into the table.
3. On success, it returns the new user; on error, it returns a failure message.

Create a route to get users from the users table.

To retrieve data, create a GET route that returns all users from the database:

1
export const GET: APIRoute = async ({ locals }) => {
2
  const db = getDb(locals);
3
  try {
4
    const users = await db.select().from(usersTable);
5
    return Response.json(users, { status: 200 });
6
  } catch (error) {
7
    return Response.json({ error: "Failed to fetch users" }, { status: 500 });
8
  }
9
};

This code runs these main steps:

1. The route receives a GET request.
2. It queries all users from the database.
3. It returns the list of users or an error message.

Test your routes.

Test your API routes to make sure you can add and retrieve users from your database.

1. Start the app by running the following command in your terminal:

   $
   npm run dev

2. In a different terminal window, add a user by sending a POST request as in the following example. Replace <YOUR_PORT> with your actual port number, for example, 4321:

   $
   curl -X POST http://localhost:<YOUR_PORT>/<YOUR_APP_MOUNT_PATH>/api/users \
   >
     -H "Content-Type: application/json" \
   >
     -d '{"name": "Arthur Dent", "age": 33, "email": "arthur.dent@bbc.co.uk"}'

   A successful response returns the new user as JSON, for example:

   1
   {
   2
     "id": 1,
   3
     "name": "Arthur Dent",
   4
     "age": 33,
   5
     "email": "arthur.dent@bbc.co.uk"
   6
   }

   Try adding more users with different data to populate your database.

3. Retrieve all users by sending a GET request:

   $
   curl http://localhost:<YOUR_PORT>/<YOUR_APP_MOUNT_PATH>/api/users

   The response should include a list of users.

Deploy the app.

Use the Webflow CLI to trigger a new deployment of the app manually or commit and push your changes to your GitHub repository to trigger a new deployment:

$
webflow cloud deploy

View the app and database in Webflow Cloud.

1. Open the app’s environment in Webflow Cloud.

2. After the deployment completes, click the “Storage” tab in the environment.

   

3. Select the DB binding to view the database.

4. You should see the users_table. For now, it is empty, because all the previous changes were local. In the “Storage” tab, you can add and update users through the UI.

   

$
curl -X POST https://<your-app-url>/<your-app-mount-path>/api/users \
>
  -H "Content-Type: application/json" \
>
  -d '{"name": "Arthur Dent", "age": 33, "email": "arthur.dent@bbc.co.uk"}'