What database should I use for some really minimal web application?
I need really basic storage in a small php web app, only one table (~300 records). I generally use something as postgres but in this particular case, doesn't want to deal with install and backup. I was thinking about firebase as it is free, what would you recommend ?
I'm currently rocking SQLite for the little project I'm working on. Seems ok... I know nomadlist runs on it in production, so it has some legs on it :D
For such a small table SQLite is probably the best option. Just local files, no separate server you need to manage, no connection to said server that could fail, so no failure management.
It has many benefits, especially for your use-case, because you don't need all the features 'real' databases offer.
For backups you can create a cronjob that copies the database to a different location every specified timeframe and append the current timestamp to the filename.
I second this. Unless you have to deal with lots of concurrent DB access or need to replicate / shard your DB, you'll be just fine with SQLite. I personnally use it very often for small websites.
could definitely fit, but I will still need to perform backups in some ways...
That's as easy as
echo .backup mybackupfile.sqlite3 | sqlite3.+1 for Firebase. Could also use Firestore (made by same people) if you don't need the realtime functionality of Firebase.
If you only have one table/collection, it shouldn't make too much difference.
I saw some comments here telling you to use Firebase and I have to disagree.
I'm using it for a web app because I just didn't want to build a backend. It is easy to use Firebase and it provides you some stuff that make everything shine, until you get deep.
You CAN'T (yes, in uppercase because I got so upset with this) order the queries results on RealTime Database, there's no ORDER BY (they have the Cloud Firestore now, but it is still in beta and I didn't tested it).
You can't use more than one condition in your queries
If you're using Firebase Storage, you can't list all files hosted there or even inside a specific folder; you have to save the filenames elsewhere (in the Realtime Database, as expected)
Firebase is something for a very specific range of apps. They have examples with chats, some games...... These things that need real time communication and don't care too much about ordering.
And I have to say that Cloud Functions and Hosting are amazing things on Firebase.
thanx for the feedback, I played a little with it and basicallly came to same conclusion. It seems a really good product but for specific needs. I'm affraid of being trapped by some limitations pretty quickly.
Hey @smknstd ! We've had that question ourselves! We built https://base.run to be the answer to exactly that question :)
It should be the easiest to use database to get you going on your project. Here's a few reasons we think its a good fit:
One click to create a new database (i.e. no install time)
Built in GUI for setting up, seeing & querying your data and schema (think built in php_admin or equivalent GUI)
Easy to use SDKs(currently only JS) that make working with your database like working. Check out the docs here: https://base.run/docs/index.html
Pre & post hooks (e.g. beforeSave, afterSave, beforeUpdate, afterUpdate) can trigger functions you've written, which are great for doing validations before saves, or triggering post-processing when you want to do things like look up additional data automatically based on what you've saved into your Base. This is especially easy to use in combination with the hosted Clay functions here: http://clay.run (which also have scheduled timers like CRON jobs)
Easily share access to your Base with other, just add their email to the Base's Team!
Friendly user interface lets you collaborate with non-developers as well who can manually edit / add data. This is useful especially for Hackathons where some of the team may be coders, while some may not be
It's super early on - but would love any feedback, and even more would love for people to give it a spin for a sideproject or internal tool :). What other features would you like to see here? Let us know if we can help you get going. We're hanging out over here:
https://join.slack.com/t/clay-run/shared_invite/enQtMzE1NTU4ODU2MjE1LTc0NTQ0ZTg2OTQ3Y2ZmMDhhYzNhMmJlODg2M2M2ODQwMTRkZTU3ODY3ZTI0MjFlNjFhOTM1Y2JjMjY4MThiNDI
You could spin up a project Heroku and use the free Postgres tier. Could even host the whole thing there on their free tier, but that depends on the app needs.
Second this, I start a stupid number of projects and just copy/paste some setup code each time. Takes less than five minutes from git init to a Heroku app with Postgres setup.
SQLite will be your best bet here. You have (Almost) all the main features of MySQL, no need for a running server, easy back up by saving the data files.
Firebase sounds like a good fit for your case. It's a hosted solution, so you don't have to worry about managing it.
Use firebase .. its good .. I used in many apps to collect email address and minor data .. pretty useful
Honestly, whatever you are most familiar with. You will find that MySQL is used the most for PHP apps, but really at that scale I wouldn't make this a big decision - go with whatever is quick and easy for you. If you are hosting on some cheap shared hosting, then MySQL is probably provided out-of-box.
Another +1 for firebase. I learned it for this exact reason - didn't want to spin up a whole back end.
If you're building something in PHP, MySQL is included in that tech stack in most cases (LEMP & LAMP). I would suggest using that as there are native PDO drivers built into PHP. It's also trivial to write a cron job that will backup the database as well.
Firebase can be a good solution, but it's built mostly for apps that live on the client side (JS single page apps for example) and use it as mostly an API.
Either way, stick to what you know and leverage that!
Glad to get my hands on and help you out if you need it too.
In my experience making back up is always harder than it first looks. It can seems trivial, but I would need an independant plateform to store the back up, plus it can fail for so many reasons, you have to try the restore process regularly, etc
If I would need backup I'd probably use @kenn's solution https://dumper.io/ :)
Thanks for mentioning Dumper, @smknstd ! :)
Yes, it's almost mind-boggling how often people still fail to back up in a reliable way. I saw many times that backup scripts taking empty backups after upgrading system A but not system B, then version mismatch creates near-empty backups silently (the worst part is it creates a backup, but an incomplete one). It happens with both PostgreSQL and MySQL.
I would suggest checking if backup size is increasing constantly, at least once a month. Make a monthly event on your calendar. Or use a tool like Dumper. :)
You could even go with a json file if you want ;)
Good call ! Unfortunatly I would still need to update some values in it :)
Well, that's easy with some PHP. You can use a JSON string as an array and vice versa in PHP :)