Transcript: MySQL & Security - Adam Johnson

I'm Carlton Gibson, joined as ever by Will Vincent.

And today we have special guest Adam Johnson with us.

Hi Adam.

So, Adam, you're gonna have to tell us about yourself.

So, who are you and how did you get into Django?

Let's begin there.

Um since then I've worked with Django at other companies, YPlan, Timeout, Genus AI, and now I am um a contractor offering services under my company AWS, Adams Web Services.

Uh I was paid a very small sum for building the podiatry website for a friend's friend of the family's business.

Um then when I was 16, me and my brother set up a shareware service offering a game creation software as Red Funk Studios.

Uh and for that we had a PHP website with no framework.

So I'd kind of experienced the world of frameworkless web development, just putting some files on a server.

Yeah.

Yeah, yeah.

Well, that's I wanted to ask you about you mentioned memorize.

So I s was at Quizlet, I think at the same time, or maybe some overlap.

And Quizlet was a PHP no framework site.

So there was lots of learnings around that fun.

But it's still going, it's scaling.

So um so what was so

Um and sort of Frankenstein, and you know, that's not uncommon.

Especially when you know the the creator was was young and it was pre these really great PHP frameworks like Laravel and stuff.

So uh you know, it was hosted on uh joint servers, you know, so it was a long time ago, 14 years ago, setting up a website.

I mean, it's way more complicated across the board.

I mean payments, ads, all that stuff was just light years more complicated.

It's just what you upload, and if it didn't work, you just re-upload it and edit it live and FTP and oh yeah, it's done, yeah, it's fixed.

Is that correct?

When you were there?

This was this is 2010 to 13 when I was at Quizlet.

Um, and we knew of Memorize and you know even back then.

Okay.

And in around mid-2013, there was like quite a big staffing change.

So we ended up with one person looking after the mobile app, me looking after the website, and a part-time contractor handling DevOps where the um the servers needed rebuilding effectively.

So that's when I learnt quite a lot of the depths of MySQL and uh the value of doing DevOps properly.

Was it Django before you came onto it?

Um and I remember doing the Django tutorial and thinking, oh wow, all this structure to building a website, this is just fantastic.

Yeah.

I mean, because Memorize was dealing with a lot of data even back then, and so I'm I'm sure you got to roll up your sleeves and feel some some real database scaling fun.

Yes.

Um that's by some of the key people in the MySQL community, like Peter Zaitsev, the CEO of Procona, who are a MySQL consultancy.

Um just digging through that.

Um we had them at the the highest tier of retainer, and they were very needed.

Like at one table was 100 gigabytes.

Well, we also had again growing pains, um, and it's been a long time and Quizlet's big, and so I don't think Andrew minds me telling this, but at one point he we didn't really have much permissions.

I mean we had two developers at at the time, or when I first joined, two developers.

And Andrew went in to delete a record and dropped the entire database by accident.

Um so that was that was a fun five, six hours, and we called up Percona, like, hey, we need some help here.

Yeah.

No, but I was I remember I uh they set the gold standard for a consultancy because we had um you know, generally scaling or whatever challenges, and we weren't you know the uh experts, and they it would always be someone different, usually in Eastern Europe, and they would just almost immediately be able to diagnose and fix the problem.

It was pretty miraculous actually.

Uh I I definitely got the impression reading the book and their blog posts that they know everything there is to know about MySQL.

It was just uh, you know, even though it was a lot, it was I I was just blown away that they always knew it.

And I I guess it makes sense though.

I mean, they're consultants, they've seen, you know, that was our first time building a site at scale, but they've been through it, so the issues uh were not unusual.

So could you tell us a little bit about that, what that's what that does and what that brings us to

the table.

Um so the package is unoriginally called Django MySQL.

Um we were using MySQL at Y Plan as well.

Um and I saw Django Contrapt Postgres come in from Mark Tamlin's great work and the Kickstarter there, and I got a bit frustrated that you know MySQL could do some of these things as well, and we had some use for this feature or that feature.

So um I first developed some stuff in-house, and then I was uh very gratefully given the chance to open source it uh under my own name.

Um, and then it kind of snowballed from there.

I tried to cover as many features as possible.

