0
# Database Integration
1

2
Datastore classes and database abstraction layer supporting multiple ORMs (SQLAlchemy, MongoEngine, Peewee, Pony) for flexible database integration with Flask-Security.
3

4
## Capabilities
5

6
### Base Datastore Classes
7

8
Abstract base classes providing the foundation for Flask-Security's database abstraction layer.
9

10
```python { .api }
11
class Datastore:
12
    """
13
    Abstract base datastore class defining the interface for all datastores.
14
    """
15
    
16
    def __init__(self, db):
17
        """
18
        Initialize datastore with database connection.
19
        
20
        Parameters:
21
        - db: Database connection or session object
22
        """
23
    
24
    def put(self, obj):
25
        """
26
        Save object to database.
27
        
28
        Parameters:
29
        - obj: Object to save
30
        
31
        Returns:
32
        Saved object
33
        """
34
    
35
    def delete(self, obj):
36
        """
37
        Delete object from database.
38
        
39
        Parameters:
40
        - obj: Object to delete
41
        
42
        Returns:
43
        Deleted object
44
        """
45
    
46
    def commit(self):
47
        """
48
        Commit current transaction.
49
        """
50

51
class UserDatastore(Datastore):
52
    """
53
    Core user management datastore with CRUD operations for users and roles.
54
    """
55
    
56
    def __init__(self, db, user_model, role_model=None):
57
        """
58
        Initialize user datastore.
59
        
60
        Parameters:
61
        - db: Database connection or session
62
        - user_model: User model class
63
        - role_model: Role model class (optional)
64
        """
65
    
66
    def get_user(self, identifier):
67
        """
68
        Get user by identifier (ID, email, username).
69
        
70
        Parameters:
71
        - identifier: User ID, email, or username
72
        
73
        Returns:
74
        User object if found, None otherwise
75
        """
76
    
77
    def find_user(self, **kwargs):
78
        """
79
        Find user by arbitrary attributes.
80
        
81
        Parameters:
82
        - kwargs: Attribute filters for user lookup
83
        
84
        Returns:
85
        User object if found, None otherwise
86
        """
87
    
88
    def create_user(self, **kwargs):
89
        """
90
        Create new user with specified attributes.
91
        
92
        Parameters:
93
        - kwargs: User attributes (email, password, etc.)
94
        
95
        Returns:
96
        Created user object
97
        """
98
    
99
    def delete_user(self, user):
100
        """
101
        Delete user from database.
102
        
103
        Parameters:
104
        - user: User object to delete
105
        
106
        Returns:
107
        True if deleted successfully, False otherwise
108
        """
109
    
110
    def activate_user(self, user):
111
        """
112
        Activate user account.
113
        
114
        Parameters:
115
        - user: User object to activate
116
        
117
        Returns:
118
        Activated user object
119
        """
120
    
121
    def deactivate_user(self, user):
122
        """
123
        Deactivate user account.
124
        
125
        Parameters:
126
        - user: User object to deactivate
127
        
128
        Returns:
129
        Deactivated user object
130
        """
131
    
132
    def create_role(self, **kwargs):
133
        """
134
        Create new role with specified attributes.
135
        
136
        Parameters:
137
        - kwargs: Role attributes (name, description, permissions)
138
        
139
        Returns:
140
        Created role object
141
        """
142
    
143
    def find_role(self, role):
144
        """
145
        Find role by name or role object.
146
        
147
        Parameters:
148
        - role: Role name (string) or role object
149
        
150
        Returns:
151
        Role object if found, None otherwise
152
        """
153
    
154
    def add_role_to_user(self, user, role):
155
        """
156
        Add role to user.
157
        
158
        Parameters:
159
        - user: User object
160
        - role: Role object or role name
161
        
162
        Returns:
163
        True if role added successfully, False if user already had role
164
        """
165
    
166
    def remove_role_from_user(self, user, role):
167
        """
168
        Remove role from user.
169
        
170
        Parameters:
171
        - user: User object
172
        - role: Role object or role name
173
        
174
        Returns:
175
        True if role removed successfully, False if user didn't have role
176
        """
177
```
178

