Akron ORM - Universal Python ORM

Overview

PostgreSQL is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance. It's an excellent choice for applications requiring advanced SQL features, data integrity, and scalability.

🐘 PostgreSQL Advantages

• Full ACID compliance and strong consistency
• Advanced data types (JSON, Arrays, Custom types)
• Powerful query optimizer and execution engine
• Extensibility with custom functions and operators
• Strong standards compliance (SQL:2016)
• Excellent concurrency control

Prerequisites

Before using PostgreSQL with Akron, ensure you have the required dependency installed:

Install PostgreSQL Adapter

1# Install the PostgreSQL adapter
2pip install psycopg2-binary
3
4# Or for production (compile from source)
5pip install psycopg2
6
7# Using conda
8conda install psycopg2

Connection Setup

Basic Connection

PostgreSQL Connection Examples

1from akron import Akron
2
3# Basic connection
4db = Akron("postgres://user:password@localhost:5432/database")
5
6# Alternative scheme
7db = Akron("postgresql://user:password@localhost:5432/database")
8
9# Connection with SSL
10db = Akron("postgres://user:password@localhost:5432/db?sslmode=require")
11
12# Remote PostgreSQL server
13db = Akron("postgres://admin:secret@pg.example.com:5432/production_db")
14
15# Connection with custom application name
16db = Akron("postgres://user:pass@localhost:5432/db?application_name=akron_app")

Connection URL Format

postgres://[user]:[password]@[host]:[port]/[database]?[options]

user: PostgreSQL username

password: PostgreSQL password

host: Server hostname or IP (default: localhost)

port: PostgreSQL port (default: 5432)

database: Database name

options: Additional connection parameters

Common Connection Options

SSL Options

sslmode=require - Force SSL
sslmode=prefer - Use SSL if available
sslmode=disable - No SSL

Performance Options

connect_timeout=10 - Connection timeout
application_name=myapp - App identifier
options=-c statement_timeout=30s - Query timeout

Data Types

PostgreSQL offers an extensive range of data types. Akron maps Python types to appropriate PostgreSQL types:

Python Type	PostgreSQL Type	Range/Notes
`int`	INTEGER	-2,147,483,648 to 2,147,483,647
`str`	VARCHAR	Variable length text
`float`	DOUBLE PRECISION	64-bit floating point
`bool`	BOOLEAN	TRUE/FALSE
`datetime`	TIMESTAMP	With/without timezone
`date`	DATE	Date only
`list`	ARRAY	Multi-dimensional arrays
`dict`	JSONB	Binary JSON with indexing

Complete Example

PostgreSQL Full CRUD Example

1from akron import Akron
2from pydantic import BaseModel
3from akron.models import ModelMixin
4from datetime import datetime
5from typing import List, Dict, Optional
6
7# Define your model with advanced types
8class Article(BaseModel, ModelMixin):
9    id: int
10    title: str
11    content: str
12    author: str
13    tags: List[str]  # PostgreSQL Array
14    metadata: Dict[str, any]  # PostgreSQL JSONB
15    published: bool = False
16    created_at: datetime
17    view_count: int = 0
18
19# Connect to PostgreSQL database
20db = Akron("postgres://user:password@localhost:5432/blog")
21
22# Create table
23Article.create_table(db)
24
25# Insert articles with complex data
26article1 = Article(
27    id=1,
28    title="Getting Started with PostgreSQL",
29    content="PostgreSQL is a powerful database...",
30    author="Jane Doe",
31    tags=["database", "postgresql", "tutorial"],
32    metadata={
33        "category": "technology",
34        "difficulty": "beginner",
35        "estimated_read_time": 5
36    },
37    created_at=datetime.now()
38)
39
40article2 = Article(
41    id=2,
42    title="Advanced SQL Queries",
43    content="Learn advanced querying techniques...",
44    author="John Smith",
45    tags=["sql", "advanced", "queries"],
46    metadata={
47        "category": "database",
48        "difficulty": "advanced",
49        "estimated_read_time": 15
50    },
51    created_at=datetime.now(),
52    published=True,
53    view_count=150
54)
55
56Article.insert(db, article1)
57Article.insert(db, article2)
58
59# Query articles
60all_articles = Article.select(db)
61print(f"Total articles: {len(all_articles)}")
62
63# Find published articles
64published_articles = Article.select(db, where={"published": True})
65print(f"Published articles: {len(published_articles)}")
66
67# Search by tags (PostgreSQL array operations)
68tech_articles = db.execute_raw(
69    "SELECT * FROM articles WHERE 'technology' = ANY(tags);"
70)
71
72# JSON queries on metadata
73beginner_articles = db.execute_raw(
74    "SELECT * FROM articles WHERE metadata->>'difficulty' = 'beginner';"
75)
76
77# Update article
78Article.update(db, {"id": 1}, {"published": True, "view_count": 50})
79
80# Complex update with JSON
81Article.update(db, {"id": 1}, {
82    "metadata": {
83        "category": "technology",
84        "difficulty": "beginner",
85        "estimated_read_time": 5,
86        "updated": True
87    }
88})
89
90print("PostgreSQL operations completed successfully!")

