0
# Connection Management
1

2
Helper utilities for building and managing database connection strings across different database backends. ConnectorX provides the ConnectionUrl class to simplify connection string construction and ensure proper formatting for various database systems.
3

4
## Capabilities
5

6
### ConnectionUrl Class
7

8
Generic connection URL builder that supports multiple database backends with type safety.
9

10
```python { .api }
11
class ConnectionUrl(Generic[_BackendT], str):
12
    """
13
    Helper class to build database connection string URLs.
14
    Inherits from str, so can be used anywhere a connection string is expected.
15
    """
16
    
17
    # SQLite constructor
18
    def __new__(
19
        cls,
20
        *,
21
        backend: Literal["sqlite"],
22
        db_path: str | Path,
23
    ) -> ConnectionUrl[Literal["sqlite"]]:
24
        """Build SQLite connection URL."""
25
    
26
    # BigQuery constructor  
27
    def __new__(
28
        cls,
29
        *,
30
        backend: Literal["bigquery"],
31
        db_path: str | Path,
32
    ) -> ConnectionUrl[Literal["bigquery"]]:
33
        """Build BigQuery connection URL."""
34
    
35
    # Server-based database constructor
36
    def __new__(
37
        cls,
38
        *,
39
        backend: _ServerBackendT,
40
        username: str,
41
        password: str = "",
42
        server: str,
43
        port: int,
44
        database: str = "",
45
        database_options: dict[str, str] | None = None,
46
    ) -> ConnectionUrl[_ServerBackendT]:
47
        """Build server-based database connection URL."""
48
    
49
    # Raw connection string constructor
50
    def __new__(
51
        cls,
52
        raw_connection: str,
53
    ) -> ConnectionUrl:
54
        """Build connection from raw connection string."""
55
```
56

57
### Supported Backend Types
58

59
```python { .api }
60
_ServerBackendT = TypeVar(
61
    "_ServerBackendT",
62
    bound=Literal[
63
        "redshift",
64
        "clickhouse", 
65
        "postgres",
66
        "postgresql",
67
        "mysql",
68
        "mssql",
69
        "oracle",
70
        "duckdb",
71
    ],
72
)
73
```
74

75
## Usage Examples
76

77
### SQLite Connections
78

79
```python
80
from connectorx import ConnectionUrl
81
from pathlib import Path
82

83
# Using string path
84
sqlite_url = ConnectionUrl(
85
    backend="sqlite",
86
    db_path="/path/to/database.db"
87
)
88

89
# Using Path object
90
db_path = Path("./data/sample.sqlite")
91
sqlite_url = ConnectionUrl(
92
    backend="sqlite", 
93
    db_path=db_path
94
)
95

96
# Result: "sqlite:///path/to/database.db"
97
```
98

99
### BigQuery Connections
100

101
```python
102
# BigQuery with service account key file
103
bq_url = ConnectionUrl(
104
    backend="bigquery",
105
    db_path="/path/to/service-account-key.json"
106
)
107

108
# Result: "bigquery:///path/to/service-account-key.json"
109
```
110

111
### PostgreSQL Connections
112

113
```python
114
# Basic PostgreSQL connection
115
postgres_url = ConnectionUrl(
116
    backend="postgresql",
117
    username="myuser",
118
    password="mypassword", 
119
    server="localhost",
120
    port=5432,
121
    database="mydb"
122
)
123

124
# With connection options
125
postgres_url = ConnectionUrl(
126
    backend="postgresql",
127
    username="myuser",
128
    password="mypassword",
129
    server="postgres.example.com",
130
    port=5432,
131
    database="production_db",
132
    database_options={
133
        "sslmode": "require",
134
        "application_name": "connectorx_app"
135
    }
136
)
137

138
# Result: "postgresql://myuser:mypassword@postgres.example.com:5432/production_db?sslmode=require&application_name=connectorx_app"
139
```
140

141
### MySQL Connections
142

143
```python
144
mysql_url = ConnectionUrl(
145
    backend="mysql",
146
    username="root",
147
    password="secret",
148
    server="mysql.example.com", 
149
    port=3306,
150
    database="analytics"
151
)
152

153
# Result: "mysql://root:secret@mysql.example.com:3306/analytics"
154
```
155

156
### SQL Server Connections
157

158
```python
159
mssql_url = ConnectionUrl(
160
    backend="mssql",
161
    username="sa",
162
    password="Password123!",
163
    server="sqlserver.example.com",
164
    port=1433,
165
    database="AdventureWorks",
166
    database_options={
167
        "TrustServerCertificate": "true",
168
        "Encrypt": "false"
169
    }
170
)
171
```
172

173
### Oracle Connections
174

175
```python
176
oracle_url = ConnectionUrl(
177
    backend="oracle",
178
    username="hr",
179
    password="oracle",
180
    server="oracle.example.com",
181
    port=1521,
182
    database="ORCL"
183
)
184
```
185