179
### SQLAlchemy Datastores
180

181
Datastores for SQLAlchemy ORM integration with various Flask-SQLAlchemy versions and configurations.
182

183
```python { .api }
184
class SQLAlchemyUserDatastore(UserDatastore):
185
    """
186
    SQLAlchemy integration datastore for Flask-Security.
187
    """
188
    
189
    def __init__(self, db, user_model, role_model):
190
        """
191
        Initialize SQLAlchemy datastore.
192
        
193
        Parameters:
194
        - db: SQLAlchemy database instance
195
        - user_model: SQLAlchemy user model class
196
        - role_model: SQLAlchemy role model class
197
        """
198
    
199
    def get_user(self, identifier):
200
        """Get user by ID, email, or username using SQLAlchemy query."""
201
    
202
    def find_user(self, case_insensitive=False, **kwargs):
203
        """
204
        Find user with SQLAlchemy filters.
205
        
206
        Parameters:
207
        - case_insensitive: Whether to perform case-insensitive search
208
        - kwargs: Filter attributes
209
        
210
        Returns:
211
        User object if found, None otherwise
212
        """
213
    
214
    def toggle_active(self, user):
215
        """
216
        Toggle user active status.
217
        
218
        Parameters:
219
        - user: User object to toggle
220
        
221
        Returns:
222
        Updated user object
223
        """
224

225
class SQLAlchemySessionUserDatastore(SQLAlchemyUserDatastore):
226
    """
227
    SQLAlchemy datastore with explicit session management.
228
    """
229
    
230
    def __init__(self, session, user_model, role_model):
231
        """
232
        Initialize SQLAlchemy session datastore.
233
        
234
        Parameters:
235
        - session: SQLAlchemy session object
236
        - user_model: SQLAlchemy user model class
237
        - role_model: SQLAlchemy role model class
238
        """
239

240
class FSQLALiteUserDatastore(UserDatastore):
241
    """
242
    Lightweight SQLAlchemy datastore for Flask-SQLAlchemy-Lite.
243
    """
244
    
245
    def __init__(self, db, user_model, role_model):
246
        """
247
        Initialize Flask-SQLAlchemy-Lite datastore.
248
        
249
        Parameters:
250
        - db: Flask-SQLAlchemy-Lite database instance
251
        - user_model: User model class
252
        - role_model: Role model class
253
        """
254
```
255

256
### Alternative ORM Datastores
257

258
Datastores for other popular Python ORMs providing flexible database backend options.
259

260
```python { .api }
261
class MongoEngineUserDatastore(UserDatastore):
262
    """
263
    MongoDB/MongoEngine integration datastore.
264
    """
265
    
266
    def __init__(self, db, user_model, role_model=None):
267
        """
268
        Initialize MongoEngine datastore.
269
        
270
        Parameters:
271
        - db: MongoEngine database connection
272
        - user_model: MongoEngine user document class
273
        - role_model: MongoEngine role document class (optional)
274
        """
275
    
276
    def get_user(self, identifier):
277
        """Get user from MongoDB using MongoEngine queries."""
278
    
279
    def find_user(self, case_insensitive=False, **kwargs):
280
        """
281
        Find user in MongoDB with case-insensitive option.
282
        
283
        Parameters:
284
        - case_insensitive: Enable case-insensitive search
285
        - kwargs: Query filters
286
        
287
        Returns:
288
        User document if found, None otherwise
289
        """
290

291
class PeeweeUserDatastore(UserDatastore):
292
    """
293
    Peewee ORM integration datastore.
294
    """
295
    
296
    def __init__(self, db, user_model, role_model=None, role_link=None):
297
        """
298
        Initialize Peewee datastore.
299
        
300
        Parameters:
301
        - db: Peewee database instance
302
        - user_model: Peewee user model class
303
        - role_model: Peewee role model class (optional)
304
        - role_link: Peewee many-to-many link model (optional)
305
        """
306
    
307
    def get_user(self, identifier):
308
        """Get user using Peewee ORM queries."""
309
    
310
    def find_user(self, **kwargs):
311
        """
312
        Find user with Peewee query filters.
313
        
314
        Parameters:
315
        - kwargs: Query filter attributes
316
        
317
        Returns:
318
        User model instance if found, None otherwise
319
        """
320

321
class PonyUserDatastore(UserDatastore):
322
    """
323
    Pony ORM integration datastore.
324
    """
325
    
326
    def __init__(self, db, user_model, role_model=None):
327
        """
328
        Initialize Pony ORM datastore.
329
        
330
        Parameters:
331
        - db: Pony database instance  
332
        - user_model: Pony user entity class
333
        - role_model: Pony role entity class (optional)
334
        """
335
    
336
    def get_user(self, identifier):
337
        """Get user using Pony ORM queries."""
338
    
339
    def find_user(self, **kwargs):
340
        """
341
        Find user with Pony ORM query syntax.
342
        
343
        Parameters:
344
        - kwargs: Entity attribute filters
345
        
346
        Returns:
347
        User entity if found, None otherwise
348
        """
349
```
350

