Setup Drizzle

Every time our application interacts with the database, it's going to use Drizzle. That means we're not really going to be writing SQL, instead we will write typescript. In this section we're going to go through the initial process of setting up Drizzle in a project so that we can query data from the database and display it on a page.

Plan Schema

It's pretty common to plan out the database schema using an ERD before we write any code. Here's a very basic ERD for our application that includes the tables for users, posts, and media.

Define Schema

Now we have some sense of what the entities are, how they relate to each other, and the data types we're using. The next step is to define the schema using Data Definition Language (DDL), that's when we write a bunch of CREATE TABLE statements and run them against the database.

We're going to avoid writing raw SQL, and instead use TypeScript for everything. That means that any time we want to interact with the database, we're going to write TypeScript using the Drizzle library.

step 1:

Install the drizzle-orm library.

npm i drizzle-orm
npm i drizzle-orm
npm i drizzle-orm
npm i drizzle-orm
step 2:

Create a new folder called db in the src folder.

step 3:

Create a new folder called schema in the db folder.

step 4:

Create users.ts, posts.ts, and media.ts files in the schema folder.

  • src
    • app
    • db
      • schema
        • users.ts
        • posts.ts
        • media.ts

It's up to us how we organize our code, Drizzle doesn't have an opinion on how we do this. We don't have to use a separate file for each entity, but this method makes sense and is easy to understand. Now we can define each of the tables within these files.

users.ts

step 5:

Add the following code to the users.ts file.

users.ts
import { pgTable, text, varchar, timestamp } from "drizzle-orm/pg-core"

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  username: varchar("username", { length: 30 }).notNull().unique(),
  firstName: varchar("first_name", { length: 50 }).notNull(),
  lastName: varchar("last_name", { length: 50 }).notNull(),
  avatar: text("avatar").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})
users.ts
import { pgTable, text, varchar, timestamp } from "drizzle-orm/pg-core"

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  username: varchar("username", { length: 30 }).notNull().unique(),
  firstName: varchar("first_name", { length: 50 }).notNull(),
  lastName: varchar("last_name", { length: 50 }).notNull(),
  avatar: text("avatar").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})
users.ts
import { pgTable, text, varchar, timestamp } from "drizzle-orm/pg-core"

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  username: varchar("username", { length: 30 }).notNull().unique(),
  firstName: varchar("first_name", { length: 50 }).notNull(),
  lastName: varchar("last_name", { length: 50 }).notNull(),
  avatar: text("avatar").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})
users.ts
import { pgTable, text, varchar, timestamp } from "drizzle-orm/pg-core"

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  username: varchar("username", { length: 30 }).notNull().unique(),
  firstName: varchar("first_name", { length: 50 }).notNull(),
  lastName: varchar("last_name", { length: 50 }).notNull(),
  avatar: text("avatar").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})

The Drizzle TypeScript we've written here is kind of similar to SQL we would write to define the table, and you can click the button to see the SQL that Drizzle generates from this TypeScript. We never really have to think about CREATE or ALTER statements though, we just define the schema in TypeScript and Drizzle takes care of everything else for us.

Let's break down the drizzle code a bit.

Use the pgTable function to define a Postgres table. The first argument is the name of the table as it will appear in postgres, and the second argument is an object that defines the columns.

import { pgTable } from "drizzle-orm/pg-core"
const users = pgTable("users", {
  // All columns here
})
import { pgTable } from "drizzle-orm/pg-core"
const users = pgTable("users", {
  // All columns here
})
import { pgTable } from "drizzle-orm/pg-core"
const users = pgTable("users", {
  // All columns here
})
import { pgTable } from "drizzle-orm/pg-core"
const users = pgTable("users", {
  // All columns here
})

Drizzle provides a set of functions that map to PostgreSQL column types.

These need to be imported from drizzle-orm/pg-core.

import { 
  text, 
  varchar, 
  timestamp 
} from "drizzle-orm/pg-core"
import { 
  text, 
  varchar, 
  timestamp 
} from "drizzle-orm/pg-core"
import { 
  text, 
  varchar, 
  timestamp 
} from "drizzle-orm/pg-core"
import { 
  text, 
  varchar, 
  timestamp 
} from "drizzle-orm/pg-core"