I found it quite enjoyable just writing Django fields and things like that, and making a package well tested.

And then at the third Django under the hood, I had quite a long chat with Josh Smeaton, who's also a core member.

And uh a few months later, uh unexpectedly, I got found myself invited into Django Core.

It was a very nice experience just seeing everyone who'd voted for me, and that I'd had some impact with my work.

Super, and so so my story, I began with PHP and MySQL back in the day, and I was using it very happily for a long, long time, and then you know the whole um Contra Postgrip uh Postgres thing came along, and I started using Postgres because I wanted the extra functionality that was available, and all of a sudden, over the course of two, three years, MySQL just dropped away.

And I think now, for me personally, and I think Postgres now is in a position where it has a lot of the mind share, but it's not necessarily the most well, it may be the most capable database or not.

But what has MySQL got to offer for us that perhaps we you know we're in this Django world where Django comes packaged with this extra functionality for Postgres?

Why should we be considering MySQL?

Because I think that's a great topic for users.

Um but now it the ecosystem there is a bit stronger.

So Pocona, who we were talking about, maintain their own fork where they have a number of fixes that they've merged in from their consultancy experience, and then there's MariaDB, which is a completely hard fork at this point that implements features faster, has a faster release cycle, and is effectively the Postgres of the MySQL world at this point.

And so it's fully open source.

If I'm you know

Um in my in in my recommendation at least.

Uh if you're maybe a more of a corporate user, you might still want to look at MySQL because Oracle's bigger, and maybe you're uh it'll be easier for you to get a contract with them.

Um the the place that I think MySQL shines over Postgres still is in replication.

Okay.

Right, okay.

Um, one use case I've seen a few times is having a replica of your master database, but still being able to write to it on perhaps a different table.

And you can't do this in Postgres.

Um one of my clients with right now, they just like dump and load the database every hour or so into a replica so that they can have a writable replica.

Yeah.

I think there's probably more big distributions of MySQL on the world than Postgres by quite a lot of orders of magnitude.

I don't think last time I looked, none of the Alexa top 100 were Postgres.

Yeah.

Yeah, okay, interesting.

Like maybe these companies started to exist and scaled up and fixed MySQL on the way.

And if you wanted to create a big company, you could also use Postgres and fix the problems on the way.

And so out of the features that Contrib Postgres gives gives us, what does um Django MySQL give us?

Um the key thing is the JSON field, which um you and I are both mentoring for uh an implementation in core that works on all database backends.

Um so if you're on uh MySQL or MariaDB today and you want to use a JSON field, then you can use Django MySQL.

And I mean the thing is as well, like um anything that's in um the core ORM is gonna have to be support all the four database gonna have to support MySQL and MariaDB, Postgres, Oracle, and um SQL Lite or SQLite.

Um which maybe it it can't do, but in reality, your project isn't using four databases, it's using one.

So if you're on MySQL or you're looking into MySQL, you can use your package, right?

And there are a few little extras in there that will never work on other databases because they're some feature that MySQL encompassed ages ago.

Yeah, okay.

Uh, one thing I found useful is the user locks.

So you can use MySQL or MariaDB as a locking server with a string.

So you can create a lock per say user if you need that.

Um and in Postgres they have such a feature, but you only get to lock by an integer between one and four billion.

So even having two applications on the same database server, it becomes a property.

Yeah, okay.

Uh this is my own ignorance, but does uh MariaDB MySQL have the same sort of full text search support that Postgres has built in?

There's um Sphinx, which I think you can use under MariaDB, but I've never really tried.

Um it has a basic full-text search, but it's definitely more basic than Postgres's.

And that that was like kind of the the okay first, well, poor person's, you know, search that you could use it and it worked and it was not bad.

And if you didn't want to scale up to, you know, a proper search engine.

So that's sort of an excuse to get to do that.

Always a good idea with Tom is to be like, oh, I wish I knew more about this.

Let me propose a talk on it.

Well, I think I have an open ticket on Django MySco to add some of the extra search support like Django Contra Postgres has.

So if you want to look at that.

And actually I had one more question.

Uh, since since you're both core, uh you guys mentioned Oracle still gets support.

Who's working on Oracle in the open source world?

