Database

Comprehensive tools for managing data schemas, editing content, and building queries.

Axellero Studio's Database section provides comprehensive tools for managing data schemas, editing content, and building queries. Work with your application's data structure and content directly within the Studio interface.

Overview

The Database section provides comprehensive data management capabilities:

Capability	Description	Key Features
Schema Management	Design and modify database structures	Table creation, column configuration, relationship management
Data Operations	Direct data manipulation and editing	CRUD operations, bulk data processing, validation
Query Building	Visual and SQL query construction	Query builder interface, custom SQL, saved queries
Relationship Visualization	Understand data connections and dependencies	Entity diagrams, relationship mapping, dependency tracking
Performance Monitoring	Track usage and optimize database operations	Query performance, index optimization, usage analytics

Key Features

Schema Management

Comprehensive database design and modification tools:

Operation	Purpose	Configuration Options	Use Cases
Table Creation	Define database structure	Names, descriptions, primary keys, indexes	Entity definition, data organization
Column Configuration	Define data fields and constraints	Data types, constraints, validation, metadata	Field specification, data integrity
Relationship Management	Connect related tables	Foreign keys, relationship types, constraints	Data modeling, referential integrity
Index Management	Optimize query performance	Index types, column selection, composite indexes	Performance optimization, query speed

Data Types and Constraints

Data Type	Description	Validation Options	Common Use Cases
Text	String data with length limits	Min/max length, regex patterns, required	Names, descriptions, identifiers
Number	Integer and decimal values	Min/max values, precision, required	Counts, prices, measurements
Date/DateTime	Temporal data	Date ranges, format validation, timezone	Timestamps, deadlines, scheduling
Boolean	True/false values	Default values, required	Flags, status indicators, preferences
JSON	Structured data objects	Schema validation, required fields	Configuration, metadata, flexible data
UUID	Unique identifiers	Auto-generation, format validation	Primary keys, references, tracking

Relationship Types

Relationship	Description	Implementation	Examples
One-to-One	Single record relates to single record	Unique foreign key constraint	User ↔ Profile, Order ↔ Payment
One-to-Many	Single record relates to multiple records	Foreign key reference	Customer → Orders, Category → Products
Many-to-Many	Multiple records relate to multiple records	Junction table with foreign keys	Users ↔ Roles, Products ↔ Tags
Self-Referencing	Records relate to other records in same table	Foreign key to same table	Employee → Manager, Category → Parent Category

Data Editing

Directly manipulate database content:

Record Management:

Add new records with form interfaces
Edit existing records with validation
Delete records with confirmation
Bulk operations for multiple records

Data Import/Export:

Import data from CSV, JSON, and other formats
Export data for backup or analysis
Batch data processing
Data migration tools

Data Validation:

Real-time validation during data entry
Custom validation rules
Constraint checking
Error reporting and correction

Query Builder

Create and execute database queries:

Visual Query Builder:

Drag-and-drop query construction
Table selection and joining
Column filtering and selection
Condition building with logical operators

SQL Editor:

Write custom SQL queries directly
Syntax highlighting and completion
Query validation and error checking
Result formatting and export

Saved Queries:

Store frequently used queries
Organize queries by category
Share queries with team members
Version control for query changes

Interface Components

Schema Designer

Visual interface for database structure:

Table List: Browse all database tables
Relationship Diagram: Visual representation of table connections
Column Editor: Detailed column configuration
Index Manager: Create and manage database indexes

Data Editor

Spreadsheet-like interface for data manipulation:

Grid View: Tabular display of database records
Form View: Individual record editing forms
Filter Controls: Quick data filtering and search
Pagination: Navigate through large datasets

Query Interface

Tools for database queries:

Query Builder: Visual query construction
SQL Editor: Direct SQL query writing
Result Panel: Query execution results
Export Options: Save results in various formats

Database Explorer

Navigate database structure:

Table Browser: Hierarchical view of database objects
Search Functionality: Find tables, columns, and data
Metadata Viewer: Detailed object information
Usage Statistics: Track table and column usage

Working with Schemas

Creating Tables

Design new database structures:

Open Schema Designer: Navigate to Database → Schema
Create New Table: Click "Add Table" button
Configure Table Properties:
- Table name and description
- Primary key selection
- Initial column definitions
Add Columns: Define column names, types, and constraints
Set Relationships: Link to other tables as needed
Save Schema: Apply changes to the database

Modifying Schemas

Update existing database structures:

Adding Columns:

Specify column properties
Choose appropriate data types
Set default values for existing records
Handle migration requirements

Removing Columns:

Verify no dependencies exist
Backup affected data
Handle cascade effects
Update related queries and workflows

Changing Data Types:

Validate data compatibility
Handle conversion requirements
Update constraints and indexes
Test dependent functionality

Relationship Management

Create and maintain table relationships:

Foreign Keys:

Select referencing and referenced tables
Choose appropriate columns
Set cascade options (update, delete)
Validate relationship integrity

Join Tables:

Create intersection tables for many-to-many relationships
Define composite keys
Set up proper indexing
Handle relationship data

Data Management

Record Operations

Manipulate individual database records:

Creating Records:

Use form interfaces for data entry
Validate required fields
Apply business rules
Handle related record creation

Updating Records:

Edit individual fields
Validate changes against constraints
Track modification history
Handle concurrent updates

Deleting Records:

Confirm deletion operations
Check for dependent records
Handle cascade deletions
Maintain data integrity

Bulk Operations

Handle multiple records efficiently:

Bulk Insert:

Import data from external files
Map columns to database fields
Validate imported data
Handle import errors and conflicts

Bulk Update:

Update multiple records simultaneously
Apply conditional updates
Preview changes before applying
Rollback capabilities

Data Export:

Export selected data sets
Choose export formats (CSV, JSON, XML)
Include related data
Schedule regular exports

Data Validation

Ensure data quality and integrity:

Constraint Validation:

Check required field values
Validate data types and formats
Enforce unique constraints
Verify foreign key relationships

Custom Validation:

Create business rule validators
Implement complex validation logic
Provide meaningful error messages
Handle validation failures gracefully

Query Operations

Query Builder Components

Component	Purpose	Options	Examples
Table Selection	Choose data sources	Single table, multiple tables, joins	SELECT FROM users, products JOIN categories
Column Selection	Define output fields	Columns, aliases, aggregates, calculations	SELECT name, COUNT(), price 0.8 AS discount_price
Filtering	Limit results based on criteria	WHERE conditions, operators, functions	WHERE status = 'active' AND created_at > '2024-01-01'
Grouping	Aggregate data by categories	GROUP BY, HAVING, aggregate functions	GROUP BY category HAVING COUNT(*) > 10
Sorting	Order query results	ORDER BY, ASC/DESC, multiple columns	ORDER BY created_at DESC, name ASC

Join Types and Usage

Join Type	Description	When to Use	Result Set
INNER JOIN	Returns matching records from both tables	Standard data relationships	Only records with matches in both tables
LEFT JOIN	Returns all records from left table, matching from right	Optional relationships, preserve left table data	All left records + matching right records
RIGHT JOIN	Returns all records from right table, matching from left	Rare usage, opposite of LEFT JOIN	All right records + matching left records
FULL OUTER JOIN	Returns all records when there's a match in either table	Complete data analysis, find missing relationships	All records from both tables
CROSS JOIN	Cartesian product of both tables	Generate combinations, mathematical operations	Every left record paired with every right record

Query Performance Optimization

Optimization Technique	Description	Implementation	Performance Impact
Index Usage	Leverage database indexes for faster lookups	Create indexes on frequently queried columns	10-100x query speed improvement
Query Simplification	Reduce complexity and unnecessary operations	Eliminate unnecessary JOINs, simplify WHERE clauses	2-5x performance improvement
Result Limiting	Restrict result set size	Use LIMIT, TOP, or pagination	Reduced memory usage, faster response
Selective Columns	Only retrieve needed data	Avoid SELECT *, specify required columns	Reduced network traffic, faster processing
Prepared Statements	Pre-compile frequently used queries	Use parameterized queries	Faster execution, improved security