I don’t want to use Alchemy for the blog. An ORM is a complex layer of software that you must know well if you plan to use it for something more than CRUD operations, or it will launch very inefficient queries to your database. ORMs have another drawback: they are tools for mapping objects to relational databases. What if I want to use another kind of storage, like a no-SQL database? I think it will be more difficult to change the persistence layer if it’s coupled to an ORM.

For this project, I would like to practice some Domain Driven Design techniques. Not the ubiquitous language (I won’t be sharing this with other people) but I’ll use things like Value Objects, Entities or Repositories.

So let’s start.

The first thing to do is to open the database. Whose responsibility should that be? The path of the database will be configured somewhere, and I think that Repository classes shouldn’t be aware of that configuration. So I created a module `db.py` with a utility to open the database and store it on Flask’s ‘g’ object: that is a kind of global variable available during the request. Flask’s documentation talks about “contexts”, but I didn't find it necessary and will investigate it later. The database is opened only when needed. See that I’m using a Repository here, more about it later:

@app.route('/')
def root():
    from simplertimes.posts import PostsRepository
    pr = PostsRepository(get_db())
    return render_template('/index.html', posts=pr.home_posts())

Then, after each request, I must close the database. That’s done with a hook:

@app.after_request
def after_request(response):
    close_db()
    return response

The only database-specific functions we have here are `get_db()` and `close_db()`. They are in a separate file (`db.py`) so I think it’s pretty isolated from the rest of the code. If I wanted to use mysql, a connection pool, etc. I would only need to change that file.

For example, this is what I have in get_db() function:

def get_db():
    if 'db' not in g:
        g.db = SQLite3DB(current_app.config['DATABASE'])
    return g.db

    

Here I’m instantiating a `SQLite3DB` object, which provides an abstraction layer to access the database. It has two methods: `query()` to launch a query to the database, and `close()` to close the connection. It opens automatically the database when needed. If I wanted to change the database to MySQL, for example, I should change only that method to instantiate the appropriate object.

I could have used a Python’s Abstract Base Class to define an interface for database access, but that would had assumed that different persistence systems would have similar methods, like query. The database object is only coupled to the repositories, and in a different persistence system maybe something like a query perhaps doesn’t make sense.

The next problem I found was how to create a Post from a SQL row. Who should do it? Post? Repository? I think none of these should know how to build a Post, so I created a PostFactory class. That class has only one class method: `from_sqlite3_row` who takes the cursor and the row and generates the post. Maybe I should have created a specific factory for SQLite, for example, a PostSQLiteFactory class, so I can have for example a PostFirebaseFactory class later if needed.

This is the code for PostFactory:

class PostFactory():
    @staticmethod
    def from_sqlite3_row(cursor, row):
            field = lambda x: value_of(field_names, row, x)
            field_names = list(map(lambda x:x[0], cursor.description))
            return Post(
                title = field('title'),
                date = field('creation_datetime'),
                content = field('content'),
                commit = field('commit'),
            )

There are things that seems to creake when I use Python, like the use of decorators for things like accessors and static methods, maybe it’s a lack of habits.

In this function, I’ve used some techniques of functional programming that I learnt with Javascript: maps, for obtaining a list which contains only the cursor’s field names, and partial application. I have a function called `value_of` which takes a list of field names, an array of data, and the field I want to get. I created the `field` lambda which always applies the cursor’s field names and row data to `value_of`, so I only need to call it with the field name. I think is more readable than having to repeat the same parameters for each call.

Maybe I could have converted the whole row to a dictionary, but in the end, I did it like above.

When is that function called? In SQLite3DB query function I have this:

def query(self, query, args=(), one=False, factory=sqlite3.Row):
    self.__db().row_factory = factory
    cur = self.__db().execute(query, args)
    … 

    

Before executing the cursor, I set the row_factory property of sqlite3 db to the previous defined function. It will be the database’s library who call the Post factory function every time I need to access a post. In PostRepository I have a single function at the moment to obtain the posts of home page:

def home_posts(self):
    cursor = self.db.query(
        '''
        SELECT * FROM Posts
        ORDER BY date(Posts.creation_datetime) desc
        '''
        ,factory = PostFactory.from_sqlite3_row)

    return cursor

When the program accesses each element of the cursor, the `row_factory` function will be called automatically. I’ve found this a very interesting feature of sqlite3 library, maybe another db libraries have something similar but I haven’t checked.

Well, enough database for now. There is something interesting that Alchemy provides and I don’t have at the moment: database migrations. I’m performing database creation and modifications by hand, but I would like to automate that in the future.