Add a SQLite database to your app

Open your project in your IDE.

Navigate to your project 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 will generate this for you once you deploy your app
6
    "migrations_dir": "drizzle" // We'll create this directory later
7
  }
8
]

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

Generate TypeScript types for your binding.

$
wrangler types

This command updates your project’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 your 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 project, create a file named drizzle.config.ts. Configure the schema path and output path:

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 your package.json 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 your local database.
• db:generate: Generates migration files from your schema.

Create the schema directory.

To keep your 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 Import the necessary Drizzle ORM methods use them to define a 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
});

This code defines a usersTable with multiple fields.

By defining your schema in code, you ensure your 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 your schema, you need to 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 your app.

Since you just created a new table, you need to generate a migration file to update the database. In your terminal, run the following commands to generate a new migration file in the drizzle directory from your 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 ensures 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 your app to add new users, create an API route that accepts user details and inserts them into the usersTable. 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
};

What’s happening?

• The route receives a POST request with user data.
• It inserts the data into the usersTable.
• 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
};

What’s happening?

• The route receives a GET request.
• It queries all users from the database.
• 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 your app by running the following command in your terminal:

   $
   npm run dev

2. Add a user by sending a POST request (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

   You should see a list of users in the response.

Deploy your app.

Use the Webflow CLI to trigger a new deployment of your app. Additionaly, you can commit and push your changes to your GitHub repository to trigger a new deployment as well.

$
webflow cloud deploy

View your app and database in Webflow Cloud

1. Open your project’s environment in Webflow Cloud.

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

   

3. Select the DB binding to view your database.

4. You should see the users_table. For now, it will be empty, since 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"}'