Decide back-end platform

[timwis]

Below are all the things I imagine the back-end will need to perform.

Reading

• Get list of tables
• Get table schema
  • Field names, types, order
  • Field validation (ie. email)
  • For foreign keys, foreign table key and display field names
• Get table rows
  • Paginate
• Filter rows
• Group/aggregate rows
• Full-text search

Writing (also notifications if other users write)

• Create new table
• Rename table
• Remove table
• Create new row
  • Validate values
• Modify one or multiple value of a row
  • Multiple if user copies & pastes multiple values
  • Validate values
• Remove row
• Create new field
  • It will always be created as a generic text type until type is set
• Modify field name
• Modify field type or validation
• Modify field order
• Remove field

Relationships

We want users to be able to associate a record in one table with a record in another table. Fieldbook takes the approach of always making this relationship many-to-many. My assumption is because that relationship is easiest for less technical users to understand: records are simply "associated" with one another, so why shouldn't a post have more than one user associated with it?

If many-to-many meets the most use cases then I think we should build for that. The question is whether there's a use case that many-to-many can't accommodate?

As far as how to accomplish this, one way (perhaps the only) is to have a joins table with left and right fields containing the record ids. Although what if you want your posts table to have an author field and an editor field, both linking to the users table? We'd need to associate the joins table records with a particular relationship, requiring a relationships table. Too complex?

Back-end options

PostgresT

• Pros: Handles relationships well. Sturdy, flexible API. Allows DB structure to be extremely portable.
• Cons: No realtime feed. We'd need to add one like this alongside it. Also if you change a field type in a relational database and one of the rows becomes invalid... what happens? Easier to handle in NoSQL.

Kinto

• Pros: Uses a postgres database underneath (though it's still a document store). Handles authentication, realtime, nice API, offline/sync support.
• Cons: No concept of relationships (NoSQL). DB structure is less portable since it's documents.

Thoughts? Other options?

[themightychris]

postgrest + lapidus gets you realtime

I don't think postgresql will let you change a field type if it will make existing values invalid

[timwis]

I started building on PostgREST but I'm looking over my shoulder now because there's no built-in way to add/alter/drop columns, so we'll have to use stored procedures (plus figure out how to send a HUP signal to the postgrest application) (#13), and that feels a bit like starting to make our own back-end...

On a separate note, I came across a related API generator from IBM called LoopBack. Probably the cool part of IBM.

[timwis]

Things are pretty well underway with postgrest. It's been abstracted to the point where I don't expect it would be too dramatic to change, but I expect we can close this issue for now.