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 the Business Plan with Extra Server Resources 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 users:

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)

Schema Layout

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.

Security

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 Extra Server Resource 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:

1. An SSH tunnel, to get through the firewall and ensure your connection is strongly encrypted. (Contact support@anvil.works for help getting this set up.)

2. 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_host= and via_port= arguments to get_connection_string().

For example, if your SSH tunnel is running on localhost port 5433:

conn = psycopg2.connect(
    anvil.tables.get_connection_string(
        via_host="localhost",
        via_port=5433
    )
)

View Structure

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 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.

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 Business Plan with Extra Server Resources or on-site installation of Anvil. With a Business Plan and Extra Server Resources or a private installation, you will receive full SQL access to all of your data tables. Please email sales@anvil.works 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 Business Plan with Extra Server Resources or Enterprise Plan 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.

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:

• 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.

Do you still have questions?

Our Community Forum is full of helpful information and Anvil experts.