Like how does that like how does that keep up with you know these other the other ones where you have folks like yourself, Adam, who are doing such such great work?

That's sort of an open question, like is is is it just sort of chugging along?

Because I don't get the sense that anyone on their free time is you know keeping Oracle current with Django,

He did he um he's our sort of Oracle superstar.

He knows it really well, he knows all the different versions, and the new versions come up and he makes sure it's um maintained, and he, you know, if an issue comes in that you know there's some incompatibility with Oracle 12 point, whatever, and he's in there and he knows the details, and you know, without Marius, we'd be in trouble there.

Um he really he he really takes that on.

I I don't know him personally, but I assume there was one or many people uh doing that work to keep it up, so I was just curious who they were.

I know Josh Smeaton used to, but changed job and then doesn't want to touch Oracle again.

Effectively.

I think that's what Oracle does does to people.

And he he he's like, look, it's not so bad.

It's nice, it's got these things, and you know, he'll tell us all about what the positives are.

Yes, they should.

Um I think I think you're more likely to see Microsoft funding before Oracle funding.

Um so I think in particular, you know AWS, right?

Is that your is that the one you

Um so we've we've done an episode or so talking about talking about this, but what's your take on Amazon Web Services versus um platforms as a service like Heroku or Divio?

How would you, if someone came to you for advice, how would you advise thinking about that and then maybe we can get into some horror stories or production stories of using them?

Um DVO, they work great out of the box.

Um and DVO have both also like contributed to Django and definitely keep their integrations working and their tutorials up to date.

Um there's also Elastic Beanstalk, which is the pass from AWS.

It's something, I think it's something else.

There's a newer Elastic Bean stock.

Oh, different.

But yeah, but it's the I'll look it up, but it's uh yeah, that Amazon has a yeah, pass option.

Um I know Christian Glass, who's uh relatively active Django blogger and user, he uh he raves about it, but everyone else I've spoken to is like I wouldn't use it again.

And at that point, I'd just rather spin up an EC2 and you know do it the old way.

So I I blogged you know Django hosting things and a post that I wrote came up, which I totally forgot about.

So it's called AWS CodeStar.

That's their okay.

Yeah.

But yeah, so AWS, I mean, just when I just using you know S3 or CDN or um CloudFront, I I I I don't even know where anything is.

I mean, uh it the underlying tech is so great, but the user experience is just garbage for me.

How is it as a power user?

Do you know where to go?

So I I just every time I use it personally, I get incredibly frustrated, but I don't work on huge projects myself.

Yeah, no, exactly that.

Um and the

We had a bit more of a polylingual environment.

So uh some Java, some Scala, JavaScript, PHP, and Python.

Um so uh we kind of set out the ways to do things and stuck every application through a very similar um similar setup.

Uh the main way we achieved this with infrastructure as code using the AWS CloudFormation tool.

So we could just copy paste the setup from one app to the other as code, and a lot of it ends up being a bit of like cargo culting.

Like we know that we need this resource and that resource, but we only know it because it's in that other template.

Um you know, interesting you say you use cloud formation rather than um, I don't know, Chef or Ansible or whatever, because it it

Um but it's exactly like that.

If you've got an Ansible role, you're just gonna reuse that role.

You're not gonna necessarily dig through the details every single time because that defeats the purpose.

Like it's meant it's meant to be kind of pluggable.

I know I need I need a cache, I need you know, a database, I need oh look like now here's my Ansible script, deploy it, brilliant, it works, and maybe it's not 100% tuned for that particular service, but it only took 20 minutes versus you know days it used to take to provision machines by hand and install the right dependencies, and you know, I wouldn't go back to this.

Um but that is why it's so overwhelming to begin with, and I think many of the decisions in terms of design are like aimed at capturing some corporate client's particular use case, and they don't really care about the average use case.

So, one example I think of is like when you create a lambda function, you can hook it up to a service, but you in in cloud formation at least, you also need to declare the middle thing, which is the permission for the one service that you provision to talk to the other service that you provisioned.

And I think that should just be the default.

Like, why do I need to give permission to things that I already just told to do things?

Like, you know, you get through it and it's but it's it can be a slog.

But that you know, it's powerful, right?

It's super powerful.

