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.
I’ve been using MySQL since 1998. I know the source code very well. I’ve designed large platforms with MySQL but I run postgres in production. I also wrote my own SQL engine.
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.
This is well done! I can see that you've done your research: you've synthesized so much of the written material on MySQL (books, docs) in each lesson. Makes learning these concepts really accessible.
Thank you Justin, that was 100% my intent so that's encouraging to hear. There is a ton of good information, but man so much of it is irrelevant for application developers (as opposed to DBAs). Hopefully this fills that gap!
Thanks for making this Aaron. content looks so good! just in time for me. Unfortunately getting "Sorry Because of its privacy settings, this video cannot be played here.", unsure what's up.
Ah it's for sure some browser extension, it's playing fine in a FF private window. my bad. Will post back once I figure out what's causing it. Again, thanks for the awesome stuff!
This looks great!! Hopefully you can answer a couple questions for me.
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.
Hey there! Looks like the TablePlus question was answered below. I don't cover connecting to a database and coding along, but that's a good idea. I'll see if I can get the sample data up somewhere for y'all to download.
> 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.
Never used or heard about TablePlus, so don't know how it compares but I have been using https://dbeaver.io/ community edition for years now and it's my go to tool for interacting with relational databases. Highly recommended.
Table Plus (Free Trial Version)
What are the limitations of the free trial?
The free trial is limited to 2 opened tabs, 2 opened windows, 2 advanced filters (filters are not available on the free TablePlus Windows) at a time. We can change the limitations without any notifications in the future releases.
Maybe i'm reading into their naming incorrectly. Free "trial" to me sounds like it's time based...as in after a week you can no longer use it and must buy. I'll give it a try.
Quite difficult to review that many hours of video but from the syllabus there are probably some missing pieces, mainly stored procedures and triggers, yes i know MySQL sucks for that particular job and probably you didn't touch them because planetscale probably doesn't support them (not a planetscale dev but this is my wild guess).
Also didn't see any topic on choosing the right charset/collation for the right data and why.
Oh there are definitely missing pieces! I tried to cover the "meaty middle" of what application developers usually need to know. I did not cover stored procedures and triggers.
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.
Since you call it intermediate level, would you recommend it for people who are already familiar with the basics of MySQL? Would someone who has no problem formulating a query to get any given result from a database and knows the basics of how to create indexes still learn anything new from this?
Yeah, I think you would still learn some good stuff! I go pretty deep into indexes and do some fun stuff with generated columns, windows, and CTEs. Based on your comment, you're the person I was hoping to hit! So if you do watch it, lemme know what you think
this is great. Thank you so much for your effort. Really appreciate it. By the way , if you are starting a startup, will you use mysql or postresql and why assuming you are equally good at both ?
Haha thank you! I'm really grateful that everyone at PlanetScale gave me _so_ much time to work on this. We're really committed to doing everything as high quality as we can, even the free stuff. Let me know what you think!
Yeah, you're not alone. Aaron posted a thread on Twitter that shares the inside skinny on what it took to ship this course (https://twitter.com/aarondfrancis/status/1638191349261377539) but I had an early peek into the work he was doing on this course, and there was a clear inflection point in the project when he joined Planetscale and had the benefit of being able to focus a substantial amount of his time on it in addition to all the work other members of the team there were able to contribute to it. Very grateful that Planetscale unlocked this so it's available as a free resource instead of having to be behind a paywall to support its creator.
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.
It's really nice to see more content for MySQL/MariaDB still being made, since it feels like they are still good solutions for a whole variety of situations! While I might look in the direction of PostgreSQL for more advanced in-database processing, for most CRUD apps the aforementioned two are going to be more than enough!
> While I might look in the direction of PostgreSQL for more advanced in-database processing, for most CRUD apps the aforementioned two are going to be more than enough
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):
This kind of argument that XYZ company uses ABC software so it's also good for my project is not correct. Your requirements and their requirements are different. Also for many of the above companies, when they were started MySQL used to be the popular solution, so they started with it and stayed with it. The way Facebook or Uber uses MySQL is not how you will use 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.
> The way Facebook or Uber uses MySQL is not how you will use it.
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.
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).
> where you want to store some amount of logic in the database itself
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 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.
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.
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.
> Even with version controlled migrations, it was an absolute mess to work with, to debug and extend, even though the performance was great.
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.
> 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.
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
If by "advanced in-database processing" you mean stuff like PostGIS, then yes PostgreSQL is the right approach. But for most people building web-apps to scale, or established businesses who want to pick a solution and stick to it for basically ever, MySQL is much easier to scale. There's a ton of companies running MySQL at scale. I never heard of the same for Postgres, and for a long while I had an intellectual preference for Postgres as being the more "pure" implementation. Experience in the field showed me that MySQL, despite its flaws, is the name of the game.
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:
MySQL also has had major bugs in recent GA releases like crashing your server when renaming a table.
PostgreSQL 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.
I wonder where that "MySQL is easier to scale" trope comes from. Have you ever tried to set up and operate a MySQL/MariaDB cluster? It's a horrible and brittle shitshow. Even if you get it running, there's no guarantee things don't just turn south at any given moment.
Also, while we may see $75 or $150 or whatever as throwaway money (especially in the context of professional help products or books), that price is wildly unaffordable for most of the planet. Having high-quality free educational material out there is a godsend for students and developers in low income countries.
The Art of PostgreSQL is great and super thorough. Knowing the author I can't fault him on price for the effort he put into it.
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
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?
Are they really different technologies? It has always been my impression that they are the same tech, but they have a slightly different syntax in some places (DESCRIBE) and different levels of capabilities (Postgres offers more).
If you know the core concepts of one, you are probably 80% good to go with the other.
A lot of devs should really be more familiar with plain ol' SQL databases. I've been at multiple companies where people have jumped to elaborate, gigantic, overcomplicated projects preemptively designed to scale to ten billion people, that could have instead been driven entirely by a single big MySQL or Postgres database set up in a day or two instead of a month.
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!
TL;DR starting with a list of the data types before querying the data is putting the cart before the horse IMO.
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.)
The creator posted this elsewhere in the comments:
> 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.
This is amazing! I've been following Aaron's short database videos on Twitter and he is the real deal. Accessible, straightforward explanations of complex db stuff. I highly recommend checking this out.
Thanks swyx! Means a lot coming from you. We're lucky to have an incredible team at PlanetScale to help me pull off such a huge lift. We had maybe 5 or 6 people building out the website and 5 or 6 people that reviewed the content for technical accuracy. No way I could've done it solo.
I really wanted to use Planetscale, but there are two blockers:
- 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.
I just read this post, it looks like a demo for basic operations.
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.
I'd say some of it is, but I don't know enough about Maria to say much more than that. MySQL and Maria have diverged quite a bit at this point, so I'd proceed with caution on any of the specifics.
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.
I've taught courses on SQL to newbies and learned a lot from it.
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.
This is perfect I was looking for something like this will definitely be checking it out over this weekend one request please give us the training data set you use for this course so that we can follow along with all the sql queries you are typing.
As someone who has been passively watching you develop and promote this course over what feels like _forever_ - I'm SO excited to see the feedback here the way that it is. Kudos to you and the team at Planetscale, Aaron.
Might be worth looking into PostgreSQL as well. It's just as readily containerized (docker) as well as being more ANSI compliant and consistent. I only say this because nearly every time I've ever used MySQL, I find annoyances.
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.
PostgreSQL, however, is one hell of a PITA to upgrade. Keeping a cluster of MySQL machines alive is way easier than PostgreSQL, not to mention how easy it is to shoot yourself in the foot with its authentication mechanisms.
> 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)
PosgreSQL has its share of annoyances, like case preserving case insensitivity and inconsistent naming of its tools/config files.
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.
Can't really comment on the material... but a minor suggestion might be to have a section on ANSI-SQL statements and quotes, and where mySQL/MariaDB breaks the standard (such as foreign key references in table creation). As well as pointing out that you should use 'utf8mb4' for UTF-8, and not 'utf8' encoding.
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.
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.