testing.go

func NewTempSQLMetadataStore(t *testing.T, prefix string) (*sql.SQL, func()) {
  // utility func to get the postgres database URL from an env var
  dbURL, err := dbutil.DBURLFromEnv("TEST_METADATA_DB_URL")
	require.NoError(t, err)

  // create a new instance of our stdlib sql wrapper
	s, err := sql.New(sql.Options{DatabaseURL: dbURL, PerPage: uint64(10)})
	require.NoError(t, err)

  // create a new database name derived from a prefix, test name and a small ID for uniqueness
	dbName := fmt.Sprintf("%s_%s_%s", prefix, t.Name(), TinyID())
	dbName = strings.ToLower(dbName)

	{ // this block gives us a new scope so we can reuse variable names like err
		var err error

		_, err = s.DB().Exec(fmt.Sprintf("create database %s", dbName))
		require.NoError(t, err)

		require.NoError(t, s.Close())

		s.DatabaseURL.Path = dbName
		require.NoError(t, s.Open())
	}

  // call setup, which loads an init.sql file as a fixture and creates the schema.
	require.NoError(t, s.Setup())

	t.Logf("created database %s", dbName)

  // return the sql instance and a cleanup callback function
	return s, func(t *testing.T, dbName string, s *sql.SQL) func() {
		return func() {
      // we can keep the database from being dropped at the end for debugging
			if os.Getenv("TEST_METADATA_DB_KEEP") == "" {
				s.DB().Exec("select pg_terminate_backend(pid) from pg_stat_activity where datname = ?", dbName)
				s.DB().Exec("drop database ?", dbName)

				s.Close()

				t.Logf("cleaned up database %s", dbName)
			} else {
				t.Logf("TEST_METADATA_DB_KEEP: %s", dbName)
			}
		}
	}(t, s.DatabaseURL.Path, s)
}

func withSQLStore(t *testing.T, fn func(s *sql.SQL)) {
	s, cleanup := testutils.NewTempSQLMetadataStore(t, "metadata")
	defer cleanup()

	fn(s)
}

func TestMyDatabaseCode(t *testing.T) {
  // Use this to create, bootstrap and use a unique database.
  // Tx is done the same way with WithTX(func(){...})
  withSQLStore(t *testing.T, func(s *sql.SQL) {
    loadTestFixuresOrWhatever(s)
    
    data, err := s.LoadOrDoSomething()
    require.NoError(t, err)
    
    require.NoError(t, models.SomethingElseThatTakesSQL(s))
  })
}