Live Chat

We'll need to share your messages (and your email address if you're logged in) with our live chat provider, Drift. Here's their privacy policy.

If you don't want to do this, you can email us instead at contact@anvil.works.

Storing Data for your app

The Data Tables service stores tables of information for your Anvil app. It’s a full database system built on top of PostgreSQL.

If you’re not familiar with databases, it’s also similar to a spreadsheet - data is stored in tables with rows and columns.

To add Data Tables to your app, first add the Data Tables service in the App Browser.

Editing Data Tables in the IDE

From the Data Tables page, you can create a new, empty table, or you can add tables from any of your other apps.

Each table has its own columns. For example, if we are making a table of people for our organisation, we might have columns for name, age and employment status. Each row in the table represents an item - in this case, a person.

Here’s an example table as it looks in the app editor:

Each column can contain data of a particular type. For example, in the table above, Name is always a string, Age is always a number, and Employed is always a boolean value (either True or False). Any column can also be empty (None in Python).

Click to edit any data in the table (just like a spreadsheet). To add a new row, click the ‘+’ button at the bottom of the table, or click in the blank row, and start typing! You can add more columns with the ‘+’ button at the top right of the table.

To change a table’s title, click on it (in the tabs at the top). Each table also has a Python name you can use to refer to it in your code. When you first create a table, the Python name for your table will match the Table’s title. If you change the title, the Python name will change to match the new title. You can also change the Python name in the IDE - by doing so, you can give your table a different Python name from its title.

If you delete your app, its tables will be deleted as well (if no other apps are using it).

Column types

All columns of a data table have fixed types. You choose the column type when it is created or, if it does not already exist, when a value is first set from code using add_row. The available types are listed below, along with their corresponding python types.

  • Text - A Python str
  • Number - Any Python number
  • True/False - A Python boolean, True or False
  • Date - A Python datetime.date
  • Date and Time - A Python datetime.datetime
  • Simple Object - Can hold Python strings, numbers, dicts or lists
  • Media - Binary data (a Media object)
  • Link - A row (or list of rows) from another table

You can list all the columns in a Data Table by calling list_columns() on the table object. This returns a list of strings.

Using Data Tables from Python

There are Python methods to add rows to your table, search your table, update rows and delete them. These are available in client code as well as in Server Modules (you can configure access to your Data Tables by setting table permissions).

In Python, your table is represented by Row Objects. These behave like Python dictionaries, allowing you to get and set their values as you would for a Python dictionary. For example:

# Get the Row object whose 'name' value is Zaphod Beeblebrox
zaphod_row = app_tables.people.get(name="Zaphod Beeblebrox")
# Get Zaphod's 'age', and print it
print("Zaphod is currently %s years old" % zaphod_row['age'])

# It's Zaphod's birthday, update the database
zaphod_row['age'] += 1

Multiple Rows are represented by SearchIterators. You can get the entire contents of a table like this:

entire_table = app_tables.people.search()

This returns an object you can iterate over to get each Row in the table:

for row in app_tables.people.search():
  print(row["age"])

The advantage of this over simply listing the Rows is that the SearchIterator is lazy - it only loads data as the data is needed, making your app more efficient (you can cast SearchIterators to lists and Rows to dicts if you want to).

The search() method returns an iterable Python object, not a list. This is so you don’t have to fetch all the rows from your table before you can start processing them. If you want a list, pass the search result to Python’s list() function.

For a full explanation of how to use Data Tables in Python, see Using Data Tables from Python.

Sharing Data Tables Between Apps

Data Tables can be shared between apps. When you click the ‘+ Add a table’ or ‘+’ button to add a new Data Table, you will be given a list of existing tables from your other apps which you can add to the current app.

Any changes you make to a shared Data Table will also be reflected in the other apps that use it.

Table permissions are on a per-app basis: if you make a table client-writable in one app, other apps that use it will not be affected. The ‘Python name’ of a table is also per-app.

A table that is included in several apps will only be deleted when it is removed from all apps that use it.