Use these functions to define each column as a property on the table object.

export const users = pgTable("users", {
  id: text("id").primaryKey(),
  username: varchar("username", { length: 30 }),
  createdAt: timestamp("created_at", { withTimezone: true }),
  // ...
})
export const users = pgTable("users", {
  id: text("id").primaryKey(),
  username: varchar("username", { length: 30 }),
  createdAt: timestamp("created_at", { withTimezone: true }),
  // ...
})
export const users = pgTable("users", {
  id: text("id").primaryKey(),
  username: varchar("username", { length: 30 }),
  createdAt: timestamp("created_at", { withTimezone: true }),
  // ...
})
export const users = pgTable("users", {
  id: text("id").primaryKey(),
  username: varchar("username", { length: 30 }),
  createdAt: timestamp("created_at", { withTimezone: true }),
  // ...
})

Each of these functions takes two arguments:

  1. The name of the column as it will appear in the PostgreSQL table.
  2. An optional object that specifies additional column constraints or properties.
varchar(
  "username", // 1
  { length: 30 } // 2
)
varchar(
  "username", // 1
  { length: 30 } // 2
)
varchar(
  "username", // 1
  { length: 30 } // 2
)
varchar(
  "username", // 1
  { length: 30 } // 2
)

Constraints and defaults can then be chained onto the end using methods like .notNull() and default().

timestamp("created_at", { withTimezone: true })
  .notNull()
  .defaultNow()
timestamp("created_at", { withTimezone: true })
  .notNull()
  .defaultNow()
timestamp("created_at", { withTimezone: true })
  .notNull()
  .defaultNow()
timestamp("created_at", { withTimezone: true })
  .notNull()
  .defaultNow()

You might have noticed that we use camelCase for the property names in TypeScript and snake_case for the column names in Postgres.

createdAt -> created_at

firstName -> first_name

This is a common practice to adhere to the naming conventions of both languages. Drizzle takes care of mapping these for us.

query.ts
db.select().from(users)
  .where(eq(users.firstName, "Sam"))
query.ts
db.select().from(users)
  .where(eq(users.firstName, "Sam"))
query.ts
db.select().from(users)
  .where(eq(users.firstName, "Sam"))
query.ts
db.select().from(users)
  .where(eq(users.firstName, "Sam"))
query.sql
SELECT * FROM users 
WHERE users.first_name = 'Sam';
query.sql
SELECT * FROM users 
WHERE users.first_name = 'Sam';
query.sql
SELECT * FROM users 
WHERE users.first_name = 'Sam';
query.sql
SELECT * FROM users 
WHERE users.first_name = 'Sam';

media.ts

step 6:

Add the following code to the media.ts file.

media.ts
import { serial, text, integer, pgTable, pgEnum, timestamp } from "drizzle-orm/pg-core"

export const mediaType = pgEnum("media_type", ["image", "video"])

export const media = pgTable("media", {
  id: serial("id").primaryKey(),
  type: mediaType("type").notNull(),
  url: text("url").notNull(),
  width: integer("width").notNull(),
  height: integer("height").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})
media.ts
import { serial, text, integer, pgTable, pgEnum, timestamp } from "drizzle-orm/pg-core"

export const mediaType = pgEnum("media_type", ["image", "video"])

export const media = pgTable("media", {
  id: serial("id").primaryKey(),
  type: mediaType("type").notNull(),
  url: text("url").notNull(),
  width: integer("width").notNull(),
  height: integer("height").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})
media.ts
import { serial, text, integer, pgTable, pgEnum, timestamp } from "drizzle-orm/pg-core"

export const mediaType = pgEnum("media_type", ["image", "video"])

export const media = pgTable("media", {
  id: serial("id").primaryKey(),
  type: mediaType("type").notNull(),
  url: text("url").notNull(),
  width: integer("width").notNull(),
  height: integer("height").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})
media.ts
import { serial, text, integer, pgTable, pgEnum, timestamp } from "drizzle-orm/pg-core"

