How to Choose and Use Database

After setting up and learning how to use frontend UI components, our next step is to dive into NextDevKit's backend logic to quickly build your business models.

From this step forward, you'll learn NextDevKit's architecture and modules in detail, and understand how to use existing module code to quickly build your business models.

Choosing a Database

Databases are essential for most projects. Whether it's user login/registration, payment logic, or your future business model needs, you need a database to store users, orders, and business data.

You can choose your database based on your business needs. The most popular mainstream relational databases are PostgreSQL and MySQL.

I personally recommend PostgreSQL because it has a richer ecosystem, more active community, better documentation, and more powerful features. Whatever needs you have in the future, PostgreSQL can usually meet them.

While MySQL has been a stable enterprise database, PostgreSQL is gradually replacing MySQL as its ecosystem becomes richer and more powerful.

For example, most AI businesses today need RAG implementation, which depends on vector databases. PostgreSQL has vector plugins that provide vector database functionality, while other databases may not. PostgreSQL also has plugins for other features like Geo, time series, full-text search, and more.

Besides this, you can also choose based on your business needs. Apart from specialized database categories like search and time series, you can also choose lightweight databases like SQLite.

SQLite can meet most lightweight application needs, mainly for read-intensive applications. SQLite has excellent read performance and low memory usage, can be deployed on edge servers closer to users, and is relatively cheap.

Default Databases in NextDevKit

Different NextDevKit templates integrate different databases by default. For example, the Next.js template integrates PostgreSQL by default, while the Cloudflare Workers template integrates Cloudflare D1 database. The SST AWS template integrates AWS RDS + Proxy + PostgreSQL database.

These default integrations are based on each template's characteristics. You can also choose replacements based on your business needs, which is very simple in NextDevKit. We'll explain how to replace databases later.

For cloud platform choices for different databases, I have these recommendations:

• Self-hosted databases: You can use Dokploy to set up your database for easy backup, migration, and monitoring.
• PostgreSQL: Neon or Supabase are both good choices.
• MySQL: PlanetScale is the mainstream choice.
• SQLite: Cloudflare D1 and Turso are both cost-effective solutions.
• Cloud platform databases: AWS, Azure, Google Cloud SQL are safe choices. Railway and Flyio are more cost-effective options.

What is ORM

After choosing a database, we need to select an ORM to operate the database.

ORM stands for Object-Relational Mapping. It's a programming technique that maps database data to objects, allowing developers to operate database data like objects. ORMs also make it easy to migrate and rollback database table structures.

Popular ORM choices include Prisma, Drizzle, and TypeORM.

NextDevKit integrates Drizzle ORM by default. Drizzle is a very popular ORM that supports multiple databases and has good ecosystem support.

The main reasons for choosing Drizzle are:

• Supports multiple databases including PostgreSQL, MySQL, SQLite, and can switch easily
• TypeScript friendly, supports libraries like Zod
• Serverless friendly, supports Edge with excellent performance

The last point about being serverless friendly is an important reason why Drizzle beats Prisma as NextDevKit's default ORM.

Defining Tables

How do you define database table structures in NextDevKit?

Defining table structures with Drizzle ORM is very simple. You just need to define your table structure, fields, and related indexes in the src/database/schema.ts file.

import {
	boolean,
	index,
	integer,
	pgTable,
	text,
	timestamp,
	uniqueIndex,
	varchar,
} from "drizzle-orm/pg-core";

export const user = pgTable(
	"user",
	{
		id: varchar("id", { length: 255 }).primaryKey(),
		name: text("name").notNull(),
		email: text("email").notNull().unique(),
		emailVerified: boolean("email_verified").notNull().default(false),
		image: text("image"),
		createdAt: timestamp("created_at").notNull().defaultNow(),
		updatedAt: timestamp("updated_at").notNull().defaultNow(),
		customerId: text("customer_id"),
		role: text("role"),
		banned: boolean("banned"),
		banReason: text("ban_reason"),
		banExpires: timestamp("ban_expires"),
		locale: text("locale"),
	},
	(table) => {
		return {
			emailIdx: uniqueIndex("email_idx").on(table.email),
		};
	},
);

The code above defines a user table with basic user information like id, name, email, and other fields. It also creates a unique index on the email field.

Different NextDevKit templates have different definitions. For example, the above example is from the Next.js template, which uses PostgreSQL's pgTable to define table structures. The Cloudflare Workers template integrates Cloudflare D1 database and uses SQLite's sqliteTable to define table structures.

If you need to switch, for example, in the Cloudflare Workers template, if you don't want to use Cloudflare D1 but want to use PostgreSQL instead, you just need to modify the table structure definitions in the src/database/schema.ts file. You can refer to the Next.js template or ask AI to make the changes - it's very accurate.

As shown in the example above, if you want to add a new table or modify the current table structure, you just need to add or modify the corresponding table structure definition in the src/database/schema.ts file.

How to Migrate

After defining table structures, we need to migrate to sync the table structures to the database.

In NextDevKit, we use DrizzleKit to perform migration operations.

