Next-auth: Request For Comments - Database Schema

[iaincollins]

Please take a look at the database schema documentation and leave feedback here.

Of primary interest is the feedback on types used for reach property.

We are not too interested in bike shedding over property names, except in cases where there is a strong supporting citation (e.g. document from a standard bodies); no personal preferences please - but if you can point to common conventions or standards and include citations, please do!

Please bear in mind the models are intended to work with any ANSI SQL database (as well as Document databases, like MongoDB, with some transformation); so features that tend to be database specific (such as relationship mapping) are out of scope.

You can find more documentation about the schema at https://next-auth.js.org/schemas/models

[iaincollins]

Update: The new schema changes have gone out in the latest beta, so I've unlocked this thread so comments can happen here, rather than in the PR (as the PR is now merged and closed).

[LoriKarikari]

In DBs they usually use created_at and updated_at or their camelCase variants for NoSQL instead of created and updated. Don't want to nitpick over names as you said but it's nice to have some consistency with the rest of your database.

[iaincollins]

Thanks for looking and feeding back! Yeah, I was torn over that one…

I've always used created_on / updated_on as that used to be really common, but it does seem like created_at is more popular, at least these days?

I didn't have any hard evidence and could find any anything like StandardJS but for SQL naming conventions, so I tried to avoid it by not using either _on or _at.

I think I saw on my feed that you also work on a database related tool, so I'm guessing you are more in touch with what is the norm these days than I am!

If we think _at is better and nobody contests that happy to change it!

[LoriKarikari]

Also why the several not null default null? This is the same as just saying that the field can be nullable as you add a default value that is null anyway. Or is there any reason for this?

[iaincollins]

Oh! I think that is a misleading artifact of how have abstracted the metadata for the types.

I was planning to update the docs with the actual SQL used to create the tables (including indexes) at some point when I figure that out.

It's easy to get the structure with MySQL but it's harder with Postgres as it doesn't have a DESCRIBE command in SQL. It does have \d in the command line app, but that is harder to access from an integration test.

I'm was thinking if seeing if I could capture the SQL TypeORM generates and using that for documentation and in the tests; it would be more complete and more useful.

[iaincollins]

Hmm maybe these documentation pages are a good use case for the tab view in the docs so we could all the schemas all on one page and people could toggle between the data structures for different databases using the tabs?

[LoriKarikari]

Haha okay, I was going through the docs while writing a migration script for Postgres. That's why I stumbled upon this. Also did you add foreign keys? I wasn't really sure because you said in the top post that you didn't add relationships.

[iaincollins]

Thought I'd write up some thoughts, interested on where you agree / disagree!

Sorry for wall of text.

Foreign Keys

I didn't add Foreign Keys because of the potential complexity it might cause to support, particularly if it turns out it's inconsistent between databases. I was also concerned about not being able to test it throughly.

I feel like it would be good candidate to add when there are automated tests for flows, so we could more easily test lots of different flows against different databases, and it would be okay as a post launch change, because it wouldn't (in theory) be a destructive change?

Supported in NextAuth.js

Just to recap (for anyone else reading this) currently we support:

• mysql
• postgres
• mongodb
• sqlite

MySQL and Postgres and MongoDB should be functionally the same - with the same constraints and same properties on objects.

The key differences between them are MongoDB uses camelCase for collections and MySQL and Postgres use snake_case and types across all three are functionality similar but not quite exactly equivalent.

_NB: SQLite is a 'workalike' intended for local development so is a 'best effort', it works, and the demo uses it, but it's mostly not a good choice for a production database._

If you use any other database it assumes ANSI SQL (with snake_case for tables and objects).

Supported by TypeORM (and maybe in NextAuth.js…)

Of the other databases that TypeORM supports, these are the only ones I think we might want to add support for in future, if we can test them:

• mssql – Should work if recent version (must support duplicate NULL values in UNIQUE column)
• oracle – Should work already
• sqljs – No idea, and just intended for testing, but am thinking of switching the demo to this instead of SQLite as I think SQLite has extra dependancies on Windows (like Python?)

Supported by TypeORM but not in NextAuth.js

The other databases supported by TypeORM are all mobile related:

• cordova
• expo
• react-native

