0
# Database Driver Support
1

2
Direct database driver integration for applications using raw PostgreSQL connections, providing vector type registration and serialization across all major Python PostgreSQL drivers.
3

4
## Capabilities
5

6
### Psycopg 3 Support
7

8
Modern PostgreSQL adapter with both synchronous and asynchronous vector type registration.
9

10
```python { .api }
11
def register_vector(context):
12
    """
13
    Register vector types with Psycopg 3 connection.
14
    
15
    Args:
16
        context: Psycopg 3 connection or cursor context
17
        
18
    Registers:
19
        - vector type (VECTOR)
20
        - bit type (BIT) 
21
        - halfvec type (HALFVEC, if available)
22
        - sparsevec type (SPARSEVEC, if available)
23
    """
24

25
def register_vector_async(context):
26
    """
27
    Asynchronously register vector types with Psycopg 3 connection.
28
    
29
    Args:
30
        context: Psycopg 3 async connection or cursor context
31
        
32
    Returns:
33
        Awaitable that registers all available vector types
34
    """
35
```
36

37
**Usage Examples:**
38

39
```python
40
import psycopg
41
from pgvector.psycopg import register_vector, register_vector_async
42
from pgvector import Vector, HalfVector, SparseVector, Bit
43

44
# Synchronous connection
45
conn = psycopg.connect("postgresql://user:password@localhost/dbname")
46
register_vector(conn)
47

48
# Insert and query vectors
49
with conn.cursor() as cur:
50
    # Create table
51
    cur.execute("""
52
        CREATE TABLE IF NOT EXISTS documents (
53
            id SERIAL PRIMARY KEY,
54
            content TEXT,
55
            embedding VECTOR(384),
56
            sparse_features SPARSEVEC(1000),
57
            binary_hash BIT(64)
58
        )
59
    """)
60
    
61
    # Insert vector data
62
    embedding = Vector([0.1, 0.2] * 192)  # 384 dimensions
63
    sparse_vec = SparseVector({0: 1.0, 100: 2.5}, 1000)
64
    binary_vec = Bit("1010" * 16)  # 64 bits
65
    
66
    cur.execute(
67
        "INSERT INTO documents (content, embedding, sparse_features, binary_hash) VALUES (%s, %s, %s, %s)",
68
        ("Sample document", embedding, sparse_vec, binary_vec)
69
    )
70
    
71
    # Query with similarity search
72
    query_vector = Vector([0.15, 0.25] * 192)
73
    cur.execute(
74
        "SELECT content, embedding <-> %s as distance FROM documents ORDER BY distance LIMIT 5",
75
        (query_vector,)
76
    )
77
    
78
    results = cur.fetchall()
79
    for content, distance in results:
80
        print(f"Content: {content}, Distance: {distance}")
81

82
conn.commit()
83
conn.close()
84

85
# Asynchronous connection
86
import asyncio
87

88
async def async_vector_operations():
89
    async with await psycopg.AsyncConnection.connect(
90
        "postgresql://user:password@localhost/dbname"
91
    ) as conn:
92
        await register_vector_async(conn)
93
        
94
        async with conn.cursor() as cur:
95
            embedding = Vector([0.1, 0.2, 0.3] * 128)
96
            
97
            await cur.execute(
98
                "SELECT content FROM documents WHERE embedding <-> %s < 0.5",
99
                (embedding,)
100
            )
101
            
102
            async for row in cur:
103
                print(f"Similar document: {row[0]}")
104

105
# Run async example
106
asyncio.run(async_vector_operations())
107
```
108

109
### Psycopg 2 Support
110

111
Legacy PostgreSQL adapter support for vector operations.
112

113
```python { .api }
114
def register_vector(conn_or_curs, globally=False, arrays=True):
115
    """
116
    Register vector types with Psycopg 2 connection.
117
    
118
    Args:
119
        conn_or_curs: Psycopg 2 connection or cursor
120
        globally (bool, optional): Register globally for all connections (default: False)
121
        arrays (bool, optional): Register array types as well (default: True)
122
    """
123
```
124

125
**Usage Examples:**
126

127
```python
128
import psycopg2
129
from pgvector.psycopg2 import register_vector
130
from pgvector import Vector, SparseVector
131

132
# Connect to database
133
conn = psycopg2.connect("postgresql://user:password@localhost/dbname")
134

135
# Register vector types for this connection
136
register_vector(conn)
137
cur = conn.cursor()
138

139
# Create table with vector column
140
cur.execute("""
141
    CREATE TABLE IF NOT EXISTS embeddings (
142
        id SERIAL PRIMARY KEY,
143
        text TEXT,
144
        embedding VECTOR(512)
145
    )
146
""")
147

148
# Insert vector data
149
texts_and_vectors = [
150
    ("First document", Vector([0.1] * 512)),
151
    ("Second document", Vector([0.2] * 512)),  
152
    ("Third document", Vector([0.3] * 512))
153
]
154

155
cur.executemany(
156
    "INSERT INTO embeddings (text, embedding) VALUES (%s, %s)",
157
    texts_and_vectors
158
)
159

160
# Similarity search
161
query_vector = Vector([0.15] * 512)
162
cur.execute(
163
    """
164
    SELECT text, embedding <-> %s as distance 
165
    FROM embeddings 
166
    ORDER BY distance 
167
    LIMIT 3
168
    """,
169
    (query_vector,)
170
)
171

172
for text, distance in cur.fetchall():
173
    print(f"Text: {text}, Distance: {distance}")
174

175
conn.commit()
176
conn.close()
177
```
178

179
### asyncpg Support
180

