uuid encoding sqlite

main.go

package main

import (
	"database/sql"
	"log"

	"github.com/google/uuid"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, err := sql.Open("sqlite3", "file:./db.sqlite3")
	if err != nil {
		panic(err)
	}
	defer db.Close()
	if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS db1 (id TEXT PRIMARY KEY);`); err != nil {
		panic(err)
	}
	if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS db2 (id BLOB PRIMARY KEY);`); err != nil {
		panic(err)
	}

	{
		u, err := uuid.Parse("b23b679a-02f2-4ea9-b6d6-3068baebc1b2")
		if err != nil {
			panic(err)
		}
		log.Printf("upserting %s into db1...", u)
		_, err = db.Exec(`INSERT INTO db1 (id) VALUES (?) ON CONFLICT DO NOTHING;`, u)
		if err != nil {
			panic(err)
		}
		log.Printf("upserting %s into db1...", u)
	}

	{
		u, err := uuid.Parse("49e21297-7a0c-436e-9bed-a34efa063bc5")
		if err != nil {
			panic(err)
		}
		log.Printf("upserting %s bytes into db1...", u)
		_, err = db.Exec(`INSERT INTO db1 (id) VALUES (?) ON CONFLICT DO NOTHING;`, u[:])
		if err != nil {
			panic(err)
		}
		log.Printf("upserting %s  bytes into db1...", u)
	}

	{
		u, err := uuid.Parse("ad8d4a02-0ed0-4f08-9008-fdfd8fb00071")
		if err != nil {
			panic(err)
		}
		log.Printf("upserting %s into db2...", u)
		_, err = db.Exec(`INSERT INTO db2 (id) VALUES (?) ON CONFLICT DO NOTHING;`, u)
		if err != nil {
			panic(err)
		}
		log.Printf("upserting %s into db2...", u)
	}

	{
		u, err := uuid.Parse("3af4fceb-fa12-4bd0-bd14-2077bdcf1625")
		if err != nil {
			panic(err)
		}
		log.Printf("upserting %s bytes into db2...", u)
		_, err = db.Exec(`INSERT INTO db2 (id) VALUES (?) ON CONFLICT DO NOTHING;`, u[:])
		if err != nil {
			panic(err)
		}
		log.Printf("upserting %s  bytes into db2...", u)
	}

}


/*
$ strings db.sqlite3
SQLite format 3
Ytabledb2db2
CREATE TABLE db2 (id BLOB PRIMARY KEY)%
indexsqlite_autoindex_db2_1db2
Ytabledb1db1
CREATE TABLE db1 (id TEXT PRIMARY KEY)%
indexsqlite_autoindex_db1_1db1
Ub23b679a-02f2-4ea9-b6d6-3068baebc1b2
U	b23b679a-02f2-4ea9-b6d6-3068baebc1b2
Uad8d4a02-0ed0-4f08-9008-fdfd8fb00071
U	ad8d4a02-0ed0-4f08-9008-fdfd8fb00071
*/


// We can see that regardless if we pass a byte slice or uuid.UUID the data will
// still be encoded a uuid string. Therefore in order to store bytes, the blob
// datatype must be used.