Supabase DB Seeder

main.go

package main

import (
	"encoding/csv"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"regexp"
	"sort"
	"strconv"
	"strings"

	"github.com/wk8/go-ordered-map/v2"
)

var FilenameRegex = regexp.MustCompile("^(\\d+)(-)(.*)$")

func readFileIntoMaps(path string) ([]*orderedmap.OrderedMap[string, string], error) {
	f, err := os.Open(path)
	if err != nil {
		return nil, err
	}
	defer f.Close()
	r := csv.NewReader(f)

	records, err := r.ReadAll()
	if err != nil {
		return nil, err
	}
	if len(records) < 2 {
		return nil, fmt.Errorf("CSV file %s does not have a header row", path)
	}

	// Create a map of column names to column values for each row.
	// The first row is the header row.

	rows := make([]*orderedmap.OrderedMap[string, string], len(records)-1)
	for i, row := range records[1:] {
		om := orderedmap.New[string, string]()
		for j, col := range row {
			om.Set(records[0][j], col)
		}
		rows[i] = om
	}
	return rows, nil
}

// createSQLInsertStatements creates SQL insert statements for the given rows.
func createSQLInsertStatements(
	name string,
	rows []*orderedmap.OrderedMap[string, string],
) ([]string, error) {
	if len(rows) == 0 {
		return nil, fmt.Errorf("no rows to insert into table %s", name)
	}

	// Create the SQL insert statement for each row.

	statements := make([]string, len(rows))
	for i, row := range rows {
		var columns []string
		var values []string
		for pair := row.Oldest(); pair != nil; pair = pair.Next() {
			columns = append(columns, pair.Key)
			values = append(values, pair.Value)
		}

		statement := fmt.Sprintf(
			"INSERT INTO %s (%s) VALUES (%s);",
			name,
			strings.Join(columns, ", "),
			strings.Join(values, ", "),
		)
		statements[i] = statement
	}
	return statements, nil
}

func handleFile(path string) ([]string, error) {
	name := strings.Replace(filepath.Base(path), filepath.Ext(path), "", 1)
	name = FilenameRegex.ReplaceAllString(name, "$3")
	rows, err := readFileIntoMaps(path)
	if err != nil {
		return nil, err
	}
	return createSQLInsertStatements(name, rows)
}

func extractLeadingNumberFromFilepath(path string) (int, error) {
	filename := filepath.Base(path)
	matches := FilenameRegex.FindStringSubmatch(filename)
	if len(matches) < 4 {
		return 1000000, nil
	}
	return strconv.Atoi(matches[1])
}

func main() {
	if len(os.Args) != 2 {
		fmt.Println("Usage: csv-seeder <supabase-directory>")
		return
	}

	supabaseDirectory := os.Args[1]
	fmt.Println("Supabase directory:", supabaseDirectory)

	// Read all CSV files in supabase/seeds and create SQL insert statements
	// for each row in each file.

	files := make([]string, 0)
	statements := make([]string, 0)
	err := filepath.Walk(
		supabaseDirectory+"/seeds",
		func(path string, info os.FileInfo, err error) error {
			if info.IsDir() {
				return nil
			}
			if filepath.Ext(path) != ".csv" {
				return nil
			}
			files = append(files, path)
			return nil
		},
	)
	if err != nil {
		log.Fatal(err)
	}

	// Sort files by filename, taking into account the leading number.
	sort.Slice(files, func(i, j int) bool {
		iNum, err := extractLeadingNumberFromFilepath(files[i])
		if err != nil {
			log.Fatal(err)
		}
		jNum, err := extractLeadingNumberFromFilepath(files[j])
		if err != nil {
			log.Fatal(err)
		}
		return iNum < jNum
	})

	for _, file := range files {
		fmt.Println("Processing file:", file)

		stmts, err := handleFile(file)
		if err != nil {
			log.Fatal(err)
		}
		statements = append(statements, stmts...)
	}

	// Get pre-seed statements from supabase/seeds/pre-seed.sql.
	preSeed, err := os.ReadFile(supabaseDirectory + "/seeds/pre-seed.sql")
	if err != nil {
		preSeed = []byte("")
	}

	// Get post-seed statements from supabase/seeds/post-seed.sql.
	postSeed, err := os.ReadFile(supabaseDirectory + "/seeds/post-seed.sql")
	if err != nil {
		postSeed = []byte("")
	}

	// Write all statements to buffer.
	content := make([]byte, 0)
	content = append(
		content,
		[]byte("-- This file was generated by csv-seeder. Do not edit directly\n")...)

	content = append(content, preSeed...)

	for _, stmt := range statements {
		content = append(content, []byte(stmt+"\n")...)
	}

	content = append(content, postSeed...)

	// Write buffer to supabase/seed.sql.
	err = os.WriteFile(supabaseDirectory+"/seed.sql", content, 0644)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("Done! Seed file written to", supabaseDirectory+"/seed.sql")
}