Entries tagged with sqlite

Encrypted SQLite Databases with Python and SQLCipher


SQLCipher, created by Zetetic, is an open-source library that provides transparent 256-bit AES encryption for your SQLite databases. SQLCipher is used by a large number of organizations, including Nasa, SalesForce, Xerox and more. The project is open-source and BSD licensed. Best of all, there are open-source python bindings.

A GitHub user known as The Dod was kind enough to contribute a sqlcipher playhouse module, making it a snap to use Peewee with SQLCipher.

In this post, I'll show how to compile SQLCipher and the pysqlcipher bindings, then use peewee ORM to work with an encrypted SQLite database.


SQLite: Small. Fast. Reliable. Choose any three.

Sqlite Logo

SQLite is a fantastic database and in this post I'd like to explain why I think that, for many scenarios, SQLite is actually a great choice. I hope to also clear up some common misconceptions about SQLite.


Using SQLite Full-Text Search with Python

Full-text search with SQLite

In this post I will show how to use SQLite full-text search with Python (and a lot of help from peewee ORM). We will see how to index content for searching, and how to order search results using two ranking algorithms.

Last week I migrated my site from Postgresql to SQLite. I had been using Redis to power my site's search, but since SQLite has an awesome full-text search extension, I decided to give it a try. I am really pleased with the results, and being able to specify boolean search queries is an added plus. Here is a brief overview of the types of search queries SQLite supports:

  • Simple phrase: peewee would return all docs containing the word peewee.
  • Prefix queries: py* would return docs containing Python, pypi, etc.
  • Quoted phrases: "sqlite extension"
  • NEAR: peewee NEAR sqlite would return docs containing the words peewee and sqlite with no more than 10 intervening words. You can also specify the max number of intervening words, e.g. peewee NEAR/3 sqlite.
  • AND, OR, NOT: sqlite OR postgresql AND NOT mysql would return docs about high-quality databases (just trollin).

Check out the full post for details on adding full-text search to your project.


Migrating to SQLite

Sqlite Logo

Small. Fast. Reliable. Choose any three.

I made the decision this week to migrate my personal sites and several other sites I host onto SQLite. Previously almost everything I hosted had been using Postgresql. The move was motivated by a couple factors:

  • SQLite is awesome!
  • Self-contained: does not require a separate server process
  • Data is stored in a single file, simplifying backups
  • Excellent Python (and peewee) support
  • Full-text search

At times it has seemed to me that there is a tacit agreement within the Flask / Django communities that if you're using SQL you should be using Postgresql. Postgresql is an amazing piece of engineering. I have spent the last five years of my career working exclusively with it, and I am continually impressed by its performance and the constant stream of great new features.

So why change things?

Well, as my list indicates, there are a handful of reasons. But the primary reason was that I wanted something lightweight. I'm running a fairly low-traffic, read-heavy site, so Postgresql was definitely overkill. My blog is deployed on a VPS with very limited resources, so every MB of RAM counts. Additionally, I wasn't using any special Postgresql features so there was nothing holding me back.