MySQL for Developers
planetscale.com
planetscale.com
The course is a bit more than 7 hours long split over 64 videos.
I was always frustrated by the lack of intermediate database content, it seemed like it was mostly intro stuff, or straight to DBA level. So I read as many database books as I could, read through the official docs, and made this course specifically for application developers.
If yall have any feedback I'd love to hear it. I'll be making updates soon, but not before I take a nice long break from editing video.
Also didn't see any topic on choosing the right charset/collation for the right data and why.
I did cover CTEs and windows, which are not yet supported by PlanetScale. I also covered foreign key constraints, which are not yet supported by PlanetScale.
As far as charset and collation, I cover that a bit in the "strings" video of the "Schema" section.
Let me know if you have any other feedback!
You mention you're using TablePlus. Doesn't look like it has a free option. Can you recommend another similar database GUI tool so I can "code along"? And is there a database I can connect to and work with? Maybe you answer these at some point in the course, but I didn't see anything in the intro.
Also you mention this is for devs and not DBA's. Do you go over strategies for creating and "maintaining" a new db for a basic app? Or is this for a dev to work against a db that fully created and maintained by a DBA? I'm interested in using PlanetScale for hobby projects, so i'm currently trying to learn about not just using a db, but also being my own DBA (on a very small and basic scale).
Thank you for the course and any feedback on my questions.
Beekeeper Studio similarly has a (community edition) and it's GPLv3 licensed
> Also you mention this is for devs and not DBA's. Do you go over strategies for creating and "maintaining" a new db for a basic app?
We do spend a lot of time talking about schema up front actually! What it takes to design good tables, what data types to use, and so on. I talk at the end of the schema section about migrations and how you can use those to keep your tables up to date as business requirements change over time.
I think if you use a hosted provider like PlanetScale, they (we) take care of a lot of the stuff that was traditionally the realm of the DBA, although maybe not all of it. I don't know exactly where to draw the DBA line to be honest.
Let me know if you have any other questions! Happy to help however I can.
This is very VERY well done. Kudos. I’m loving that we have a contender for mongo atlas with planetscale. Keep this kind of content coming and you’ll be the next snowflake.
Kudos to Aaron though, will gladly dive in later on
I've always felt that one of the big "level ups" that an app developer can do is get a better understanding of how databases work and how/when to leverage their features.
The course kind of feels like cheating as it's so much less effort than slogging through all the books and failed projects you'd otherwise need to get the experience.
I see this sentiment often here on HN. Something long the lines of "MySQL is enough for small apps but you want PotgreSQL for serious work."
When in practice I find the opposite to be true. PotgreSQL is hard to scale and hard to upgrade when compared to MySQL. I mean, just take a look at the caliber of companies that leverage MySQL at scale using Vitess to orchestrate it (spoiler, it powers Youtube, GitHub, Slack, Shopify and more):
https://planetscale.com/vitess
What's PostgreSQL comparable list?
Netflix, Instagram, Spotify, Skype, Reddit, Twitch, Yahoo.
e: removed Uber.
https://www.uber.com/en-JP/blog/postgres-to-mysql-migration/
> Most of our data (users, photo metadata, tags, etc) lives in PostgreSQL; we’ve previously written about how we shard across our different Postgres instances
Another relatively big company that uses PostgreSQL is Gitlab.
https://www.uber.com/en-US/blog/postgres-to-mysql-migration/
And as far as I know Netflix was a big Cassandra user then migrated to CockroachDB. I tried to search for "Netflix Postgresql" and found this comment from 2016 stating that they chose MySQL over PostgreSQL: https://news.ycombinator.com/item?id=11950811
Could you share a source?
Best source I found is https://www.thomsondata.com/customer-base/companies-that-use... but not sure how current it is.
I'm sure that others can comment on that, but in my experience PL/pgSQL is the killer feature that's hard to beat in PostgreSQL, for those cases where you want to store some amount of logic in the database itself (MySQL stored procedures feel a bit more limited). That said, it's not even the only procedural language that is available: https://www.postgresql.org/docs/current/xplang.html
In addition, working with JSON in PostgreSQL can be pretty nice for niche use cases, as is using PostGIS for geospatial data, in addition to some of the REST (e.g. PostgREST) or GraphQL (e.g. PostGraphile) projects, if you want to interact with the database as something that exposes web endpoints to let you retrieve and manipulate data directly, as opposed to just SQL communication with some back end.
That's not to say that MySQL or MariaDB don't have their own great offerings, but it's clear that PostgreSQL has gotten a lot of love in regards to people developing various integrations and extensions. That said, usually not needing the equivalent of PgBouncer out of the box is nice and personally MySQL Workbench feels better than pgAdmin due to the advanced ER functionality (forwards/backwards engineering and schema synchronization, so that you can create versioned DB migrations more easily if you write them in plain SQL).
Edit: as for some crowd sourced data, a quick search turned up this from a few years ago: https://learnsql.com/blog/companies-that-use-postgresql-in-b...
But then again, all of the mentioned RDBMSes have proven themselves as viable for a variety of projects.
If you’ve ever worked on a decently sized project, you’ll quickly realize this is an anti-pattern that you should avoid at all cost. Imagine having multiple teams updating that logic without any version control or visibility in what’s stored in pg.
If your shop isn't using version control for the database, that's the problem.
Not sure about this.
On one hand, packages of reusable logic in the DB can be useful - like processing some data when you're selecting it, or doing common validations before inserting data, or even when trying to do some batch processing or reporting. On the other hand, I've worked on a large enterprise project where almost everything was done in the DB and Java was more or less used as a templating technology and to serve REST endpoints. Even with version controlled migrations, it was an absolute mess to work with, to debug and extend, even though the performance was great.
I've also talked with some people who still believe that the majority of logic should indeed be implemented as close to the source of the data as possible, as well as some other folks who don't feel using anything but their ORM of choice and prefer to abstract SQL away somewhat on the opposite end of the spectrum. Either approach can lead to issues, personally I'm somewhere in the middle - use ORMs if you please, map against views in the DB for when you want to select data in a non-trivial manner, consider some functions, or even stored procedures for batch processing, but don't get too trigger happy about it.
If you need lots of in-database processing for whatever reason, might as well use something that has a good procedural language, like PostgreSQL.
As for the critiques, the closest thing to a factual response I can give is by taking a look at the JetBrains developer survey: https://www.jetbrains.com/lp/devecosystem-2021/databases/
The reality seems to be that about half of respondents don't version their scripts, half don't debug stored procedures and the majority doesn't have tests in or against their database. It's not that you can't do these things, it's just that people choose not to. I'd expect a locally launched DB instance with all of the migrations versioned and automated, as well as data import/seeding to be the norm.
This is exactly what I mean. Sure, anything is technically possible, I’m not saying that you can’t version your stored procedures (even though even that has almost never been the norm on any team I’ve worked on). But is it the ideal setup for your team/project? Far from it.
Both PostgreSQL and MySQL are powerful databases and if you have right people with proper knowledge highly scalable systems can be developed with both.
The reason PostgreSQL is recommended in last 8-10 years is because just before that time, though MySQL was very popular it had some issues which were solved by PostgreSQL. So when web developers encountered those problems and they saw that PostgreSQL didn't have those issues, they started recommending PostgreSQL.
In my personal case, I was responsible for managing a Wordpress site with a few million visitors every day. (This is before AWS RDS, we had to set replication manually in those days) We had set up replication with MySQL 5.6. At that time MySQL replication had a few issues, and it used to break every few days.
At that time PostgreSQL replication which I was using in other projects was rock solid. So I started recommending PostgreSQL over MySQL. For others they have similar stories but for different issues they faced in MySQL.
Over the years of MySQL has improved and so has PostgreSQL.
With large companies like Meta/Facebook, there is no singular "way" that the company uses a particular database. Larger companies typically have self-service generic managed database infrastructure, similar to RDS but internal. The workloads tend to be quite varied.
> (This is before AWS RDS, we had to set replication manually in those days) We had set up replication with
> MySQL 5.6. At that time MySQL replication had a few issues, and it used to break every few days.
Your chronology isn't right: AWS RDS was released in Oct 2009, and gained multi-AZ replication in May 2010. At this time, Postgres didn't even have built-in replication support at all yet; it first gained built-in streaming replication support in Postgres 9.0, released in Sept 2010.
Meanwhile MySQL 5.6 was released (GA) in Feb 2013, several years after RDS already existed.
In any case, if your replication was breaking every few days in MySQL 5.6, that was something specific to your environment / configuration / workload. What you're describing is definitely far from common. If a replication stream breaking this often was the typical experience with MySQL 5.6, at Facebook's scale we would have had a replication breakage every few seconds, and that definitively was not the case.
That's especially true with out-of-the-box software like WordPress. I can't imagine Automattic experienced frequent replication breakages with a normal WP workload, as this would have been hugely operationally problematic for their hosted wordpress.com product. Perhaps you had a misbehaving plugin performing non-deterministic DML or something like that?
That all said -- Postgres is an amazing database, and there are many good reasons to choose it; but as with all technical choices, there's a set of trade-offs to consider. For example, originally Postgres only supported physical replication, not logical replication, and this made upgrading to a new major version quite painful as compared to MySQL.
Further "evidence" by referring to other experienced folks who worked on scaling SQL databases, and MySQL is what's used and what folks have experience with:
- https://twitter.com/Sirupsen/status/1602347646961606656
- https://blog.nelhage.com/post/some-opinionated-sql-takes/#my-personal-choice-mysqlPostgreSQL scales differently since it doesn't have redo-log based MVCC or other things as well. It does value correctness and has (mostly) better defaults. It has also had its own embarrassing bugs, though IME few put data integrity or availability at risk.
In Postgres, replication just... works.
I will take this rare opportunity to ask, anyone knows when MySQL 9.0 is coming?
I know there are some differences in SQL syntax, but in my case we use pretty basic sql…
MySQL mingles its custom language with SQL which is a common point of confusion... Whom amongst us wasn't confused when "DESCRIBE" didn't work in SQLite or Postgres?
If you know the core concepts of one, you are probably 80% good to go with the other.
Yes, your impression is wrong.
But also your point of students and developers in low income countries completely understand that side. It's a shameless plug but we built our Postgres playground with tutorials on a number of topics which are completely free aiming to help target some of that audience - https://www.crunchydata.com/developers/tutorials
There's an opportunity for a creator here.
I've really been feeling your "lack of intermediate resources" comment recently. I'm comfortable writing basic SQL queries, but wouldn't know how to set up a new DB and don't really know how to build/structure/optimize/manage a database (outside of tools like the Django ORM, which very helpfully abstracts a lot of it). I've been wanting to get more into SQLite as a starting point, since it seems pretty accessible (as opposed to like, running a server).
Relatedly, I've got an almost-outgrown-Airtable project that I've been considering moving into my own setup, but the 0-to-1 process is pretty intimidating. Plus, Airtable's schema setup I don't think can be replicated 1:1 in SQL (but I'm not even sure that's true).
Anyway, I'm really looking forward to your course. Congrats on the launch, it looks awesome!
It seems a bit like DuoLingo. You learn a random assortment of facts, rather than being exposed to what people are actually doing. I learned MySQL by example and this is far from it. I don't recommend learning about the schema or the data types first, unless you're just being shown a CREATE TABLE statement with just the types you need in order to go through a CRUD example (having you start with a table that already exists and do a SELECT query first is also good). Otherwise, like with DuoLingo, you'll likely get bored, unless the reward mechanism grabs you. (DuoLingo is fully gamified. This has a bunch of short steps so you can watch yourself progress through them.)
> it seemed like it was mostly intro stuff, or straight to DBA level
This would have been perfect for someone like me a few years ago. I went into my first job knowing how to make simple queries and simple joins. I changed jobs into a legacy system where there was an overwhelming amount of data sharded across few databases and several hundred schemas.
I didn't need to learn how to do simple joins or basic keywords like union and intersect that every tutorial talked about. I also didn't need a bunch of DBA knowledge like distribution strategies, replication, and HA techniques. I needed to learn how to leverage what was in the database to pull the exact data I needed more efficiently than what I was doing - windows, cursors, and good subqueries. I was the type of person being addressed.
In MySQL, "schema" means the structure of the whole database. In other RDBMSes, "schema" means a namespace within the database.
With its cross-database queries, MySQL puts no distinction between a database and a schema.
Once again, not a fault of the training here, but a common source of confusion I've run into between MySQL devs and folks working on other systems.
* Schema means the same thing on all RDBMS: a namespace for tables
* MySQL doesn't support the notion of multiple databases per server installation
Can we eventually expect something similar for PostgreSQL [for Developers]?
And does this course include ORM's, like SQLAlchemy?
I think a good teacher could make a lot of money doing it if they wanted.
This course talks briefly about ORMs, but does not go into usage of any particular one. All raw sql!
- No foreign key support
- No CTE support
I tried using Planetscale knowing that there is no foreign key support because it is fine to me. But I had to stop using Planetscale because of the second. My ORM (ent for golang) relies on CTE so I simply cannot run complicated queries.
So if you are considering Planetscale, test it enough especially if you are using an ORM.
Correct that we don't yet support CTEs. I'm surprised to hear of an ORM that relies on them! That's pretty cool, never heard of that.
Did you follow that blog post already?
When I tried it at the end of the last year, ent works fine with Planetscale for basic reads. However, it fails to read when I use complex queries with Ent GraphQL integration.
ent added quite a lot of features last year so things might changed from the time when the blog post was written.
Start to finish... a long, long time. 18 months on and off, 4 months full time. I started this before I was employed at PlanetScale and when I joined PS the course came along with me. I wouldn't have had the space to do it if it hadn't been my full time job. For the past four months or so I've been spending 100% of my work time on it, and much of my spare time.
Part of what made it tough is that, while I've been comfortable with MySQL for a long time, _teaching_ it is a whole different thing. So I ended up having to study a _ton_. Lots and lots of videos trashed when I got to a point of explaining something and realized I didn't know it well enough to teach. Back to the docs to figure it out myself and then back to recording.
I could do it again in about 1/3rd the time, but of course I could! I've done all the hard parts now! The actual recording and editing was of course hard, but the up front work to make sure I wasn't making stuff up was probably the biggest slog of it all.
My rule for the slide deck was "don't use a term you haven't explained". So I'd write a slide and check for new words. If I really needed them I'd have to add a slide before that introduced and explained the term.
Figuring out exercises that got students oriented to the concepts was another trick. People really don't get stuff until they've done it for themselves.
By the end of the second day I had people writing joins on their own.
Well done!
https://learn.clickhouse.com/visitor_catalog_class/show/9134...
But there is quite a bit on learn.clickhouse ;) And if something is missing, tell us.
Also, be prepared to be blown away with Clickhouse. I've not seen many (any?) technologies that impressed me this much right out the box.
Such as the fact that 'utf8' isn't 'utf8', 'utf8mb4' is... the feature was in testing as utf8 was being standardized and mysql used 'utf8mb3', and never updated the reference for 'utf8' for compatibility, even across major versions.
There's also the fact that collation on indexes for binary fields are case-insensitive if your default collation is, even if it's "binary".
Also, PostgreSQL has nicer support, imo, for JSON (JSONB) data, as well as a rich extension ecosystem.
MySQL's JSON data type, which exists since 5.7, which is quite old, is a solutely comparable to PostgreSQL's JSONB. (But don't be confused by MariaDB, which is a MySQL fork, where JSON is an alias to MEDIUMTEXT or something like that with little snytax validation)
Nothing like trying to do a select on a table/column that you know is there and getting an error...becuase the table/column was created with quotes by your ORM so it doesn't automagically get case in-sensitized.
It's maddening.