6. Add a table
Notes need somewhere to live. In this chapter you'll create a notes table, using the same migration tools you used for SelfAuth's own tables in chapter 1.
A Go project keeps its migrations as plain SQL files, which you write yourself. The workflow is:
- Write the SQL in a new folder under
db/migrations/. - Review it with
npx selfauth db plan. - Apply it with
npx selfauth db migrate.
Write the migration
Each migration is a folder holding one file, migration.sql. Migrations run in the order of their folder names, so start the name with the date and time, then say what it does:
db/migrations/20261119120000_add_notes/migration.sqlUse today's date and time: YYYYMMDDHHMMSS. Then write the SQL for your database:
CREATE TABLE notes (
id TEXT PRIMARY KEY,
owner_id TEXT NOT NULL,
text TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX notes_owner_id_idx ON notes (owner_id);CREATE TABLE notes (
id TEXT PRIMARY KEY,
owner_id TEXT NOT NULL,
text TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX notes_owner_id_idx ON notes (owner_id);CREATE TABLE notes (
id VARCHAR(36) NOT NULL PRIMARY KEY,
owner_id CHAR(36) NOT NULL,
text TEXT NOT NULL,
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);
CREATE INDEX notes_owner_id_idx ON notes (owner_id);Line by line:
notesis the table's name. Lowercase plural is the usual style, and it keeps clear of SelfAuth's tables, which all start withauth_.owner_idholds the id of the user who wrote the note.created_atrecords when, so you can show the newest first.- The index makes "find this user's notes" fast, even with millions of rows.
MongoDB
MongoDB doesn't need a migration for a new collection. It appears the first time you save a document. You only need the index on ownerId: open cmd/migrate/main.go, and just after the store.Migrate(...) call and its error check, add:
// Your own indexes (MongoDB only): created here, with SelfAuth's, never when the server starts.
if database == "mongodb" {
conn, err := store.Connect(ctx, database, url)
if err != nil {
return err
}
defer conn.Close()
notes := conn.DB.Mongo.Collection("notes")
if _, err := notes.Indexes().CreateOne(ctx, mongo.IndexModel{Keys: bson.D{{Key: "ownerId", Value: 1}}}); err != nil {
return err
}
}and these imports at the top:
"go.mongodb.org/mongo-driver/v2/bson"
"go.mongodb.org/mongo-driver/v2/mongo"Then run npx selfauth db migrate as below.
Review and apply
npx selfauth db planPending migrations (1):
• 20261119120000_add_notes (db/migrations/20261119120000_add_notes/migration.sql)
✓ Nothing here loses data or touches SelfAuth's tables.plan reads every migration that hasn't run yet and looks for statements that could lose data (dropping a table or a column, changing a type) and for changes to SelfAuth's own tables. A new table of yours does neither, so it's clear. Apply it:
npx selfauth db migratePending migrations (1):
• 20261119120000_add_notes (db/migrations/20261119120000_add_notes/migration.sql)
✓ Nothing here loses data or touches SelfAuth's tables.
Applied: [20261119120000_add_notes]
✓ Migrations applied.Check it worked
npx selfauth db statusapplied: [20261116085533_selfauth_init 20261119120000_add_notes]
pending: []If something went wrong
- A syntax error from the database. The SQL is for another database: check you used the tab for yours. Nothing was applied, so fix the file and run
migrateagain. - The migration doesn't show up as pending. The file must be called exactly
migration.sql, inside its own folder directly underdb/migrations/. - You made a mistake in a migration you haven't applied yet. Fix the file and run
planagain. Once a migration has been applied, don't edit it. Add a new one instead (that's the next chapter but one). If you edit one by accident, SelfAuth notices and stops:migration 20261119120000_add_notes was applied and has been edited since; add a new migration instead of changing an applied one.