Getting Started with pgmi#

This guide takes you from zero to a working deployment in about 10 minutes. The commands are concrete — the only values you supply are your local PostgreSQL password and the database name.

What you’ll do:

1. Install pgmi
2. Make sure PostgreSQL is reachable
3. Create a project
4. Deploy it
5. Verify the deployment

What you need:

• PostgreSQL running on localhost:5432 (the default), reachable over a direct connection or a session-mode pooler — transaction-mode poolers (PgBouncer txn mode, RDS Proxy) break pgmi’s session temp tables; see Connection Requirements
• A PostgreSQL user with database creation rights (typically postgres)

No Go toolchain required — the quickstart installs a prebuilt binary. (Installing from source with go install is an optional alternative; see Step 1.)

Step 1: Install pgmi#

Install the prebuilt binary — no Go toolchain needed.

macOS / Linux:

curl -sSL https://raw.githubusercontent.com/vvka-141/pgmi/main/scripts/install.sh | bash

Windows (PowerShell):

irm https://raw.githubusercontent.com/vvka-141/pgmi/main/scripts/install.ps1 | iex

Prefer a package manager or a checksum-verified download? brew install --cask vvka-141/pgmi/pgmi (macOS), APT, or a GitHub Releases archive all work — see the full install options .

Verify the installation:

pgmi --version

You should see output like:

pgmi 0.x.x (compat 1)
Commit: <sha>, Built: <date>, Platform: <os>/<arch>

If pgmi is not found, the install directory is not on your PATH. The install script prints the location it used; add that directory to your PATH and restart your terminal.

go install github.com/vvka-141/pgmi/cmd/pgmi@latest

Alternative installation methods:

brew install --cask vvka-141/pgmi/pgmi

curl -1sLf 'https://dl.cloudsmith.io/public/vvka-141/pgmi/setup.deb.sh' | sudo bash
sudo apt update && sudo apt install pgmi

Step 2: Make sure PostgreSQL is reachable#

pgmi needs to connect to PostgreSQL. Let’s verify your server is running and accepting connections.

Using pgAdmin:

1. Open pgAdmin
2. In the left panel, you should see a server (usually called “PostgreSQL” or “Local”)
3. Click on it — if it connects without errors, you’re good
4. Note the connection details: right-click the server → Properties → Connection tab. You’ll see the host, port, and username

Using the terminal:

psql -h localhost -U postgres -c "SELECT version();"

If prompted for a password, enter your postgres user password. You should see something like:

                          version
------------------------------------------------------------
 PostgreSQL 16.x on x86_64-...

Common issues:

• “Connection refused”: PostgreSQL is not running. Start it via your OS service manager, or open pgAdmin — it often shows a clear error about the server being down.
• “Password authentication failed”: Wrong password. If you just installed PostgreSQL, the default password is whatever you set during installation. On some Linux installs, local connections use peer authentication — try sudo -u postgres psql instead.
• “Could not connect to server”: Check that PostgreSQL is listening on localhost:5432. In pgAdmin: right-click server → Properties → Connection tab.

Step 3: Create a project#

pgmi init myapp --template basic
cd myapp

Interactive setup: if you run pgmi init myapp (without flags) on a TTY, an interactive wizard walks you through template choice and connection setup, saving a pgmi.yaml for later pgmi deploy runs. Add --template <name> to skip the template prompt, or set PGMI_NON_INTERACTIVE=1 / CI=1 to bypass wizards entirely.

If you are using the advanced template (or adopting metadata-driven ordering) and already have SQL files without <pgmi-meta> blocks, run pgmi metadata scaffold from the project root to auto-generate them. Basic template users can ignore this.

This creates a ready-to-deploy project:

myapp/
├── deploy.sql              ← Your deployment logic (the brain)
├── project.json            ← Project metadata (read by deploy.sql, not by pgmi)
├── pgmi.yaml               ← Connection defaults (the config)
├── migrations/             ← Your SQL files go here
│   ├── 001_users.sql
│   └── 002_user_crud.sql
├── __test__/               ← Your test files (or __tests__/)
│   ├── _setup.sql          ← Test fixture (seed data)
│   └── test_user_crud.sql
└── README.md

Note: Both __test__/ and __tests__/ work identically. Use whichever matches your team’s convention.

Let’s look at what was generated.

