SQLiteConnection provides a streamlined interface for interacting with SQLite databases in Dart, supporting various operations like search, insert, update, delete, and more.
- Search Operations: Case-insensitive, column-specific, multi-column search.
- Insert Operations: Single and batch insertions.
- Update Operations: Single and batch updates with conflict handling.
- Delete Operations: Single and batch deletions.
- Advanced Queries: Group by, pagination, and random row retrieval.
- Database Management: Table creation, dropping, and backups.
Add the package to your pubspec.yaml
:
dependencies:
sqlite_flutter_pcl: ^1.0.0
import 'package:sqlite_flutter_pcl/sqlite_connection.dart';
# Create table class implements ISQLiteItem
```dart
class SqlModel implements ISQLiteItem {
static const String tableName = 'sql_model';
static const String tableId = 'id';
static const String tableTitle = 'title';
static const String tableValue = 'value';
int? id;
String? title;
String? value;
SqlModel({this.id, this.title, this.value});
@override
String getTableName() {
return tableName;
}
@override
int getPrimaryKey() {
return id ?? 0;
}
@override
Map<String, dynamic> toMap() {
return {
tableId: id,
tableTitle: title,
tableValue: value,
};
}
@override
String getPrimaryKeyName() {
return tableId;
}
@override
ISQLiteItem fromMap(Map<String, dynamic> map) {
return SqlModel(
id: map[tableId],
title: map[tableTitle],
value: map[tableValue],
);
}
}
//Init if SQLiteConnection not initialize when new instance created
SQLiteConnection.initDatabaseLib();
//Sqlite filepath
final databasePath = await getTemporaryDatabaseFilePath();
final connection = SQLiteConnection(path: databasePath);
//create table
connection.createTable(SqlModel());
//insert new item;
var newItem = SqlModel(title: 'Title 1', value: 'Value 1');
await connection.insert(newItem);
//retrieve items
var isqliteItems = await connection.toList(SqlModel());
//convert to type list
var items = isqliteItems.whereType<SqlModel>().toList();
var items = isqliteItems.cast<SqlModel>().toList();
//update items
for (var item in items) {
item.value = 'Updated';
await connection.update(item);
}
//OR
await connection.updateAll(items);
//delete items
await connection.deleteAll(items);
//query single value
var queryItems = await connection.toListWhere(SqlModel(), 'title', 'Title 1');
//query items by value
var queryItems = await connection.searchColumns(SqlModel(), 'title', 'Title 1');
// Returns a list of items with titles 'title1' and 'title2', using a batch query for efficiency.
var results = await connection.toListWhereValuesAre(SqlModel(), 'title', ['title1', 'title2']);
//Search across multiple columns.
var columnNames = ['word', 'number', 'lemma', 'xlit', 'pronounce', 'description'];
var items = await db.toListColumns(Strongs(), columnNames, query);
//Delete all table records
connection.deleteRecords(SqlModel());
//Drop table
connection.dropTable(SqlModel());
Future<String> getTemporaryDatabaseFilePath() async {
final directory = await getTemporaryDirectory();
final path = join(directory.path, 'your_database.db');
print(path);
return path;
}
var sqlItem = SqlModel(title: 'Title 1', value: 'Value 1');
final id = await connection.insert(sqlItem);
final totalInserted = await connection.insertAll(sqlItems);
await connection.update(sqlItem);
final totalUpdated = await connection.updateAll(sqlItems);
final rowsDeleted = await connection.delete(sqlItem);
final totalDeleted = await connection.deleteAll(sqlItems);
final results = await connection.search(sqlItem, 'columnName', 'searchQuery');
final results = await connection.searchColumns(
sqlItem,
['column1', 'column2'],
'searchQuery',
);
final result = await connection.whereColumnHasValue(sqlItem, 'columnName', value);
final items = await connection.toList(sqlItem);
final items = await connection.toListWhereColumnHasValue(sqlItem, 'columnName', value);
final items = await connection.paginate(sqlItem, limit: 10, offset: 20);
final items = await connection.groupBy(
sqlItem,
['column1', 'column2'],
'groupByColumn',
orderByColumn: 'orderByColumn',
);
-
Backup Database
await connection.backupDatabase('/path/to/backup.db');
-
Drop Table
await connection.dropTable(sqlItem);
-
Vacuum Database
await connection.vacuum(sqlItem);
-
Count Rows
final count = await connection.count(sqlItem);
-
Retrieve Table Columns
final columns = await connection.tableColumns('tableName');