SQL schema declaration

You declare SQL schema in TypeScript in either one schema.ts file or you can group them logically in multiple logically grouped files, whichever you prefer, all the freedom!

📦 <project root>
 └ 📂 src
    └ 📂 db
       └ 📜 schema.ts

📦 <project root>
 └ 📂 src
    └ 📂 db
       └ 📂 schema
          ├ 📜 users.ts
          ├ 📜 countries.ts
          ├ 📜 cities.ts
          ├ 📜 products.ts
          ├ 📜 clients.ts
          ├ 📜 enums.ts
          └ 📜 etc.ts

📦 <project root>
 └ 📂 src
    ├ 📂 get-user
    │  ├ 📜 user.ts
    │  └ 📜 handler.ts
    ├ 📂 get-city
    │  ├ 📜 city.ts
    │  └ 📜 handler.ts    
    ├  ...

You can declare tables, indexes and constraints, foreign keys and enums.
Please pay attention to export keyword, they are mandatory if you'll be using drizzle-kit SQL migrations generator

import { integer, pgEnum, pgTable, serial, uniqueIndex, varchar } from 'drizzle-orm/pg-core';
 
// declaring enum in database
export const popularityEnum = pgEnum('popularity', ['unknown', 'known', 'popular']);
 
export const countries = pgTable('countries', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
}, (countries) => {
  return {
    nameIndex: uniqueIndex('name_idx').on(countries.name),
  }
});
 
export const cities = pgTable('cities', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
  countryId: integer('country_id').references(() => countries.id),
  popularity: popularityEnum('popularity'),
});

Database and table explicit entity types:

import { pgTable, serial, text, varchar } from 'drizzle-orm/pg-core';
import { InferModel } from 'drizzle-orm';
import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';
 
const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: text('full_name'),
  phone: varchar('phone', { length: 256 }),
});
 
export type User = InferModel<typeof users>; // return type when queried
export type NewUser = InferModel<typeof users, 'insert'>; // insert type
...
 
const db: NodePgDatabase = drizzle(...);
 
const result: User[] = await db.select().from(users);
 
export async function insertUser(user: NewUser): Promise<User> {
  return db.insert(users).values(user).returning();
}

HTTP proxy PostgreSQL