just_database #

A pure-Dart SQL database engine with in-memory storage and optional file persistence. Perfect for Flutter applications that need a lightweight, embeddable SQL database with advanced indexing and query optimization features.

Features #

🚀 Pure Dart Implementation - No native dependencies, works everywhere Flutter runs
💾 Flexible Storage - In-memory with optional file persistence
🔍 Smart Indexing - Automatic index creation based on query patterns
📊 Composite Indexes - Multi-column indexes for complex queries
🔒 Multiple Concurrency Modes - Choose between standard, read-fast, or write-fast locking
📈 Query Tracking - Automatic performance monitoring and optimization
🎨 Built-in Admin UI - Optional Flutter widgets for database management
✅ Full SQL Support - CREATE, SELECT, INSERT, UPDATE, DELETE, and more
🔑 Constraints - PRIMARY KEY, UNIQUE, FOREIGN KEY support
📦 Table-Level Constraints - Composite primary keys and unique constraints

Installation #

Add this to your package's pubspec.yaml file:

dependencies:
  just_database: ^0.1.0

Then run:

flutter pub get

Quick Start #

Basic Usage #

import 'package:just_database/just_database.dart';

void main() async {
  // Create a database
  final db = JustDatabase('mydb');
  
  // Create a table
  await db.execute('''
    CREATE TABLE users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT UNIQUE,
      age INTEGER
    )
  ''');
  
  // Insert data
  await db.execute(
    "INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 28)"
  );
  
  // Query data
  final result = await db.execute('SELECT * FROM users WHERE age > 25');
  print(result.rows); // [{id: 1, name: Alice, email: alice@example.com, age: 28}]
}

With File Persistence #

import 'package:just_database/just_database.dart';

void main() async {
  // Create a persistent database
  final db = await DatabaseManager.createDatabase(
    'mydb',
    mode: DatabaseMode.standard,
    persist: true,
  );
  
  // Data is automatically saved to disk
  await db.execute("INSERT INTO users (name) VALUES ('Bob')");
  
  // Close and reopen - data persists
  await DatabaseManager.closeDatabase('mydb');
  final reopened = await DatabaseManager.openDatabase('mydb');
}

Database Modes #

Choose the right concurrency mode for your use case:

// Balanced read/write performance
final standardDb = JustDatabase('db', mode: DatabaseMode.standard);

// Optimized for many concurrent readers
final readFastDb = JustDatabase('db', mode: DatabaseMode.readFast);

// Optimized for write-heavy workloads
final writeFastDb = JustDatabase('db', mode: DatabaseMode.writeFast);

Advanced Features #

Composite Indexes #

Create multi-column indexes for better query performance:

await db.execute('''
  CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    category TEXT,
    price REAL,
    stock INTEGER
  )
''');

// Create a composite index
final table = db.getTable('products');
table.createCompositeIndex(['category', 'price'], IndexType.btree);

// Queries on these columns are automatically optimized
final result = await db.execute(
  "SELECT * FROM products WHERE category = 'Electronics' AND price < 100"
);

Automatic Query-Based Indexing #

The database tracks query patterns and automatically creates indexes:

// After 100+ queries with the same WHERE clause pattern,
// an index is automatically created
for (int i = 0; i < 150; i++) {
  await db.execute("SELECT * FROM users WHERE age > 25");
}

// Check auto-created indexes
final table = db.getTable('users');
print(table.indexMetadata); // Shows auto-created index on 'age' column

Table Constraints #

// Composite primary key
await db.execute('''
  CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    PRIMARY KEY (user_id, role_id)
  )
''');

// Composite unique constraint
await db.execute('''
  CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    slug TEXT,
    UNIQUE (user_id, slug)
  )
''');

// Foreign key (automatically indexed)
await db.execute('''
  CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    post_id INTEGER,
    FOREIGN KEY (post_id) REFERENCES posts(id)
  )
''');

Index Management #

final table = db.getTable('users');

// Create manual index
table.createIndex('email', IndexType.hash);

// Create composite index
table.createCompositeIndex(['department', 'salary'], IndexType.btree);

// Check index usage and performance
for (final metadata in table.indexMetadata) {
  print('Index: ${metadata.columns}');
  print('Type: ${metadata.type}');
  print('Lookups: ${metadata.lookupCount}');
  print('Hit ratio: ${metadata.hitRatio}');
}

// Remove index
table.removeIndex('email');

Built-in Admin UI #

The package includes optional Flutter UI components for database management:

import 'package:flutter/material.dart';
import 'package:provider/provider.dart';
import 'package:just_database/ui.dart';

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return ChangeNotifierProvider(
      create: (_) => DatabaseProvider(),
      child: MaterialApp(
        home: JUDatabaseScreen(
          // Optional: customize theme
          theme: ThemeData(
            colorScheme: ColorScheme.fromSeed(seedColor: Colors.blue),
          ),
          // Optional: provide seed data function
          onSeedDatabase: (context, provider, info) async {
            // Your custom seed logic
          },
        ),
      ),
    );
  }
}

