save things in MySQL

main.go

@@ -1,6 +1,7 @@
 package main
 
 import (
+	"database/sql"
 	"encoding/json"
 	"fmt"
 	"log"
@@ -10,6 +11,7 @@ import (
 	"time"
 	"github.com/go-webauthn/webauthn/webauthn"
 	"github.com/joho/godotenv"
+	"github.com/go-sql-driver/mysql"
 )
 
 var (
@@ -49,9 +51,12 @@ func main() {
 	host := getEnv("HOST", "localhost")
 	port := ":" + getEnv("PORT", "8080")
 	origin := fmt.Sprintf("%s://%s%s", proto, host, port)
-	dbUser := getEnv("DB_USER")
-	dbPassword := getEnv("DB_PASSWORD")
-	dbName := getEnv("DB_NAME")
+	cfg := mysql.NewConfig()
+    cfg.User = os.Getenv("DB_USER")
+    cfg.Passwd = os.Getenv("DB_PASS")
+    cfg.Net = "tcp"
+    cfg.Addr = "127.0.0.1:3306"
+	dbName := os.Getenv("DB_NAME")
 
 	l.Printf("[INFO] make webauthn config")
 	wconfig := &webauthn.Config{
@@ -66,17 +71,46 @@ func main() {
 		os.Exit(1)
 	}
 
-	db, err := sql.Open("mysql", dbUser + ":" + dbPassword + "@/")
+	db, err := sql.Open("mysql", cfg.FormatDSN())
 	if err != nil {
 		panic(err)
 	}
+	defer db.Close()
 	// See "Important settings" section.
 	db.SetConnMaxLifetime(time.Minute * 3)
 	db.SetMaxOpenConns(10)
 	db.SetMaxIdleConns(10)
+	_, err = db.Exec(fmt.Sprintf("CREATE DATABASE IF NOT EXISTS %s", dbName))
+	if err != nil {
+		log.Fatalf("Failed to verify/create database: %v", err)
+	}
+
+	// 3. Switch your current connection pool to the database
+	_, err = db.Exec(fmt.Sprintf("USE %s", dbName))
+	if err != nil {
+		log.Fatalf("Failed to switch database: %v", err)
+	}
+
+	query := "CREATE TABLE IF NOT EXISTS users (" +
+		"username 				  VARCHAR(64)	NOT NULL," +
+    	"created_at               TIMESTAMP		NOT NULL DEFAULT CURRENT_TIMESTAMP," +
+//    	"last_used_at             TIMESTAMP		NULL," +
+    	"rpid                     VARCHAR(64)	NOT NULL," + // -- Relying Party ID" +
+    	"kid                      VARBINARY(64)	NOT NULL," + // -- Credential.ID" +
+    	"aaguid                   CHAR(36)		NULL," + // -- Authenticator.AAGUID" +
+    	"public_key               BLOB			NOT NULL," + // -- Credential.PublicKey (encrypt at rest)" +
+    	"attestation_type         VARCHAR(32)	NOT NULL," + // -- CredentialAttestation.AttestationType" +
+    	"attestation_format       VARCHAR(32)	NOT NULL," + // -- CredentialAttestation.AttestationFormat" +
+    	"attestation              BLOB			NULL DEFAULT NULL" + // -- CredentialAttestation serialized as Message Pack or JSON (encrypt at rest)" +
+		")"
+
+	_, err = db.Exec(query)
+	if err != nil {
+		log.Fatalf("Failed to verify/create table: %v", err)
+	}
 
 	l.Printf("[INFO] create datastore")
-	datastore = NewInMem(l)
+	datastore = NewInMem(l, db)
 
 	l.Printf("[INFO] register routes")
 	// Serve the web files