ORM Cheatsheet — Django QuerySet API¶
A quick reference for all the main Django ORM operations. The examples are adapted for
notes_chat_app(models:Note,Notebook,Tag,TodoItem,User).
Contents¶
- Basic CRUD
- Filtering — filter / exclude
- Lookup Types — double underscore
- Q objects — OR / NOT conditions
- F objects — fields in expressions
- Aggregation and annotation
- select_related and prefetch_related
- values / values_list
- order_by / distinct / only / defer
- Updating and deleting
- ManyToMany operations
- Chaining and lazy evaluation
- get_object_or_404 and get_or_create
- Transactions
- Raw SQL (when the ORM is not enough)
Basic CRUD¶
CREATE¶
# objects.create() — INSERT + returns the object
note = Note.objects.create(user=alice, title='Test', priority=2)
# Or: __init__ + save()
note = Note(user=alice, title='Test', priority=2)
note.save() # INSERT INTO notes_app_note ...
# bulk_create — insert several rows in one query
Note.objects.bulk_create([
Note(user=alice, title='Note 1'),
Note(user=alice, title='Note 2'),
Note(user=alice, title='Note 3'),
])
# SQL: INSERT INTO notes_app_note (...) VALUES (...), (...), (...)
# WARNING: does not call .save(), and pre_save/post_save signals do not fire
# get_or_create — find or create
tag, created = Tag.objects.get_or_create(
user=alice, name='python',
defaults={'color': '#3776AB'}
)
# created=True → the tag was created (did not exist)
# created=False → the tag was found (already existed)
READ¶
# all() — all records (lazy!)
notes = Note.objects.all()
# The SQL runs only on iteration/list()/count() etc.
# get() — exactly one object or an exception
note = Note.objects.get(pk=1)
# DoesNotExist if not found
# MultipleObjectsReturned if more than one is found
# first() / last() — first/last or None
note = Note.objects.filter(user=alice).first() # None if not found
note = Note.objects.filter(user=alice).last()
# count() — number of rows (SELECT COUNT(*))
n = Note.objects.filter(user=alice).count()
# exists() — EXISTS query (the fastest way to check)
if Note.objects.filter(user=alice, is_pinned=True).exists():
...
# SQL: SELECT 1 FROM notes_app_note WHERE ... LIMIT 1 (very fast!)
# filter() — a selection (QuerySet)
notes = Note.objects.filter(user=alice, is_archived=False)
UPDATE¶
# A single object
note = Note.objects.get(pk=1)
note.title = 'New title'
note.save() # UPDATE ... SET title=... (all fields)
note.save(update_fields=['title', 'updated_at'])
# UPDATE ... SET title=..., updated_at=... (only the listed ones → more efficient)
# Bulk update (QuerySet.update)
Note.objects.filter(user=alice).update(is_archived=True)
# SQL: UPDATE notes_app_note SET is_archived=TRUE WHERE user_id=alice.id
# Returns the number of updated rows (int)
# WARNING: does not call .save(), and signals do not fire
# update_or_create
notebook, created = Notebook.objects.update_or_create(
user=alice, title='Work', # lookup fields
defaults={'color': '#0d6efd', 'is_default': True} # what to update/set
)
DELETE¶
# A single object
note = Note.objects.get(pk=1)
note.delete() # DELETE FROM notes_app_note WHERE id=1
# CASCADE automatically deletes the related Reminder objects
# Bulk delete
Note.objects.filter(user=alice, is_archived=True).delete()
# Returns (count, {model: count})
# → (5, {'notes_app.Note': 5, 'notes_app.Reminder': 12})
# Delete everything (CAREFULLY!)
Note.objects.all().delete() # Deletes ALL notes!
Filtering¶
# Several conditions = AND
Note.objects.filter(user=alice, is_pinned=True, priority=3)
# SQL: WHERE user_id=... AND is_pinned=TRUE AND priority=3
# Chaining filter() = AND
Note.objects.filter(user=alice).filter(is_pinned=True)
# SQL: WHERE user_id=... AND is_pinned=TRUE (same result)
# exclude() = NOT
Note.objects.exclude(is_archived=True)
# SQL: WHERE NOT is_archived=TRUE (= WHERE is_archived=FALSE)
Note.objects.exclude(priority__in=[1, 2])
# SQL: WHERE priority NOT IN (1, 2)
# Combining filter + exclude
Note.objects.filter(user=alice).exclude(is_archived=True)
# SQL: WHERE user_id=... AND NOT is_archived=TRUE
Lookup Types¶
# ── Comparison ───────────────────────────────────────────────────────────────
Note.objects.filter(priority__exact=3) # = 3 (same as priority=3)
Note.objects.filter(priority__gt=1) # > 1
Note.objects.filter(priority__gte=2) # >= 2
Note.objects.filter(priority__lt=3) # < 3
Note.objects.filter(priority__lte=2) # <= 2
Note.objects.filter(priority__range=(1, 3)) # BETWEEN 1 AND 3
# ── Strings ──────────────────────────────────────────────────────────────────
Note.objects.filter(title__exact='Django') # = 'Django' (case-sensitive)
Note.objects.filter(title__iexact='django') # = 'django' (case-insensitive)
Note.objects.filter(title__contains='ang') # LIKE '%ang%'
Note.objects.filter(title__icontains='ang') # ILIKE '%ang%'
Note.objects.filter(title__startswith='Dj') # LIKE 'Dj%'
Note.objects.filter(title__istartswith='dj') # ILIKE 'dj%'
Note.objects.filter(title__endswith='.md') # LIKE '%.md'
Note.objects.filter(title__regex=r'^\d+') # REGEXP (case-sensitive)
Note.objects.filter(title__iregex=r'^\d+') # REGEXP (case-insensitive)
# ── NULL ─────────────────────────────────────────────────────────────────────
Note.objects.filter(notebook__isnull=True) # IS NULL
Note.objects.filter(notebook__isnull=False) # IS NOT NULL
# ── Lists ────────────────────────────────────────────────────────────────────
Note.objects.filter(priority__in=[1, 2]) # IN (1, 2)
Note.objects.filter(id__in=[1, 5, 10, 42]) # IN (1, 5, 10, 42)
# Also: you can pass a QuerySet!
active_user_ids = User.objects.filter(is_active=True).values_list('id', flat=True)
Note.objects.filter(user_id__in=active_user_ids) # Subquery!
# ── Dates ────────────────────────────────────────────────────────────────────
Note.objects.filter(created_at__year=2026)
Note.objects.filter(created_at__month=6)
Note.objects.filter(created_at__day=13)
Note.objects.filter(created_at__hour=10)
Note.objects.filter(created_at__date=date(2026, 6, 13))
Note.objects.filter(created_at__gte=datetime(2026, 1, 1, tzinfo=timezone.utc))
# ── FK Traversal (double __ = JOIN) ────────────────────────────────────────
Note.objects.filter(notebook__title='Work') # JOIN notebooks WHERE title='Work'
Note.objects.filter(notebook__user=alice) # 2 levels of JOIN
Note.objects.filter(notebook__title__icontains='w') # JOIN + ILIKE
Note.objects.filter(tags__name='python') # JOIN through the M:N junction table
# ── Reverse FK ───────────────────────────────────────────────────────────────
# Find Notebooks that have at least one note:
Notebook.objects.filter(notes__isnull=False).distinct()
# Find Notebooks with HIGH-priority notes:
Notebook.objects.filter(notes__priority=Note.PRIORITY_HIGH)
Q objects¶
from django.db.models import Q
# OR
Note.objects.filter(Q(title__icontains='django') | Q(content__icontains='django'))
# SQL: WHERE (title ILIKE '%django%' OR content ILIKE '%django%')
# AND (explicit)
Note.objects.filter(Q(is_pinned=True) & Q(priority=3))
# NOT
Note.objects.filter(~Q(is_archived=True))
# SQL: WHERE NOT is_archived = TRUE
# Combining Q and kwargs
Note.objects.filter(
Q(user=alice) | Q(group__in=alice_groups),
is_archived=False # ← kwargs are always AND
)
# SQL: WHERE (user_id=... OR group_id IN (...)) AND is_archived=FALSE
# Dynamic filters — build up Q:
query = Q()
if search:
query &= Q(title__icontains=search) | Q(content__icontains=search)
if tag:
query &= Q(tags=tag)
notes = Note.objects.filter(query, user=alice)
F objects¶
from django.db.models import F
# Compare a field with a field
Note.objects.filter(updated_at__gt=F('created_at'))
# SQL: WHERE updated_at > created_at
# Atomic update without a race condition:
TodoItem.objects.filter(todo_list=todo_list).update(order_position=F('order_position') + 1)
# SQL: UPDATE ... SET order_position = order_position + 1
# WITHOUT F: you need SELECT → Python → UPDATE (two queries + a race condition!)
# Using it in annotate:
from django.db.models import ExpressionWrapper, DurationField
from django.db.models.functions import Now
Note.objects.annotate(
age=ExpressionWrapper(Now() - F('created_at'), output_field=DurationField())
)
# F in save():
item.order_position = F('order_position') + 1
item.save(update_fields=['order_position'])
# SQL: UPDATE ... SET order_position = order_position + 1
Aggregation and annotation¶
from django.db.models import Count, Avg, Max, Min, Sum
# ── aggregate() — one value for the whole QuerySet ────────────────────────
Note.objects.aggregate(total=Count('id'))
# → {'total': 42}
Note.objects.filter(user=alice).aggregate(
total=Count('id'),
pinned=Count('id', filter=Q(is_pinned=True)),
avg_priority=Avg('priority'),
max_priority=Max('priority'),
min_priority=Min('priority'),
)
# → {'total': 42, 'pinned': 5, 'avg_priority': 1.8, 'max_priority': 3, 'min_priority': 1}
# ── annotate() — a value for EACH row ────────────────────────────────────
Notebook.objects.annotate(note_count=Count('notes'))
# SELECT *, COUNT(note.id) AS note_count FROM notebooks LEFT JOIN notes ...
# notebook.note_count is available on every object!
# Filtered annotate:
Notebook.objects.annotate(
active_notes=Count('notes', filter=Q(notes__is_archived=False)),
pinned_notes=Count('notes', filter=Q(notes__is_pinned=True)),
)
# annotate + filter (filtering BY THE ANNOTATION):
Notebook.objects.annotate(
note_count=Count('notes')
).filter(note_count__gt=5)
# Notebooks with more than 5 notes
# annotate + order_by:
Notebook.objects.annotate(
note_count=Count('notes')
).order_by('-note_count')
# Sorted by the number of notes
# Tags with a note count (a real example from selectors.py):
Tag.objects.filter(user=user).annotate(
note_count=Count('notes')
).order_by('name')
select_related and prefetch_related¶
# ── select_related: ForeignKey / OneToOne → JOIN (1 query) ────────────────
# WITHOUT optimization: N+1
notes = Note.objects.all()
for note in notes:
print(note.notebook.title) # ← 1 SQL query per iteration!
# With select_related: 1 SQL JOIN
notes = Note.objects.select_related('notebook', 'user').all()
# SQL: SELECT notes.*, notebooks.*, auth_user.*
# FROM notes
# LEFT OUTER JOIN notebooks ON (notes.notebook_id = notebooks.id)
# INNER JOIN auth_user ON (notes.user_id = auth_user.id)
# Depth (nested FK):
notes = Note.objects.select_related('notebook__user')
# Access: note.notebook.user.username — no extra SQL!
# ── prefetch_related: ManyToMany / reverse FK → 2 queries ────────────────
# WITHOUT optimization:
for note in notes:
for tag in note.tags.all(): # ← 1 SQL query per note!
print(tag.name)
# With prefetch_related: 2 SQL queries total (regardless of the number of notes)
notes = Note.objects.prefetch_related('tags')
# SQL 1: SELECT * FROM notes_app_note WHERE ...
# SQL 2: SELECT tags.* FROM tags
# INNER JOIN notes_app_note_tags ON ...
# WHERE notes_app_note_tags.note_id IN (1, 2, 3, ...)
# Django joins them in memory itself
# Reverse FK:
notes = Note.objects.prefetch_related('reminders')
# Access: note.reminders.all() → from the cache, no SQL
# ── Combining ────────────────────────────────────────────────────────────
notes = Note.objects.filter(user=user, is_archived=False) \
.select_related('notebook', 'user') \
.prefetch_related('tags', 'reminders') \
.order_by('-is_pinned', '-priority')
# TOTAL: 3 SQL queries for any number of notes!
# ── Prefetch with a filter (Prefetch object) ─────────────────────────────
from django.db.models import Prefetch
notes = Note.objects.prefetch_related(
Prefetch('reminders', queryset=Reminder.objects.filter(is_sent=False))
# Load only reminders that have not been sent
)
# note.reminders.all() → only is_sent=False (from the cache)
values / values_list¶
# values() → a list of dicts (faster than full objects)
Note.objects.values('title', 'priority')
# → [{'title': 'Test', 'priority': 1}, {'title': 'Django', 'priority': 2}]
# SQL: SELECT title, priority FROM notes_app_note
# values() with FK traversal:
Note.objects.values('title', 'notebook__title')
# → [{'title': 'Test', 'notebook__title': 'Work'}, ...]
# SQL: SELECT notes.title, notebooks.title FROM ... JOIN ...
# values_list() → a list of tuples (even faster)
Note.objects.values_list('title', 'priority')
# → [('Test', 1), ('Django', 2), ...]
# flat=True → one column = a flat list
note_ids = Note.objects.filter(user=alice).values_list('id', flat=True)
# → [1, 5, 10, 42] (not [(1,), (5,), ...])
# A useful application:
tag_ids = note.tags.values_list('id', flat=True)
# → [3, 7, 12] — tag IDs for a form
# Checking whether an id is in the list:
if request.user.id in group.user_set.values_list('id', flat=True):
...
order_by / distinct / only / defer¶
# ── Sorting ──────────────────────────────────────────────────────────────
Note.objects.order_by('priority') # ASC (ascending)
Note.objects.order_by('-priority') # DESC (descending)
Note.objects.order_by('-is_pinned', '-priority', '-updated_at') # Multiple
Note.objects.order_by('?') # Random (SLOW on large tables!)
Note.objects.order_by() # Remove ordering
# Sorting by FK:
Note.objects.order_by('notebook__title') # By notebook title
# ── distinct() ───────────────────────────────────────────────────────────
# A JOIN through tags can produce duplicates → distinct removes them:
Note.objects.filter(tags__name='python').distinct()
# SQL: SELECT DISTINCT * FROM notes JOIN note_tags JOIN tags WHERE ...
# ── Limiting (slicing) ───────────────────────────────────────────────────
Note.objects.all()[:10] # LIMIT 10
Note.objects.all()[10:20] # LIMIT 10 OFFSET 10
Note.objects.all()[0] # The first one (LIMIT 1)
Note.objects.all()[-1] # ⚠️ NOT SUPPORTED! Use .last()
# ── only() / defer() — load only the fields you need ─────────────────────
# only(): load ONLY the listed fields
Note.objects.only('id', 'title', 'priority')
# SQL: SELECT id, title, priority FROM notes_app_note
# WARNING: accessing another field → an extra SELECT!
# defer(): load ALL fields EXCEPT the listed ones
Note.objects.defer('content') # content = a large TextField
# SQL: SELECT id, title, priority, is_pinned, ... FROM ... (without content)
# Useful when content = a large text not needed in the list
# iterator() — do not cache the QuerySet (for large selections)
for note in Note.objects.filter(user=alice).iterator():
process(note) # Does not load everything into memory at once
Updating and deleting¶
# ── update() — bulk update with one SQL query ─────────────────────────────
Note.objects.filter(user=alice, is_archived=False).update(
is_archived=True
)
# SQL: UPDATE notes_app_note SET is_archived=TRUE WHERE user_id=... AND is_archived=FALSE
# Returns: int (number of updated rows)
# With F():
TodoItem.objects.filter(todo_list=todo_list).update(order_position=F('order_position') + 1)
# With a condition:
from datetime import timedelta
from django.utils import timezone
Note.objects.filter(
created_at__lt=timezone.now() - timedelta(days=365)
).update(is_archived=True)
# Archive notes older than a year
# ── delete() — bulk delete ───────────────────────────────────────────────
deleted_count, breakdown = Note.objects.filter(
user=alice, is_archived=True
).delete()
# deleted_count = 5
# breakdown = {'notes_app.Note': 5, 'notes_app.Reminder': 12}
# Django automatically deletes related objects (CASCADE)
# ── bulk_update() — bulk update of objects ───────────────────────────────
notes = list(Note.objects.filter(user=alice))
for note in notes:
note.is_archived = True
Note.objects.bulk_update(notes, ['is_archived'])
# One UPDATE instead of N separate .save() calls
ManyToMany¶
note = Note.objects.get(pk=1)
tag_py = Tag.objects.get(name='python', user=alice)
tag_dj = Tag.objects.get(name='django', user=alice)
# Add
note.tags.add(tag_py) # INSERT INTO note_tags (note_id, tag_id)
note.tags.add(tag_py, tag_dj) # Several at once
# Remove a specific one
note.tags.remove(tag_py) # DELETE FROM note_tags WHERE note_id=... AND tag_id=...
# Check whether it is present
note.tags.filter(pk=tag_py.pk).exists()
# Clear all
note.tags.clear() # DELETE FROM note_tags WHERE note_id=...
# Set exactly (replace):
note.tags.set([tag_py, tag_dj]) # DELETE old + INSERT new
note.tags.set(Tag.objects.filter(user=alice, name__in=['python', 'django']))
# Reverse — notes with a tag:
tag_py.notes.all() # related_name='notes' on the ManyToManyField
tag_py.notes.filter(user=alice)
# Number of tags on a note:
note.tags.count() # SELECT COUNT(*) FROM ...
# All tags:
note.tags.all()
note.tags.order_by('name')
note.tags.values_list('name', flat=True) # → ['django', 'python']
Chaining¶
# A QuerySet is LAZY — SQL does not run when it is created
qs = Note.objects.filter(user=alice) # Nothing yet!
qs = qs.filter(is_archived=False) # Refinement
qs = qs.select_related('notebook') # Optimization
qs = qs.order_by('-priority') # Sorting
# The SQL runs only here:
result = list(qs) # ← SELECT here!
count = qs.count() # ← SELECT COUNT(*) here!
first = qs.first() # ← SELECT ... LIMIT 1 here!
exists = qs.exists() # ← SELECT 1 ... LIMIT 1 here!
for note in qs: ... # ← SELECT here!
# A QuerySet can be passed to a template — the SQL runs during rendering
return render(request, 'template.html', {'notes': qs})
# The Django Template Engine iterates over qs → SQL here!
# Store the result in Python (run the SQL now):
notes_list = list(qs) # Loads everything into memory
notes_tuple = tuple(qs)
# Cloning a QuerySet (does not run SQL):
cloned_qs = qs.all() # A new QuerySet with the same parameters
get_object_or_404 and get_or_create¶
from django.shortcuts import get_object_or_404, get_list_or_404
from django.http import Http404
# get_object_or_404 — get() or Http404
note = get_object_or_404(Note, pk=pk)
# If not found → Http404 → Django 404 page
# NOT: DoesNotExist → 500 Internal Error
# With several conditions (IDOR protection!):
note = get_object_or_404(Note, pk=pk, user=request.user)
# → 404 if the note does not exist OR belongs to another user
# get_list_or_404 — QuerySet or Http404
notes = get_list_or_404(Note, user=request.user, is_pinned=True)
# → Http404 if the list is empty
# Raising Http404 manually:
if not Note.objects.filter(pk=pk, user=request.user).exists():
raise Http404("Нотатку не знайдено")
# get_or_create — find or create (atomic):
tag, created = Tag.objects.get_or_create(
user=alice,
name='python', # Lookup fields
defaults={'color': '#3776AB'} # Values used only on CREATE
)
# If 'python' already exists → created=False, tag = existing
# If not → created=True, tag = new
# update_or_create:
notebook, created = Notebook.objects.update_or_create(
user=alice,
title='Work', # Lookup fields
defaults={'color': '#0d6efd'} # Update these fields (always)
)
Transactions¶
from django.db import transaction
# Context manager:
with transaction.atomic():
note = Note.objects.create(user=alice, title='Test')
tags = Tag.objects.filter(id__in=tag_ids, user=alice)
note.tags.set(tags)
# On an exception → ROLLBACK of both operations
# COMMIT if everything is OK
# Decorator:
@transaction.atomic
def create_notebook(user, title, is_default=False):
if is_default:
Notebook.objects.filter(user=user, is_default=True).update(is_default=False)
return Notebook.objects.create(user=user, title=title, is_default=is_default)
# select_for_update — row locking (SELECT ... FOR UPDATE):
with transaction.atomic():
note = Note.objects.select_for_update().get(pk=1)
# Other transactions wait until this block finishes
note.is_pinned = not note.is_pinned
note.save()
Raw SQL¶
# When the ORM is not enough:
# Manager.raw() — raw SQL, returns a RawQuerySet
notes = Note.objects.raw(
"SELECT * FROM notes_app_note WHERE user_id = %s ORDER BY priority DESC",
[alice.id]
)
# connection.cursor() — full control
from django.db import connection
with connection.cursor() as cursor:
cursor.execute(
"SELECT notebook_id, COUNT(*) FROM notes_app_note WHERE user_id=%s GROUP BY notebook_id",
[alice.id]
)
rows = cursor.fetchall()
# → [(1, 5), (2, 12), ...]
# WARNING: always use parameterized queries!
# ❌ cursor.execute(f"SELECT * WHERE id = {pk}") → SQL injection!
# ✅ cursor.execute("SELECT * WHERE id = %s", [pk]) → safe
Quick cheatsheet¶
# READING
Note.objects.all() # All (lazy)
Note.objects.filter(user=user) # WHERE
Note.objects.exclude(is_archived=True) # NOT
Note.objects.get(pk=1) # One or an exception
Note.objects.first() / .last() # First/last or None
Note.objects.count() # SELECT COUNT(*)
Note.objects.exists() # SELECT 1 LIMIT 1
# OPTIMIZATION
.select_related('notebook', 'user') # FK → JOIN
.prefetch_related('tags', 'reminders') # M:N / reverse FK → 2 queries
.only('id', 'title') # Load only these fields
.defer('content') # All fields except content
# FILTERING
filter(priority__gt=1) # > 1
filter(title__icontains='django') # ILIKE '%django%'
filter(notebook__isnull=True) # IS NULL
filter(tags__name='python') # M:N JOIN
filter(Q(a=1) | Q(b=2)) # OR
filter(~Q(archived=True)) # NOT
# SORTING AND LIMITING
.order_by('-priority', '-updated_at') # ORDER BY
.distinct() # SELECT DISTINCT
[:10] # LIMIT 10
[10:20] # LIMIT 10 OFFSET 10
# AGGREGATION
.count() # COUNT(*)
.aggregate(avg=Avg('priority')) # {'avg': 1.8}
.annotate(n=Count('notes')) # Per-row count
# VALUES (without full objects)
.values('title', 'priority') # → [{'title': ...}, ...]
.values_list('id', flat=True) # → [1, 2, 3, ...]
# WRITING
Note.objects.create(user=user, title='T') # INSERT + return
Note.objects.filter(...).update(field=val) # UPDATE (bulk)
Note.objects.filter(...).delete() # DELETE (bulk)
note.save(update_fields=['title']) # UPDATE (one record, selected fields)
# MANY-TO-MANY
note.tags.add(tag1, tag2) # INSERT
note.tags.remove(tag1) # DELETE
note.tags.set([tag1, tag2]) # DELETE + INSERT
note.tags.clear() # DELETE all
note.tags.all() # SELECT
# TRANSACTIONS
with transaction.atomic(): ... # ROLLBACK on an exception