351
### Database Helper Classes
352

353
Utility classes for database compatibility and data type management.
354

355
```python { .api }
356
class AsaList:
357
    """
358
    Custom list type for database storage compatibility.
359
    Handles serialization/deserialization of list data for database storage.
360
    """
361
    
362
    def __init__(self, data=None):
363
        """
364
        Initialize AsaList with optional data.
365
        
366
        Parameters:
367
        - data: Initial list data (optional)
368
        """
369
    
370
    def append(self, item):
371
        """
372
        Append item to list.
373
        
374
        Parameters:
375
        - item: Item to append
376
        """
377
    
378
    def remove(self, item):
379
        """
380
        Remove item from list.
381
        
382
        Parameters:
383
        - item: Item to remove
384
        """
385
    
386
    def __contains__(self, item):
387
        """Check if item is in list."""
388
    
389
    def __iter__(self):
390
        """Iterate over list items."""
391
```
392

393
## Usage Examples
394

395
### SQLAlchemy Setup
396

397
```python
398
from flask import Flask
399
from flask_sqlalchemy import SQLAlchemy
400
from flask_security import Security, SQLAlchemyUserDatastore, UserMixin, RoleMixin
401

402
app = Flask(__name__)
403
app.config['SECRET_KEY'] = 'super-secret'
404
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/mydb'
405

406
db = SQLAlchemy(app)
407

408
# Define many-to-many relationship table
409
roles_users = db.Table('roles_users',
410
    db.Column('user_id', db.Integer(), db.ForeignKey('user.id')),
411
    db.Column('role_id', db.Integer(), db.ForeignKey('role.id'))
412
)
413

414
# Define User model
415
class User(db.Model, UserMixin):
416
    __tablename__ = 'user'
417
    
418
    id = db.Column(db.Integer, primary_key=True)
419
    email = db.Column(db.String(255), unique=True, nullable=False)
420
    username = db.Column(db.String(80), unique=True, nullable=True)
421
    password = db.Column(db.String(255), nullable=False)
422
    active = db.Column(db.Boolean(), default=True)
423
    confirmed_at = db.Column(db.DateTime())
424
    
425
    # Two-factor authentication fields
426
    tf_totp_secret = db.Column(db.String(255))
427
    tf_primary_method = db.Column(db.String(20))
428
    
429
    # Unified signin fields
430
    us_phone_number = db.Column(db.String(20))
431
    us_totp_secrets = db.Column(db.Text)  # JSON field
432
    
433
    # Relationships
434
    roles = db.relationship('Role', secondary=roles_users,
435
                           backref=db.backref('users', lazy='dynamic'))
436

437
# Define Role model
438
class Role(db.Model, RoleMixin):
439
    __tablename__ = 'role'
440
    
441
    id = db.Column(db.Integer(), primary_key=True)
442
    name = db.Column(db.String(80), unique=True, nullable=False)
443
    description = db.Column(db.String(255))
444

445
# Initialize Flask-Security with SQLAlchemy datastore
446
user_datastore = SQLAlchemyUserDatastore(db, User, Role)
447
security = Security(app, user_datastore)
448

449
# Create tables
450
with app.app_context():
451
    db.create_all()
452
```
453

