Drizzle ORMのメモ

import { defineConfig } from 'drizzle-kit';

const databaseUrl =
  process.env.DATABASE_URL ||
  'postgresql://postgres:postgres@127.0.0.1:54322/postgres';
export default defineConfig({
  schema: './app/backend/db/schemas/**',
  out: './app/backend/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: databaseUrl,
  },
});

import { timestamp, uuid } from 'drizzle-orm/pg-core';

export const id = uuid('id').primaryKey().defaultRandom();

const createdAt = timestamp('created_at', { withTimezone: true })
  .defaultNow()
  .notNull();
const updatedAt = timestamp('updated_at', { withTimezone: true })
  .defaultNow()
  .notNull();

export const defaultTimestamps = {
  createdAt,
  updatedAt,
};

export const schemaBase = {
  id,
  createdAt,
  updatedAt,
};

npx drizzle-kit generate --config drizzle.config.ts --name=create_hoges_table

npx drizzle-kit generate --config drizzle.config.ts --custom --name=seed_hoges_table

INSERT INTO "hoges" ("name") VALUES ('hogehoge');
INSERT INTO "hoges" ("name") VALUES ('foofoo');

CREATE FUNCTION refresh_updated_at_step1() RETURNS trigger AS
$$
BEGIN
  IF NEW.updated_at = OLD.updated_at THEN
    NEW.updated_at := NULL;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION refresh_updated_at_step2() RETURNS trigger AS
$$
BEGIN
  IF NEW.updated_at IS NULL THEN
    NEW.updated_at := OLD.updated_at;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION refresh_updated_at_step3() RETURNS trigger AS
$$
BEGIN
  IF NEW.updated_at IS NULL THEN
    NEW.updated_at := CURRENT_TIMESTAMP;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER refresh_accounts_updated_at_step1
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION refresh_updated_at_step1();

CREATE TRIGGER refresh_accounts_updated_at_step2
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION refresh_updated_at_step2();

CREATE TRIGGER refresh_accounts_updated_at_step3
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION refresh_updated_at_step3();