스킬 파일

Netlify Db

Name: Netlify Db
Author: jherr

Use Netlify DB, a managed Postgres database powered by Neon. Use when you need relational database capabilities, SQL queries, or persistent structured data storage on Netlify.

jherr0 스타2026. 2. 23.

직업
카테고리: 데이터베이스 도구

스킬 내용

Netlify DB provides instant Postgres database instances powered by Neon. It auto-connects to your functions with zero configuration.

When to Use

Relational data storage
Complex queries with joins
ACID transactions
Structured data with schemas
SQL-based data access

Quick Setup

# Initialize Netlify DB for your project
netlify db:init

# This creates:
# - A Neon Postgres database
# - DATABASE_URL environment variable
# - drizzle.config.ts (if using Drizzle)

Using with Drizzle ORM (Recommended)

Installation

npm install drizzle-orm @neondatabase/serverless
npm install -D drizzle-kit

관련 스킬

Netlify Db | Skills Pool

// src/db/schema.ts
import { pgTable, serial, text, timestamp, integer, boolean } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  published: boolean("published").default(false),
  authorId: integer("author_id").references(() => users.id),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

// src/db/index.ts
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import * as schema from "./schema";

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

export { schema };

# Generate migration from schema changes
npx drizzle-kit generate

# Push schema directly to database (development)
npx drizzle-kit push

# Run migrations
npx drizzle-kit migrate

import { db, schema } from "./db";

// Insert single record
const newUser = await db.insert(schema.users).values({
  email: "[email protected]",
  name: "Alice",
}).returning();

// Insert multiple records
await db.insert(schema.posts).values([
  { title: "First Post", authorId: newUser[0].id },
  { title: "Second Post", authorId: newUser[0].id },
]);

import { db, schema } from "./db";
import { eq, and, like, desc } from "drizzle-orm";

// Get all users
const allUsers = await db.select().from(schema.users);

// Get user by ID
const user = await db
  .select()
  .from(schema.users)
  .where(eq(schema.users.id, 1));

// Get with conditions
const publishedPosts = await db
  .select()
  .from(schema.posts)
  .where(
    and(
      eq(schema.posts.published, true),
      like(schema.posts.title, "%Tutorial%")
    )
  )
  .orderBy(desc(schema.posts.createdAt))
  .limit(10);

// Join tables
const postsWithAuthors = await db
  .select({
    postTitle: schema.posts.title,
    authorName: schema.users.name,
  })
  .from(schema.posts)
  .leftJoin(schema.users, eq(schema.posts.authorId, schema.users.id));

import { db, schema } from "./db";
import { eq } from "drizzle-orm";

// Update single field
await db
  .update(schema.posts)
  .set({ published: true })
  .where(eq(schema.posts.id, 1));

// Update multiple fields
await db
  .update(schema.users)
  .set({ 
    name: "Alice Smith",
    updatedAt: new Date(),
  })
  .where(eq(schema.users.email, "[email protected]"));

import { db, schema } from "./db";
import { eq, lt } from "drizzle-orm";

// Delete single record
await db.delete(schema.posts).where(eq(schema.posts.id, 1));

// Delete with condition
await db
  .delete(schema.posts)
  .where(
    and(
      eq(schema.posts.published, false),
      lt(schema.posts.createdAt, thirtyDaysAgo)
    )
  );

// netlify/functions/users.ts
import type { Context } from "@netlify/functions";
import { db, schema } from "../../src/db";
import { eq } from "drizzle-orm";

export default async (request: Request, context: Context) => {
  const url = new URL(request.url);
  
  switch (request.method) {
    case "GET": {
      const id = url.searchParams.get("id");
      
      if (id) {
        const user = await db
          .select()
          .from(schema.users)
          .where(eq(schema.users.id, parseInt(id)));
        
        if (!user.length) {
          return new Response("User not found", { status: 404 });
        }
        return Response.json(user[0]);
      }
      
      const users = await db.select().from(schema.users);
      return Response.json(users);
    }
    
    case "POST": {
      const body = await request.json();
      const newUser = await db
        .insert(schema.users)
        .values(body)
        .returning();
      
      return Response.json(newUser[0], { status: 201 });
    }
    
    case "DELETE": {
      const id = url.searchParams.get("id");
      if (!id) {
        return new Response("ID required", { status: 400 });
      }
      
      await db.delete(schema.users).where(eq(schema.users.id, parseInt(id)));
      return new Response(null, { status: 204 });
    }
    
    default:
      return new Response("Method not allowed", { status: 405 });
  }
};

