testdb

A repo for building test Postgres databases.

This is mostly for personal use, but feel free to use whatever you want in the repo.

Features

• Docker containers for PostgreSQL
• Migrations with dbmate
• Generate Go code from queries with sqlc
• Automatically generate fake data for any PostgreSQL table (uses types, not realistic)
• Music schema with realistic data generation

Requirements

• dbmate (for migrations)
• sqlc (for generating Go code from SQL queries)

Usage

Clone

git clone https://github.com/joeychilson/testdb

Setup

# contains POSTGRES_URL for containers
cp .env.example .env

Migration

# create new migration file
make dbnew name={migration_name}

# migrates database to latest version
make dbup

# migrates database to previous version
make dbdown

Container

# start docker container
make up

# stop docker container
make down

# destory docker container
make stop

Generate Go code

# generate Go code for both PostgreSQL and MySQL queries
make sqlc

Generate fake data

# generate fake data for any postgres sql tables
# only supports postgres for now
go run cmd/main.go autogen -t table_name -r 100

# generate realistic date for music schema
# this will generate 2 artists, 3 albums per artist, and 8 songs per album
go run cmd/main.go gen music -r 2 -a 3 -s 8

Example using generated Go code

package main

import (
	"context"
	"database/sql"
	"log"
	"os"

	"github.com/brianvoe/gofakeit/v6"
	"github.com/joho/godotenv"

	"github.com/joeychilson/testdb/db"
	"github.com/joeychilson/testdb/db/sqlc"
	"github.com/joeychilson/testdb/gen"
)

func main() {
	ctx := context.Background()

	_ = godotenv.Load()

	db, err := db.NewPostgres(ctx, os.Getenv("POSTGRES_URL"))
	if err != nil {
		log.Fatalf("failed to connect to database: %v", err)
	}

	gofakeit.Seed(0)

	var genArtist *gen.Artist
	gofakeit.Struct(&genArtist)

	artist := sqlc.CreateArtistParams{
		Name:  genArtist.Name,
		Image: sql.NullString{String: genArtist.Image, Valid: genArtist.Image != ""},
	}

	artistsID, err := g.db.CreateArtist(ctx, artist)
	if err != nil {
		log.Fatalf("failed to create artist: %v", err)
	}

	log.Printf("Created artist %s with ID", genArtist.Name, artistsID)
}