Queries

Queries are used to retrieve data from a database.

A query is a request for information from a database table or combination of tables. A query can be used to retrieve data from a single table or multiple tables. A query can also be used to insert, update, or delete data from a table.

To execute a query you must first create a rx.session. You can use the session to query the database using SQLModel or SQLAlchemy syntax.

The rx.session statement will automatically close the session when the code block is finished. If session.commit() is not called, the changes will be rolled back and not persisted to the database. The code can also explicitly rollback without closing the session via session.rollback().

The following example shows how to create a session and query the database. First we create a table called User.

Then we create a session and query the User table.

The get_users method will query the database for all users that contain the value of the state var name.

Similarly, the session.add() method to add a new record to the database or persist an existing object.

To update the user, first query the database for the object, make the desired modifications, .add the object to the session and finally call .commit().

To delete a user, first query the database for the object, then call .delete() on the session and finally call .commit().

The objects returned by queries are bound to the session that created them, and cannot generally be used outside that session. After adding or updating an object, not all fields are automatically updated, so accessing certain attributes may trigger additional queries to refresh the object.

To avoid this, the session.refresh() method can be used to update the object explicitly and ensure all fields are up to date before exiting the session.

Now the self.user object will have a correct reference to the autogenerated primary key, id, even though this was not provided when the object was created from the form data.

If self.user needs to be modified or used in another query in a new session, it must be added to the session. Adding an object to a session does not necessarily create the object, but rather associates it with a session where it may either be created or updated accordingly.

If an ORM object will be referenced and accessed outside of a session, you should call .refresh() on it to avoid stale object exceptions.

Avoiding SQL is one of the main benefits of using an ORM, but sometimes it is necessary for particularly complex queries, or when using database-specific features.

SQLModel exposes the session.execute() method that can be used to execute raw SQL strings. If parameter binding is needed, the query may be wrapped in sqlalchemy.text, which allows colon-prefix names to be used as placeholders.