4 ms·
SQLite schema boilerplate for user accounts, roles, logins and auth tokens
- Thetawaves 10y agoPlease do not use SQLite for multiuser systems. You will have a bad time.
- koistya 10y agoWhy to build a car from the beginning, while you can start with a skateboard (free)? Lean startup, WTF :)
- juskrey 10y agoWhy not. I am having a good time with this for years.
- Thetawaves 10y agoYou must not be doing very many writes, because all writes lock the particular table from all other writers. Better databases have row level locking which allows many users to update the same table at the same time. - And this isn't all that strange, say you wanted to store a timestamp for the last time a user visited your site, session ids, etc.
- juskrey 10y agoGood point. Still with thousands of users per day I can hardly experience any difficulties. I guess with a sane app architecture and coding this is not a problem up to a million/day.. for, say, some generic CMS site. UPD: Good explanation http://sqlite.org/whentouse.html http://sqlite.org/whentouse.html
- Arcsech 10y ago> Better databases have row level locking which allows many users to update the same table at the same time. Nitpick: Databases that make different tradeoffs have row level locking. SQlite is very good, for the niche that it's in, which is a lightweight, embeddable database for primarily single-user applications. It's incredibly popular in that space, to the point of likely being the most-used database on the planet by number of running instances since it's in so many smartphones/embedded devices.
- Thetawaves 10y agoNone of the use cases you mention are 'multiuser' so I'm going to take this as just a helpful hint to the internet at large.
- gecko 10y agoIn all seriousness, ever since SQLite got WAL support, it's been fine for light multiuser systems. I would define "multiuser" as "around ten or fewer concurrent users", and I think there are some major backup considerations that go with this, but I don't think it's an inherent nonstarter.
- Thetawaves 10y agoWAL still requires a table level write lock.
- vhost- 10y agoWhy the camel cased columns?
- koistya 10y agoGood question. This db schema is supposed to be used by web developers. With camelCase columns it might be easier for developers to query the database directly from the code (unless they use an ORM, in that case snake_case should also work well).
- yeukhon 10y agoHow so? It doesn't really matter. With uppercase you now have to put quote in SQL, I am not sure other drivers, but in psql you do. Just saying.
- koistya 10y agoBTW, there is also a PostgreSQL flavor of this schema. That one is using snake_case convention for column names: https://github.com/membership/membership.db/tree/master/postgres https://github.com/membership/membership.db/tree/master/post...
- HNSucksAss 10y agoWhy not? How else would you name columns? The real question is why you'd name a key column "id." I call mine "ID." But maybe this guy's a big fan of Jung or something.
- HNSucksAss 10y agoThe naming conventions are good, except this: "Single column primary key fields should be named id" NO. They should be named ID. An id is a theoretical psychological construct; an ID is an identifier.
- henrikschroder 10y agoI hate to be that guy, but why is something as trivial and inflexible as this being voted up? It's the schema for the user accounts and logins for a specific application, but without the actual application or documentation that would explain what the idea of each table and column is? Is this meant as a "Here's how you could model a database for doing this", why is it lacking any explanations on how to use it, justifications for the choices, or examples on how the UI flow would look like? Why is a user's phone number so important that it's part of this, but not a user's last IP address or time of login? Super confused here.
- datashaman 10y agoIf you want something that is a lot more descriptive and generic, get your ideas from this. https://schema.org https://schema.org
- UnoriginalGuy 10y agoIs there any documentation for this? For example is "securityStamp" in User.sql designed to store the salt? Or is it something else entirely? There's security stamps in other frameworks which are just unique values (e.g. UIDs) which just change when a user's password changes so you can invalid old cookies without storing the hash within the cookie. If there is no salt support at all then this is inappropriate in 2016 as boilerplate.
- datashaman 10y agoYour table naming convention needs work... User model -> User table Role model -> UserRole table UserRole model -> UserUserRole table If you consider that User is the module, then your User table should be called UserUser if you want to be consistent. But that's silly. Calling your module Auth would be better. Then the table names don't contain RepeatedRepeated parts. Or even better, don't use the module name in the table name. It's pretty clear what User, Role and UserRole mean just on their own. And since you're considering using SQLite as the DB, your system will (hopefully) be small enough not to worry about naming collisions, so why bother with module scoping the tables at all?
- cookiemonsta 10y agoHow is this on the front page? Surely if you are coding something to use this, you would be able to write the 30-odd lines that these sql statements are... also table names like "UserUserRole" don't look right to me.