454
### MongoEngine Setup
455

456
```python
457
from flask import Flask
458
from flask_mongoengine import MongoEngine
459
from flask_security import Security, MongoEngineUserDatastore, UserMixin, RoleMixin
460

461
app = Flask(__name__)
462
app.config['SECRET_KEY'] = 'super-secret'
463
app.config['MONGODB_DB'] = 'mydatabase'
464
app.config['MONGODB_HOST'] = 'localhost'
465
app.config['MONGODB_PORT'] = 27017
466

467
db = MongoEngine(app)
468

469
# Define Role document
470
class Role(db.Document, RoleMixin):
471
    name = db.StringField(max_length=80, unique=True, required=True)
472
    description = db.StringField(max_length=255)
473

474
# Define User document
475
class User(db.Document, UserMixin):
476
    email = db.EmailField(unique=True, required=True)
477
    username = db.StringField(max_length=80, unique=True)
478
    password = db.StringField(max_length=255, required=True)
479
    active = db.BooleanField(default=True)
480
    confirmed_at = db.DateTimeField()
481
    
482
    # Two-factor fields
483
    tf_totp_secret = db.StringField(max_length=255)
484
    tf_primary_method = db.StringField(max_length=20)
485
    
486
    # Role relationship
487
    roles = db.ListField(db.ReferenceField(Role), default=[])
488

489
# Initialize Flask-Security with MongoEngine datastore
490
user_datastore = MongoEngineUserDatastore(db, User, Role)
491
security = Security(app, user_datastore)
492
```
493

494
### Peewee Setup
495

496
```python
497
from flask import Flask
498
from peewee import *
499
from flask_security import Security, PeeweeUserDatastore, UserMixin, RoleMixin
500

501
app = Flask(__name__)
502
app.config['SECRET_KEY'] = 'super-secret'
503

504
# Initialize Peewee database
505
database = SqliteDatabase('myapp.db')
506

507
# Define base model
508
class BaseModel(Model):
509
    class Meta:
510
        database = database
511

512
# Define Role model
513
class Role(BaseModel, RoleMixin):
514
    name = CharField(unique=True, max_length=80)
515
    description = CharField(max_length=255, null=True)
516

517
# Define User model
518
class User(BaseModel, UserMixin):
519
    email = CharField(unique=True, max_length=255)
520
    username = CharField(unique=True, max_length=80, null=True)
521
    password = CharField(max_length=255)
522
    active = BooleanField(default=True)
523
    confirmed_at = DateTimeField(null=True)
524
    
525
    # Two-factor fields
526
    tf_totp_secret = CharField(max_length=255, null=True)
527
    tf_primary_method = CharField(max_length=20, null=True)
528

529
# Define many-to-many relationship
530
class UserRole(BaseModel):
531
    user = ForeignKeyField(User, backref='user_roles')
532
    role = ForeignKeyField(Role, backref='role_users')
533
    
534
    class Meta:
535
        indexes = (
536
            (('user', 'role'), True),  # Unique constraint
537
        )
538

539
# Initialize Flask-Security with Peewee datastore
540
user_datastore = PeeweeUserDatastore(database, User, Role, UserRole)
541
security = Security(app, user_datastore)
542

543
# Create tables
544
with app.app_context():
545
    database.create_tables([User, Role, UserRole], safe=True)
546
```
547

548
### Pony ORM Setup
549

