Create Database for Content

[RyanNovas]

Goal: We need a database so that we can add additional content. It should probably be PostGres or MongoDB.

Content will include additional lessons composed of vocab words, prompts, definitions, and win points (e.g. 6/10 words used).

[wlaurance]

I would lean towards using Postgres because it is a solid database and there is use of it already in Quill, https://github.com/search?l=&q=postgres+user%3Aempirical-org&ref=advsearch&type=Code .

With Postgres 9.4, JSON support is better than ever. Complete with indexing and functions. http://www.postgresql.org/about/news/1522/ http://www.postgresql.org/docs/9.4/static/datatype-json.html

I've recently gathered some thoughts about node and postgres here, http://www.wlaurance.com/2014/04/Using-PostgreSQL-and-Node/ caution examples are in coffee script ;) Here I talk about a migration tool, the actual node-pg library, and the module to generate sql from JavaScript.

We could using an existing Postgres server and create a new database for stories-with-friends or run a separate server. If we want to utilize the new JSONB features, we will need a 9.4 server.

Once we have a database, create a table with the either the JSON type or for now just use a text type and JSON.parse and JSON.stringify to deserialize/serialize.

We can build model code with https://github.com/brianc/node-sql which will generate SQL statements.

I would like to take the time to properly use a db-migration tool and establish a nice pattern for the model code so someone else can easily extend things.

Since Postgres is already used in the Quill ecosystem, I see no reason to through another datastore in the mix. Postgres has one of the cleanest code bases around and is very solid.

[petergault]

This sounds great, I definitely agree that we should stick with Postgres. @quinn Do you have thoughts on this?

[TGOlson]

I don't have a huge problem with Postrges (I'm a big Rails guy, after-all), I just don't really see the benefit here. Node and Mongo pair very well without any need to worry about bringing in a bunch of different modules, plus Mongo is generally preferred over relational databases for storing large text blobs.

My perspective is, unless you have a good reason to do otherwise, take the easy shot (Mongo).

[quinn]

I'd rather not add another database into the mix with no clear benefit (especially considering JSON in pg 9.4). If we were to consider a non-relational database there would have to be a strong argument for it (a need for massive scaling, massive I/O, realtime / operational transformation, etc) but Mongo wouldn't end up being a fit for these scenarios either way. For standard web application scenarios we will go with postgres, even if it requires a little extra work to fit into node.

[wlaurance]

I spent some time vetting Bookshelf.js which is an ORM for SQL. It follows a very similar pattern as Backbone.js and would eliminate us from having to write any SQL.

It is built upon Knex which is the layer that emits dialect specific SQL statements and executes them using the database driver of your choosing, pg in our case.

Knex also features a built-in migration tool with a CLI very similar to rake db:migrate, http://knexjs.org/#Migrations

Both Knex and Bookshelf are used by the Ghost blogging platform. They have a large migrations abstraction https://github.com/TryGhost/Ghost/tree/master/core/server/data, but this can be simplified greatly.

I have no problem using mongo for the described data, but even now I can see relational data. My biggest push for Postgres here, is to not introduce another technology for the Quill ecosystem.

I've worked on projects before where each small project had it's own datastore and it was a hassle to maintain. Especially if someone with specific knowledge left the team.

[quinn]

I've used sequelize before on projects: http://sequelizejs.com/ it has AR style migrations as well as "automatic" migrations akin to datamapper.. they seem to have similar APIs but bookshelf's might be more "native" feeling (new Post vs. Post.build()) I don't have a preference here, just offering an alternative.

Let's move forward with postgres, @wlaurance pick an ORM that seems the most flexible and mature (take into considering frequency of commits / # of downloads, etc if you haven't already), it'd be great to use the same one going forward on other node projects.

[RyanNovas]

@quinn, can we use the database we've already set up for Quill? Or will this have to be a new one?

[TGOlson]