Maybe for for listeners who haven't ever done that kind of switch, so you you have it on AWS, I guess maybe just like the DNS part.

How do you know?

So you have a duplicate copy, how do you feel comfortable that you can switch over?

And then what is that switching over process like?

Um I assume is it just the DNS?

What what else do you what else are you doing?

Um it depends on your uptime goals.

So if you're happy with your website going down for an hour, then switching the DNS uh can be the easiest switchover.

And the most complicated involves like moving each individual service inside that.

So maybe you move the database, then you move the cache service and you maintain like a network connection, like a VPN between the old data center and the new one.

Um yeah, and as for like checking that a copy is as good as the one that's running, um, the the best way is to edit your etc.

hosts file to point to the new DNS record effectively.

Um, maybe do you have a list of the top 20 URLs to check, or someone who uses the system to step through any automated testing also helps.

Before the weekend.

I only ask because uh for my new book, Jingo for Professionals, I you use Docker and then we use containers on Heroku.

And this is a growing trend that makes a lot of sense to me, but I don't know if is that in the AWS world?

Can you do containers within AWS?

I think one was it elastic Kubernetes service on elastic container service classic.

Yeah.

Um I personally have avoided containers up to this point.

Like timeout would have been one point where we would reconsidered it, but ultimately went down the EC2 route, and we looked at using Easy2 with the same benefits that containers tend to bring, which is like a frozen image, so a new instance is faster, create things configured from like uh variables outside of that server, so you can use the EC2 user data, much like you can use environment variables in Kubernetes, um, w

ithout so much of the complexity and the tool turn.

Um VMs and EC2 in general, any any VM provider, they're pretty stable in what they offer.

Whereas I find even like every six months there's some blog post that tells me something brand new about containers that I know I'd have to rework everything just to keep up.

Um but I mean, and then at the same time, Kubernetes is clearly built by and for big companies with their needs.

So I find that the leap from the leap from contain you know a couple containers to Kubernetes is a really hard one to make.

Um yeah, I think there is a bit of a trend towards like AWS and containers being the kind of IBM decisions of our era.

So, you know, nobody got fired for choosing IBM, maybe nobody got fired for trying containers because that's what everyone's doing.

That's sort of a horrible thought, but probably true.

Um well, and it's also I I mean, just you mentioning your experiences and so much on the DevOps side speaks to something Carlton and I have mentioned on this podcast, which is that on the spectrum, Django is actually kind of a front-end framework.

Um, it sort of doesn't really, yeah, at massive scale, Django or another framework isn't the issue in terms of scaling up.

It's the database, it's all these other things that are yeah, very separate from your programming languages and your um frameworks.

Um I think one of the questions I've come across on core recently was Simon Willison's answer to does Django scale?

And the answer was it's just like any other framework that reads the database, it will go horizontally, it's gonna be the database that's the problem.

Uh just came out today as we record.

Um I know Simon sort of flippantly said, Oh, it's sort of boring, you know, how you shard and go horizontally, but I think his definition of boredom is different than most people's.

Um, but just before we switch gear, there was an old maxim when I was, you know, cutting my teeth was that the hard the hardest thing you go is from one server to two.

You can learn loads, and then you reach the limit of that one box and you have to scale to two boxes.

And that's that's like a whole life-changing experience.

But then the third box, that's just the same as what you did to get to the second box.

And yeah, you know, I think I think that still holds true.

I haven't gone off gone off the one box.

Um so testing.

I want to talk about testing because I know you have um you've written and spoken about it, and it's a question we get a a lot of about.

Um and maybe specifically we can talk about PyTest.

So for folks who don't know, this isn't I guess an addition to built-in tools that I think you've worked a lot with that a lot of people in a professional setting use PyTest, but maybe people who are learning Django don't understand what it is or why you would use it over um unit tests and the built-in Django test runner.

Um it's definitely the more Pythonic way of testing.

Um I remember learning Django's unit test and having done a lot of uh um Java at university, I thought it was quite natural, but then I came to realize it doesn't really fit in very well with the rest of more like idiomatic Python code that I was writing in my views and models.

So I think PyTest is um like the only way I would write tests these days.