181
High-performance asynchronous PostgreSQL driver integration.
182

183
```python { .api }
184
def register_vector(connection):
185
    """
186
    Register vector types with asyncpg connection.
187
    
188
    Args:
189
        connection: asyncpg connection instance
190
        
191
    Returns:
192
        Awaitable that registers all available vector types
193
    """
194
```
195

196
**Usage Examples:**
197

198
```python
199
import asyncio
200
import asyncpg
201
from pgvector.asyncpg import register_vector
202
from pgvector import Vector, HalfVector
203

204
async def asyncpg_example():
205
    # Connect to database
206
    conn = await asyncpg.connect("postgresql://user:password@localhost/dbname")
207
    
208
    # Register vector types
209
    await register_vector(conn)
210
    
211
    # Create table
212
    await conn.execute("""
213
        CREATE TABLE IF NOT EXISTS products (
214
            id SERIAL PRIMARY KEY,
215
            name TEXT,
216
            features VECTOR(256),
217
            description_embedding HALFVEC(128)
218
        )
219
    """)
220
    
221
    # Insert data with vectors
222
    products = [
223
        ("Laptop", Vector([0.1] * 256), HalfVector([0.2] * 128)),
224
        ("Phone", Vector([0.3] * 256), HalfVector([0.4] * 128)),
225
        ("Tablet", Vector([0.5] * 256), HalfVector([0.6] * 128))
226
    ]
227
    
228
    await conn.executemany(
229
        "INSERT INTO products (name, features, description_embedding) VALUES ($1, $2, $3)",
230
        products
231
    )
232
    
233
    # Vector similarity search
234
    query_features = Vector([0.2] * 256)
235
    
236
    results = await conn.fetch(
237
        """
238
        SELECT name, features <-> $1 as similarity
239
        FROM products 
240
        ORDER BY similarity
241
        LIMIT 5
242
        """,
243
        query_features
244
    )
245
    
246
    for row in results:
247
        print(f"Product: {row['name']}, Similarity: {row['similarity']}")
248
    
249
    # Batch operations with prepared statements
250
    stmt = await conn.prepare(
251
        "SELECT name FROM products WHERE features <-> $1 < $2"
252
    )
253
    
254
    similar_products = await stmt.fetch(query_features, 0.5)
255
    print(f"Found {len(similar_products)} similar products")
256
    
257
    await conn.close()
258

259
# Run async example
260
asyncio.run(asyncpg_example())
261
```
262

263
### pg8000 Support
264

265
Pure Python PostgreSQL driver integration.
266

267
```python { .api }
268
def register_vector(context):
269
    """
270
    Register vector types with pg8000 connection.
271
    
272
    Args:
273
        context: pg8000 connection instance
274
    """
275
```
276

277
**Usage Examples:**
278

279
```python
280
import pg8000.native
281
from pgvector.pg8000 import register_vector
282
from pgvector import Vector, Bit
283

284
# Connect to database
285
conn = pg8000.native.Connection(
286
    user="user",
287
    password="password", 
288
    host="localhost",
289
    database="dbname"
290
)
291

292
# Register vector types
293
register_vector(conn)
294

295
# Create table with vector and bit columns
296
conn.run("""
297
    CREATE TABLE IF NOT EXISTS items (
298
        id SERIAL PRIMARY KEY,
299
        name TEXT,
300
        embedding VECTOR(128),
301
        tags BIT(32)
302
    )
303
""")
304

305
# Insert data
306
items = [
307
    ("Item 1", Vector([0.1] * 128), Bit("1010" * 8)),
308
    ("Item 2", Vector([0.2] * 128), Bit("0101" * 8)),
309
    ("Item 3", Vector([0.3] * 128), Bit("1100" * 8))
310
]
311

312
for name, embedding, tags in items:
313
    conn.run(
314
        "INSERT INTO items (name, embedding, tags) VALUES (:name, :embedding, :tags)",
315
        name=name,
316
        embedding=embedding,
317
        tags=tags
318
    )
319

320
# Query with vector similarity
321
query_vector = Vector([0.15] * 128)
322
results = conn.run(
323
    """
324
    SELECT name, embedding <-> :query as distance
325
    FROM items
326
    ORDER BY distance
327
    LIMIT 3
328
    """,
329
    query=query_vector
330
)
331

332
for row in results:
333
    print(f"Item: {row[0]}, Distance: {row[1]}")
334

335
# Hamming distance for bit vectors
336
query_bits = Bit("1010" * 8)
337
bit_results = conn.run(
338
    """
339
    SELECT name, tags <~> :query_bits as hamming_distance
340
    FROM items
341
    ORDER BY hamming_distance
342
    LIMIT 3
343
    """,
344
    query_bits=query_bits
345
)
346

347
for row in bit_results:
348
    print(f"Item: {row[0]}, Hamming Distance: {row[1]}")
349

350
conn.close()
351
```
352

353
## Driver Comparison
354

355
| Driver | Sync | Async | Performance | Pure Python | Vector Types |
356
|--------|------|-------|-------------|-------------|--------------|
357
| Psycopg 3 | ✅ | ✅ | High | No | All |
358
| Psycopg 2 | ✅ | ❌ | High | No | All |
359
| asyncpg | ❌ | ✅ | Very High | No | All |
360
| pg8000 | ✅ | ❌ | Medium | Yes | All |
361
362
## Error Handling
363

364
All drivers will raise appropriate database errors for:
365
- Invalid vector dimensions
366
- Unsupported vector operations  
367
- Connection failures
368
- PostgreSQL extension not installed
369
- Type registration failures
370

371
Register vector types immediately after connection establishment and before any vector operations.