186
### Raw Connection Strings
187

188
```python
189
# Use existing connection string
190
raw_conn = "postgresql://user:pass@host:port/db?sslmode=require"
191
conn_url = ConnectionUrl(raw_conn)
192

193
# ConnectionUrl inherits from str, so it works everywhere
194
import connectorx as cx
195
df = cx.read_sql(conn_url, "SELECT * FROM table")
196
```
197

198
## Advanced Features
199

200
### Connection String Rewriting
201

202
ConnectorX automatically rewrites certain connection strings for compatibility:
203

204
```python
205
# Redshift connections are rewritten to use PostgreSQL driver
206
redshift_url = ConnectionUrl(
207
    backend="redshift",
208
    username="user",
209
    password="pass", 
210
    server="cluster.redshift.amazonaws.com",
211
    port=5439,
212
    database="dev"
213
)
214
# Internally rewritten to: postgresql://user:pass@cluster.redshift.amazonaws.com:5439/dev
215
# Protocol automatically set to "cursor"
216

217
# ClickHouse connections are rewritten to use MySQL driver  
218
clickhouse_url = ConnectionUrl(
219
    backend="clickhouse",
220
    username="default",
221
    password="",
222
    server="clickhouse.example.com",
223
    port=9000,
224
    database="default"
225
)
226
# Internally rewritten to: mysql://default:@clickhouse.example.com:9000/default
227
# Protocol automatically set to "text"
228
```
229

230
### Type Safety
231

232
ConnectionUrl provides type safety for different backends:
233

234
```python
235
from typing import Literal
236

237
def connect_to_postgres(url: ConnectionUrl[Literal["postgresql"]]) -> None:
238
    # Function specifically expects PostgreSQL connection
239
    pass
240

241
postgres_url = ConnectionUrl(backend="postgresql", ...)  # Type: ConnectionUrl[Literal["postgresql"]]
242
mysql_url = ConnectionUrl(backend="mysql", ...)         # Type: ConnectionUrl[Literal["mysql"]] 
243

244
connect_to_postgres(postgres_url)  # ✓ Type check passes
245
connect_to_postgres(mysql_url)     # ✗ Type error
246
```
247

248
### Integration with ConnectorX Functions
249

250
ConnectionUrl objects work seamlessly with all ConnectorX functions:
251

252
```python
253
from connectorx import ConnectionUrl, read_sql, get_meta, partition_sql
254

255
# Build connection
256
conn = ConnectionUrl(
257
    backend="postgresql",
258
    username="user",
259
    password="pass",
260
    server="localhost", 
261
    port=5432,
262
    database="mydb"
263
)
264

265
# Use with all ConnectorX functions
266
df = read_sql(conn, "SELECT * FROM table")
267
meta = get_meta(conn, "SELECT * FROM table")
268
queries = partition_sql(conn, "SELECT * FROM table", "id", 4)
269
```
270

271
## Connection String Formats
272

273
### Supported URL Schemes
274

275
- `postgresql://` - PostgreSQL databases
276
- `mysql://` - MySQL and MariaDB databases  
277
- `sqlite://` - SQLite database files
278
- `mssql://` - Microsoft SQL Server
279
- `oracle://` - Oracle databases
280
- `bigquery://` - Google BigQuery
281
- `redshift://` - Amazon Redshift (rewritten to PostgreSQL)
282
- `clickhouse://` - ClickHouse (rewritten to MySQL)
283

284
### URL Encoding
285

286
ConnectionUrl automatically handles URL encoding for special characters:
287

288
```python
289
# Password with special characters
290
conn = ConnectionUrl(
291
    backend="postgresql",
292
    username="user",
293
    password="p@ssw0rd!#$",  # Special characters automatically encoded
294
    server="localhost",
295
    port=5432,
296
    database="mydb"
297
)
298
```
299

300
### Common Connection Patterns
301

302
```python
303
# Local development
304
local_pg = ConnectionUrl(
305
    backend="postgresql",
306
    username="postgres", 
307
    password="postgres",
308
    server="localhost",
309
    port=5432,
310
    database="dev_db"
311
)
312

313
# Production with SSL
314
prod_pg = ConnectionUrl(
315
    backend="postgresql", 
316
    username="app_user",
317
    password="secure_password",
318
    server="prod-db.company.com",
319
    port=5432,
320
    database="production",
321
    database_options={"sslmode": "require"}
322
)
323

324
# Read-only replica
325
replica_pg = ConnectionUrl(
326
    backend="postgresql",
327
    username="readonly_user", 
328
    password="readonly_pass",
329
    server="replica.company.com",
330
    port=5432,
331
    database="production",
332
    database_options={
333
        "sslmode": "require",
334
        "default_transaction_read_only": "on"
335
    }
336
)
337
```