Skip to main content

Database

Our application employs a hybrid database architecture, combining the strengths of a traditional SQL database (SQLite3) with JSON file storage. This approach ensures structured query capabilities while maintaining human-readable data persistence. JSON provides a reliable and platform-independent format for storage, inspection, and backup, whereas SQLite offers efficient querying, indexing, and relationship management.

Key Technologies and Libraries​

The database implementation relies on the following technologies:

  1. JSON Files: The primary storage format for song data, providing human-readable data persistence
  2. SQLite3: A lightweight, serverless SQL database engine used as a runtime database
  3. Sequelize ORM: An object-relational mapping library for structured database interactions
  4. Electron IPC: For secure communication between UI and database operations
  5. Node.js fs/path modules: For file system operations and path management

Database Architecture​

JSON​

The core of our database system is the collection of JSON files stored in resources/assets/songData/. Each song is represented by a dedicated JSON file with a filename matching the song's ID:

The model is defined using Sequelize's type system, which provides runtime validation and type-checking during database operations:

{
  "id": "0qj67KE5VXI",
  "title": "Troy, Gabriella - Breaking Free (From \"High School Musical\")",
  "uploader": "DisneyMusicVEVO",
  "audioPath": "audio/0qj67KE5VXI.mp3",
  "jacket": "images/0qj67KE5VXI/jacket.png",
  "images": [
    "images/0qj67KE5VXI/image_1741452244800.png"
  ],
  "moods": [],
  "status": "Yellow",
  // ...other fields...
}

These JSON files serve as the core for our application data, containing all metadata and configuration for each song.

SQLite as Runtime Database​

When the application starts, it reads all JSON files from the songData directory and loads them into a SQLite database using Sequelize ORM:

// ...existing code...

// Read in json files from assets/songData
const songDataDir = path.join(app.getAppPath(), 'resources', 'assets', 'songData');
const songFiles = fs
  .readdirSync(songDataDir)
  .filter((file) => file.endsWith('.json'));

const sampleSongs = songFiles.map((file) => {
  const filePath = path.join(songDataDir, file);
  const songData = JSON.parse(fs.readFileSync(filePath, 'utf-8'));
  return songData;
});

// Add songs to SQLite database
const createdSongs = await Song.bulkCreate(sampleSongs);
// ...existing code...

This SQLite database provides:

  • Efficient querying and filtering
  • Transaction support
  • Relationship management
  • A structured interface for application code

Bidirectional Synchronisation​

Whenever song metadata is modified in the application, updates are reflected in both JSON files and the SQLite database. This is handled by saveSongAsJson:

// ...existing code...

// Function to save a Song instance as a JSON file
const saveSongAsJson = async (song: SongModel) => {
  const songData = {
    id: song.id,
    title: song.title,
    // ...other fields...
  };

  const songDataDir = getResourcePath('assets', 'songData');
  if (!fs.existsSync(songDataDir)) {
    fs.mkdirSync(songDataDir, { recursive: true });
  }

  const filePath = path.join(songDataDir, `${song.id}.json`);
  fs.writeFileSync(filePath, JSON.stringify(songData, null, 2));
  console.log(`Song saved as JSON file: ${filePath}`);
};
// ...existing code...

Data Model​

The Song model defines the structure for both the SQLite database and the JSON files:

// ...existing code...
interface SongModel
  extends Model<
    InferAttributes<SongModel>,
    InferCreationAttributes<SongModel>
  > {
  id: CreationOptional<string>;
  title: string;
  uploader: string;
  audioPath: string;
  jacket: string;
  images: string[];
  moods: string[];
  status: string;
  colours: string[];
  colours_reason: string[];
  objects: string[];
  object_prompts: string[];
  particles: string[];
  backgrounds: string[];
  background_prompts: string[];
  shaderBackground: string;
  shaderTexture: string;
  particleColour: string[];
  createdAt: CreationOptional<Date>;
  updatedAt: CreationOptional<Date>;
}
// ...existing code...

