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?
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'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.
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
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.
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.
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!
ecesena|11 years ago
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
dpweb|11 years ago
example, https://spreadsheets.google.com/feeds/list/1btWWclsRW6-wrIdC...
Ciantic|11 years ago
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
thalesmello|11 years ago
chunkiestbacon|11 years ago
bcRIPster|11 years ago
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.
Please.
crdb|11 years ago
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.
hbhakhra|11 years ago
spinlock|11 years ago
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.
cdcarter|11 years ago
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.
unknown|11 years ago
[deleted]
mooreds|11 years ago
How does blockspring deal with Google Spreadsheet availability issues? I remember the spreadsheet not always being available.
donpinkus|11 years ago
What "availability issues" did you run into? I haven't noticed any with my sheets yet
bduerst|11 years ago
We went from using Spreadsheets to using our own flat files on Drive, but the API service would throw random rejection errors for both.
Long story short, we learned that you should not try to use Drive or Google docs as a program database. It's designed first and foremost for users.
eddyparkinson|11 years ago
mavhc|11 years ago
ryanjodonnell|11 years ago
jasoncrawford|11 years ago
crazygringo|11 years ago
F_J_H|11 years ago
mooreds|11 years ago
mavhc|11 years ago
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).
troels|11 years ago
ngoel36|11 years ago
donpinkus|11 years ago
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.
pkpp1233|11 years ago
- http://blog.apps.npr.org/2014/04/23/how-we-built-borderland-...
- http://www.gamasutra.com/blogs/WillHankinson/20150323/239489...
huskyr|11 years ago
dudus|11 years ago
# 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:
https://developers.google.com/drive/web/appdata
jedschmidt|11 years ago
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.
[1] https://github.com/jed/sheet-down
[2] https://github.com/rvagg/node-levelup/wiki/Modules#storage
bradleyland|11 years ago
Really curious, because I'd love to use something like this in our app.
donpinkus|11 years ago
jlouvel|11 years ago
In the latest version, it comes with a server-side API cache to prevent GSheet latency and availability issues.
Note: I'm the founder of APISpark
eatonphil|11 years ago
donpinkus|11 years ago
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.
Ciantic|11 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?
jaybna|11 years ago
aofstad|11 years ago
cdcarter|11 years ago
Still very expensive, but free for non-profits!
eddyparkinson|11 years ago
blockspring has SQL like commands in the client. gridspree has data formatting in the client.
https://assembly.com/gridspree
ww520|11 years ago
donpinkus|11 years ago
I haven't hit a limit yet and tested with 200 queries / minute.
Will update this comment when I find that out.
grw_|11 years ago
donpinkus|11 years ago
unknown|11 years ago
[deleted]
wuyingzhong1|11 years ago
mslate|11 years ago
donpinkus|11 years ago
clucktheduck|11 years ago