0
# Configuration and Extensions
1

2
Configuration management for DuckDB settings, extension loading, and filesystem registration with support for MotherDuck and custom configuration options. This module provides utilities for configuring DuckDB connections and managing DuckDB-specific features.
3

4
## Capabilities
5

6
### Core Configuration Management
7

8
Functions for managing DuckDB configuration parameters and settings.
9

10
```python { .api }
11
def get_core_config():
12
    """
13
    Get set of core DuckDB configuration parameters.
14
    
15
    Retrieves all available DuckDB settings from the database plus
16
    MotherDuck-specific configuration keys for cloud connectivity.
17
    
18
    Returns:
19
    Set[str]: Set of configuration parameter names including:
20
        - All DuckDB settings from duckdb_settings()
21
        - MotherDuck parameters: motherduck_token, attach_mode, saas_mode
22
    """
23

24
def apply_config(dialect, conn, ext):
25
    """
26
    Apply configuration settings to DuckDB connection.
27
    
28
    Processes configuration parameters and applies them as DuckDB SET
29
    statements with proper type handling and SQL escaping.
30
    
31
    Parameters:
32
    - dialect (Dialect): SQLAlchemy dialect instance for processing
33
    - conn (DuckDBPyConnection): DuckDB connection to configure  
34
    - ext (Dict[str, Union[str, int, bool]]): Configuration parameters
35
    
36
    Raises:
37
    AssertionError: If configuration value type is not supported
38
    """
39
```
40

41
### Type Processing for Configuration
42

43
Type mapping and processing utilities for configuration values.
44

45
```python { .api }
46
TYPES: Dict[Type, TypeEngine]
47
"""
48
Maps Python types to SQLAlchemy type processors for configuration.
49

50
Supported types:
51
- int -> Integer()
52
- str -> String() 
53
- bool -> Boolean()
54
"""
55
```
56

57
### Version and Feature Detection
58

59
Utilities for detecting DuckDB version and available features.
60

61
```python { .api }
62
def has_comment_support():
63
    """
64
    Check if current DuckDB version supports table comments.
65
    
66
    Tests COMMENT ON TABLE functionality by attempting to create
67
    a test table and add a comment.
68
    
69
    Returns:
70
    bool: True if comments supported, False otherwise
71
    """
72

73
has_uhugeint_support: bool
74
"""Boolean flag indicating if DuckDB version supports UHUGEINT type (v0.10.0+)."""
75

76
duckdb_version: Version
77
"""Parsed version object for current DuckDB installation."""
78
```
79

80
## Connection Configuration Options
81

82
### Standard DuckDB Settings
83

84
All DuckDB configuration parameters are supported through the `config` parameter:
85

86
```python
87
engine = create_engine('duckdb:///:memory:', connect_args={
88
    'config': {
89
        'threads': 4,
90
        'memory_limit': '2GB',
91
        'max_memory': '4GB',
92
        'default_order': 'ASC',
93
        'enable_progress_bar': True,
94
        'enable_profiling': 'json',
95
        'profile_output': '/tmp/profile.json'
96
    }
97
})
98
```
99

100
### Extension Loading
101

102
Load DuckDB extensions at connection time:
103

104
```python
105
engine = create_engine('duckdb:///:memory:', connect_args={
106
    'preload_extensions': ['spatial', 'json', 'httpfs', 'parquet']
107
})
108
```
109

110
### Filesystem Registration
111

112
Register custom filesystems for remote data access:
113

114
```python
115
import fsspec
116

117
# Create custom filesystem
118
fs = fsspec.filesystem('s3', key='access_key', secret='secret_key')
119

120
engine = create_engine('duckdb:///:memory:', connect_args={
121
    'register_filesystems': [fs]
122
})
123
```
124

125
### MotherDuck Configuration
126

127
Connect to MotherDuck cloud service:
128

129
```python
130
engine = create_engine('duckdb:///', connect_args={
131
    'config': {
132
        'motherduck_token': 'your_token_here',
133
        'attach_mode': 'auto',
134
        'saas_mode': True
135
    }
136
})
137
```
138

