Space invaders with SQLAlchemy - Michael Bayer

Tags: pygrunn, python

His talk is titled “space invaders, relational modeling and domain models, a mashup” instead of just the plain sqlalchemy that I expected :-)

Space invaders

He started his computer career with a TRS-80 in 1980. The killer app at that time (5th grade school) was printing out a picture of Snoopy on the matrix printer.

Next up an Atari 800. There was a great ‘vertical scroller’ game and he wanted to build his own. But you needed to write assembly to pull it off which was way over his head. Machine level programming is depressing.

Relational modeling

Somewhere in the 90 was, for him, the time of architecture astronauts and enterprise java beans. 1200 database tables for reading a big SGML document, that sort of nightmare. He was made to use the Pure waterfall method. Spending a month only drawing Rational Rose UML diagrams. Also depressing.

But he did finally understand objects and liked them. A domain model is important.

He showed wordpress’ comment edit code. PHP code; hacked often. Hardcoded ad-hoc SQL code to delete comments, embedded right in the PHP code. Comments are only an integer ID, not real comment objects, of course. In the end, he showed the entire PHP page in 3px font size: HTML, SQL and PHP all mixed up in one file. Horrid.

You basically give an integer ID through a couple of calls all the way through the application. Only valid for small scripts, not really for a system the size of wordpress. Can we call it the water slide model? One straight flow down from an ID to some action.

Domain models

A domain model is “a conceptual model of a domain of interest”, according to wikipedia. You model the problem, agnostic of implementation details. And we create explicit adaptions between the outside world and the model (adapters, serializers, views, etc: separation of concerns). And ideally we only use neat normalized data.

To him, dealing with such a domain model feels a bit like a horizontal pipeline. (Reinout: not sure what he means with this).

At the core of relational databases is the relational model. Information represented as collections of rows, consisting of columns. Mostly ACID.

Essential to relational databases are joins: intersecting or joining data. SQL, the language, is a declarative language (as opposed to imperative or functional, see stackoverflow).

How do we reconcile the SQL/relational model with the domain model? He things an ORM, object relational mapper, is a good idea. An ORM needs to map objects and collections to the tables, rows and columns of the database, including relations.

Keeping the “R is for relation” intact is a real challenge. There are several ORMs that don’t expose enough of the available relations. Sqlalchemy does expose it, you can keep thinking in terms of your database and its tables and relations that you know.

Another challenge is to limit the amount of queries. For instance, when you iterate over some set of database objects (rows) in Python, there is a big risk that you do a follow-up query for every row. So you need to be able to give the ORM some instructions for loading that extra related data directly. Sqlalchemy helps you with this: this way you get the luxury of a nice ORM, but you still have well-optimized SQL.


Mashup? Yep, he wrote a space invaders clone that stores all the game data in an SQL database (sqlite, in-memory)! Idiotic example, but loads of fun.

The domain model includes missile, player, splat, enemy, army and saucer glyphs. Every glyph has a coordinate.

The code has a main loop with update_state() that moves the missiles, player, etcetera and draw() that draws everything. Both query the database! Are there coordination overlaps between missiles and a saucer? Boom! You can finally get an explosion out of a database query. And drawing means querying for all coordiates and rendering what’s there. Wow.

Losing is also determined with an SQL query: an enemy coordinate matches the player coordinate. Or an enemy coordinate matches the bottom row coordinate :-)

Then he started playing the game in one screen with a log file showing all the SQL queries live in another screen. Fun!

Pygrunn lunch logo

About me

My name is Reinout van Rees and I work a lot with Python (programming language) and Django (website framework). I live in The Netherlands and I'm happily married to Annie van Rees-Kooiman.

Weblog feeds

Most of my website content is in my weblog. You can keep up to date by subscribing to the automatic feeds (for instance with Google reader):