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.

Indexing and SQL

Data in Anvil Data Tables is fully indexed for simple equality searches (e.g. name="Brian") using a GIN Index by default.

Users on the Dedicated Server plan and above have the option to add further indexes on particular Data Table columns to speed up more advanced queries using operators in the anvil.tables.query namespace.

Index Types

B-tree for range queries

Optimising a Data Table column for range queries adds a B-Tree index to the data in that column. Range queries include:

  • less_than and greater_than queries: e.g. due_date=q.less_than(date.today())
  • between queries: e.g. age=q.between(30,45)

Trigram for like and ilike

Optimising a Data Table column for pattern matching adds a Trigram index to the data in that column. This allows like and ilike searches to run much faster on large tables.

Full text search index

Optimising a Data Table column for full-text queries adds a Full-Text index to the data in that column. This allows full_text_match searches to run much faster on large tables.

Running SQL on your Data Tables

Developers more familiar with traditional database systems may prefer to access their data using SQL (a specialised database query language).

Users on Dedicated Hosting or Enterprise plans can access the data in their Data Tables using SQL directly. To get started, contact support@anvil.works to get your dedicated database credentials.

Anvil Data Tables are stored in PostgreSQL, and you have access to writeable views of these tables through SQL. Every table created in your account can be accessed through the data_tables schema in the database. This example shows a typical query for a table with ID 4851:

# Assuming your password is stored in the Anvil Secrets Service as 'db_pw'

import psycopg2
conn = psycopg2.connect("""host=anvil-db 
                           port=5432 
                           user=anvil 
                           dbname=anvil 
                           password=%s""" % anvil.secrets.get_secret('db_pw'))
cur = conn.cursor()
cur.execute("SELECT * FROM data_tables.table_4851")
...

You can also choose to set the default schema to 'data_tables' to simplify your queries, as in this example:

conn = psycopg2.connect("""host=anvil-db 
                           port=5432 
                           user=anvil 
                           dbname=anvil 
                           password=%s 
                           options='-c search_path=data_tables'""" % anvil.secrets.get_secret('db_pw'))
cur = conn.cursor()
cur.execute("SELECT * FROM table_4851")
...

Every row in these views has an _id column, which is a unique identifier for the row. Columns that link to other tables show the ID of the linked row, so you can use standard JOIN queries if you wish.

The data_tables.* views are writeable, meaning you can update the data in your tables in the usual way with INSERT, UPDATE and DELETE queries. Note that you cannot update Media objects or links between tables via SQL right now - you must use the Python APIs for that.

In order to prevent malformed queries from harming performance or security for other users, we do not permit users to execute SQL queries against data tables for apps hosted on our shared hosting plans. If you are used to using SQL, there are three options available:

  1. Use our Python APIs. It is often sufficient to use Python list comprehensions instead of complicated SELECT statements.
  2. Connect to an existing, external database. Anvil’s Full Python runtimes provide many modules, including libraries for connecting to popular databases such as PostgreSQL, MySQL, MongoDB, Microsoft SQL Server, and Oracle. If you require a driver for an external database we don’t support yet, please email contact@anvil.works and we can usually get it installed within 24 hours.
  3. Purchase a dedicated or on-site installation of Anvil. With a dedicated or private installation, you will receive full SQL access to all of your data tables. Please email sales@anvil.works for more details.