And for Django, you install the PyTest Django plugin and it hooks into your Django settings and configures the databases just like Django's test runner does.

Carlton, does that ring true for you?

I use that every time.

And I love, I absolutely love using plain Python asserts because I can never remember whether it's self-assert, you know, has more than or less than.

I can never remember those 52 methods.

I can just about remember assert equal assert true, you know, assert numQueries is kind of handy in Django land.

Um but I I'd much rather just write assert some Python expression and then you know a message that's kind of handy.

Um but the thing I still do quite like and still use a lot is um unit test test cases because I like them to group, use them to group.

So I'm using PyTest to run them and I'm using plain asserts inside the test cases, but I'm still using that unit test class because for the for the grouping that it gives me.

And that's just sort of where I'm at where I'm at.

Um it's not a religious thing, it's just it's kind of where I've reached.

I I haven't delved into the the further realms of PyTest with the fixtures and uh you know the parameters and those kind of things, which all look really cool, but I just one more thing for me to learn at this stage.

Uh I wrote this post what last last week?

Oh, is it that okay?

I guess yeah, I guess it's the 15th of July, yeah.

So we'll link to

We'll link

Um they're an electric vehicle charging optimization company.

I thought I hope I've done them justice in that explanation.

Um so they have um a not huge Django app at the moment, but it had tests running for what was it, was about six minutes, and I got them down to two minutes or so.

Um, and the main uh way of doing this was by changing the test case class from Django that was in use.

So for historical reasons, they'd used the transaction test case, um, which I think is slightly badly named, and um this has more complicated database rollback behavior than the um test case, which you're recommended to use generally.

And they'd use that because it it fixed a bug in their testing, it made sense at the time, but and the speed up the speed difference wouldn't have been noticeable when there were just maybe a few hundred tests, but now their test suite had scaled, it was now taking um a not insignificant amount of time.

Six minutes is still pretty fast.

It's uh it's pretty fast, but it's also long enough to be a nuisance.

Um so uh you can go read the blog post and see why it made a difference to switch down to test case for most of their um test classes.

Um, but I really wrote the post because I thought this was a great story of like where to look in Django testing to have fast tests.

Yeah.

And actually, uh Ned, who maintains it, is here in Boston.

Um I need to get him on.

So that how would you describe coverage to people who haven't used it before?

So coverage is when you run a program and you keep a record of which lines were run.

Um, this isn't so easy in compiled languages, but uh Python makes it relatively easy.

And uh Ned Batuda, who creates the coverage Python package, has maintained it for many years at this point, maybe 10 years or more, maybe.

Um so when you run the program with coverage, you get to see which lines have run.

And so the main use case for that is to create a test suite and check which lines of your actual program, in our case a Django app, have been touched by the tests.

And the goal you can set is to try to get to 100% coverage, that is, every line in your program has run.

So if there's an if in your code, it must go through both true and false.

So you can't have a line of code that does like if something that's only true in your test suite.

Oh so you might have like if user is admin.

Yeah, yeah, yeah.

And then to run all your tests with admin, but then you haven't tested the normal user experience of your website.

That's fantastic.

I didn't know about that.

I love that.

Um, so my blog post guides you through how to set up Django for coverage testing.

Um, two ways, one with the Django build-in test runner, one with PyTests, which you install the PyTest Cov plugin, which sets up coverage.

Um I think everywhere I've worked at has had not 100% coverage.

Well, because apart from once when I worked on an app on my own.

Um, and so it should just be used as a metric of that's kind of why I asked.

I've I've heard people expouse that theory that you know the last five-10% isn't worth the time.

Uh if you're building a nuclear power plant, I sincerely hope that you have better testing than just 100% coverage.

You know, you're testing 100% of the scenarios, hopefully.

So the the it everyone's always like, oh, software engineering doesn't deserve the name because it's impossible to build reliable software.

It's no, it's not impossible.

There are standards, you know, NASA have these standards which you can use, but they they cost five times as much to build the software.

So you take that to a company and say, hey, we're gonna build it to NASA standards, they're they're just gonna throw it out the door and they want it shipped quickly, and if it's got some bugs, it doesn't matter.

Um you know, so the there's always the

Going from an hour to you know a second or something is exponential in cost and time.