export const mediaType = pgEnum("media_type", ["image", "video"])

export const media = pgTable("media", {
  id: serial("id").primaryKey(),
  type: mediaType("type").notNull(),
  url: text("url").notNull(),
  width: integer("width").notNull(),
  height: integer("height").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})

pgEnum is used to define a Postgres enum. Then we can use it just like any other type.

const mediaType = pgEnum("media_type", ["image", "video"])

const media = pgTable("media", {
  id: serial("id").primaryKey(),
  type: mediaType("type").notNull(),
  // ...
})
const mediaType = pgEnum("media_type", ["image", "video"])

const media = pgTable("media", {
  id: serial("id").primaryKey(),
  type: mediaType("type").notNull(),
  // ...
})
const mediaType = pgEnum("media_type", ["image", "video"])

const media = pgTable("media", {
  id: serial("id").primaryKey(),
  type: mediaType("type").notNull(),
  // ...
})
const mediaType = pgEnum("media_type", ["image", "video"])

const media = pgTable("media", {
  id: serial("id").primaryKey(),
  type: mediaType("type").notNull(),
  // ...
})

posts.ts

step 7:

Add the following code to the posts.ts file.

posts.ts
import { serial, text, timestamp, integer, pgTable, AnyPgColumn } from "drizzle-orm/pg-core"
import { users } from "./users"
import { media } from "./media"

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id),
  mediaId: integer("media_id").references(() => media.id),
  replyId: integer("reply_id").references((): AnyPgColumn => posts.id),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})
posts.ts
import { serial, text, timestamp, integer, pgTable, AnyPgColumn } from "drizzle-orm/pg-core"
import { users } from "./users"
import { media } from "./media"

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id),
  mediaId: integer("media_id").references(() => media.id),
  replyId: integer("reply_id").references((): AnyPgColumn => posts.id),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})
posts.ts
import { serial, text, timestamp, integer, pgTable, AnyPgColumn } from "drizzle-orm/pg-core"
import { users } from "./users"
import { media } from "./media"

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id),
  mediaId: integer("media_id").references(() => media.id),
  replyId: integer("reply_id").references((): AnyPgColumn => posts.id),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})
posts.ts
import { serial, text, timestamp, integer, pgTable, AnyPgColumn } from "drizzle-orm/pg-core"
import { users } from "./users"
import { media } from "./media"

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  userId: text("user_id").notNull().references(() => users.id),
  mediaId: integer("media_id").references(() => media.id),
  replyId: integer("reply_id").references((): AnyPgColumn => posts.id),
  content: text("content").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
})

import another table to reference it in the constaint.

Chaining a references() function will add a foreign key constraint.

A self-referencing foreign key constraint requires us to explicitly set the return type because of TypeScript limitations.

integer("reply_id").references((): AnyPgColumn => posts.id),
integer("reply_id").references((): AnyPgColumn => posts.id),
integer("reply_id").references((): AnyPgColumn => posts.id),
integer("reply_id").references((): AnyPgColumn => posts.id),

Column Types

You can see all of the column types that are available for schema decleration in the Drizzle docs.

Table TypeScript Types

At some point it's going to be necessary to have TypeScript types that represent theses entities. You might already imagine a type for the users table that looks something like this:

type User = {
    id: string;
    username: string;
    firstName: string;
    lastName: string;
    avatar: string;
    createdAt: Date;
}

type User = {
    id: string;
    username: string;
    firstName: string;
    lastName: string;
    avatar: string;
    createdAt: Date;
}

type User = {
    id: string;
    username: string;
    firstName: string;
    lastName: string;
    avatar: string;
    createdAt: Date;
}

type User = {
    id: string;
    username: string;
    firstName: string;
    lastName: string;
    avatar: string;
    createdAt: Date;
}

Fortunatly for us, Drizzle has some convenient table model type inference properties that we can use:

$inferSelect will give us a type that represents the object we will receive when making a select statement.

users.ts
type User = typeof users.$inferSelect
users.ts
type User = typeof users.$inferSelect
users.ts
type User = typeof users.$inferSelect
users.ts
type User = typeof users.$inferSelect