This comprehensive model supports:

  • Basic metadata: Title, artist, creation date
  • Asset references: Paths to audio files, images, and visualisations
  • Status tracking: Processing state of each song (Blue, Yellow, Green, Red)
  • AI-generated content: Colors, moods, particles, backgrounds
  • Customisation options: User-defined settings and preferences

Database Configuration​

The SQLite database is configured in config.ts:

// ...existing code...
const dbPath = path.join(app.getPath('userData'), 'database.sqlite');

// Initialise Sequelize with SQLite
export const sequelize = new Sequelize({
  dialect: 'sqlite',
  storage: dbPath,
  dialectModule: require('sqlite3'),
  logging: console.log,
  define: {
    timestamps: true
  }
});
// ...existing code...

We also provide direct SQLite access through dbAsync for operations where Sequelize might not be optimal:

// ...existing code...
export const dbAsync = {
  run(sql: string, params: any[] = []): Promise<any> {
    return new Promise((resolve, reject) => {
      db.run(sql, params, function(err) {
        if (err) reject(err);
        else resolve({ id: this.lastID, changes: this.changes });
      });
    });
  },
  // ...other methods...
};
// ...existing code...

Asset Management​

Data entries reference media assets stored in a structured file hierarchy:

/assets
│
├── /audio/                  # Audio files for each song
│   ├── {songId}.mp3
│   └── {songId}.wav
│
├── /images/                 # Song-related images
│   └── /{songId}/
│       ├── jacket.png
│       └── image_1.png
│
├── /shader/                 # Shader-related visuals
│   ├── /background/
│   │   └── {songId}.jpg
│   └── /texture/
│       └── {songId}.jpg
│
└── /songData/               # Metadata or configuration per song
    └── {songId}.json

When UI components need to access these assets, they convert relative paths from the database to absolute file paths using the Electron IPC bridge:

// ...existing code...
const [imagePath, setImagePath] = React.useState<string>('');

React.useEffect(() => {
  const findImagePath = async () => {
    const response = await window.electron.fileSystem.mergeAssetPath(
      songDetails.jacket,
    );
    setImagePath(response);
  };
  findImagePath();
}, [songDetails.jacket]);
// ...existing code...

Workflow: Adding a YouTube Song​

When a user adds a new song from YouTube, the following sequence occurs:

  1. User enters a YouTube URL in the AddSongForm
  2. Form submits the URL to the database:add-song IPC channel
  3. Main process downloads the audio using the YouTube module
  4. Metadata is extracted and the audio is converted to multiple formats
  5. A new song record is created with the YouTube ID as the unique identifier
  6. The song data is saved as a JSON file
  7. The database UI is refreshed to show the new song

Database Refresh Mechanism​

Since the JSON files are the core storage location for the database, the application needs to refresh the SQLite database when:

  1. The application starts
  2. External changes are made to JSON files
  3. The user triggers a manual refresh

This is handled by our initialisation logic in init.ts, which reads all JSON files and updates the database accordingly.

Sequence Diagram: Song Data Flow​

Application Startup​

Adding a Song​

Updating a Song​

Error Handling and Data Integrity​

To maintain data consistency, we implement:

  • JSON Backups – Every change is persisted in JSON files.
  • Unique Song IDs – Ensuring data integrity.
  • Validation – Data is validated before being written to storage.
  • Recovery Mechanism – If SQLite fails, JSON data can be restored.

Advantages of the Hybrid Approach​

  1. Human-Readable Data – JSON allows easy inspection and modification.
  2. Structured Queries – SQLite ensures efficient filtering and relationships.
  3. Reliability – JSON files act as persistent backups.
  4. Performance – SQLite enables fast queries without performance degradation.
  5. Cross-Platform Compatibility – Works across Windows, macOS, and Linux.

Conclusion​

Our hybrid database solution balances flexibility and efficiency, combining JSON for persistent storage and SQLite for structured queries. This approach ensures data remains accessible, performant, and easy to maintain across different environments. The bidirectional synchronisation between JSON and SQLite guarantees consistency, making the system robust and scalable for future enhancements.