AutoSqfLite
A simple, automatic SQLite database helper for Flutter that handles table creation and schema updates dynamically. No more writing CREATE TABLE statements or managing migrations manually!
Features
- 🚀 Automatic table creation from Dart objects
- 📊 Dynamic schema updates when new fields are added
- 🔄 Automatic type mapping between Dart and SQLite
- 💾 Simple CRUD operations
- 🛠Zero configuration required
- 🎯 Type-safe operations
- 🔒 Password protection for databases
- 🔄 Support for nested objects with foreign key relationships
- 📅 Automatic DateTime handling
Installation
Add this to your package's pubspec.yaml
file:
dependencies:
autosqflite: ^1.0.0
Usage
Basic Setup
// Create an instance of AutoSqfLite
final db = AutoSqfLite(databaseName: 'my_app');
Database Encryption
To use an encrypted database, simply provide a password when creating the AutoSqfLite instance:
final db = AutoSqfLite(
databaseName: 'my_app',
password: 'your_secure_password'
);
Important Security Notes:
- Store your encryption password securely (e.g., using flutter_secure_storage)
- The password must be provided consistently for all database operations
- If the password is lost, the database cannot be recovered
- Consider implementing proper key management in your production environment
Creating Models
class Todo {
final int? id;
final String title;
final bool completed;
final DateTime createdAt;
Todo({
this.id,
required this.title,
required this.completed,
required this.createdAt,
});
// Convert to Map for database operations
Map<String, dynamic> toMap() {
return {
'id': id,
'title': title,
'completed': completed,
'createdAt': createdAt,
};
}
// Create from Map
factory Todo.fromMap(Map<String, dynamic> map) {
return Todo(
id: map['id'],
title: map['title'],
completed: map['completed'] == 1,
createdAt: DateTime.fromMillisecondsSinceEpoch(map['createdAt']),
);
}
}
CRUD Operations
// Insert
await db.insert('todos', todo.toMap());
// Get all records
final todos = await db.getAll('todos');
// Get single record
final todo = await db.get('todos', 1);
// Update
await db.update('todos', todo.toMap(), 1);
// Delete
await db.delete('todos', 1);
Automatic Schema Updates
AutoSqfLite automatically handles:
- Table creation when you first insert data
- Adding new columns when your model evolves
- Type mapping between Dart and SQLite
// Original model
class User {
String name;
int age;
}
// Later, add new fields - AutoSqfLite handles it automatically!
class User {
String name;
int age;
String email; // New field
bool isActive; // New field
}
Supported Types
AutoSqfLite automatically maps between Dart and SQLite types:
Dart Type | SQLite Type | Storage Format |
---|---|---|
int | INTEGER | Direct |
double | REAL | Direct |
String | TEXT | Direct |
bool | INTEGER | 0 or 1 |
DateTime | INTEGER | Milliseconds since epoch with '_datetime' suffix |
DateTime Handling
DateTime fields are automatically:
- Stored as milliseconds since epoch
- Suffixed with '_datetime' in the database
- Automatically converted back to DateTime when retrieved
- No manual conversion needed in your models
// Example with DateTime
class Event {
final DateTime createdAt;
// AutoSqfLite handles the DateTime conversion automatically
// The field will be stored as 'createdAt_datetime' in the database
Event({required this.createdAt});
}
Example
Here's a complete example using AutoSqfLite in a Todo app:
class TodoService {
final AutoSqfLite _db;
static const String tableName = 'todos';
TodoService() : _db = AutoSqfLite(databaseName: 'todo_app');
Future<void> addTodo(Todo todo) async {
await _db.insert(tableName, todo.toMap());
}
Future<List<Todo>> getAllTodos() async {
final maps = await _db.getAll(tableName);
return maps.map((map) => Todo.fromMap(map)).toList();
}
Future<void> updateTodo(Todo todo) async {
await _db.update(tableName, todo.toMap(), todo.id!);
}
Future<void> deleteTodo(int id) async {
await _db.delete(tableName, id);
}
}
Additional Features
- Automatic handling of boolean values (converts to 0/1 for SQLite)
- DateTime conversion to milliseconds for storage
- Null-safe operations
- Automatic table existence checking
Contributing
Contributions are welcome! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.
License
This project is licensed under the MIT License - see the LICENSE file for details.
Acknowledgments
- Built on top of the excellent sqflite_sqlcipher package
- Inspired by the need for simpler database operations in Flutter
Support
If you find this package helpful, please give it a like on pub.dev and star our GitHub repository!
DateTime and Nested Objects
AutoSqfLite provides enhanced support for DateTime fields and nested objects:
// Example with DateTime and nested object
class Order {
final DateTime createdAt;
final DateTime updatedAt;
Order({
required this.createdAt,
required this.updatedAt,
});
// Convert to Map for database operations
Map<String, dynamic> toMap() {
return {
'createdAt': createdAt,
'updatedAt': updatedAt,
};
}
// Create from Map
factory Order.fromMap(Map<String, dynamic> map) {
return Order(
createdAt: DateTime.fromMillisecondsSinceEpoch(map['createdAt']),
updatedAt: DateTime.fromMillisecondsSinceEpoch(map['updatedAt']),
);
}
}