Next-auth: How does database migration work?

[adarnon]

I couldn't find a way to manage a production DB. The ?synchronize=true option is OK for development, but obviously it is risky in production settings.
Is there some way to generate migrations so that I can configure a production database before deploying my app?

[iaincollins]

Not yet, but this is planned at some point (probably after initial release).

A one time initial setup of manually exporting the table schema from a test database into the production database is the approach I would recommend for now (or just running once with ?synchronize=true if it's a new DB).

There have been some side discussions about this in other tickets or PRs, but it's not been captured as work yet. Our first step will probably be automating checking table structure across supported databases, as a test, and we can at least document those somewhere.

The current thinking is that there will be a CLI tool for this, that will handle initialising and upgrading safely. When we have that I intend to remove all references to ?synchronize=true from the examples / docs.

A tool specifically for migration support may not come until the first release we do that actually changes the structure; generally I do not anticipate there will be too many of these.

(This is not counting the beta of 2.0 which may have breaking changes to allow for rapid development; once 2.0 is released and out of beta will go slower and more carefully.)

[lnikell]

Thank you for the great product @iaincollins. Had the same issue today, that were not able to find documented tables definition, so I ran synchronize=true at first and then converted it into migrations.
From the structure I see now there are no indexes/fk that have set during initialization of DB data. I'm looking on it there https://github.com/iaincollins/next-auth/blob/master/src/adapters/typeorm/models/account.js. Also should be a good thing to improve

Definitely upvote for changing the current approach from auto sync to migrations.
And for those who probably will need table structure:
verification_request table:

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS verification_request_id_seq;

-- Table Definition
CREATE TABLE "public"."verification_request" (
    "id" int4 NOT NULL DEFAULT nextval('verification_request_id_seq'::regclass),
    "identifer" text NOT NULL,
    "token" varchar NOT NULL,
    "expires" timestamp NOT NULL,
    "created" timestamp NOT NULL,
    PRIMARY KEY ("id")
);

account table:

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS account_id_seq;

-- Table Definition
CREATE TABLE "public"."account" (
    "id" int4 NOT NULL DEFAULT nextval('account_id_seq'::regclass),
    "compoundId" varchar NOT NULL,
    "userId" int4 NOT NULL,
    "providerId" varchar NOT NULL,
    "providerType" varchar NOT NULL,
    "providerAccountId" varchar NOT NULL,
    "refreshToken" text,
    "accessToken" text NOT NULL,
    "accessTokenExpires" timestamp,
    "created" timestamp NOT NULL,
    PRIMARY KEY ("id")
);

user table:

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS user_id_seq;

-- Table Definition
CREATE TABLE "public"."user" (
    "id" int4 NOT NULL DEFAULT nextval('user_id_seq'::regclass),
    "name" varchar,
    "email" varchar NOT NULL,
    "image" varchar,
    "created" timestamp NOT NULL,
    PRIMARY KEY ("id")
);

session talbe:

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS session_id_seq;

-- Table Definition
CREATE TABLE "public"."session" (
    "id" int4 NOT NULL DEFAULT nextval('session_id_seq'::regclass),
    "userId" int4 NOT NULL,
    "expires" timestamp NOT NULL,
    "sessionToken" varchar NOT NULL,
    "accessToken" varchar NOT NULL,
    "created" timestamp NOT NULL,
    PRIMARY KEY ("id")
);

[iaincollins]

From the structure I see now there are no indexes/fk that have set during initialization of DB data. I'm looking on it there https://github.com/iaincollins/next-auth/blob/master/src/adapters/typeorm/models/account.js. Also should be a good thing to improve

Thanks!

Please note the database schema is in flux and will be until release - in fact the schema has already changed slightly in master, and is likely change in a release that drops in the next few day. The changes are mostly fairly minor; with the exception that email addresses will become optional to support using oAuth providers that don't return them (but you can still choose to enforce providing them if you wish).

There are no change logs until we get to release, so folks using the beta will need to keep an eye on the documentation on each update and understand there will be breaking changes on a regular basis until it is final (there will be official documentation before release!).

To clarify there is no migration script currently, because there is nothing to update to or from as the beta is still pre-release software and breaking changes to the API should still be expected. I was hoping to postpone them till later, but it's less painful to delay the release a bit than to release a breaking update very soon after release.

RE: Indexes / relational mappings

By release, all fields that should have indexes on them should be there on supported databases, however I should add that relational mapping is not planned as the focus is on support for multiple very different backends rather than optimising database metadata for a specific backend.

How to extend and customise models and schemas will be part of the documentation that drops before release though. (This is currently possible, it's just not documented.)

At some point in future dedicated adapters that have more sophisticated support for specific databases might be developed.

[lnikell]

Thank you @iaincollins for the reply. Is there any chance before the release that you can consider moving to a more common practice in naming sql tables/fields rather than use JS style.

Usually, for fields name the common practice to use lower_case_underscored names and for tables also lowercase but in plural form.

Orms usually provide a way to continue to use camelCase while having underscored names in the database.

[iaincollins]

I think that's a very reasonable request but have been concerned about adding additional complexity to the already monkey patched models.

However, it looks like it's possible to apply a naming strategy with TypeORM and we could do that conditionally (we could even use a pre-canned model.

[iaincollins]

Note: You can already use the namingStrategy option in TypeORM with NextAuth.js, but I don't know how well it will work in practice.

You can track this is #245.

If there are any other feature requests, please create them as a new issue.

[iaincollins]

Update for anyone following this issue - Request for Comment on DB schema changes at #254

The Pull Requested linked to also includes built in snake case support for SQL databases, while maintaining camel case for Document databases like MongoDB.

A custom naming strategy (and custom models / schemas) can still be specified.