While if I try really hard I can think of a use case for supporting these, I think they are extreme edge cases (you'd have to be building a mobile app in Next.js to want to use NextAuth.js in one).

So, that probably limits the scope of what we need to worry about (now and in future). Anything else would need to use a different adapter.

Databases we might support in future?

The other databases I can think might want to add support for over time are all variations on Document databases (rather than SQL). Not yet clear how best to handle sharing data for models in this scenario.

• Prisma - We already have a "working" adapter from an older version of the beta as reference
• FaunaDB
• Firebase
• Elasticsearch

Welcome any thoughts on priority and/or any other suggestions!

Some of the folks at Vercel seem to like Prisma, and have referenced it in comments / on Twitter and we already have some example code from am older beta created by @Fumler that just needs a bit of updating. There is a limit to what it is sensible to include and support in NextAuth.js but maybe there is a case for including Prisma in future?

Related: I just noticed it has createdAt in it's models too 😄

Migrations

Getting back on topic (with that context in mind), am very interested on thoughts on handling migrations with TypeORM. I've used migration script generation in other ORMs but not specifically TypeORM so I'm not sure how well it works in practice (though I have read the docs).

A single database for a project is fairly easy to support, but it's much more work to support migration multiple databases (especially with more complex changes) and not maintaining a reasonable level of feature parity would make things confusing for NextAuth.js users.

I guess there is an open question as to how much work we need to do to work out how to support migration vs just document any schema changes in a change log and release notes and get people to do it themselves.

Current challenges it seems we have around automatic migration are it seems we'd have maintain (and test) different flows for different SQL databases and I don't yet understand how to stack changes in TypeORM to support upgrading between multiple versions (e.g. v2.0 -> v2.1 -> v2.2).

[LoriKarikari]

Prisma is not a database it is a replacement for TypeOrm. Well technically it's not an ORM but you can read more about it here. It supports SQLite, Postgres, and MySQL.

[iaincollins]

Oh! I thought it was a database as a service, like FaunaDB! 🤦‍♂️

I'm not sure where I got that idea.

[LoriKarikari]

Haha yeah, it can be confusing. They started as a GraphQL API generator but pivoted. I'll read this again tonight and comment on the other points. But I'll be offline for most of the day.

[lnikell]

I would propose moving from auto-incremental id to UUID. It makes sense for security, scalability, and flexibility reason.

Issues with auto-incremental IDs:

Security

• Users by mistake won't be able to reveal their ID which will be equal to a number of users in the system. So if the system uses somewhere incremental IDs, by registering a new user you could actually get an understanding of the total number of users in the system.
• Incremental IDs could be used in brute force attacks if that ID used as a part of API call parameters for example.

Scalability

• Auto-incremental IDs don't work great for distributed db, since basically you have to make sure that record will be created only on a specific node. When with UUID record could be created in any node/instance of the db, since the ID randomly generated.

Flexibility

• It's probably won't be a case for the next-auth library, but who knows. With auto-incremental ids, you can't generate final objects on the client-side.

[LoriKarikari]

I agree @lnikell

[iaincollins]

I think this is a reasonable request, I personal prefer UUID's (and agree they are a better choice) but I don't see them being commonly used in SQL databases like MySQL or Postgres.

The primary issues seem to be performance and size related. From what I gather, the performance implications apply to MySQL but not Postgres (which doesn't have a problem with them).

I don't encounter them very often so have stuck with convention of int (4) on SQL and ObjectID on MongoDB. I like UUIDs, but I think that's where the community is at and I think it would be opinionated to use a different default, which I've tried to avoid in most cases (except perhaps around security).

Perhaps worth noting that most OAuth providers actually return ints for account IDs (including Twitter, Facebook, Google); I think this is possibly by convention that Twitter started when they created OAuth.

I am not against making this an option though.

[lnikell]

Actually I think for the last couple of years things changed a lot, most of the projects that I touched during the past few years on Postgresql had UUID, but can't say anything about MySQL since don't have enough experience with it

[iaincollins]

^ I want to add that I 100% agree with the security implications, I think incremental IDs can be problematic as notoriously they are misused and exposed publicly (which you should be avoided with any internal ID); and the impact is worse when it's an incremental ID because they are predictable.

NextAuth.js deliberately avoids this by never returning the User ID from to the front end in a session - this is why they are not included in the user object and an AccessToken associated with the session is sent instead (which can then be used to look up the User ID from the session table by server side logic).

[iaincollins]

I think I'm about ready to merge in the Pull Request to address feedback (#274) unless anyone has any additional feedback.

I've created #283 to address the issue of how we can provide a way to support custom models and potentially even an option to toggle UUIDs in the default adapter, without having to go to the hassle of custom models - e.g. uuid: true as an adapter option and applying a suitable transform - which is something it is much easier to do with the changes in the PR above.

[iaincollins]

Have merged in #274 so closing this. If anyone would like to comment on this issue in future, please raise feedback as a new issue to be sure we don't miss it! Thanks!