Remember me, DB struct (table) hints.

[frederikhors]

Issue opened for the creation of a wiki page that summarizes the doubts and problems for newbies (#210).

In README.md is suggested the use of:

• PostgreSQL arrays: http://www.postgresqltutorial.com/postgresql-array or
• specific table.

Any hint for indexes and columns?

Can we use something like:

type AuthToken struct {
  Pid   string `db:"type:varchar(100)"` // maybe an index?
  Token string // maybe an index here?
}

What are the advice of an expert to a newbie in this case?

[aarondl]

There's not much in the way of advice for newbies, you have to understand databases and be able to create a schema that's adequate to store the data for authboss by yourself. That's the level of competency that's expected. The best thing we can do (as I talked about before) is document what pieces of data need to be stored and what they look like (string? int? max lengths?).

[frederikhors]

Ok, but maybe we can also suggests a schema for postgres using SQLBoiler...

I think FAQ with examples are the best way to learn and the best way to speed up using the right tools (SQLBoiler which I love).

What do you think about?

[aarondl]

I'd be fine with a wiki page having this information. Could link to it from the readme. But I still think the most pertinent information for the README is what columns/types/tables you might need so that any database could be leveraged.

[frederikhors]

I think for simple things a model like this is enough:

type AuthToken struct {
	Pid       string `sql:"type:varchar(100)"`
	Token     string
	CreatedAt time.Time `sql:"default:now()"`
}

The created_at field I think is useful for batch operations as "delete all" tokens older than 30 days...

Does it make sense to you, @aarondl?

[aarondl]

Not sure what exactly this struct is. SQL for postgres might look like this:

create table auth_tokens (
  pid text not null,
  token text not null,
  created_at timestamp not null,

 constraint primary key (pid, token)
);

[frederikhors]

Not sure what exactly this struct is.

I'm not using sqlboiler in every project so I need to use an ORM sometimes.

So I ended up with this code (generated from ORM) for Postgresql:

DROP TABLE IF EXISTS "public"."auth_tokens";
CREATE TABLE "public"."auth_tokens" (
  "pid" varchar(100) COLLATE "pg_catalog"."default" NOT NULL,
  "token" text COLLATE "pg_catalog"."default" NOT NULL,
  "created_at" timestamptz(6) NOT NULL DEFAULT now()
);

ALTER TABLE "public"."auth_tokens" ADD CONSTRAINT "auth_tokens_pkey" PRIMARY KEY ("pid", "token");

Do you think I'm ok with this?

Do I really need a composite primary key for this table?