550
```python
551
from flask import Flask
552
from pony.orm import *
553
from flask_security import Security, PonyUserDatastore, UserMixin, RoleMixin
554

555
app = Flask(__name__)
556
app.config['SECRET_KEY'] = 'super-secret'
557

558
# Initialize Pony database
559
db = Database()
560

561
# Define User entity
562
class User(db.Entity, UserMixin):
563
    _table_ = 'user'
564
    
565
    id = PrimaryKey(int, auto=True)
566
    email = Required(str, unique=True, max_len=255)
567
    username = Optional(str, unique=True, max_len=80)
568
    password = Required(str, max_len=255)
569
    active = Required(bool, default=True)
570
    confirmed_at = Optional(datetime)
571
    
572
    # Two-factor fields
573
    tf_totp_secret = Optional(str, max_len=255)
574
    tf_primary_method = Optional(str, max_len=20)
575
    
576
    # Role relationship
577
    roles = Set('Role')
578

579
# Define Role entity
580
class Role(db.Entity, RoleMixin):
581
    _table_ = 'role'
582
    
583
    id = PrimaryKey(int, auto=True)
584
    name = Required(str, unique=True, max_len=80)
585
    description = Optional(str, max_len=255)
586
    
587
    # User relationship
588
    users = Set(User)
589

590
# Bind database and generate mapping
591
db.bind('sqlite', 'myapp.db')
592
db.generate_mapping(create_tables=True)
593

594
# Initialize Flask-Security with Pony datastore
595
user_datastore = PonyUserDatastore(db, User, Role)
596
security = Security(app, user_datastore)
597
```
598

599
### Custom Datastore Operations
600

601
```python
602
from flask_security import current_user
603

604
@app.route('/admin/users')
605
def list_users():
606
    """List all users using datastore."""
607
    users = user_datastore.user_model.query.all()  # SQLAlchemy example
608
    return render_template('admin_users.html', users=users)
609

610
@app.route('/admin/create-user', methods=['POST'])
611
def create_user():
612
    """Create user via datastore."""
613
    email = request.form.get('email')
614
    password = request.form.get('password')
615
    
616
    # Create user using datastore
617
    user = user_datastore.create_user(
618
        email=email,
619
        password=hash_password(password),
620
        active=True
621
    )
622
    
623
    # Assign default role
624
    default_role = user_datastore.find_role('user')
625
    if default_role:
626
        user_datastore.add_role_to_user(user, default_role)
627
    
628
    user_datastore.commit()
629
    
630
    flash(f'User {email} created successfully')
631
    return redirect(url_for('list_users'))
632

633
@app.route('/admin/toggle-user/<int:user_id>')
634
def toggle_user_active(user_id):
635
    """Toggle user active status."""
636
    user = user_datastore.get_user(user_id)
637
    
638
    if user:
639
        if hasattr(user_datastore, 'toggle_active'):
640
            # SQLAlchemy datastore has toggle_active method
641
            user_datastore.toggle_active(user)
642
        else:
643
            # Manual toggle for other datastores
644
            user.active = not user.active
645
            user_datastore.put(user)
646
        
647
        user_datastore.commit()
648
        flash(f'User {user.email} {"activated" if user.active else "deactivated"}')
649
    
650
    return redirect(url_for('list_users'))
651
```
652

653
### Role and Permission Management
654

655
```python
656
@app.route('/admin/setup-roles')
657
def setup_roles():
658
    """Initialize default roles and permissions."""
659
    
660
    # Create roles if they don't exist
661
    roles_to_create = [
662
        ('admin', 'Administrator with full access'),
663
        ('moderator', 'Content moderator'),
664
        ('user', 'Regular user'),
665
    ]
666
    
667
    for role_name, description in roles_to_create:
668
        if not user_datastore.find_role(role_name):
669
            user_datastore.create_role(
670
                name=role_name,
671
                description=description
672
            )
673
    
674
    user_datastore.commit()
675
    flash('Default roles created')
676
    return redirect(url_for('admin_dashboard'))
677

678
@app.route('/admin/assign-role/<int:user_id>/<role_name>')
679
def assign_role(user_id, role_name):
680
    """Assign role to user."""
681
    user = user_datastore.get_user(user_id)
682
    role = user_datastore.find_role(role_name)
683
    
684
    if user and role:
685
        if user_datastore.add_role_to_user(user, role):
686
            user_datastore.commit()
687
            flash(f'Role {role_name} assigned to {user.email}')
688
        else:
689
            flash(f'{user.email} already has role {role_name}')
690
    else:
691
        flash('User or role not found')
692
    
693
    return redirect(url_for('list_users'))
694
```
695

