Django SQLite: How to Use SQLite in Django

If you want to learn how to use SQLite databases with the Django web framework, you’ve come to the right place. SQLite is a great choice for starting Django because it doesn’t require setting up a separate database server. In this comprehensive guide, we’ll walk through everything you need to know, from setting up your first SQLite database to querying data and optimizing for production.

Why SQLite Works Well with Django

SQLite is a self-contained, serverless, zero-configuration SQL database engine that is very easy to integrate into Python applications. Unlike databases like MySQL and PostgreSQL, SQLite doesn’t run as a separate database server process. The entire database is stored as a single cross-platform file on disk that can be easily copied.

The simplicity of SQLite makes it an excellent fit for Django web applications in development. Key SQLite advantages include:

• No database server required – Get up and running instantly without complex installs
• Uses simple file access – The database is a file on disk that can integrate nicely with source control
• Lightweight and fast – Quinton small datasets with low overhead
• Built-in Django support – Integration is seamless with no extra dependencies

While SQLite has some limitations that make other databases preferable for high-traffic production apps, it’s perfectly suitable for low to medium-traffic sites and for development and testing.

Setting Up SQLite Database for Django Project

Getting started with SQLite and Django is straightforward since Django has built-in support for SQLite.

To demonstrate, we’ll create a new Django project called articles and configure it to use SQLite:

# Create project folder 
$ mkdir articles
$ cd articles

# Create and activate virtualenv
$ python3 -m venv env 
$ source env/bin/activate

# Install Django
$ pip install django

# Create the Django project called articles
$ django-admin startproject articles .

This will create a new Django project structure for us. To enable SQLite as the database, first install the sqlite3 package:

$ pip install sqlite3

Then update the DATABASES settings in settings.py:

# settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': BASE_DIR / 'db.sqlite3',
    }
}

That configures SQLite as the default database using a file called db.sqlite3 in the base directory.

We can now run migrations to initialize the database tables Django needs:

$ python manage.py migrate  
Operations to perform:  
  Apply all migrations: admin, auth, contenttypes, sessions  
Running migrations:  
  ...  
  Applying contenttypes.0001_initial... OK  
  ...  

Our SQLite database is now all set up! Let’s look at working with models and query data next.

Models, Migrations, and Querying Data

As a simple example to illustrate using SQLite and Django together, let’s create an Article model to store blog posts in the database:

# models.py

from django.db import models

class Article(models.Model):
    title = models.CharField(max_length=100)  
    content = models.TextField()
    published_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.title 

To create the corresponding articles table in SQLite, run:

$ python manage.py makemigrations
$ python manage.py migrate   

This will execute the SQLite statements to create the articles table. We can view the table creation SQL with:

$ python manage.py sqlmigrate articles 0001

BEGIN;  
--  
-- Create model Article  
--  
CREATE TABLE "articles_article" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "title" varchar(100) NOT NULL, "content" text NOT NULL, "published_at" datetime NOT NULL);  
COMMIT;

With that table made in our SQLite database, we can now query and persist data with the Article model.

For example in the Django shell:

>>> from articles.models import Article
>>> article = Article(title='My First Article', content='Hello world!')
>>> article.save()

>>> Article.objects.all()  
<QuerySet [<Article: My First Article>]> 

>>> article = Article.objects.first() 
>>> article.title
'My First Article'

We can create, persist, and query Article records seamlessly using Django’s object-relational mapper capabilities.

Some key SQLite-specific model field types and database functions are also available to unlock the full capabilities of SQLite:

import sqlite3
from django.db import models

class Store(models.Model):
    name = models.CharField(max_length=200)
    opens_at = models.TimeField() 
    closes_at = models.TimeField()

    def open_status(self):
        now = sqlite3.func.current_time()
        return now > self.opens_at and now < self.closes_at  

This shows the usage of SQLite-specific TimeField and current_time() to determine if a store is currently open.

Django thus makes it very convenient to work with SQLite databases in Python web apps!

Comparing SQLite and Other Databases

How does SQLite compare to other database options for Django like PostgreSQL and MySQL? Here’s a quick comparison:

Feature	SQLite	PostgreSQL	MySQL
Database server required	No	Yes	Yes
Transactions supported	Yes	Yes	Yes
Concurrency	Low	High	High
Data capacity	Small	Large	Large
Data types	Limited	Advanced	Advanced
Optimized for	Local data, rapid dev	Production apps	Production apps

• SQLite is the easiest to setup since it doesn’t require installing and configuring a separate database server process.
• PostgreSQL and MySQL can handle larger data volumes and higher traffic applications.
• Advanced data types and performance tuning capabilities in PostgreSQL and MySQL
• SQLite is best suited for low to medium-traffic sites and for development/testing.

SQLite works great to get your Django application prototyped and developed quickly, while PostgreSQL and MySQL are recommended for deployment to production.

Optimizing SQLite Databases for Use with Django

While SQLite performance works reasonably well out of the box, there are a few best practices worth implementing, especially when deploying Django apps to production:

Use Memory File Backing

For single-process scenarios like most Django deployments, configuring SQLite to keep the entire database in memory instead of repeatedly reading/writing from disk can provide a nice performance boost.

Enable memory-backed SQLite databases in Django by specifying a :memory: name:

# settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': 'file::memory:', 
    }
}

Tune Cache and Sync Settings

A couple of SQLite performance tuning PRAGMA settings to tweak:

• cache_size – Increase cache size for better hit ratio
• synchronous – Set to NORMAL to enable OS-level syncing instead of slower step-by-step syncing

Tune these PRAGMAs at runtime:

import sqlite3 
conn = sqlite3.connect('db.sqlite3')

conn.execute('PRAGMA cache_size = -20000')  
conn.execute('PRAGMA synchronous = NORMAL')

Use WAL Journal Mode

SQLite’s WAL mode uses a write-ahead log instead of locking the entire database file on each write. This allows better concurrency with multiple readers/single writer.

Enable WAL mode with:

conn.execute('PRAGMA journal_mode = WAL')

With those best practices implemented, SQLite can also handle increased traffic very efficiently within Django web apps!

Wrap Up

And there you have it – a comprehensive guide to using SQLite databases with Django!

We covered key topics like:

• Setting up SQLite as the default Django database
• Creating models and migrations to persist data
• Querying and working with model data
• Comparing SQLite with other Django database options
• Optimizing SQLite databases for better performance

SQLite is a fast, lightweight, and convenient database engine that integrates nicely with Django’s ORM and Python focus. It’s a great choice for development, testing and smaller production workloads.

I hope this guide gave you a hands-on overview of everything you need to effectively leverage SQLite in your next Django project! Let me know if you have any other questions.