Additional Column Constraints?

[IgnoredAmbience]

I'm looking to import data from XML with a pre-defined schema that maps fairly closely to a relational database.
In particular, it has explicit annotations for when fields are required, optional, or when a default value should be inferred.

Would there be value in adding the ability to define NOT NULL and DEFAULT column constraints to sqlite-utils?

[simonw]

This seems reasonable. It could look like this:

db["dogs"].add_column("instagram", str, not_null=True)
db["dogs"].add_column("is_good_dog", bool, default=True)

And this:

$ sqlite-utils add-column mydb.db dogs instagram --not-null
$ sqlite-utils add-column mydb.db dogs is_good_dog integer --default=1

[simonw]

Hmm... we need the ability to pass --not-null when we are creating a table as well.

If you attempt to add NOT NULL to a column after a table has first been created you get this error:

sqlite3.OperationalError: Cannot add a NOT NULL column with default value NULL

[simonw]

Since you can't have one without the other, I'm going with --not-null-default= and not_null_default= for the add column versions of this.

[simonw]

But what to do for creating a table?

For the Python function I could do this:

db["cats"].create({
    "id": int,
    "name": str,
    "score": int, 
    "weight": float,
}, pk="id", not_null={"weight"}, defaults={"score": 1})

The CLI tool only every creates tables as a side-effect of a sqlite-utils insert or sqlite-utils upsert. I can have them accept optional arguments, --not-null colname and --default colname value:

echo '{"name": "Cleo", "age": 4, "score": 2}' | \
  sqlite-utils insert dogs.db dogs - \
    --not-null age \
    --not-null name \
    --default score 1

[simonw]

Maybe it's time to create a sqlite-utils create-table command here too, rather than forcing people to create tables only by inserting example data.

[simonw]

@IgnoredAmbience this is now shipped in sqlite-utils 1.2 - documentation here:

• https://sqlite-utils.readthedocs.io/en/latest/python-api.html#python-api-defaults-not-null
• https://sqlite-utils.readthedocs.io/en/latest/cli.html#cli-defaults-not-null