pgstrap

pgstrap is a powerful bootstrapping utility for Postgres/TypeScript projects, designed to streamline your database management workflow and enhance type safety.

Features

• 🚀 Quick setup with TypeScript support
• 📁 Organized migration structure in [src]/db/migrations
• 🔧 "Standard Shorthands" for UUIDs and created_at timestamps
• 🧪 Automated migration runs in tests
• 🔄 Automatic type generation for Zapatos and Kysely
• 🔁 Database reset and migration scripts
• 📊 Automatic schema dumps with [src]/db/zapatos
• 🏗️ Database structure dumps in [src]/db/structure

Installation

npm install pgstrap --save-dev

Quick Start

1. Initialize pgstrap in your project:

   npx pgstrap init

2. This will set up the necessary configuration and scripts in your package.json.

3. Create your first migration:

   npm run db:create-migration my-first-migration

4. Edit the migration file in src/db/migrations/.

5. Run the migration:

   npm run db:migrate

6. Generate types and structure:

   npm run db:generate

Usage

Available Scripts

• npm run db:migrate - Run pending migrations
• npm run db:reset - Drop and recreate the database, then run all migrations
• npm run db:generate - Generate types and structure dumps
• npm run db:create-migration - Create a new migration file

Configuration

After running pgstrap init, you'll find a pgstrap.config.js file in your project root. Customize it as needed:

module.exports = {
  defaultDatabase: "mydb",
  schemas: ["public"],
  dbDir: "./src/db", // optional, defaults to "./src/db"
}

Writing Migrations

Migrations in pgstrap are written using node-pg-migrate, which provides a powerful and flexible way to define database changes.

To create a new migration:

npm run db:create-migration my-migration-name

This will create a new file in src/db/migrations/ with a timestamp prefix.

Migration Structure

A typical migration file looks like this:

import { MigrationBuilder, ColumnDefinitions } from "node-pg-migrate"

export const shorthands: ColumnDefinitions | undefined = undefined

export async function up(pgm: MigrationBuilder): Promise<void> {
  // Your migration code here
}

export async function down(pgm: MigrationBuilder): Promise<void> {
  // Code to revert your migration (optional)
}

Example Migration

Here's an example of a migration that creates a new table:

export async function up(pgm: MigrationBuilder): Promise<void> {
  pgm.createTable("users", {
    id: "id",
    username: { type: "text", notNull: true },
    email: { type: "text", notNull: true, unique: true },
    created_at: {
      type: "timestamptz",
      notNull: true,
      default: pgm.func("current_timestamp"),
    },
  })

  pgm.createIndex("users", "username")
}

export async function down(pgm: MigrationBuilder): Promise<void> {
  pgm.dropTable("users")
}

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

• This project was originally developed as seam-pgm by Seam Labs Inc. in 2024.
• Special thanks to the Zapatos and Kysely projects for their excellent TypeScript database tools.

Support

If you encounter any issues or have questions, please file an issue on the GitHub repository.

Happy coding with pgstrap! 🚀