import { neon } from "@neondatabase/serverless";

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

// Simple query
const users = await sql`SELECT * FROM users`;

// Parameterized query (safe from SQL injection)
const user = await sql`
  SELECT * FROM users WHERE id = ${userId}
`;

// Insert
await sql`
  INSERT INTO users (email, name) 
  VALUES (${email}, ${name})
`;

// Transaction
const result = await sql.transaction([
  sql`INSERT INTO users (email, name) VALUES (${email}, ${name}) RETURNING id`,
  sql`INSERT INTO profiles (user_id, bio) VALUES (${userId}, ${bio})`,
]);

import { db, schema } from "./db";

// Transaction ensures all operations succeed or none do
await db.transaction(async (tx) => {
  const [user] = await tx
    .insert(schema.users)
    .values({ email: "[email protected]", name: "Bob" })
    .returning();
  
  await tx.insert(schema.posts).values({
    title: "Bob's First Post",
    authorId: user.id,
  });
  
  // If any operation fails, all are rolled back
});

// Schema with soft delete
export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  deletedAt: timestamp("deleted_at"),
});

// Query only non-deleted
const activePosts = await db
  .select()
  .from(schema.posts)
  .where(isNull(schema.posts.deletedAt));

// Soft delete
await db
  .update(schema.posts)
  .set({ deletedAt: new Date() })
  .where(eq(schema.posts.id, postId));

async function getPaginatedPosts(page: number, pageSize: number = 10) {
  const offset = (page - 1) * pageSize;
  
  const [posts, countResult] = await Promise.all([
    db
      .select()
      .from(schema.posts)
      .orderBy(desc(schema.posts.createdAt))
      .limit(pageSize)
      .offset(offset),
    db
      .select({ count: sql<number>`count(*)` })
      .from(schema.posts),
  ]);
  
  return {
    data: posts,
    page,
    pageSize,
    total: countResult[0].count,
    totalPages: Math.ceil(countResult[0].count / pageSize),
  };
}

// Use PostgreSQL's full-text search
const results = await sql`
  SELECT * FROM posts 
  WHERE to_tsvector('english', title || ' ' || content) 
        @@ plainto_tsquery('english', ${searchQuery})
  ORDER BY ts_rank(
    to_tsvector('english', title || ' ' || content),
    plainto_tsquery('english', ${searchQuery})
  ) DESC
  LIMIT 20
`;

# Initialize database
netlify db:init

# Open database in Neon console
netlify db:open

# Run migrations
netlify db:migrate

# Pull schema from existing database
netlify db:pull

# Push schema to database
netlify db:push

# Run with Netlify Dev (auto-connects to database)
netlify dev

# Or set DATABASE_URL in .env for local testing
DATABASE_URL=postgres://user:pass@localhost:5432/mydb

Netlify Db

When to Use

Quick Setup

Using with Drizzle ORM (Recommended)

Installation

Netlify Db

When to Use

Quick Setup

Using with Drizzle ORM (Recommended)

Installation

Schema Definition

Drizzle Configuration

Database Client

Migrations

Basic Queries with Drizzle

Create

Read

Update

Delete

Using in Netlify Functions

Using Raw SQL (Without ORM)

Transactions with Drizzle

Common Patterns

Soft Deletes

Full-Text Search

CLI Commands

Environment Variables

Local Development

Limits

Database Migrations

Database Migrations

Postgres Patterns

Frontend Query & Mutation

Db Migrations

Drizzle

Netlify Db

When to Use

Quick Setup

Using with Drizzle ORM (Recommended)

Installation

Netlify Db

When to Use

Quick Setup

Using with Drizzle ORM (Recommended)

Installation

Schema Definition

Drizzle Configuration

Database Client

Migrations

Basic Queries with Drizzle

Create

Read

Update

Delete

Using in Netlify Functions

Using Raw SQL (Without ORM)

Transactions with Drizzle

Common Patterns

Soft Deletes

Pagination

Full-Text Search

CLI Commands

Environment Variables

Local Development

Limits

Database Migrations

Database Migrations

Postgres Patterns

Frontend Query & Mutation

Db Migrations

Drizzle