Database Tools for Developers: GUIs, CLIs, Migrations, and Local Setup

Database Tools for Developers: GUIs, CLIs, Migrations, and Local Setup

Your database client matters more than most developers realize. A good tool makes exploration fast, catches mistakes before they hit production, and turns schema management from a chore into a reasonable workflow. Here's a survey of the best options across GUI clients, CLI tools, migration frameworks, and local development setups.

GUI Tools

DBeaver

DBeaver is the Swiss Army knife of database GUIs. It supports virtually every database (PostgreSQL, MySQL, SQLite, Oracle, MongoDB, Redis, and dozens more) through JDBC drivers. The Community Edition is free and open source.

Strengths: Universal database support, ER diagram generation, data export/import, SQL auto-completion, and a visual query builder. It handles large result sets well and has a dark theme that doesn't hurt your eyes.

Weaknesses: It's a Java application, so startup is slow and memory usage is high. The UI is functional but not elegant. Some advanced features (SSH tunneling configuration, driver management) have rough edges.

Best for: Developers who work with multiple database types and need one tool for everything.

TablePlus

TablePlus is a native database client for macOS, Windows, and Linux. It's fast, clean, and intuitive. Supports PostgreSQL, MySQL, SQLite, Redis, MongoDB, and more.

Strengths: Native UI that feels fast and responsive. Inline editing with a staging area (changes aren't applied until you commit). Multiple tabs with different connections. SSH tunneling built in.

Weaknesses: The free version limits you to 2 tabs and 2 filters. The paid license ($99 one-time) is reasonable but not everyone wants to pay for a database client. Linux support is less polished than macOS.

Best for: Developers who value a clean, fast UI and primarily work on macOS.

pgAdmin

pgAdmin is the official administration tool for PostgreSQL. It's web-based (runs as a local web app) and deeply integrated with PostgreSQL features.

Strengths: Full PostgreSQL admin capabilities (roles, tablespaces, extensions, replication monitoring). The query tool has EXPLAIN visualization. Free and open source.

Weaknesses: Web-based UI feels sluggish compared to native apps. Cluttered interface with too many options exposed at once. Not useful for non-PostgreSQL databases.

Best for: PostgreSQL DBAs who need full administrative capabilities.

Beekeeper Studio

Beekeeper Studio is an open-source database client built with Electron. It supports PostgreSQL, MySQL, SQLite, SQL Server, and CockroachDB.

Strengths: Clean, modern UI. Free Community Edition covers most needs. Query history and saved queries. Table data filtering is intuitive.

Weaknesses: Electron-based means higher memory usage than native apps. Fewer advanced features than DBeaver. Limited database support compared to DBeaver.

Best for: Developers who want a free, attractive GUI without DBeaver's complexity.

GUI Comparison

CLI Tools

For quick queries and exploration, CLI tools are faster than any GUI. These enhanced CLI clients provide auto-completion, syntax highlighting, and intelligent suggestions.

pgcli (PostgreSQL)

pip install pgcli
# or
brew install pgcli

pgcli postgresql://user:pass@localhost/mydb

pgcli provides auto-completion that's context-aware (it knows your table names, column names, and SQL keywords), syntax highlighting, multi-line editing, and query history. It's dramatically better than the stock psql client for interactive use.

mycli (MySQL)

pip install mycli
mycli -u root -p mydb

Same concept as pgcli but for MySQL and MariaDB. Auto-completion, syntax highlighting, and a pleasant interactive experience.

litecli (SQLite)

pip install litecli
litecli my_database.db

Essential for working with SQLite databases. The default sqlite3 CLI is bare-bones; litecli makes it usable.

usql

go install github.com/xo/usql@latest
usql postgres://localhost/mydb
usql mysql://root@localhost/mydb
usql sqlite:///path/to/db.sqlite

usql is a universal database CLI that supports PostgreSQL, MySQL, SQLite, SQL Server, Oracle, and more through a single interface. If you work with multiple database types and want one CLI tool, this is it.

Migration Tools

Schema migrations are how you evolve your database structure over time. The tool you choose depends on your language, ORM preferences, and whether you want SQL-first or code-first migrations.

Prisma Migrate (TypeScript/JavaScript)

bun add prisma @prisma/client
bunx prisma init

Define your schema in schema.prisma:

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

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

bunx prisma migrate dev --name add-users
bunx prisma migrate deploy  # production

Strengths: Declarative schema, auto-generated migrations, type-safe client, excellent DX. Weaknesses: Prisma's query abstraction doesn't cover all SQL features. Complex queries often need $queryRaw. The Prisma engine adds a runtime dependency.

Drizzle (TypeScript/JavaScript)

// schema.ts
import { pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name"),
  createdAt: timestamp("created_at").defaultNow(),
});

bunx drizzle-kit generate
bunx drizzle-kit migrate

Strengths: SQL-like query builder (closer to actual SQL), no runtime engine, lighter weight than Prisma. Weaknesses: Less mature ecosystem, fewer guides and examples, migration tooling is newer.

Choose Prisma for rapid development and if you value DX over SQL control. Choose Drizzle if you want to stay closer to SQL and avoid runtime dependencies.

golang-migrate

# Create migration files
migrate create -ext sql -dir migrations -seq add_users

# Run migrations
migrate -path migrations -database "postgres://localhost/mydb" up

Plain SQL migration files, no ORM, language-agnostic. Each migration is a pair of .up.sql and .down.sql files. Simple and predictable.

Flyway

flyway -url=jdbc:postgresql://localhost/mydb migrate

Java-based but works with any database. Popular in enterprise environments. Supports SQL and Java migrations. Versioned migration files with a naming convention (V1__create_users.sql).

Migration Comparison

Local Development with Docker Compose

Every developer should be able to spin up the full database stack locally. Docker Compose makes this reproducible.

PostgreSQL + Redis

# docker-compose.yml
services:
  postgres:
    image: postgres:17
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: dev
      POSTGRES_PASSWORD: dev
      POSTGRES_DB: myapp
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

  redis:
    image: redis:7-alpine
    ports:
      - "6379:6379"

volumes:
  postgres_data:

MySQL + Adminer

services:
  mysql:
    image: mysql:8.4
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: dev
      MYSQL_DATABASE: myapp
    volumes:
      - mysql_data:/var/lib/mysql

  adminer:
    image: adminer
    ports:
      - "8080:8080"

volumes:
  mysql_data:

Adminer is a lightweight web-based database manager (single PHP file). Adding it to your Compose file gives you a quick GUI at localhost:8080 without installing anything.

Tips for Local Database Development

• Use named volumes (postgres_data) so data persists across container restarts
• Mount init scripts (./init.sql:/docker-entrypoint-initdb.d/) to seed data on first start
• Match production versions exactly. Don't develop against PostgreSQL 17 if production runs 15
• Use .env files for connection strings so they're consistent across the team
• Add health checks so dependent services wait for the database to be ready:

  postgres:
    image: postgres:17
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U dev"]
      interval: 5s
      timeout: 5s
      retries: 5

Recommendations

• GUI: Start with Beekeeper Studio (free, clean) or TablePlus (paid, fast). Move to DBeaver only if you need exotic database support.
• CLI: Install pgcli/mycli for your primary database. They're strictly better than the stock clients.
• Migrations: For TypeScript projects, choose between Prisma (better DX, more abstraction) and Drizzle (closer to SQL, lighter weight). For other languages, golang-migrate or Flyway with plain SQL files.
• Local development: Always use Docker Compose. Pin database versions to match production. Include seed data for a consistent development experience.