I'm a big fan of spreadsheets instead of db -- it has it's limitations of course, but works great for apps with a few thousand read-only records (items/products or even just text captions).
I built HasGluten [1, 2] with react + google spreadsheet, hosted on github for free, you get a cheap, scalable, geo-distributed software stack, with simple interfaces to maintain both code (GitHub Pages) and data (Google Sheets — also great for the non-tech).
You probably should try to do international free wiki-like open database of all foods with EAN codes. Maybe Fitbit and other vendors could chip in to provide food details.
I would love to scan all my food EAN codes and see in the computer what I have in my refrigator in calories, protein etc.
P.S. Google spreadsheet is not enough for database of all the world's food
In Brazil all food manufactures are required to put in the package whether the product contains gluten or not. Isn't that the case in the United States?
It is, but try to go to Safeway and buy a GF powerbar... :)
Jokes aside, we maintain the list mostly for ingredients. Many people, especially "novice", often ask the same questions -- or you may have doubts for strange/unfamiliar foods, for instance if you're traveling in a foreign country.
As a person who gets asked to fix these kinds of projects once they hit a wall (performance/concurrency/etc) and then have to migrate them to a proper DB platform, just stop it! Put it on in a DB up front and save some poor developer their sanity.
Want a cheap database? A Postgres RDS micro instance costs $51 a year upfront or $0.018 per hour. Medium is $200 or $0.073, and that medium instance will probably be more than enough for a dozen projects.
You get the solidity and ease of use of PostgreSQL with all the admin details abstracted away by RDS, and you can be up and running in 10 minutes or less. Get AWS account, spin up DB, write to DB.
Should one of your projects take off and require more, it's a one click upgrade. Or you can pg_dump in seconds, and rebuild it on a separate instance and account and point your app at it. And you get backups and all kinds of nice things out of the box.
I seem to be in the minority in this thread, but I personally much prefer using UPDATE/INSERT/DELETE in the command line to modify data, than clicking on a cell and typing the modification.
He mentions that its explicitly for quick prototyping. I've started a few projects but stopped when setting up the infrastructure became too tedious. For the quick and dirty prototype, this seems perfect.
I understand, but every time I get handed one of these it's because someone who didn't know what they were doing was rapid prototyping something and woops they have 200 hundred users and it's crashing, it's corrupting data, etc...
At one job I'm like "people, just use Access, it's installed on your computer" and they look at me like I'm talking dark wizardry shit with their fingers itching on their pitchforks because they don't know if I'm going to eat their babies.
I'll admit right away that I haven't looked at the API OP posted so this is a little off the cuff, but .... this is exactly the way you want to do it. If you have a well defined API, you don't care what's on the other side of it. The migration of an app from this library to a proper DB is as well defined as the API.
Imagine if you had a Google Sheets adapter for ActiveRecord. You could start any project this way and it would be super easy to then migrate it to another supported DB.
But in all seriousness, using a spreadsheet for prototyping makes perfect sense. Why waste a ton of time setting up a database when you're still figuring out what you are doing, and a spreadsheet works just fine? Yes, there's some hassle when you have to migrate, but that's compared to the hassle of setup. The amount of time to get the first iteration launched is a LOT more valuable than time down the road.
No, it wouldn't, there's more than enough stuff like this that goes around that taking one slice off the stack isn't really going to change the stack. ;)
The thing is these solutions all sounds great on paper, but in practice for the common person? Not so much. For those people that know what their doing it really doesn't matter because they know what they are doing and update as they scale.
It's the 99% of the rest of the people who see "how easy that was" and suddenly they're over their head. And they are the same people who tell you that you can't change anything about the broken ass interface bolted onto the spreadsheet while you're fixing it.
"Can't you just fix it so it will stop crashing? Why do you want to change all of this? We don't have approvals to change this, the spreadsheet is what was approved by change control. Just make it work."
Thanks! Yea I was trying to use Google Spreadsheet, getting annoyed with the JSONP and the goofy way they returned their JSON, so figured I'd just wrap it in something easier to use...
What "availability issues" did you run into? I haven't noticed any with my sheets yet
Just the spreadsheet data being intermittently down, and of course, impossible to troubleshoot. We ran processes against it fairly often, and even had it proxied to some mobile devices.
I was looking for the google spreadsheets api earlier today and I noticed it has not been migrated away from GData yet. I wonder why this is not getting any love
I've looked at AirTable. It is, unfortunately, not MS Access for the web. It's cool, but the two are not analogous.
One of the greatest things about MS Access was that it made it trivially easy to create master-detail forms where a sub-form contains records related to the master record shown in the main form.
I've had this as my startup idea for at least four years now. I'd build a web-based database product that would fit in google apps with the intention of getting bought by google and integrated into google apps.
Even if I didn't make any money on the deal, at least I'd be able to have something better for tracking the progress of the cub scouts awards. It'd also let my wife and me set up the complex budget that we're trying to do right now in spreadsheets.
I bring this up with people who work for small businesses and they all recognize the pain point. Those that are old enough remember the good old days when you could put an Access file on a network share and then run your entire business out of it.
Zoho Creator is probably the closest thing to a web-based Access product that I've seen, and it's still not quite Access. Access occupied a very strange space in between an actual application framework, a database GUI, and an IDE.
I personally would not want to be responsible for security for a web-based Access clone. Enormous attack surface area.
But I think Access is awesome for making and battle-testing prototypes that can eventually become actual CRUD apps. Great intermediate step somewhere in-between e-mailing spreadsheets and building a Rails app, for getting shit done at the office.
Agree 100% on this. To truly replicate Access online, you'll have to provide some sort of scripting interface that is moderately performant. Seems like a complete nightmare.
If you're interested, I've wanted to work on this too. In particular, creating an online, PaaS database service so you can store and manipulate data via SQL queries without needing to setup and maintain (and share across machines) your own sql server. It's something I find myself wishing I had.
The key parts of Access aren't that it can have SQL queries, it's that it (a) has a UI where you can easily edit tables (without worrying much about schemas and so on), (b) has a UI where you can plug tables into WYSIWYG-edited reports, and (c) has a UI where you can plug tables into WYSIWYG-edited forms.
Check out Oracle's Application Express (APEX). I was an MS Access developer way, way back, an Apex is a very close replacement. And, you can use it with the free version of Oracle (XE), which gives you 11GB. Plenty for small projects.
I've been looking for that too. Google Fusion Tables is a bit of what I was looking for, but too limited (and not well supported for general purpose business applications--the main use case was building maps).
Thanks for everyone's replies, I found airtable to be nice and simple for people who want to use a database, fieldbookapp a bit higher end, and APEX too confusing.
However I still can't see how to do the "getting started with SQL" type stuff, for people who don't want to use the command line for everything, but still want to use SQL in not too complex ways, maybe a more friendly version of phpmyadmin (and friends).
I thought of it since a buddy has some Tiki Bar that he wanted a site for, and I really didn't want to drop into WordPress or anything serious. Knew he could handle a spreadsheet.
Checkout http://www.tarbell.io/ - It's a CMS designed around google sheets. I think it's a bit of setup, but might be a solid solution.
In the real world I think there are 2 main use cases here.
# 1st) Using Google Spreadsheets as a CMS
In this case you'd store data in a Google Spreadsheet and retrieve the content before showing to the user. Probably it makes sense to put some durable caching in place so you can sync the cache offline and worry less about Google Spreadsheets API downtime, quotas or latency. In this scenario the app would only read data from the Spreadsheet and not write. It will probably not support writes consistently for anything more than a toy.
# 2nd) Use Google Drive to store user Data
The main difference here is that in this case it would make more sense to store the spreadsheet in the user account, not yours. You'd fetch the userData once he logs in your application. If this is the use case there are better things than writing spreadsheets to users Google Drive. There's actually a feature in Google Drive to store application data:
I really like the idea of using Google Spreadsheets as a quick and familiar GUI for entry or querying on data sets, as long as you understand the tradeoffs (write latency isn't great and they max out at 400,000 cells).
But this is especially nice when you build a layer on top of Google reduces lock-in, instead of adding another proprietary API. This is what I did with sheet-down[1], which turns a Google Spreadsheet into a LevelDB-compatible data store that can be swapped out with a file system or other compatible backend[2] once you outgrow Google.
How do you handle type constraints? For example, how do you prevent users from inputing text in to number fields? I know Google Sheets has validators, but I don't know of any way to restrict someone from changing those validations while also providing the ability to input data.
Really curious, because I'd love to use something like this in our app.
My situation is for an intranet environment, so I suppose I could just ask nicely that they don't change validations. But chances are most of my target users aren't familiar enough with Google Spreadsheets to even get that far.
I've been wanting to use Drive as a db recently on an app I'm working on as cheaper/free data storage. That is, instead of hosting a db and storing user data there, I would store it on each user's google drive. I see it as a way to save costs by not worrying about the security of my databases, cost of space/uptime/traffic, etc. What are issues am I not thinking of?
It's an interesting idea - here are some issues to consider:
1. Joining data will be very slow. If you need to access 500 database to get the "comments" on a "post", you're going to have issues.
2. How will you change the database structure as you iterate?
3. Storage cost of data is so low, that by the time you would start paying for data, you would have greatly exceeded the capabilities of Google Sheets.
4. Google sheets are slower than databases - there are no indexes, keys, the data is not stored in a way meant for most db operations (selections, etc)
I'm sure theres more but these seem to be the biggest ones for me.
That said, you should definitely try it - it's an interesting project at the very least.
Storage costs are not low. They are free since Google Spreadsheets do not count towards your Storage Limits. You'll be limited only by the 2M maximum cells a spreadsheet can hold.
I have used Google Spreadsheet as a database, the hassle is not worth it. You need to do some serious caching: insert caching, update caching etc. The API calls weren't very sturdy few years ago.
Even then you hesitate to give rights to laymen to edit the spreadsheet since everything breaks if they screw up. And what is the point if it can't be shared?
Closest any company has come to a decent web version of Access is Intuit Quickbase. But it is priced for enterprise and not hobbiest. Lacks full SQL but can do some pretty amazing things. Also has reasonable REST-like API. I built a PoC data backend for an iPhone app really easily.
We also recently released an early version of the Airtable API. It provides an API that's specific to each database you've configured in the app:
https://airtable.comhttps://airtable.com/api
Another option is of course Salesforce/Force.com. Point and click, incredible customization potential, easy to build UI and reports, point-and-click business processes, and very extensible by code if your admins can't accomplish something.
1. open google spreadsheet
2. think about your application
3. chose normal form, design schema, add integrity constraints, build indexes and query execution and optimization engine and done!
I built HasGluten [1, 2] with react + google spreadsheet, hosted on github for free, you get a cheap, scalable, geo-distributed software stack, with simple interfaces to maintain both code (GitHub Pages) and data (Google Sheets — also great for the non-tech).
[1] http://hasgluten.com
[2] https://github.com/hasgluten/hasgluten