Advanced PostgreSQL Features

JSON Operations

1# Query JSON data
2json_queries = [
  # Extract JSON field
  "SELECT title, metadata->>'category' as category FROM articles;",
  
  # Filter by JSON field
  "SELECT * FROM articles WHERE metadata->>'difficulty' = 'advanced';",
  
  # Check if JSON key exists
  "SELECT * FROM articles WHERE metadata ? 'estimated_read_time';",
  
  # Update JSON field
  "UPDATE articles SET metadata = metadata || '{"featured": true}' WHERE id = 1;"
14]
15
16for query in json_queries:
  result = db.execute_raw(query)
  print(f"Query result: {list(result)}")

Array Operations

1# Array operations
2array_queries = [
  # Find articles with specific tag
  "SELECT * FROM articles WHERE 'postgresql' = ANY(tags);",
  
  # Find articles with multiple tags
  "SELECT * FROM articles WHERE tags @> ARRAY['database', 'tutorial'];",
  
  # Count array elements
  "SELECT title, array_length(tags, 1) as tag_count FROM articles;",
  
  # Expand array to rows
  "SELECT title, unnest(tags) as tag FROM articles;"
14]
15
16for query in array_queries:
  result = db.execute_raw(query)
  print(f"Array query result: {list(result)}")

Full-Text Search

1# Create full-text search index
2db.execute_raw("""
3    ALTER TABLE articles ADD COLUMN search_vector tsvector;
4    UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);
5    CREATE INDEX idx_articles_search ON articles USING gin(search_vector);
6""")
7
8# Perform full-text search
9search_results = db.execute_raw("""
10    SELECT title, author, ts_rank(search_vector, query) as rank
11    FROM articles, plainto_tsquery('postgresql database') query
12    WHERE search_vector @@ query
13    ORDER BY rank DESC;
14""")
15
16for article in search_results:
17    print(f"Found: {article['title']} (rank: {article['rank']})")

Window Functions

1# Advanced analytics with window functions
2analytics_queries = [
  # Ranking articles by view count
  """SELECT title, view_count, 
     RANK() OVER (ORDER BY view_count DESC) as rank
     FROM articles;""",
  
  # Running total of articles by author
  """SELECT author, title, created_at,
     COUNT(*) OVER (PARTITION BY author ORDER BY created_at) as article_number
     FROM articles ORDER BY author, created_at;""",
  
  # Moving average of view counts
  """SELECT title, view_count,
     AVG(view_count) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
     FROM articles ORDER BY created_at;"""
17]
18
19for query in analytics_queries:
  result = db.execute_raw(query)
  print(f"Analytics result: {list(result)}")

Performance Optimization

Indexing Strategy

1# B-tree indexes for equality and range queries
2db.execute_raw("CREATE INDEX idx_articles_author ON articles(author);")
3db.execute_raw("CREATE INDEX idx_articles_created ON articles(created_at);")
4
5# GIN indexes for JSON and array operations
6db.execute_raw("CREATE INDEX idx_articles_tags ON articles USING gin(tags);")
7db.execute_raw("CREATE INDEX idx_articles_metadata ON articles USING gin(metadata);")
8
9# Partial indexes for specific conditions
10db.execute_raw("CREATE INDEX idx_published_articles ON articles(created_at) WHERE published = true;")
11
12# Composite indexes for multi-column queries
13db.execute_raw("CREATE INDEX idx_author_published ON articles(author, published);")  

Query Analysis