$inferInsert will give us a type that represents the object we will insert into the table.

users.ts
type NewUser = typeof users.$inferInsert
users.ts
type NewUser = typeof users.$inferInsert
users.ts
type NewUser = typeof users.$inferInsert
users.ts
type NewUser = typeof users.$inferInsert
step 8:

export the User and NewUser types from the users.ts file.

step 9:

Do this for the other tables as well.

users.ts
import { text, varchar, pgTable, timestamp } from "drizzle-orm/pg-core"

export const users = pgTable("users", {
  // ...
})

export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert

users.ts
import { text, varchar, pgTable, timestamp } from "drizzle-orm/pg-core"

export const users = pgTable("users", {
  // ...
})

export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert

users.ts
import { text, varchar, pgTable, timestamp } from "drizzle-orm/pg-core"

export const users = pgTable("users", {
  // ...
})

export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert

users.ts
import { text, varchar, pgTable, timestamp } from "drizzle-orm/pg-core"

export const users = pgTable("users", {
  // ...
})

export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert

Migrating/Pushing Schema

The schema is now defined for our three tables, that's great! We could even use these to start writing out some type safe queries in TypeScript, however, we should probably add these tables to our Neon database before we do anything else. That way we can actually add some data and run some queries.

To run the schema against the database we can do one of two things:

  • Migrate: Generate a migration, which creates sql files, then run those files against the database.
  • Push: Apply the shema directly to the database without creating migration files.

Either way, we're going to need Drizzle Kit.

Drizzle Kit

To run migrations, pushes, or do a bunch of other cool things, we can use the drizzle-kit library.

drizzle-kit push:pg # Update Database Schema
drizzle-kit generate:pg # Generate migration files from the database schema
drizzle-kit studio # Start Drizzle Studio
drizzle-kit push:pg # Update Database Schema
drizzle-kit generate:pg # Generate migration files from the database schema
drizzle-kit studio # Start Drizzle Studio
drizzle-kit push:pg # Update Database Schema
drizzle-kit generate:pg # Generate migration files from the database schema
drizzle-kit studio # Start Drizzle Studio
drizzle-kit push:pg # Update Database Schema
drizzle-kit generate:pg # Generate migration files from the database schema
drizzle-kit studio # Start Drizzle Studio

This is the CLI companion for Drizzle.

step 10:

Install drizzle-kit as a dev dependancy.

npm install -D drizzle-kit
npm install -D drizzle-kit
npm install -D drizzle-kit
npm install -D drizzle-kit

All of the drizzle-kit commands use a drizzle.config.ts file in the root of the project. This file tells Drizzle Kit where to find the schema files, what database driver to use, and where to output the generated files.

step 11:

Create a new drizzle.config.ts file in the root of your project (with all the other config files).

  • public
  • src
  • drizzle.config.ts
  • next.config.js
  • ...
step 12:

Add the following code to the drizzle config file:

drizzle.config.ts
import { defineConfig } from 'drizzle-kit'
import * as dotenv from 'dotenv';

dotenv.config({
  path: '.env.local',
});

export default defineConfig({
  schema: "./src/db/schema/*",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.MIGRATION_DATABASE_URL!,
  },
  out: "./drizzle",
})
drizzle.config.ts
import { defineConfig } from 'drizzle-kit'
import * as dotenv from 'dotenv';

dotenv.config({
  path: '.env.local',
});

export default defineConfig({
  schema: "./src/db/schema/*",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.MIGRATION_DATABASE_URL!,
  },
  out: "./drizzle",
})
drizzle.config.ts
import { defineConfig } from 'drizzle-kit'
import * as dotenv from 'dotenv';

dotenv.config({
  path: '.env.local',
});

export default defineConfig({
  schema: "./src/db/schema/*",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.MIGRATION_DATABASE_URL!,
  },
  out: "./drizzle",
})
drizzle.config.ts
import { defineConfig } from 'drizzle-kit'
import * as dotenv from 'dotenv';

dotenv.config({
  path: '.env.local',
});

export default defineConfig({
  schema: "./src/db/schema/*",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.MIGRATION_DATABASE_URL!,
  },
  out: "./drizzle",
})

