Hi Folks,
I have been using Mysql for forever, and broadly it works fine for me, with two complaints
occasional issues with mismatches in encoding/collation on strings
hours lost on every mac upgrade trying to get mysql working properly again
I know that people are generally favouring postgresql these days, I’m wondering if anyone can comment on whether it’s worth putting in the effort to learn a new db?
all the best
Rob
Usually ActiveRecord takes care of most of the differences, and postresql has a lot of similarities with mysql. I guess it would be a different call if you ask about learning a completely different db engine like mongo or graphql, but learning postgresql shouldn’t take too much time knowing MySQL, at least to be proficient. I even have a project that I used sqlite to prototype and postresql when deploying it to heroku for tests and I had to do nothing to make it work on both databases thanks to AR.
Also, heroku prefers postrgres for example and heroku is really popular for rails hosting.
I think Ariel’s reply is spot-on. Rails is basically DB agnostic, however I think the trend is that PostgreSQL is the more favored backend (except for stuff like Wordpress). We’re doing a lot more SQL procedural stuff these days with our rails apps (e.g. kicking out JSON from a single query can be literally 1,000x times faster in cases vs. asking Rails to query a model and format the same thing). I find PostgreSQL a lot easier to admin as well, but to each their own.
Good luck!
Phil
thanks folks,
I guess I’m asking more about the practical pain points. - As you say, I trust rails to deal with most of the day-to-day.
do you end up battling to get your postgres install working after upgrading mac (or postgres?)
do you have problems with string encoding incompatibilities in obscure email addresses?
do you have a convenient gui for browsing (like SequelPro)
are there other similar annoyances, or does it ‘just work’
whatever I use, I like to stick with the same db in dev & production, ( using mysql or sqlite for my tests and postgres on the server seems like it is asking for trouble)
cheers,
Rob
thanks folks,
I guess I'm asking more about the practical pain points. - As you say, I trust rails to deal with most of the day-to-day.
do you end up battling to get your postgres install working after upgrading mac (or postgres?)
You can install Postgres as a user-space application (with an icon and everything), so this can be a non-issue.
do you have problems with string encoding incompatibilities in obscure email addresses?
If it doesn't fit into UTF-8, then you may have a problem. Do you have a problem now, in MySQL? What encoding and charset are you using now?
do you have a convenient gui for browsing (like SequelPro)
TablePlus is pretty nice.
are there other similar annoyances, or does it 'just work'
Like another commenter posted, I have used it on Heroku with SQLite on the dev side, and no issues.
whatever I use, I like to stick with the same db in dev & production, ( using mysql or sqlite for my tests and postgres on the server seems like it is asking for trouble)
It could be a problem, or it could be a sign that you are depending on a database feature to hide a bug. At work, we use SQLite for testing (including the CI stuff on GitLab) and Oracle on the production server. We try for 100% test coverage.
Walter
Sorry, using different db on different environment is not something I’m recommending, it was just to point out that the transition should be really easy. I do think it’s faster to prototype using sqlite as long as it fits the needs, but in a more mature projects I would use the same db everywhere so I can use all the features I need.
I didn’t have that many projects with postgres to talk about upgrading versions and those things, but personally I never had issues with MySQL either.
About encodings and collations, I guess it depends on the error but if you have encoding or collation errors I guess the problem is the configuration of your tables and the input and not the actual db, you may encounter problems with any db if you try to handle incompatible encodings at the same time.
I’ve used PgAdmin, but didn’t try others to compare or recommend anything.
One more thing you can do if you don’t want to struggle with updates, version, etc, is to use docker maybe, you just run the image on any system and you can replicate the actual production environment anywhere.
No, it isn't. If you notice only those 2 downside, you'd better stick with you current choice. MySQL 8 has better encoding support, I am assuming you're mentioning about emoji encodings. And you can also learn Docker for ease of development to address the other issue you have.
I also recommend running at least the DB in Docker. That should prevent most fiddling if your Mac OS changes. I use Docker for as much as I can. It ends up making things easier once you get up to speed with Docker.
Furthermore, I just use an ubuntu virtual machine running in VirtualBox. That way did doesn’t matter if you are using a Mac or Windows. The docker setup is constant in the ubuntu VM. I use Cyberduck/Filezilla/WinSCP to visualize the files and edit them locally with one click on the Mac or Windows machine.
install: no, on macos just use brew and be done with it encoding: no, just use UTF8 gui: TablePlus, DataGrip, PhPPgAdmin if you like
Usually just install and use it. PostgreSQL does have its idiosyncracies but they usually do not come into play unless your database has a lot of rows or processes a lot of transactions. On IRC there is #postgresql where people are very helpful.