xata-postgres-platform

# Xata Postgres Platform > Skill by [ara.so](https://ara.so) — Daily 2026 Skills collection. Xata is an open-source, cloud-native Postgres platform built on Kubernetes that provides copy-on-write (CoW) branching, scale-to-zero, auto-scaling, high-availability, PITR backups, and a serverless SQL driver. It sits on top of [CloudNativePG](https://github.com/cloudnative-pg/cloudnative-pg) and [OpenEBS](https://github.com/openebs/openebs). ## Architecture Overview ``` ┌─────────────────────────────────────────────────────┐ │ Xata Platform │ │ CLI ──► REST API (clusters/projects services) │ │ SQL Gateway (routing, scale-to-zero wakeup) │ │ Branch Operator (manages K8s resources per branch) │ │ Auth Service (Keycloak-based, RBAC API keys) │ ├─────────────────────────────────────────────────────┤ │ CloudNativePG (HA, failover, backups, pooling) │ │ OpenEBS (local NVMe or Mayastor replicated storage) │ └─────────────────────────────────────────────────────┘ ``` ## Prerequisites - Docker - [Kind](https://github.com/kubernetes-sigs/kind) - [Tilt](https://github.com/tilt-dev/tilt) - `kubectl` ## Local Development Setup ### Step 1: Create Kind Cluster ```bash kind create cluster --wait 10m ``` ### Step 2: Deploy with Tilt ```bash tilt up ``` Wait for all resources to become ready. First run downloads images and takes longer; subsequent runs are fast. ### Step 3: Install and Authenticate the CLI ```bash # Install Xata CLI curl -fsSL https://xata.io/install.sh | bash # Authenticate to local profile # Default credentials: email=dev@xata.tech, password=Xata1234! xata auth login --profile local --env local --force # Switch to the local profile xata auth switch local ``` ### Step 4: Create Project and Branch ```bash # Create a new project xata project create --name my-project # Create the main branch (done automatically with project) # Create a child branch (CoW copy of parent) xata branch create ``` ## CLI Reference ### Authentication ```bash # Login to Xata Cloud xata auth login # Login to self-hosted local instance xata auth login --profile local --env local --force # Switch between profiles xata auth switch local xata auth switch default # List profiles xata auth list ``` ### Projects ```bash # Create a project xata project create --name my-project # List projects xata project list # Get project details xata project get <project-id> # Delete a project xata project delete <project-id> ``` ### Branches ```bash # Create a branch (CoW snapshot of parent, completes in seconds even for TB of data) xata branch create # Create a named branch from a specific parent xata branch create --name feature-xyz --from main # List branches xata branch list # Get branch details xata branch get <branch-id> # Delete a branch xata branch delete <branch-id> ``` ### Connecting to a Branch ```bash # Get connection string for a branch xata branch connection-string <branch-id> # Connect via psql psql "$(xata branch connection-string <branch-id>)" ``` ## Configuration ### Environment Variables ```bash # Xata API endpoint (for self-hosted) export XATA_API_URL=http://localhost:8080 # API key for authentication export XATA_API_KEY=your_api_key_here # Default project ID export XATA_PROJECT_ID=your_project_id ``` ### API Key Management (RBAC) ```bash # Create an API key with specific permissions xata apikey create --name ci-key --role branch:read,branch:write # List API keys xata apikey list # Revoke an API key xata apikey delete <key-id> ``` ## Serverless SQL Driver (SQL over HTTP/WebSockets) Xata exposes a serverless driver for executing SQL over HTTP or WebSockets, useful for edge functions and environments without persistent TCP connections. ### HTTP SQL Query (Go) ```go package main import ( "bytes" "encoding/json" "fmt" "net/http" "os" ) type SQLRequest struct { Query string `json:"query"` Params []any `json:"params,omitempty"` } type SQLResponse struct { Records []map[string]any `json:"records"` Error string `json:"error,omitempty"` } func queryXata(query string, params ...any) (*SQLResponse, error) { apiURL := os.Getenv("XATA_API_URL") apiKey := os.Getenv("XATA_API_KEY") branchID := os.Getenv("XATA_BRANCH_ID") reqBody := SQLRequest{Query: query, Params: params} data, err := json.Marshal(reqBody) if err != nil { return nil, err } url := fmt.Sprintf("%s/branches/%s/sql", apiURL, branchID) req, err := http.NewRequest("POST", url, bytes.NewReader(data)) if err != nil { return nil, err } req.Header.Set("Authorization", "Bearer "+apiKey) req.Header.Set("Content-Type", "application/json") resp, err := http.DefaultClient.Do(req) if err != nil { return nil, err } defer resp.Body.Close() var sqlResp SQLResponse if err := json.NewDecoder(resp.Body).Decode(&sqlResp); err != nil { return nil, err } return &sqlResp, nil } func main() { result, err := queryXata("SELECT id, name FROM users WHERE active = $1", true) if err != nil { panic(err) } for _, record := range result.Records { fmt.Printf("User: %v\n", record) } } ``` ### Direct Postgres Connection (Go) ```go package main import ( "context" "fmt" "os" "github.com/jackc/pgx/v5" ) func main() { connStr := os.Getenv("XATA_DATABASE_URL") // e.g. postgresql://user:pass@sql-gateway-host:5432/dbname conn, err := pgx.Connect(context.Background(), connStr) if err != nil { fmt.Fprintf(os.Stderr, "Unable to connect: %v\n", err) os.Exit(1) } defer conn.Close(context.Background()) rows, err := conn.Query(context.Background(), "SELECT id, name FROM users LIMIT 10") if err != nil { panic(err) } defer rows.Close() for rows.Next() { var id int var name string if err := rows.Scan(&id, &name); err != nil { panic(err) } fmt.Printf("id=%d name=%s\n", id, name) } } ``` ## Common Patterns ### Preview Environment Workflow (CI/CD) Create a per-PR branch for isolated testing, then destroy it after merge: ```bash #!/usr/bin/env bash # create-preview.sh set -euo pipefail PR_NUMBER="${1:?PR number required}" BRANCH_NAME="pr-${PR_NUMBER}" # Create a CoW branch from main (completes in seconds, even for TB of data) BRANCH_ID=$(xata branch create --name "$BRANCH_NAME" --from main --output json | jq -r '.id') echo "Created branch: $BRANCH_ID" # Get connection string for tests CONN_STR=$(xata branch connection-string "$BRANCH_ID") echo "::set-output name=database_url::${CONN_STR}" echo "::set-output name=branch_id::${BRANCH_ID}" ``` ```bash #!/usr/bin/env bash # cleanup-preview.sh BRANCH_ID="${1:?Branch ID required}" xata branch delete "$BRANCH_ID" echo "Deleted branch $BRANCH_ID" ``` ### GitHub Actions Integration ```yaml name: Preview Environment on: pull_request: types: [opened, synchronize] jobs: create-preview: runs-on: ubuntu-latest steps: - uses: actions/checkout@v4 - name: Install Xata CLI run: curl -fsSL https://xata.io/install.sh | bash - name: Authenticate env: XATA_API_KEY: ${{ secrets.XATA_API_KEY }} run: xata auth login --api-key "$XATA_API_KEY" - name: Create Preview Branch id: branch run: | BRANCH_ID=$(xata branch create \ --name "pr-${{ github.event.number }}" \ --from main \ --output json | jq -r '.id') echo "branch_id=$BRANCH_ID" >> "$GITHUB_OUTPUT" CONN=$(xata branch connection-string "$BRANCH_ID") echo "database_url=$CONN" >> "$GITHUB_OUTPUT" - name: Run Tests env: DATABASE_URL: ${{ steps.branch.outputs.database_url }} run: go test ./... cleanup: runs-on: ubuntu-latest needs: create-preview if: always() steps: - name: Delete Preview Branch env: XATA_API_KEY: ${{ secrets.XATA_API_KEY }} run: | xata auth login --api-key "$XATA_API_KEY" xata branch delete "${{ needs.create-preview.outputs.branch_id }}" ``` ### Programmatic Branch Management (Go) ```go package main import ( "context" "encoding/json" "fmt" "net/http" "os" "strings" ) type XataClient struct { BaseURL string APIKey string Project string http *http.Client } func NewXataClient() *XataClient { return &XataClient{ BaseURL: os.Getenv("XATA_API_URL"), APIKey: os.Getenv("XATA_API_KEY"), Project: os.Getenv("XATA_PROJECT_ID"), http: &http.Client{}, } } type Branch struct { ID string `json:"id"` Name string `json:"name"` ParentID string `json:"parentId"` State string `json:"state"` ConnectionURL string `json:"connectionUrl"` } type CreateBranchRequest struct { Name string `json:"name"` ParentID string `json:"parentId"` } func (c *XataClient) CreateBranch(ctx context.Context, name, parentID string) (*Branch, error) { payload := CreateBranchRequest{Name: name, ParentID: parentID} data, _ := json.Marshal(payload) url := fmt.Sprintf("%s/projects/%s/branches", c.BaseURL, c.Project) req, err := http.NewRequestWithContext(ctx, "POST", url, strings.NewReader(string(data))) if err != nil { return nil, err } req.Header.Set("Authorization", "Bearer "+c.APIKey) req.Header.Set("Content-Type", "application/json") resp, err := c.http.Do(req) if err != nil { return nil, err } defer resp.Body.Close() var branch Branch if err := json.NewDecoder(resp.Body).Decode(&branch); err != nil { return nil, err } return &branch, nil } func (c *XataClient) DeleteBranch(ctx context.Context, branchID string) error { url := fmt.Sprintf("%s/projects/%s/branches/%s", c.BaseURL, c.Project, branchID) req, err := http.NewRequestWithContext(ctx, "DELETE", url, nil) if err != nil { return err } req.Header.Set("Authorization", "Bearer "+c.APIKey) _, err = c.http.Do(req) return err } func main() { client := NewXataClient() ctx := context.Background() branch, err := client.CreateBranch(ctx, "feature-test", "main-branch-id") if err != nil { panic(err) } fmt.Printf("Created branch %s, connect at: %s\n", branch.ID, branch.ConnectionURL) // ... run migrations, tests, etc. ... if err := client.DeleteBranch(ctx, branch.ID); err != nil { panic(err) } fmt.Println("Branch cleaned up") } ``` ## Scale-to-Zero Branches automatically hibernate after inactivity and wake up on the next connection. The SQL Gateway handles the wakeup transparently — clients may experience a short delay (~seconds) on the first connection after hibernation. ```bash # Manually hibernate a branch xata branch hibernate <branch-id> # Wake a branch xata branch wake <branch-id> # Check branch state (running, hibernated, starting) xata branch get <branch-id> --output json | jq '.state' ``` ## Troubleshooting ### Tilt/Kind Issues ```bash # Check all pods are running kubectl get pods -A # Check Xata-specific pods kubectl get pods -n xata # View logs for a specific component kubectl logs -n xata -l app=sql-gateway --tail=100 kubectl logs -n xata -l app=branch-operator --tail=100 kubectl logs -n xata -l app=clusters --tail=100 # Restart tilt if resources are stuck tilt down && tilt up