696
### Database Migration Helpers
697

698
```python
699
def migrate_user_data():
700
    """Example migration function for updating user data."""
701
    
702
    # Add new field to existing users (example)
703
    for user in user_datastore.user_model.query.all():
704
        if not hasattr(user, 'created_at') or user.created_at is None:
705
            user.created_at = datetime.utcnow()
706
            user_datastore.put(user)
707
    
708
    user_datastore.commit()
709

710
def cleanup_inactive_users():
711
    """Clean up old inactive user accounts."""
712
    from datetime import datetime, timedelta
713
    
714
    cutoff_date = datetime.utcnow() - timedelta(days=365)
715
    
716
    # Find inactive users older than cutoff
717
    if hasattr(user_datastore, 'find_user'):
718
        # Use datastore method if available
719
        inactive_users = user_datastore.user_model.query.filter(
720
            user_datastore.user_model.active == False,
721
            user_datastore.user_model.confirmed_at < cutoff_date
722
        ).all()
723
    else:
724
        # Manual query for other datastores
725
        inactive_users = []
726
    
727
    for user in inactive_users:
728
        print(f"Would delete inactive user: {user.email}")
729
        # user_datastore.delete_user(user)  # Uncomment to actually delete
730
    
731
    # user_datastore.commit()  # Uncomment when ready to commit changes
732
```
733

734
## Datastore Configuration
735

736
### Connection Configuration
737

738
```python
739
# SQLAlchemy configurations
740
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://user:pass@localhost/mydb'
741
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
742
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
743
    'pool_size': 10,
744
    'pool_recycle': 3600,
745
    'pool_pre_ping': True
746
}
747

748
# MongoEngine configurations  
749
app.config['MONGODB_SETTINGS'] = {
750
    'db': 'mydatabase',
751
    'host': 'localhost',
752
    'port': 27017,
753
    'username': 'myuser',
754
    'password': 'mypassword',
755
    'authentication_source': 'admin'
756
}
757

758
# Peewee configurations (handled in code)
759
database_config = {
760
    'engine': 'peewee.PostgresqlDatabase',
761
    'name': 'mydatabase',
762
    'user': 'myuser',
763
    'password': 'mypassword',
764
    'host': 'localhost',
765
    'port': 5432
766
}
767
```
768

769
### Model Configuration
770

771
```python
772
# Configure Flask-Security model field names
773
app.config['SECURITY_USER_IDENTITY_ATTRIBUTES'] = ['email', 'username']
774
app.config['SECURITY_USERNAME_ENABLE'] = True
775
app.config['SECURITY_USERNAME_REQUIRED'] = False
776

777
# Password configuration
778
app.config['SECURITY_PASSWORD_HASH'] = 'bcrypt'
779
app.config['SECURITY_PASSWORD_SALT'] = 'your-password-salt'
780

781
# Role configuration
782
app.config['SECURITY_ROLE_HIERARCHY'] = {
783
    'admin': ['moderator', 'user'],
784
    'moderator': ['user']
785
}
786
```
787

788
## Performance Considerations
789

790
### Query Optimization
791
- Use appropriate database indexes on frequently queried fields
792
- Implement connection pooling for high-traffic applications
793
- Consider read replicas for read-heavy workloads
794
- Use database-specific optimizations (e.g., PostgreSQL partial indexes)
795

796
### Caching Strategies
797
- Implement user session caching to reduce database lookups
798
- Cache role and permission data for authorization checks
799
- Use Redis or Memcached for distributed caching
800
- Implement query result caching for expensive operations
801

802
### Scalability
803
- Design models to support horizontal scaling
804
- Use database sharding for very large user bases
805
- Implement proper connection pooling and timeout settings
806
- Monitor database performance and query patterns