coolqlite

A Semi-Opinionated, Simple But Featureful, Low-Dependency, Sqlite Python Library that:

• Builds queries with 3.14 template strings (it's safe I promise)
• Makes converting to/from python and sql datatypes easy
  (Including cattrs integration!)
• Encourages good practices & has reasonable defaults:
  • Temporal handling is built around datetype.
  • Everything has type annotations.
  • The defaults are tuned for a nice sqlite experience.

It is meant to feel similar to the standard library's sqlite module, but with a smoother, more modern UX.

Warranty & Maintenance

None is offered, express or implied. Back up your data.

This project has best-effort hobbyist maintenance status.

Usage & Example

Add to your project with uv add coolqlite, or however else your python package manager works.

Connecting looks similar to the stdlib, but with modern "good practices" defaults.

from coolqlite import connect
db = connect(":memory:")

You get autocommit=True and the row factory set to Row* for free.
(It's actually our own wrapper over sqlite3.Row, you'll see later.)

In honor of Little Bobby Tables, let's track some student info.

(Note: template strings are actually so new, syntax highlighting might look off!)

>>> from dataclasses import dataclass
>>> from datetime import datetime
>>> import zoneinfo
>>> from coolqlite import connect
>>> from datetype import AwareDateTime
>>> 
>>> @dataclass
... class Student:
...     name: str
...     teacher: str
...     graduated: AwareDateTime      
...
>>> db = connect(":memory:")
>>>
>>> with db.with_savepoint():
...     db.run(t"""create table Students
...         ( name TEXT NOT NULL
...         , teacher TEXT NOT NULL
...         , graduated TEXT NOT NULL
...         ) strict""")
...     for name, teach, when in (
...         ("Caleb", "Liam", datetime(2018, 5, 10)),
...         ("Nott", "Sam", datetime(2020, 2, 27)),
...         ("FCG", "Sam", datetime(2024, 4, 11)),
...     ):
...         when = when.replace(tzinfo=zoneinfo.ZoneInfo("America/Los_Angeles"))
...         db.run(t"insert into Students values ({name}, {teach}, {when:keeptz})")
...
>>> def students_for_teacher(teach: str):
...     print(f"Students for {teach}:")
...     for student in db.query(
...         t"select * from Students where teacher = {teach}", Student
...     ):
...         print(f"{student.name} graduated {student.graduated.isoformat(' ')}")
...     print()
...
>>> students_for_teacher("Sam")
Students for Sam:
Nott graduated 2020-02-27 00:00:00-08:00
FCG graduated 2024-04-11 00:00:00-07:00

>>> # No injection here!
>>> students_for_teacher("'' or TRUE; --")
Students for '' or TRUE; --:

Here's what we saw:

1. Values are properly parameterized (we didn't need to quote strings).
2. datetimes were converted into text.
3. Rows have a nicer repr than with sqlite3.Row.
4. Sql injection didn't work!

This is a simple intro which didn't cover features like defining converters, result manipulation functions, or cattrs integration. For that, check out the published documentation.

FAQ

What's the maintenance status of this library? How stable is it?

This project has best-effort hobbyist maintenance status, and no warranty is offered (implied or otherwise).

I made it because I needed it, and shared it because I thought it was cool.

If you wouldn't be comfortable copying and pasting it into your project as-is, you should not use it. Maybe it will inspire someone to make a more comprehensive alternative!

Expect potential breaking changes until 1.0. Also maybe after 1.0.

Why should I use this over SQLAlchemy?

Maybe you shouldn't! SQLAlchemy is pretty great.

Some folks might have opinions on ORMs versus query builders or whatnot, and sometimes one or the other is a better tool for the job.

Haven't you heard of Little Bobby Tables???

Yeah I went to school with him. Nice kid. Wonder what he's up to.

Anyways, note that python's "template strings" (t-strings) are different from "formatted string literals" (f-strings).

>>> bobby = "Robert'); DROP TABLE Students; --"

# plain string interpolation is DANGEROUS!
>>> f"insert into Students(name) values ('{bobby}');"

"insert into Students(name) values ('Robert'); DROP TABLE Students; --');"

# But t-strings are _managed_!
>>> t"insert into Students(name) values ({bobby});"

Template(strings=("insert into Students(name) values (", ");"), interpolations=(Interpolation("Robert'); DROP TABLE Students; --", 'bobby', None, ''),))

Low Dependency?

There are only two dependencies:

• datetype, which is incredibly small (and is mostly typing information), and itself has no dependencies
• cattrs, which in turn only depends on attrs. Attrs is such a ubiquitous package that if something went wrong with it, trust me, you'd hear about it.

How can I use this with other database backends?

I'm not interested in adding that feature. But that's part of why this is a "proof of concept". Hopefully someone will be inspired and make a version that works with other backends.

With that said, it could probably be adapted to anything PEP-249 compliant.

Is it blazing fast? 🚀🚀🚀

Speed was not a focus when designing this. But who knows, it might be fine.

How do I manage migrations?

You write the sql yourself.

We expose a helper function to get and set the user_version pragma with Connection.user_version so you can track what's been applied.

This ate my data!

Sorry! File a ticket. Restore from that backup you have. You have a backup, right?

Why do you keep saying "good practices"? Isn't the term "best practices"?

At its worst, "best practices" is a thought-terminating cliche, used to reinforce parrotted advice and block all nuance.

When you hear "best", think "best for whom?". There are no one-size-fits-all solutions in technology. Who cares what patterns or tools Google is using? Are you Google?

If you wanted to follow "best practices", you'd be using postgres anyway, right?

What does semi-opinionated mean?

We're semi-opinionated because we think the defaults are reasonable, and at worst are a good starting point for people.

At the same time, we make overriding the defaults and customizing things to your heart's content easy.

Is it thread-safe?

No. Maybe? TODO! The python docs are confusing re: check_same_thread, how does that work with sqlite's threading support?

What's the name mean?

"coolqlite" is short for "cool sqlite library". It may be shortened further to "cql", which is pronounced the same way as "sql", so probably just call it "coolqlite".

Prior Art

• The Node.js stdlib's sqlite module does something similar.