Table

Tables determine what kind of data we can store in a database.

If we have a user table, we can record information for each registered user. Like this:

id	name	email	gender	createdAt	updatedAt
1	Amber	amber@example.com	female	1970-01-01 00:00:00	1970-01-01 00:00:00
2	July	july@example.com	male	1970-01-01 00:00:00	1970-01-01 00:00:00
3	Reynard	reynard@example.com	male	1970-01-01 00:00:00	1970-01-01 00:00:00

Each row represents a user, and each column represents a data field.

In general, we have common fields for each table such as id, createdAt, and updatedAt. The id is used to uniquely identify a row of data, and createdAt and updatedAt are used to record the time of each data update.

Column Types

We can set different types for each column, and database types are much richer than JavaScript types. The most commonly used type is varchar, which is typically used to store strings. Different database systems may have different data types, and you can find all types in MySQL Column Types and PostgreSQL Column Types.

Creating Table Schema

The table schema determines the columns of the table and their types. Let’s create a /src/databases directory where we will write the schemas for our database tables. Once created, we can write a user-table.ts file to store our user data:

// For MySQL
import type typia from "typia";
import { relations, sql } from "drizzle-orm";
import { mysqlTable, bigint, varchar, datetime } from "drizzle-orm/mysql-core";

export const userTable = mysqlTable(
  "user",
  {
    id: bigint("id", { unsigned: true, mode: "bigint" }).autoincrement().primaryKey(),

    name: varchar("name", { length: 32 }),
    email: varchar("email", { length: 320 }).unique(),
    gender: varchar("gender", { length: 320 }).unique(),

    createdAt: datetime("createdAt").notNull().default(sql`CURRENT_TIMESTAMP`),
    updatedAt: datetime("updatedAt").notNull().default(sql`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`),
  },
  (t) => ({})
);

The above example is for MySQL. If you are using PostgreSQL, you can use the following code:

// For PostgreSQL
import type typia from "typia";
import { relations, sql } from "drizzle-orm";
import { pgTable, bigserial, time, varchar } from "drizzle-orm/pg-core";

export const userTable = pgTable(
  "user", // table name
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),

    name: varchar("name", { length: 32 }),
    avatar: varchar("avatar", { length: 255 }),
    email: varchar("email", { length: 320 }).unique(),
    gender: varchar("gender", { length: 320 }),

    createdAt: time("createdAt").notNull(),
    updatedAt: time("updatedAt").notNull(),
  },
  (t) => ({})
);

Auto-increment Column

Every table should have an auto-increment column named id. Each time a row of data is inserted, an id will be automatically generated for that row.

MySQL

bigint("id", { unsigned: true, mode: "bigint" }).autoincrement().primaryKey()

PostgreSQL

bigserial("id", { mode: "bigint" }).primaryKey()

Created Time

It is recommended to have a column named createdAt in every table to automatically record the time of each row insertion.

MySQL

datetime("createdAt").notNull().default(sql`CURRENT_TIMESTAMP`)

PostgreSQL

time("updatedAt").notNull().defaultNow()

Updated Time

It is recommended to have a column named updatedAt in every table to automatically record the time of each row update.

MySQL

datetime("updatedAt").notNull().default(sql`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`)

PostgreSQL

PostgreSQL does not currently support automatic setting of creation time, so it needs to be manually set in the code.

Index

Indexes can effectively improve query performance, and we recommend adding indexes to all fields that may be queried. It is customary to name indexes by appending X to the end of the field name.

export const userTable = mysqlTable(
  "user", // table name
  {
    id: bigint("id", { unsigned: true, mode: "bigint" }).autoincrement().primaryKey(),

    name: varchar("name", { length: 32 }),
    email: varchar("email", { length: 320 }).unique(),
    gender: varchar("gender", { length: 320 }).unique(),

    createdAt: datetime("createdAt").notNull().default(sql`CURRENT_TIMESTAMP`),
    updatedAt: datetime("updatedAt").notNull().default(sql`CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`),
  },
  (t) => ({
    nameX: index("nameX").on(t.name),
  })
);