SQLpy is open source!…well it’s been open source since about September 2017, but more on that in a bit.

I really like SQL, and I am a very big fan of PostgreSQL . Just how much of a fan, and the reasons why are probably the topic of a very long after work chat! As for SQL, spending over 4 years within a huge financial enterprise IT role, you use it a lot. From my first day on the job never having touched a line of SQL, to reading huge decades old procedures, analysing performance, table design, and development. Safe to say I was comfortable in the language, and appreciated how much wide ranging control I had over the data I was working with.

How did we get here?

One part of the story of that led me to SQLpy, is that around late 2014 I had begun to learn web development. I had made a simple toy application and got it up onto AWS. (Also I just thought it was cool to make something show up on a web browser!) Around mid 2015, one of the senior architects on my team was showing me Clojure and was encouraging me to give it a shot. I think also that week he was showing me EMACS. (After a couple of days passed to allow me to recover from parenthesis exhaustion.) I found a nice looking tutorial to write an address book web app in Clojure. At the obligatory data persistence chapter, the author did not reach for an ORM, but for a library called YeSQL . I instantly clicked with this design pattern of writing SQL in a queries file, and using functions in Clojure to access them. The rest as they say is history.

After using the very nice Python port anosql for some more toy projects, by the time it was late 2016 and we’d started 9fin, I wanted to do a lot more with my SQL and our PostgreSQL database. Scoping out the many features that I needed, I saw that I’d really be making a large shift from anosql. A major feature being the ability to safely, quickly and easily compose a SQL query. So I started on our own port. This is what SQLpy has become today.

Why not use an ORM?

I must say there is absolutely nothing wrong with ORMs, we use the equally fantastic SQLAlchemy and Records for some of our satellite systems, where data is more or less accessed in a CRUD manner. Also, with the advent of data science tasks within our stack, or in general data processing tasks. We find that the ORM pattern, where model objects are defined in application code, does not always marry neatly when the underlying data is changeable. Breaking out into SQL from ORMs and other wrappers is possible, but it’s typically not the intended primary use case for the library.

SQL is the future..again

After being cast into the shadows by the meteoric rise of noSQL circa 2010-2016, it seems like now RDBMS are upping their game and accelerating development. If you want to access these cool new DB specific features, you’re going to need to interface directly via SQL to your RDBMS of choice.

Write Python and write SQL

SQLpy has been running within 9fin production since November 2016, it’s a core part of our application development patterns. I first threw it up on pypi in Sept 2017, in a not great form. With heavy refactoring, and cleaning up of the module structure to handle other database types, Postgres still getting most of the attention at the moment however! Version 0.2.0 was completed in Dec 2017. We’re really excited for SQLpy to join the ranks of (the hopefully growing) YeSQL ports. Go check it out! sqlpy.readthedos.io