Creating tables

First thing you need to do for any database is create one or more tables. The basic syntax is straightforward.

The only required argument is a table name:

person = Table('person')

By default, tables have an _id column. So printing person results in a very simple table:

print(person)

result:

CREATE TABLE People (_id INTEGER PRIMARY KEY) 

Columns

Naturally, we need more than an id. So let's add a few basic columns. Columns need a name, and then optionally a type and default value. Default type is TEXT.

name = Column('name')

Let's also require the column to be non-null, and default to the empty string:

name.not_null
name.default("''")

All methods and properties return the object itself so you can chain all the methods in one go like so:

name = Column('name').not_null.default("''")

Great. Let's add it to the table and see what it looks like now:

person.add_cols(name)

CREATE TABLE person
  (_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL DEFAULT '')

Unique constraints

Now let's one more column, but require that the values specified there to be unique:

person.add_cols(Column('idnumber').integer.not_null)
person.add_constraints(Unique('idnumber').on_conflict_replace)

CREATE TABLE person
  (_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL DEFAULT '',
  idnumber INTEGER NOT NULL,

  UNIQUE (idnumber) ON CONFLICT REPLACE)

Foreign keys

Let's say we know also want people to be part of organizations, let's create a table for that.

Table('organization').add_cols(Column('name').not_null)

CREATE TABLE organization
  (_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL)

And let's add another column to our previous person table, which references the id column of this new table:

person.add_cols(Column('orgId').integer.not_null)
person.add_constraints(ForeignKey('orgId').references('organization').on_delete_cascade)

Result:

CREATE TABLE person
  (_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL DEFAULT '',
  idnumber INTEGER NOT NULL,
  orgId INTEGER NOT NULL,

  UNIQUE (idnumber) ON CONFLICT REPLACE,
  FOREIGN KEY (orgId) REFERENCES organization(_id) ON DELETE CASCADE)

Best practices

Above I have written the names of columns and tables manually for sake of clarity. When you write your own script later, I recommend using variable names instead so it is easy to change the script without breaking the code.

All the above should thus be done more like this ideally:

org = Table('organization').add_cols(Column('name').not_null)
person = Table('person').add_cols(Column('name').not_null.default("''"))

col_idnum = Column('idNumber').integer.not_null
person.add_cols(col_idnum)
person.add_constraints(Unique(col_idnum.name).on_conflict_replace)

col_orgid = Column('orgId').integer.not_null
person.add_cols(col_orgid)
person.add_constraints(ForeignKey(col_orgid.name).references(org.name))

print(org)
print(person)

Result:

CREATE TABLE organization
  (_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL)

CREATE TABLE person
  (_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL DEFAULT '',
  idNumber INTEGER NOT NULL,
  orgId INTEGER NOT NULL,

  UNIQUE (idNumber) ON CONFLICT REPLACE,
  FOREIGN KEY (orgId) REFERENCES organization(_id) )