DOCSEARCHbyAlgolia
Submit Docs

postgrest-py

repository·main·Indexed 43 days ago

https://github.com/supabase/postgrest-py

An ORM-like Python client for PostgREST providing both synchronous (SyncPostgrestClient) and asynchronous (AsyncPostgrestClient) interfaces to interact with PostgreSQL databases. Version 1.1.1 supports CRUD operations via .insert(), .select(), .update(), and .delete(), as well as calling stored procedures using .rpc().

Tokens
8.9K
Snippets
43
Records
58
Agent score
55%

What's inside postgrest-py

  1. Choose between synchronous and asynchronous clients

    main
    The postgrest-py library provides two modes of operation: synchronous and asynchronous. Both modes offer the exact same API interface, allowing you to switch between them based on your application's concurrency requirements without changing your query logic.

  2. Understand the Request Builder architecture

    main

    The postgrest-py library uses Request Builder classes to construct PostgREST queries. These builders are split into two identical sets of interfaces: Synchronous (SyncRequestBuilder, SyncSelectRequestBuilder, SyncQueryRequestBuilder) and Asynchronous (AsyncRequestBuilder, AsyncSelectRequestBuilder, AsyncQueryRequestBuilder).

    Important Usage Note: You should not instantiate these classes directly. Instead, they are intended to be obtained through the client API (e.g., by calling methods on a client instance) to ensure the correct execution context (sync vs async) is maintained.

  3. How to use the Filter Builder to query data

    main

    The Filter Builder is a specialized request builder used to apply filters to your queries. It provides methods to restrict the data returned by your PostgREST requests based on specific column values.

    Key characteristics:

    • Fluent Interface: All filter methods return a modified instance of the filter builder, allowing you to chain multiple filters together in a single statement.
    • Sync vs Async: The library provides both SyncFilterRequestBuilder and AsyncFilterRequestBuilder. They share the exact same interface and methods, so you can use the same filtering logic regardless of whether your application is synchronous or asynchronous.
    • Note: You should not instantiate these classes directly; instead, use them via the main client interface.

    For a complete list of all available operators (like eq, gt, lt, etc.), refer to the official PostgREST documentation.

    # Example of fluent chaining (conceptual)
query = client.from_('table_name') \
    .select('*') \
    .eq('column_name', 'value') \
    .gt('other_column', 10)

  5. Get started with AsyncPostgrestClient

    main

    To use postgrest-py, instantiate an AsyncPostgrestClient with your PostgREST API URL. You can use an async with context manager to ensure the connection is automatically closed when the block ends. Use .auth("Bearer <token>") to authenticate your requests.

    import asyncio
from postgrest import AsyncPostgrestClient

async def main():
    async with AsyncPostgrestClient("http://localhost:3000") as client:
        client.auth("Bearer <token>")
        r = await client.from_("countries").select("*").execute()
        countries = r.data

asyncio.run(main())

  7. Initialize a PostgREST client

    main

    To run queries, you must first construct a client. The library provides two types of clients depending on your application's execution model:

    1. AsyncPostgrestClient: Use this for asynchronous applications (e.g., using asyncio).
    2. SyncPostgrestClient: Use this for standard synchronous Python applications.

    Both clients serve as the entry point for all PostgREST operations.

  8. Install postgrest-py via pip or poetry

    main

    You can install the postgrest-py library using standard Python package managers. This library provides an ORM interface to PostgREST.

    Requirements:

    • Python >= 3.7

    Note: This library is currently unstable. If you encounter bugs, please report them via GitHub issues.

    # Using pip
pip install postgrest-py

# Using poetry
poetry add postgrest-py

  10. Perform CRUD operations

    main

    You can perform Create, Read, Update, and Delete operations using the .from_(table_name) method followed by the appropriate action and .execute().

    • Create: Use .insert(data).
    • Read: Use .select(columns).
    • Update: Use .update(data) combined with filters like .eq(column, value).
    • Delete: Use .delete() combined with filters like .eq(column, value).
    # Create
await client.from_("countries").insert({ "name": "Việt Nam", "capital": "Hà Nội" }).execute()

# Read
r = await client.from_("countries").select("id", "name").execute()
countries = r.data

# Update
await client.from_("countries").update({"capital": "Hà Nội"}).eq("name", "Việt Nam").execute()

# Delete
await client.from_("countries").delete().eq("name", "Việt Nam").execute()

  11. Use Request Builders for queries

    main

    Once a client is initialized, you use Request Builders to construct PostgREST queries. The library separates builders into synchronous and asynchronous versions to match the client type used.

    Synchronous Builders

    Used with SyncPostgrestClient:

    • SyncRequestBuilder: The base builder.
    • SyncSelectRequestBuilder: For selecting specific columns.
    • SyncFilterRequestBuilder: For applying filters (e.g., .eq(), .gt()).
    • SyncQueryRequestBuilder: For general query construction.
    • SyncSingleRequestBuilder / SyncMaybeSingleRequestBuilder: For handling single-row returns.
    • SyncRPCFilterRequestBuilder: For calling RPC functions with filters.

    Asynchronous Builders

    Used with AsyncPostgrestClient:

    • AsyncRequestBuilder
    • AsyncSelectRequestBuilder
    • AsyncFilterRequestBuilder
    • AsyncQueryRequestBuilder
    • AsyncSingleRequestBuilder / AsyncMaybeSingleRequestBuilder
    • AsyncRPCFilterRequestBuilder

  12. Create records using insert()

    main

    To insert new rows into a table, use the .insert() method with a dictionary representing the row data, followed by .execute().

    await client.from_("countries").insert({ "name": "Việt Nam", "capital": "Hà Nội" }).execute()