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
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:
Optimising a Data Table column for pattern matching adds a Trigram index to the data in that column. This allows
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.
full_text_match searches to run much faster on large 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 email@example.com 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.
data_tables.* views are writeable, meaning you can update the data in your tables in the usual way with
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:
- Use our Python APIs. It is often sufficient to use Python list comprehensions instead of complicated
- 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 firstname.lastname@example.org and we can usually get it installed within 24 hours.
- 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 email@example.com for more details.