PlanetScale

-- PlanetScale CLI og branching workflow

# 1. Opret ny database branch
pscale branch create my-database feature-add-users

# 2. Connect til branch
pscale connect my-database feature-add-users

-- 3. Kør schema changes på branch
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,  -- Note: No FOREIGN KEY!
  title VARCHAR(200),
  content TEXT,
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

# 4. Opret deploy request (merge til main)
pscale deploy-request create my-database feature-add-users

# 5. Deploy (zero-downtime migration!)
pscale deploy-request deploy my-database 1

-- Normal SQL queries (MySQL syntax)
INSERT INTO users (email, name) VALUES 
  ('peter@email.dk', 'Peter Hansen');

INSERT INTO posts (user_id, title, content) VALUES
  (1, 'Min første post', 'Dette er content');

-- JOINs (men ingen FK constraints)
SELECT 
  u.name,
  u.email,
  COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

-- Node.js eksempel med @planetscale/database
import { connect } from '@planetscale/database'

const config = {
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD
}

const conn = connect(config)

// Query
const results = await conn.execute(
  'SELECT * FROM users WHERE email = ?',
  ['peter@email.dk']
)

console.log(results.rows)

// Insert
await conn.execute(
  'INSERT INTO users (email, name) VALUES (?, ?)',
  ['new@email.dk', 'New User']
)

// Transaction
await conn.transaction(async (tx) => {
  const user = await tx.execute(
    'INSERT INTO users (email, name) VALUES (?, ?)',
    ['test@email.dk', 'Test User']
  )
  
  const userId = user.insertId
  
  await tx.execute(
    'INSERT INTO posts (user_id, title) VALUES (?, ?)',
    [userId, 'First Post']
  )
})

// Prisma eksempel (populær med PlanetScale)
// schema.prisma
datasource db {
  provider = "mysql"
  url = env("DATABASE_URL")
  relationMode = "prisma" // Important for PlanetScale!
}

model User {
  id        BigInt   @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        BigInt   @id @default(autoincrement())
  userId    BigInt
  user      User     @relation(fields: [userId], references: [id])
  title     String
  content   String?  @db.Text
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  
  @@index([userId])
}

// JavaScript med Prisma
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// Create med relation
const user = await prisma.user.create({
  data: {
    email: 'prisma@email.dk',
    name: 'Prisma User',
    posts: {
      create: [
        { title: 'Post 1', content: 'Content 1' },
        { title: 'Post 2', content: 'Content 2' }
      ]
    }
  },
  include: { posts: true }
})

// Query med relation
const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: {
      where: { published: true }
    }
  }
})

# PlanetScale CLI commands
pscale database create my-database --region eu-west
pscale branch list my-database
pscale shell my-database main
pscale backup create my-database main
pscale password create my-database main my-password