Notice that we're using the MIGRATION_DATABASE_URL from the neon roles section. This url will be used for migrations, pushes, and drizzle studio.

We won't generate any migration files right now, but when we do, they will be generated in the ./drizzle directory.

step 13:

Install dotenv as a dev dependancy. Only do this if you're not using bun.

npm install -D dotenv
npm install -D dotenv
npm install -D dotenv
npm install -D dotenv
step 14:

In tsconfig.json, update the compilerOptions target to es6

tsconfig.json
{
  "compilerOptions": {
    "target": "es6",
    // ...
  }
}
tsconfig.json
{
  "compilerOptions": {
    "target": "es6",
    // ...
  }
}
tsconfig.json
{
  "compilerOptions": {
    "target": "es6",
    // ...
  }
}
tsconfig.json
{
  "compilerOptions": {
    "target": "es6",
    // ...
  }
}

Now we're ready to start using Drizzle Kit!

Migrate

If you're familiar with migrations already, then you already know how this works.

Anytime you update the schema in one of those typescript files, you can generate a new migration which generates a new SQL file in the ./drizzle/migrations directory. That SQL file will contain the SQL needed to update the database to match the schema in the typescript files. Then you can run the SQL file against your database to update it.

This is a really amazing feature for so many reasons, but we're going to ignore migrations for now. We will come back to this topic later, but for now there's a better option, pushing.

Push

Running a migration creates a new SQL file that we have to run against the database. This is really great in production, but when we're rapidly prototyping it can be a bit of a pain to always be generating and running new SQL files for every little change we make to the database. So drizzle has a push feature to make prototyping and rapid development easier.

step 15:

Push the database schema to the database.

npx drizzle-kit push:pg
npx drizzle-kit push:pg
npx drizzle-kit push:pg
npx drizzle-kit push:pg

This will run the DDL, the CREATE TABLE statements, against the database and create the tables for us. You can verify this by checking out the Tables section in the Neon dashboard. Feel free to add some data to the tables while you're there.

If we need to make a change to the schema, we can just update the TypeScript files and run drizzle-kit push:pg again. Drizzle will handle the ALTER TABLE statements, we only need to worry about the structure of the schema.

Drizzle Studio

Drizzle Studio is a GUI for Drizzle. It's a really great tool for exploring your database and creating or updating data for testing.

step 16:

For Drizzle Studio to work with Postgres, we first need to install pg.

npm install -D pg
npm install -D pg
npm install -D pg
npm install -D pg
step 17:

Run drizzle-kit studio to start Drizzle Studio.

npx drizzle-kit studio
npx drizzle-kit studio
npx drizzle-kit studio
npx drizzle-kit studio

It should start running on http://127.0.0.1:4983. Visit that URL in your browser and make sure the three tables exist there.

step 18:

Create a new fake user for yourself. Here's a placeholder image for a user avatar: "https://www.gravatar.com/avatar/?d=mp".

Create a few posts for yourself. Remember that user_id and content are the only required fields that you must provide values for.

Feel free to add more data to the tables while you're there, just make sure you have at least one user and a few posts.

Drizzle and Next

Good job making it this far. We have a database with tables in it, let's write some queries.

Actually, first we need our next app to be able to connect to the database, then we can write and run queries.

Neon Serverless & Drizzle ORM

Drizzle is responsible for translating our TypeScript code into SQL and running it, but it doesn't actually connect to the database. We need to use a database driver to connect to the neon database, and we're going to use @neondatabase/serverless.

step 19:

Install @neondatabase/serverless.

npm i @neondatabase/serverless
npm i @neondatabase/serverless
npm i @neondatabase/serverless
npm i @neondatabase/serverless
step 20:

Create a new file called index.ts in the src/db folder.

  • src
    • app
    • db
      • index.ts
      • schema
step 21:

Add the following code to the src/db/index.ts file.