Two nines, yeah, no problem.

Six nines, that's hard.

Yeah.

Um well, and related to security, I also wanted to get a chance to talk about your talk you gave at DjangoCon Europe this year on security headers, because that's I liked how you're gonna be able to do that.

Which is fantastic.

A couple people before you had said security is boring, and you emphatically made the case that it is not.

So perhaps you could give the highlights of that talk.

Um so the talk, which has a corresponding blog post, um, goes through how you set up seven different headers on a Django site to make it more secure.

Um, this is to pass a grade on a website called securityheaders.com, um, which is by a security researcher called Scott Helm and will help you get like some assurance that you're keeping your users secure.

Security can be seen as boring because there's often like checking some boxes or re-auditing things you've already written.

But I like to think of it uh from the perspective of a hacker.

Um, I kind of got into web security as a teenager on a website called hackthisite.org, which I checked recently, it's still up.

It teaches you like the basics of all the different attacks hackers might run.

Uh, and then these security headers that you can activate in Django, four of them come out of the box.

Um, they help you protect against all these relatively basic attacks.

And if you're running a Django website without these headers on, you're definitely at more risk.

Um, they're very well-known, simple attacks that you can protect against.

Um, I think of the headers, it's only the X-Frame options that is set by default.

But most of them, and this is only top of line because I just wrote these chapters for my book.

Only most of them are not are turned off by default.

And I opened a ticket after my talk to consider turning them on by default from Django 3.0.

Because that sounds like a good idea.

Did we accept that?

Well, I think the challenge for maybe um beginner intermediate folks is setting up the the local and the production environment for testing.

Uh, you know, because you you want different things in both environments, and that leap, do you do it with containers, do you do it with different settings files?

Uh it's not the fault or it's not the responsibility of Django core to explain that, but I think that's probably the hardest part of doing that, because everyone does it a little bit differently.

So yeah.

X XSS protection is another one that just helps the browser protect you against injection attacks.

I think there's I'm pretty sure there's nine uh things that in a deployment checklist you need to switch.

And one of those, I think two of those, it's like debug and allowed host, and the other seven are um largely header related.

But I've found a number of my clients slash friends have not gone through the deployment checklist when deploying.

Uh aside from Django MySQL, there are a number of smaller packages that work with Django.

Um one of them is for a security header, that's Django feature policy.

That's for a draft security header called feature policy that enables disabling features in the web browser that you don't want to use.

Um so if you if you if someone injects adverts onto your site, you're not gonna let them ask for the webcam of your visitors.

Yeah.

I'm always curious, life as uh as a freelancer, because you worked at companies, obviously the mix works for you.

How would you describe the difference between those two lifestyles?

Why makes it a switch?

Yeah.

So um it I find it quite stimulating to jump between different projects and help people out in various capacities.

And um it's quite interesting in that regard.

Also, I think it's helping me like make some better decisions for Django core.

Um I'm no longer stuck on one big website that I'm thinking about like, oh, most users are ending up not doing this, like not activating these security headers.

So hopefully I can feed that back in.

Um the lifestyle-wise, like at the start, I didn't have much work coming in.

I found it a bit stressful thinking about how am I gonna pay rent this month or um pay for that.

But on the flip side, like it's a lot more flexibility and freedom.

Um when work does come my way, sometimes I'm turning it down these days.

And you you know, if you get sucked into a current project and don't work the pipeline, you suddenly the if the project finishes and you've you've got some money and you've got a nice break, but you haven't got any work coming in.

It's like, ah I d I needed to be doing the pipe the the client development, the business development work whilst the other project was still going on.

And learning to manage that is you know, it's it's it's the freelance, but it's the business side of freelancing.

It seems to be quite a popular way of moving developers between projects as well, and a lot of companies are engaging with that.

Yeah.

Well, Adam, thank you so much for taking the time to come on and share all your work on Django with us.

Thank you.

Thanks for thanks.

Oh, and we should mention, Carlton, for people want to listen to the podcast, you can find it on your favorite podcast player.

It's on DjangoChat.com.

And then if you have feedback, you can leave it on the site or on Twitter.

We're at chatjango.

So again, Adam, thank you so much for taking the time.

Thank you, Adam.