Postgres is fine, obviously the call has been made. I have to ask though, if you are so skeptical about introducing new technology, why is this app in Node? Wouldn't Rails make more sense, since Quill is made in that?

[quinn]

if we were to consider a replacement technology for the primary data store there would have to be a strong argument for it, and we would have to consider the other technologies in the space. Almost always a sql DB is best for this sort of general scenario. If we needed a database for analytics, caching, search, realtime, etc we would find the best technology for the job. Considering all of this, picking Mongo for the reason that it has a good node client would be an uninformed decision.

I'm also not familiar with the argument that mongo is the preferred storage for large text blobs. I'm curious about this so please link any relevant documentation. especially if it's not written by 10gen ;)

[TGOlson]

Quinn, that's fine. My previous question was regarding why we are using Node to build the app instead of Rails, since Rails is the framework Quill is built on.

I'm fine with the choice of Postgres, it just sounds like the primary reason for using it - because it was used before - is a conflicting stance to the use of Node.

[quinn]

sorry, I should have addressed your question more directly: since postgres is best for the job, previous technology choices are irrelevant. I tried to address specifically your statements for why Mongo may be a better fit: it has a good / widely used client for node and it is better for storing text blobs. i'm happy to discuss this further (I want to make sure everyone is on board) but let's move it to email: q.shanahan@gmail.com

[RyanNovas]

Hey, this conversation seems like it is covering some great issues. Since we are open-source and want everything to be as publicly visible as possible, let's keep the discussion in GitHub rather than moving to private email. This way if other contributors ever have similar thoughts they can reference the issue.

On Thu, Jun 12, 2014 at 6:06 PM, Quinn Shanahan notifications@github.com wrote:

sorry, I should have addressed your question more directly: since postgres is best for the job, previous technology choices are irrelevant. I tried to address specifically your statements for why Mongo may be a better fit: it has a good / widely used client for node and it is better for storing text blobs. i'm happy to discuss this further (I want to make sure everyone is on board) but let's move it to email: q.shanahan@gmail.com

— Reply to this email directly or view it on GitHub https://github.com/empirical-org/Stories-With-Friends/issues/20#issuecomment-45954138 .

Ryan Novas Chief Operations Officer

Quill: Interactive Grammar Learn English grammar by writing sentences and proofreading passages. http://quill.org

Connect with us on Twitter! @Quill_Grammar https://twitter.com/Quill_Grammar

Learn more about Quill: http://youtu.be/BA6jaeberMg

[TGOlson]

@quinn Ok, I guess I was getting sidetracked by the comments about Postgres being the best choice because that is what is currently used. My take is that technology is cheap, databases are cheap unless you are doing in-house database admin, which I'm thinking you aren't - so we certainly agree on that.

If Postgres is the best choice because of performance - boom, easy. If it's being chosen because it's what was used before than I am (or, should say, was) confused.

[spacez320]

So to sum up the discussion so far, PostgreSQL + a yet-to-be-decided ORM. Has there been any further discussion on what that middleware should be?

Is this issue still relevant after storage considerations when using Compass, or will there still be plenty of data to keep more locally?

[quinn]

This one is on hold for now

On Monday, June 16, 2014, Matthew Coleman notifications@github.com wrote:

So to sum up the discussion so far, PostgreSQL + a yet-to-be-decided ORM. Has there been any discussion on what the middleware should be?

Is this issue still relevant after storage considerations when using Compass, or will there still be plenty of data to keep more locally?

— Reply to this email directly or view it on GitHub https://github.com/empirical-org/Stories-With-Friends/issues/20#issuecomment-46247423 .

[wlaurance]

After last night's discussion, https://docs.google.com/document/d/1IP08EbfTxWPMr6khbra25XKmkyyeKQeEG93pE59T5lA/edit, we have decided to use a backend storage engine for Stories with Friends. The main motivation is storing all of the students completed stories.

[petergault]

This is now closed as we the Empirical Core API will handle all data storage.