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.