pgmi.yaml — your project config#

Open pgmi.yaml. It looks like this:

connection:
  database: mydb
  host: localhost
  port: 5432
  sslmode: prefer

params:
  env: development

timeout: 3m

This tells pgmi: “when I deploy, connect to localhost:5432 and use a database called mydb”. No JSON, no XML — just this small YAML file for connection defaults.

Update the database name and add your username:

connection:
  host: localhost
  port: 5432
  username: postgres
  database: myapp

params:
  env: development

timeout: 3m

Where does the password go? Not in pgmi.yaml — that file is meant to be committed to Git. Set the password as an environment variable instead:

# Linux/macOS
export PGPASSWORD="your-postgres-password"

# Windows PowerShell
$env:PGPASSWORD="your-postgres-password"

# Windows CMD
set PGPASSWORD=your-postgres-password

Alternatively, use a full connection string:

# Linux/macOS
export PGMI_CONNECTION_STRING="postgresql://postgres:your-postgres-password@localhost:5432/postgres"

# Windows PowerShell
$env:PGMI_CONNECTION_STRING="postgresql://postgres:your-postgres-password@localhost:5432/postgres"

When PGMI_CONNECTION_STRING is set, it provides the host, port, username, and password. pgmi connects to the database in the connection string first (typically postgres) to run CREATE DATABASE, then switches to the target database from pgmi.yaml to deploy. This is why the connection string points to postgres while pgmi.yaml says myapp — they serve different purposes.

deploy.sql — your deployment logic#

Open deploy.sql. This is the only file that controls what happens during deployment. Not a config file. Not a framework. Just PostgreSQL — the templates use PL/pgSQL, PostgreSQL’s procedural language, for loops and conditionals.

pgmi loads your project files into session-scoped views, then runs deploy.sql:

• pg_temp.pgmi_source_view — raw access to all files (for introspection)
• pg_temp.pgmi_plan_view — files sorted by execution order (for deployment)

The basic template queries pg_temp.pgmi_source_view (raw file access); the advanced template uses pg_temp.pgmi_plan_view (metadata-driven ordering).

migrations/ — your SQL files#

These are regular SQL files — no annotations or special comments required. The basic template creates a "user" table (001_users.sql) and CRUD functions (002_user_crud.sql).

Step 4: Deploy#

Make sure your password is set (see Step 3), then:

pgmi deploy . --overwrite --force

What this does:

• . means “this directory” (where pgmi.yaml and deploy.sql are)
• --overwrite allows dropping and recreating the database if it already exists
• --force replaces interactive confirmation with a 5-second countdown (you can still press Ctrl+C to cancel)

Note: --overwrite is for local development. In production, deploy incrementally without this flag.

CI/CD: pin the pgmi session API with --compat 1 so a pgmi upgrade can’t change your deploy’s behavior, and drop --overwrite for incremental deploys:

pgmi deploy . -d myapp --compat 1 --force

See the CI/CD Guide for a complete pipeline example.

You should see output including test notices and ending with an ASCII art “DONE” banner:

NOTICE: Dev seed: admin user ready (admin@example.com id=1)
NOTICE: [pgmi] Test suite started
NOTICE: [pgmi] Fixture: ./__test__/_setup.sql
NOTICE: [pgmi] Test: ./__test__/test_user_crud.sql
NOTICE: [pgmi] Test suite completed (...)

  ___   ___  _  _ ___
 |   \ / _ \| \| | __|
 | |) | (_) | .` | _|
 |___/ \___/|_|\_|___|

Your database myapp now exists with a "user" table, CRUD functions, and the built-in tests have already verified they work.

Step 5: Verify the deployment#

Using pgAdmin#

1. In the left panel, right-click “Databases” → Refresh
2. You should see a new database called myapp
3. Open the Query Tool (Tools → Query Tool) and run:

SELECT * FROM get_user('admin@example.com');

You should see the admin user record with email = 'admin@example.com' and name = 'Administrator'.

Using the terminal#

psql -h localhost -U postgres -d myapp -c "SELECT * FROM get_user('admin@example.com');"

 id |        email        |     name      |          created_at
----+---------------------+---------------+-------------------------------
  1 | admin@example.com   | Administrator | 2026-...

Try it with a custom admin email parameter:

pgmi deploy . --overwrite --force --param admin_email=you@example.com
psql -h localhost -U postgres -d myapp -c "SELECT * FROM get_user('you@example.com');"

You just deployed a PostgreSQL project with pgmi.

Step 6: Add a new migration#

Create a new file migrations/003_orders.sql:

CREATE TABLE IF NOT EXISTS "order" (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES "user"(id),
    total NUMERIC(10,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Deploy again:

pgmi deploy . --overwrite --force

Check the database (pgAdmin: refresh, or terminal):

psql -h localhost -U postgres -d myapp -c "\dt"

You should see both the user and order tables. The basic template uses CREATE OR REPLACE / IF NOT EXISTS patterns, so you can also deploy without --overwrite for incremental changes — though during early development, --overwrite --force is the simplest approach.

What just happened?#

Here’s the entire model in four points:

1. pgmi loaded your files (everything in the project folder) into PostgreSQL temporary tables and views (pg_temp.pgmi_source_view, pg_temp.pgmi_plan_view)
2. pgmi ran deploy.sql, which queries those views and directly executes files using EXECUTE v_file.content
3. Your SQL files are regular SQL — no framework conventions, no special syntax
4. deploy.sql is the only thing that decides what runs, in what order, with what transaction boundaries. Not a config file. Not pgmi. Your SQL.

This is what makes pgmi different from migration tools: PostgreSQL itself is the deployment engine. You write the logic in SQL, and pgmi just provides the infrastructure to get your files into the database session.

Choosing a template#

pgmi provides two templates for pgmi init. Start with basic when you want a small migration scaffold. Reach for advanced when you want a large, editable reference system — metadata tracking, roles, schemas, audit logging, and API/MCP patterns you adapt to your needs. Advanced is not a “more production” version of basic; it’s a different, richer starting point you own and trim. Either can be production depending on the project — advanced is more complete, not more production.

When to use basic:

• Learning pgmi
• Simple projects with < 20 SQL files
• Linear migrations without complex ordering needs

When to use advanced:

• You want pre-built patterns for script tracking, idempotency, role separation, audit logging, or API/MCP integration — and you’re comfortable adapting or removing what doesn’t apply
• Large projects with explicit execution phases
• Teams that benefit from script tracking and audit logging
• Projects integrating with AI assistants via MCP

Advanced template requirements:

• PostgreSQL 15+ (the RLS model relies on security_invoker views)
• A role with CREATEROLE + CREATE EXTENSION for initial setup — no superuser (creates owner, admin, api, customer roles). The admin role on managed providers (rds_superuser, azure_pg_admin, supabase_admin, …) carries these grants.
• Extensions: uuid-ossp, pgcrypto, pg_trgm, hstore (all available on managed clouds — RDS, Azure Flexible Server, Cloud SQL, Supabase, Neon)

Switching templates:

You can migrate from basic to advanced later:

1. Run pgmi metadata scaffold . --write to add metadata blocks to existing files
2. Adjust idempotent flags (migrations → false, functions → true)
3. Update deploy.sql to query pg_temp.pgmi_plan_view instead of pg_temp.pgmi_source_view

See Metadata Guide for details on the migration process.

Next steps#

Now that you have a working project:

Troubleshooting#

“pgmi: command not found”#

Your Go bin directory is not in your PATH. See Step 1 for instructions.

“connection refused” or “could not connect to server”#

PostgreSQL is not running or not listening on the expected host/port. Verify in pgAdmin (see Step 2 ) or check your OS service manager.

“password authentication failed”#

The password you set via PGPASSWORD or PGMI_CONNECTION_STRING doesn’t match your PostgreSQL user. Try connecting with pgAdmin or psql first to confirm the correct password.

“database already exists” (without –overwrite)#

Without --overwrite, pgmi creates the database if it doesn’t exist and deploys to it if it does — this is normal incremental deployment. The --overwrite flag is only needed when you want to drop and recreate the database from scratch (local development, CI).

“deploy.sql not found”#

You’re running pgmi deploy from the wrong directory. Make sure you’re inside your project folder (where deploy.sql is), or pass the path explicitly: pgmi deploy ./myapp.

“permission denied” when creating database#

Your PostgreSQL user needs the CREATEDB privilege. In pgAdmin: right-click your login role → Properties → Privileges → check “Can create databases”. Or via SQL:

ALTER ROLE postgres CREATEDB;