Indexing and Direct SQL Access
SQL queries 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. Anvil Data Tables are stored in PostgreSQL, and by connecting directly to the Postgres server, apps can get access to writeable views of these tables through SQL.
To connect to your database, call
anvil.tables.get_connection_string() to get a Postgres connection string you can use with
psycopg2, the Python module for connecting to Postgres.
For example, if your app has a Data Table called
import psycopg2 import anvil.tables conn = psycopg2.connect(anvil.tables.get_connection_string()) with conn.cursor() as cur: cur.execute("SELECT * FROM users") all_users = list(cur)
Anvil automatically creates a Postgres schema for each application environment, with views on all the Data Tables to which this application has access. These views may be read-only, depending on this app’s server permissions on that table. The connection string returned by
get_connection_string() sets up the Postgres search path so that bare relation names (eg
users in the example above) are resolved in this schema.
Within this schema, table views have the same name as the Python identifier for the table. So, if you access your table from Python as
app_tables.my_table, you can access it from SQL with
SELECT * FROM my_table.
The connection string returned by
get_connection_string() contains temporary, expiring credentials for logging into the Postgres database, as a user which only has access to tables in this app’s environment. Don’t save and re-use the connection string. If you need to log into the database again at a later time, call
get_connection_string() again to get a fresh set of temporary credentials, then use them immediately.
By default, your dedicated database is behind a firewall, and only accessible from your app’s Server Modules.
Connecting from outside Anvil
If you do want to connect directly to your database from outside Anvil, you will need:
An SSH tunnel, to get through the firewall and ensure your connection is strongly encrypted. (Contact
firstname.lastname@example.org help getting this set up.)
An Uplink connection, to call
get_connection_string()and obtain temporary credentials.
Once you have set up your SSH tunnel, you will need to connect to the hostname and port corresponding to your SSH tunnel, rather than the underlying Anvil database. To do this, pass the
via_port= arguments to
For example, if your SSH tunnel is running on
conn = psycopg2.connect( anvil.tables.get_connection_string( via_host="localhost", via_port=5433 ) )
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.
Assuming your app has write access to the table, these 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.
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 email@example.com 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 firstname.lastname@example.org for more details.
Custom indexes for advanced queries
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 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
The following types of index are available:
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.
Do you still have questions?
Our Community Forum is full of helpful information and Anvil experts.