DrizzleKit is a powerful tool that can help you automatically generate database migration files and supports multiple databases.

NextDevKit has DrizzleKit commands integrated by default. Run the pnpm db:generate command, and DrizzleKit will automatically generate database migration files in the drizzle directory at the root. You can check the generated files in the drizzle directory - these files contain NextDevKit's existing table structures.

After generating the corresponding table structure files, you just need to run the pnpm db:migrate command, and DrizzleKit will automatically execute the database migration files to sync the table structures to the database. Before doing this, you need to configure the test database connection information in the .env file.

1. Generate migrations: This command compares your schema file with the database state and creates a new SQL migration file in the drizzle directory.

   pnpm db:generate

2. Apply migrations: This command executes the migration file to update your database schema.

   pnpm db:migrate

If you want to automatically execute migrate operations during deployment through a pipeline to sync table changes to the production database, you just need to add the corresponding commands to the pipeline. For example, add the corresponding command in Vercel.

pnpm build && pnpm db:migrate

You can also add an environment variable file .env.production to store production environment database connection information, so you can execute migrate operations locally.

{
  "scripts": {
    "db:migrate:prod": "NODE_ENV=production drizzle-kit migrate"
  }
}

pnpm db:migrate:prod

NextDevKit has different optimizations for different templates. For example, the Cloudflare Workers template is suitable for independent development projects where most people deploy locally, so the db:migrate:prod command is already integrated in scripts by default. You can explore this yourself.

Migrating Data

Besides using migrate commands to sync table structures to the database, you can also sync data to the database in the same way.

For example, if you have some built-in data that needs to be synced to databases in different environments to keep them consistent, you can also use the migrate command to sync data.

You just need to write data through insert SQL statements into drizzle/xxx.sql files, and DrizzleKit will automatically execute the database migration files to sync data to the database.

INSERT INTO "users" ("name") VALUES('Dan');
INSERT INTO "users" ("name") VALUES('Andrew');
INSERT INTO "users" ("name") VALUES('Dandrew');

Building Test Data

If you need to build a batch of test data in your local environment database, consider using Drizzle Seed to build test data.

How to Use Client

After defining table structures and migrating them to the database, we need to use the client to operate the database.

Different NextDevKit templates have subtle differences in client handling. For example, the Cloudflare Workers template uses SQLite locally, which doesn't need the DATABASE_URL environment variable locally, so its client has some special optimizations.

Here we'll use the Next.js template as an example to show how to use the client to operate the database.

Define the client in the src/database/client.ts file.

import { drizzle } from "drizzle-orm/node-postgres";

const databaseUrl = process.env.DATABASE_URL as string;

if (!databaseUrl) {
	throw new Error("Environment variable DATABASE_URL is not set");
}

export const db = drizzle(databaseUrl);

All database-related operations can be done through the db object. For example, you can use the client to operate the database in API routes.

import { db } from "@/database/client";

const user = await db.insert(user).values({
	name: "John Doe",
	email: "john.doe@example.com",
});

You can also use the client to operate the database in server components, or through server actions.

You can refer to the code in the src/lib/actions directory to learn how to use the client to operate the database in server actions.

Local Development Database Connection

Next.js Template

In the NextDevKit Next.js template, we use the DATABASE_URL environment variable to connect to the database by default, so you just need to configure the corresponding connection information in the .env file.

DATABASE_URL=postgresql://postgres:postgres@localhost:5432/postgres

For this database, you can use local Docker to start a PostgreSQL database, or use other cloud database instances like Supabase, Neon, etc.

After configuration, you can run the pnpm db:studio command to open the database studio interface for your locally connected database to view and operate the database.

Cloudflare Workers Template

If you're using the Cloudflare Workers template, this template has D1 database integrated locally. You just need to run:

# Create local D1 database
npx wrangler d1 execute your-database-name --local --command='SELECT 1'

Note that your-database-name needs to be consistent with the database_name in the wrangler.jsonc.

This will start a local D1 database and execute the SELECT 1 command to verify the database is working properly.

After starting, you can also run the pnpm db:studio command to open the database studio interface to view and operate the database.

SST AWS Template

If you're using the SST AWS template, since SST has RDS database integrated by default, for local development, you just need to configure the corresponding local environment connection information in the .env file.

DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_NAME=postgres
DATABASE_USERNAME=postgres
DATABASE_PASSWORD=postgres

For production environment database connection information for different templates, you can refer to the corresponding template database documentation.

Production Environment Database Studio

If you want to use studio to view and operate the database in the production environment, you need to use different methods.

If you're using Dokploy to deploy your database, or cloud platforms like Supabase, Neon, AWS RDS, etc., besides viewing and operating data through their consoles, you can now also consider using Drizzle Gateway to view and operate the database.

If you're using Cloudflare D1 database, you can connect to the database through the Drizzle Chrome extension plugin.

Drizzle Chrome Extension Plugin. The usage is simple - just install the extension plugin in your browser, log in to the Cloudflare dashboard, click the Drizzle button at the bottom of the page, and select the database you want to connect to.