A side project I'm working on called for some form of authentication on top of a Play! Framework project. I've decided to use the securesocial library so I don't need to redesign the wheel. I'm using a MySQL database in the background, and securesocial needs some way to persist user data. The only data persistence code that comes along with it is an InMemoryUserService.scala. As the name suggests, all user info is just stored in memory with this method. I needed a way to persist it in MySQL, and after a bit of Googling, I couldn't find a database initialization script for the securesocial identity model. This is the main purpose of this post.
So I found what each securesocial class involved and what datatype each field required. Then I drew up a draft of how I thought the database would look, and it seemed to be around 5 tables: user, oAuth1Info, oAuth2Info, passwordInfo, and identity. Securesocial also has a way to store tokens, but I thought persisting those in memory is fine for now. After writing all the SQL and testing the userpassword, Google and Facebook providers, this is what I've come up with.
You'll notice the user table doesn't have much. This is where all your application-specific data will go. The userSSId column that it has does not stand for a service set id; it stands for the user's securesocial id. These can be quite different based on the provider you're using. A thing to note: securesocial allows for one user (a 1 to 1 mapping to a human in real life) to have multiple identities (not schizophrenia). For instance, Bobby Brown can log in through Facebook one day and Google the next. If you implement identity merging and then Bobby properly merges these two accounts, he becomes one user with multiple backing identities. This allows him to log in through either provider and be able to access the same data.
Feel free to check out my Play! Framework playground github repo where this database schema is used. This project also includes using Slick to interact with the data from the tables. Leave a comment if this post helped you with securesocial!