69 lines
2.1 KiB
SQL
69 lines
2.1 KiB
SQL
-- Migration: Initial Schema
|
|
-- Generated by Drizzle Kit
|
|
-- This is an example migration file showing the structure
|
|
-- Actual migrations should be generated with: drizzle-kit generate
|
|
|
|
-- Create users table
|
|
CREATE TABLE `users` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`email` text NOT NULL,
|
|
`name` text NOT NULL,
|
|
`bio` text,
|
|
`created_at` integer NOT NULL,
|
|
`updated_at` integer
|
|
);
|
|
|
|
-- Create unique index on email
|
|
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);
|
|
|
|
-- Create index on email for faster lookups
|
|
CREATE INDEX `users_email_idx` ON `users` (`email`);
|
|
|
|
-- Create index on created_at for sorting
|
|
CREATE INDEX `users_created_at_idx` ON `users` (`created_at`);
|
|
|
|
-- Create posts table
|
|
CREATE TABLE `posts` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`title` text NOT NULL,
|
|
`slug` text NOT NULL,
|
|
`content` text NOT NULL,
|
|
`published` integer DEFAULT false NOT NULL,
|
|
`author_id` integer NOT NULL,
|
|
`created_at` integer NOT NULL,
|
|
`updated_at` integer,
|
|
FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE cascade
|
|
);
|
|
|
|
-- Create unique index on slug
|
|
CREATE UNIQUE INDEX `posts_slug_unique` ON `posts` (`slug`);
|
|
|
|
-- Create index on slug for URL lookups
|
|
CREATE INDEX `posts_slug_idx` ON `posts` (`slug`);
|
|
|
|
-- Create index on author_id for user's posts
|
|
CREATE INDEX `posts_author_idx` ON `posts` (`author_id`);
|
|
|
|
-- Create composite index on published + created_at
|
|
CREATE INDEX `posts_published_created_idx` ON `posts` (`published`, `created_at`);
|
|
|
|
-- Create comments table
|
|
CREATE TABLE `comments` (
|
|
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
`content` text NOT NULL,
|
|
`post_id` integer NOT NULL,
|
|
`author_id` integer NOT NULL,
|
|
`created_at` integer NOT NULL,
|
|
FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE cascade,
|
|
FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE cascade
|
|
);
|
|
|
|
-- Create index on post_id for post's comments
|
|
CREATE INDEX `comments_post_idx` ON `comments` (`post_id`);
|
|
|
|
-- Create index on author_id for user's comments
|
|
CREATE INDEX `comments_author_idx` ON `comments` (`author_id`);
|
|
|
|
-- Optimize database
|
|
PRAGMA optimize;
|