00:00:00,770 --> 00:00:03,480 VIDEO: And then we print out the success page. But this isn't all that useful. DAVID MALAN: So we've used SQL in the class for some time. In fact, ever since 2007, we introduced my SQL as a database engine. And we gave students user names and passwords and they actually used that. And we made a pretty big change this year. We're still teaching SQL. But we transitioned to SQLite, as you may recall. Which was actually pretty game changing, I thought. And I regret not having thought about it earlier. This is partly because, I think in my mind, SQL light had generally been used as a little toy applications and so forth. But the reality is, it's being used more and more for mobile programming. When you want to have a local database that's still SQL queryable. And it just made it's so much easier to actually set everything up. DOUG LLOYD: Yeah. It really lowers the barrier to entry for students to get acclimated to a database. DAVID MALAN: You don't have to install the software configure. And in the case of CS50 IDE, we don't have to run multiple services on multiple TCP ports. Which was really the motivation. And that then to provide students with a web based interface to it. We were going to have to spawn a second web server in addition to the Flask instance that they're running as their app server. So there were just some annoying real world complications. That we don't need something like PostgreS or MySQL. We do want them to learn SQL. DOUG LLOYD: And they can learn the fundamentals of the language without needing the more complex database. DAVID MALAN: All of the CRUD operations, select, insert, delete, update, we introduce them to. We have them create their own tables now, actually. Which is nice. We even introduce them to indexes, like primary keys, unique indexes, and so forth. And then we did end up using the web front end. So we used PHP light admin. Which is very similar in spirit and actually much better than phpMyAdmin. Which is Ajax based UI has just been getting buggier and buggier over time. So PHP light admin actually worked quite well. So I've been thrilled with all this work. Oh and from a teacher's perspective, the database then gets stored in a local binary file. Which just means when students submit it, it's included with their submissions. DOUG LLOYD: Yeah. Oh my gosh. In the past we had to have students do a SQL dump. DAVID MALAN: Oh my gosh. And then so many people forget. DOUG LLOYD: And then you can't even get the program to work because you don't have access to the schema that's underneath. DAVID MALAN: The TFs have to import it. It was just so much more work for really no upside. So I don't know why it took me 10 years to think of this. DOUG LLOYD: Iterations. But this is interesting. It's not the last piece of the puzzle, for sure. There's still JavaScript. And there's lots of other things that students can use to make their sites more robust. But it is a key piece of the puzzle to creating a full fledged application. And once we've introduced SQL, this is where many students' final projects will originate. Is having a front end, like we do with CS50 finance piece at seven, with a SQL or SQLite back end. So finally allowing students to control and search through data, really gives them that final piece. Because they have by this point they've learned Python. And in the future, it's just making the user experience that much better with JavaScript than Ajax. DAVID MALAN: Yeah. And it's a conscious pedagogical decision too that we give them. Now in Python, formerly in PHP. Now an execute function, so a querying function. Written in Python that actually abstracts away some of the implementation details of using a library for SQL. I want them to learn a bit of SQL Fundamentals from this. I don't want them to have to worry as much about the specific API that they're using. Whether it's PTO in PHP or something like SQL alchemy in Python. So we instead give them just an execute method. That if a student has executed select, returns you a list of dict objects. If it's an insert or update, we tell you how many rows were updated or deleted. Or an insert, how many rows were inserted. And so that simplification, I think, works well. But what we did consciously this year too, is you can relatively easily remove that from your code. The CS50 library and just use SQL alchemy, which is a popular SQL library in Python on its own. DOUG LLOYD: And our XU query also does some sanitization. Right? DAVID MALAN: Yes. And that's actually the most important thing. In most any library instance, you should absolutely be doing that. And we didn't want them to be piecing together their own queries and repeating mistakes that we ourselves highlighted in class. And for the first time this year too, we actually introduced an ORM, so an abstraction layer on top of SQL. Which we didn't encourage students to use but I wanted them to see it even though we didn't spend much time on it. And this of course is a way of abstracting away the SQL syntax altogether so that you just think of things in terms of objects. And the hope was that maybe some of our most comfortable students would actually leverage that functionality if they already came in knowing a bit of a SQL. But we didn't want to hide the implementation details. So almost every student in the end probably did use raw SQL queries as expected. DOUG LLOYD: There's definitely an advantage to using an ORM. And not having even more concepts to learn. Like having to learn the raw SQL. DAVID MALAN: Agreed. But especially these days. Especially with so many students going off into finance, the natural social sciences, or natural sciences where there's just data to be processed. And with data science so very much in vogue, just having some SQL chops. And having that savvy so that you can actually execute raw queries, I think is also just a practical skill these days. And I think better that students understand database design at this stage. And this is why we introduce indexes, primary keys, foreign keys, and so forth. So that they actually think about the underlying data that they're storing. And not just treat it as an abstract key value store. So much like we start with C and transition to Python. So do we start with SQL. And if they want for their final projects, or beyond CS50 to use an ORM, so be it. DOUG LLOYD: No, that's fair. Because now we're again encouraging us to be mindful of the design decisions that they're making when putting together their own schema.