1# Analyze query performance
2result = db.execute_raw("""
3    EXPLAIN (ANALYZE, BUFFERS) 
4    SELECT * FROM articles 
5    WHERE author = 'Jane Doe' AND published = true
6    ORDER BY created_at DESC;
7""")
8
9for row in result:
10    print(row)
11
12# Check index usage
13stats = db.execute_raw("""
14    SELECT schemaname, tablename, indexname, idx_tup_read, idx_tup_fetch
15    FROM pg_stat_user_indexes 
16    WHERE tablename = 'articles';
17""")
18
19for stat in stats:
20    print(f"Index {stat['indexname']}: {stat['idx_tup_read']} reads")

CLI Commands

Use Akron CLI for PostgreSQL database management:

PostgreSQL CLI Examples

1# Create a table with complex schema
2akron create-table articles --db "postgres://user:pass@localhost:5432/blog" \
3  --schema '{
4    "id": "int", 
5    "title": "str", 
6    "content": "str", 
7    "tags": "list", 
8    "metadata": "dict",
9    "published": "bool",
10    "created_at": "datetime"
11  }'
12
13# Inspect database schema
14akron inspect-schema --db "postgres://user:pass@localhost:5432/blog"
15
16# Seed with complex data
17akron seed articles --db "postgres://user:pass@localhost:5432/blog" \
18  --data '[{
19    "id": 1, 
20    "title": "Sample Article", 
21    "content": "This is a sample...",
22    "tags": ["sample", "test"],
23    "metadata": {"category": "example"},
24    "published": true,
25    "created_at": "2024-01-01T00:00:00"
26  }]'
27
28# Execute PostgreSQL-specific queries
29akron raw-sql --db "postgres://user:pass@localhost:5432/blog" \
30  --query "SELECT tablename FROM pg_tables WHERE schemaname = 'public';"
31
32# Create migration with advanced features
33akron makemigrations add_search_features \
34  --db "postgres://user:pass@localhost:5432/blog" \
35  --schema '{
36    "id": "int",
37    "title": "str",
38    "content": "str", 
39    "search_vector": "tsvector",
40    "tags": "text[]",
41    "metadata": "jsonb"
42  }'

Best Practices

✅ Do

• Use JSONB instead of JSON for better performance
• Create appropriate indexes (B-tree, GIN, GiST)
• Use prepared statements (Akron handles this)
• Leverage PostgreSQL's advanced data types
• Monitor query performance with EXPLAIN ANALYZE
• Use connection pooling for production applications
• Take advantage of PostgreSQL's concurrency features

❌ Don't

• Use TEXT for large documents (consider external storage)
• Create unnecessary indexes (they slow down writes)
• Ignore PostgreSQL-specific features and optimizations
• Use LIKE queries without proper indexing
• Store binary data directly in the database
• Forget to vacuum and analyze tables regularly

Common Issues & Solutions

Connection refused

PostgreSQL server is not running or not accepting connections.

1# Check PostgreSQL service status
2sudo systemctl status postgresql
3
4# Start PostgreSQL service
5sudo systemctl start postgresql
6
7# Check if PostgreSQL is listening on the correct port
8sudo netstat -tlnp | grep :5432

Authentication failed

Incorrect credentials or authentication method configuration.

1# Check pg_hba.conf for authentication settings
2# Common entry for local development:
3# local   all   all   trust
4# host    all   all   127.0.0.1/32   md5
5
6# Create user and grant privileges
7CREATE USER akron_user WITH PASSWORD 'secure_password';
8GRANT ALL PRIVILEGES ON DATABASE blog TO akron_user;

Slow queries

Queries taking too long due to missing indexes or poor query design.

1# Enable slow query logging
2ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries > 1 second
3SELECT pg_reload_conf();
4
5# Find slow queries
6SELECT query, calls, total_time, mean_time
7FROM pg_stat_statements
8ORDER BY mean_time DESC
9LIMIT 10;

PostgreSQL Support

Overview

🐘 PostgreSQL Advantages

Prerequisites

Connection Setup

Basic Connection

Connection URL Format

Common Connection Options

SSL Options

Performance Options

Data Types

Complete Example

Advanced PostgreSQL Features

JSON Operations

Array Operations

Full-Text Search

Window Functions

Performance Optimization

Indexing Strategy

Query Analysis

CLI Commands

Best Practices

✅ Do

❌ Don't

Common Issues & Solutions

Connection refused

Authentication failed

Slow queries

Related Documentation

Akron Constructor

CLI Commands

MongoDB Support

Model Definitions