139
### User Agent Configuration
140

141
Custom user agent strings for connection tracking:
142

143
```python
144
engine = create_engine('duckdb:///:memory:', connect_args={
145
    'config': {
146
        'custom_user_agent': 'MyApp/1.0'
147
    }
148
})
149
```
150

151
## Usage Examples
152

153
### Basic Configuration
154

155
```python
156
from sqlalchemy import create_engine
157

158
# Configure DuckDB with performance settings
159
engine = create_engine('duckdb:///data.db', connect_args={
160
    'config': {
161
        'threads': 8,
162
        'memory_limit': '8GB',
163
        'enable_progress_bar': True
164
    }
165
})
166
```
167

168
### Extension and Filesystem Setup
169

170
```python
171
import fsspec
172
from sqlalchemy import create_engine
173

174
# Setup S3 filesystem
175
s3_fs = fsspec.filesystem('s3', 
176
    key='your_access_key',
177
    secret='your_secret_key'
178
)
179

180
engine = create_engine('duckdb:///analytics.db', connect_args={
181
    'preload_extensions': ['httpfs', 'spatial', 'json'],
182
    'register_filesystems': [s3_fs],
183
    'config': {
184
        'threads': 4,
185
        'memory_limit': '4GB',
186
        's3_region': 'us-west-2'
187
    }
188
})
189
```
190

191
### MotherDuck Cloud Connection
192

193
```python
194
engine = create_engine('duckdb:///', connect_args={
195
    'config': {
196
        'motherduck_token': 'your_motherduck_token',
197
        'attach_mode': 'auto',
198
        'saas_mode': True,
199
        'custom_user_agent': 'MyDataApp/2.1'
200
    }
201
})
202
```
203

204
### Programmatic Configuration
205

206
```python
207
from duckdb_engine.config import get_core_config, apply_config
208
from duckdb_engine import Dialect
209
import duckdb
210

211
# Get available configuration options
212
core_configs = get_core_config()
213
print("Available configs:", core_configs)
214

215
# Apply configuration programmatically
216
dialect = Dialect()
217
conn = duckdb.connect(':memory:')
218

219
config_settings = {
220
    'threads': 2,
221
    'memory_limit': '1GB',
222
    'enable_profiling': 'json'
223
}
224

225
apply_config(dialect, conn, config_settings)
226
```
227

228
### Feature Detection
229

230
```python
231
from duckdb_engine._supports import (
232
    has_comment_support, 
233
    has_uhugeint_support,
234
    duckdb_version
235
)
236

237
print(f"DuckDB version: {duckdb_version}")
238
print(f"Supports comments: {has_comment_support()}")
239
print(f"Supports UHUGEINT: {has_uhugeint_support}")
240

241
# Conditional feature usage
242
if has_comment_support():
243
    engine.execute("COMMENT ON TABLE users IS 'User data table'")
244
```
245

246
### Connection String Configuration
247

248
Configuration can also be passed via connection string query parameters:
249

250
```python
251
# Basic configuration via URL
252
engine = create_engine(
253
    'duckdb:///data.db?threads=4&memory_limit=2GB&enable_progress_bar=true'
254
)
255

256
# MotherDuck connection via URL
257
engine = create_engine(
258
    'duckdb:///?motherduck_token=your_token&saas_mode=true'
259
)
260
```
261

262
### Advanced Configuration Patterns
263

264
```python
265
from sqlalchemy import create_engine, event
266

267
def configure_connection(dbapi_connection, connection_record):
268
    """Custom connection configuration function."""
269
    with dbapi_connection.cursor() as cursor:
270
        cursor.execute("SET enable_profiling = 'json'")
271
        cursor.execute("SET profile_output = '/tmp/queries.json'")
272

273
engine = create_engine('duckdb:///data.db')
274

275
# Apply configuration after connection
276
event.listen(engine, 'connect', configure_connection)
277
```