The admin UI provides:

📋 Database Management - Create, open, and delete databases
🔍 Schema Inspector - View tables, columns, and constraints
💻 Query Editor - Execute SQL with syntax examples
⚙️ Settings - Configure persistence and database modes
📊 Query History - Track executed queries

API Reference #

Core Classes #

`JustDatabase`

Main database class for executing SQL operations.

// Constructor
JustDatabase(String name, {DatabaseMode mode = DatabaseMode.standard})

// Methods
Future<QueryResult> execute(String sql)
Table getTable(String name)
TableSchema? getTableSchema(String name)
List<String> getTableNames()
Future<void> close()

`DatabaseManager`

Static methods for managing multiple databases.

// Create database
static Future<JustDatabase> createDatabase(
  String name, {
  DatabaseMode mode = DatabaseMode.standard,
  bool persist = false,
})

// Open existing database
static Future<JustDatabase> openDatabase(String name)

// List all databases
static Future<List<DatabaseInfo>> listDatabases()

// Close database
static Future<void> closeDatabase(String name)

// Delete database
static Future<void> deleteDatabase(String name)

`Table`

Represents a database table with manual control.

// Indexing
void createIndex(String column, IndexType type)
void createCompositeIndex(List<String> columns, IndexType type)
void removeIndex(String column)
List<IndexMetadata> get indexMetadata

// Data access
List<Row> get rows
int get rowCount
TableSchema get schema

Query Results #

class QueryResult {
  List<Map<String, dynamic>> rows;  // Result rows
  int affectedRows;                  // For INSERT/UPDATE/DELETE
  String? message;                   // Status message
  Duration? executionTime;           // Query duration
}

SQL Support #

Supported Statements

CREATE TABLE - With constraints (PK, UNIQUE, FK, NOT NULL, DEFAULT)
DROP TABLE
INSERT INTO - Single and bulk inserts
SELECT - With WHERE, ORDER BY, LIMIT
UPDATE - With WHERE clauses
DELETE - With WHERE clauses

Supported Data Types

INTEGER - Whole numbers
REAL - Floating-point numbers
TEXT - Strings
BOOLEAN - True/false values
BLOB - Binary data

Supported Constraints

PRIMARY KEY - Single or composite
UNIQUE - Single or composite
FOREIGN KEY - Automatically indexed
NOT NULL - Required fields
DEFAULT - Default values
AUTOINCREMENT - Auto-incrementing integers

Performance Tips #

Use Indexes - Create indexes on frequently queried columns
Composite Indexes - Use for multi-column WHERE clauses
Batch Operations - Use transactions for multiple inserts
Choose Right Mode - Pick the database mode that fits your workload
Enable Persistence Wisely - In-memory is faster but not persistent
Monitor Queries - Check indexMetadata for hit ratios

Examples #

Complete CRUD Application #

import 'package:just_database/just_database.dart';

class TodoApp {
  late JustDatabase db;
  
  Future<void> init() async {
    db = await DatabaseManager.createDatabase('todos', persist: true);
    
    await db.execute('''
      CREATE TABLE IF NOT EXISTS todos (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        completed BOOLEAN DEFAULT false,
        created_at INTEGER DEFAULT 0
      )
    ''');
  }
  
  Future<void> addTodo(String title) async {
    await db.execute(
      "INSERT INTO todos (title, created_at) VALUES ('$title', ${DateTime.now().millisecondsSinceEpoch})"
    );
  }
  
  Future<List<Map<String, dynamic>>> getTodos() async {
    final result = await db.execute(
      'SELECT * FROM todos ORDER BY created_at DESC'
    );
    return result.rows;
  }
  
  Future<void> updateTodo(int id, bool completed) async {
    await db.execute(
      "UPDATE todos SET completed = $completed WHERE id = $id"
    );
  }
  
  Future<void> deleteTodo(int id) async {
    await db.execute("DELETE FROM todos WHERE id = $id");
  }
}

Relational Data with Foreign Keys #

// Create related tables
await db.execute('''
  CREATE TABLE authors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
  )
''');

await db.execute('''
  CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id)
  )
''');

// Foreign key columns are automatically indexed
final books = db.getTable('books');
print(books.indexMetadata); // Shows auto-index on author_id

// Query with joins (manual for now)
final authorsResult = await db.execute('SELECT * FROM authors WHERE id = 1');
final author = authorsResult.rows.first;

final booksResult = await db.execute(
  "SELECT * FROM books WHERE author_id = ${author['id']}"
);

Limitations #

No JOIN operations in SQL parser (manual joins required)
No transactions in SQL syntax (atomic operations at table level)
No ALTER TABLE support yet
Limited aggregate functions (COUNT, SUM, AVG, MIN, MAX planned)

Contributing #

Contributions are welcome! Please feel free to submit a Pull Request.

License #

This project is licensed under the MIT License - see the LICENSE file for details.

Support #

For issues, questions, or contributions, please visit the GitHub repository.

just_database 0.0.1 just_database: ^0.0.1 copied to clipboard

Metadata