src/db/index.ts
import { neon } from '@neondatabase/serverless'
import { drizzle } from 'drizzle-orm/neon-http'

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);
src/db/index.ts
import { neon } from '@neondatabase/serverless'
import { drizzle } from 'drizzle-orm/neon-http'

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);
src/db/index.ts
import { neon } from '@neondatabase/serverless'
import { drizzle } from 'drizzle-orm/neon-http'

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);
src/db/index.ts
import { neon } from '@neondatabase/serverless'
import { drizzle } from 'drizzle-orm/neon-http'

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

Queries

All database interactions will happen through the db object. We just need to import the db object and any tables we want to query from, then we can run queries like this:

import { db } from '@/db'
import { users } from '@/db/schema/users'

const users = await db.select().from(users) // get all users
await db.insert(users).values({ name: 'Sam' }) // create a new user
await db.update(users).set({ name: 'saM' }).where(eq(users.name, "Sam")) // update a user
await db.delete(users).where(eq(users.name, 'saM')) // delete a user
import { db } from '@/db'
import { users } from '@/db/schema/users'

const users = await db.select().from(users) // get all users
await db.insert(users).values({ name: 'Sam' }) // create a new user
await db.update(users).set({ name: 'saM' }).where(eq(users.name, "Sam")) // update a user
await db.delete(users).where(eq(users.name, 'saM')) // delete a user
import { db } from '@/db'
import { users } from '@/db/schema/users'

const users = await db.select().from(users) // get all users
await db.insert(users).values({ name: 'Sam' }) // create a new user
await db.update(users).set({ name: 'saM' }).where(eq(users.name, "Sam")) // update a user
await db.delete(users).where(eq(users.name, 'saM')) // delete a user
import { db } from '@/db'
import { users } from '@/db/schema/users'

const users = await db.select().from(users) // get all users
await db.insert(users).values({ name: 'Sam' }) // create a new user
await db.update(users).set({ name: 'saM' }).where(eq(users.name, "Sam")) // update a user
await db.delete(users).where(eq(users.name, 'saM')) // delete a user

You can read the Drizzle Docs docs to see more examples of queries.

Async Components

Now let's use this on the home page where we display a list of posts.

step 22:

Update your src/app/page.tsx file to query the posts from the database.

src/app/page.tsx
import { db } from '@/db'
import { posts as postsTable } from '@/db/schema/posts'

export default async function Home() {
  const posts = await db.select().from(postsTable)

  console.log(posts)

  // ...
}
src/app/page.tsx
import { db } from '@/db'
import { posts as postsTable } from '@/db/schema/posts'

export default async function Home() {
  const posts = await db.select().from(postsTable)

  console.log(posts)

  // ...
}
src/app/page.tsx
import { db } from '@/db'
import { posts as postsTable } from '@/db/schema/posts'

export default async function Home() {
  const posts = await db.select().from(postsTable)

  console.log(posts)

  // ...
}
src/app/page.tsx
import { db } from '@/db'
import { posts as postsTable } from '@/db/schema/posts'

export default async function Home() {
  const posts = await db.select().from(postsTable)

  console.log(posts)

  // ...
}

Notice that the component is now an async component. That's totally fine and normal.

The posts coming back from the database don't contain any user or media information, so you might need to comment out some of your JSX to get this to run.

Run the app and navigate to the home page, if you check the server console, you should see an array of posts. It should look something like this:

[
  {
    id: 1,
    userId: 'user-1',
    mediaId: null,
    replyId: null,
    content: 'This is a test post'
  },
  // ...
]
[
  {
    id: 1,
    userId: 'user-1',
    mediaId: null,
    replyId: null,
    content: 'This is a test post'
  },
  // ...
]
[
  {
    id: 1,
    userId: 'user-1',
    mediaId: null,
    replyId: null,
    content: 'This is a test post'
  },
  // ...
]
[
  {
    id: 1,
    userId: 'user-1',
    mediaId: null,
    replyId: null,
    content: 'This is a test post'
  },
  // ...
]

This is a great start, but we really need to be able to get the user and media information for each post, so we need to join the tables together...

Adjust the rest of your code to grab all posts from the real database instead of the fake one. Remember to add more data to the database using Drizzle Studio so that you have more than one post to display.

Don't worry about creating or updating any data for now, just focus on reading data from the database. We'll come back to creating and updating data later.