I am building an Electron desktop app using React, Vite, ShadCN, Jotai, React Query, and…
- User
I am building an Electron desktop app using React, Vite, ShadCN, Jotai, React Query, and SQLite for state and data management. The app is a high school library management system used exclusively by the school director. The app will manage books, students, loans, archives, and offer statistics to show the necessary data in an easy-to-understand format. Below is a detailed breakdown of the project's requirements and architecture:
Project Details:
-
Authentication:
- The app should have a login page to authenticate users, only allowing access to the school director.
- There should also be a welcome page for first-time users that helps register the first account.
- Passwords should be securely hashed using bcrypt.
-
Main Pages:
- Main Page (Dashboard): This should display statistics related to the system (total books, students, active loans, etc.).
- Books Page:
- This page allows for adding, editing, deleting books.
- Books should be organized by school year (the system needs to manage different school years).
- Students Page:
- Add, edit, delete students.
- Add students via Excel import (use the xlsx library for this feature).
- Loans Page:
- Show both open and completed loans with their statuses.
- Ability to add or delete loans.
- Loans should only be created for students who have a library card.
- Archive Page:
- A simple table displaying archived items.
- Add, edit, and delete archive items.
- Statistics Page:
- Display key data points in a visually appealing format (use charts to represent loan statuses, number of books, etc.).
-
School Year Management:
- Each dataset (books, students, loans) should be associated with a school year.
- Users should be able to select a school year and add/edit/delete the data for that specific year.
- When switching between school years, all data should update accordingly.
-
SQLite Database:
- Use SQLite as the local database to store all data (books, students, loans, archives, users).
- Each table in the database should include a school year field to track which data belongs to which year.
- Ensure CRUD operations for books, students, loans, and archives work properly.
-
General Requirements:
- The app should be fast and lightweight (use Vite for fast builds).
- Electron should handle the main process (window management, database access) and securely pass information to the React frontend using IPC (Inter-Process Communication).
- Preload scripts should be used to securely expose APIs for React to interact with the SQLite database.
- Use Jotai for global state management (for user sessions, school year selection, etc.).
- Use React Query to handle data fetching and caching between the frontend and backend.
- The UI should be styled with ShadCN components to ensure a clean, professional interface.
Tech Stack:
- Electron: To build the desktop app and manage local file systems.
- React (with Vite): For the frontend and faster builds.
- ShadCN: To ensure a modern and clean UI.
- Jotai: To manage global state (such as authentication and school year selection).
- React Query: To handle API fetching and caching for efficient communication with the backend.
- SQLite: For the local database to store and manage books, students, loans, and archives.
- bcrypt: For password hashing and user authentication.
- xlsx: To allow importing student data from Excel files.
Detailed Requirements:
- Explain how to implement state management using Jotai for user authentication and global school year management.
- Walk through the process of connecting the SQLite database to Electron and how to set up IPC to handle communication between the main process (Electron) and the renderer process (React).
- Provide instructions for setting up React Query to handle data fetching for books, students, and loans.
- Describe how to create custom hooks in the React app for various CRUD operations using the Electron API.
- Provide guidance on using ShadCN to build reusable components for forms, tables, and charts. this is the boilerplate i use Libs and tools To develop a Electron app, you probably will need some UI, test, formatter, style or other kind of library or framework, so let me install and configure some of them to you.
Core 🏍️ Electron 32 Vite 5 SWC DX 🛠️ TypeScript 5 Prettier Zod React Query (Tan Stack) UI 🎨 React Tailwind CSS Shadcn UI Geist as default font i18next Lucide Test 🧪 Jest Playwright React Testing Library Packing and distribution 📦 Electron Forge Documentation 📚 Storybook CI/CD 🚀 Pre-configured GitHub Actions workflow, for test with Playwright Project preferences 🎯 Use Context isolation titleBarStyle: hidden (Using custom title bar) Geist as default font Some default styles was applied, check the styles directory If you don't know some of these libraries or tools, I recommend you to check their documentation to understand how they work and how to use them.
Directory structure . └── ./src/ ├── ./src/assets/ │ └── ./src/assets/fonts/ ├── ./src/components/ │ └── ./src/components/ui/ ├── ./src/helpers/ │ └── ./src/helpers/ipc/ ├── ./src/layout/ ├── ./src/lib/ ├── ./src/pages/ ├── ./src/stories/ ├── ./src/style/ └── ./src/tests/ src/: Main directory assets/: Store assets like images, fonts, etc. components/: Store UI components ui/: Store Shadcn UI components (this is the default direcotry used by Shadcn UI) helpers/: Store IPC related functions to be called in the renderer process ipc/: Directory to store IPC context and listener functions Some implementations are already done, like theme and window for the custom title bar layout/: Directory to store layout components lib/: Store libraries and other utilities pages/: Store app's pages stories/: Store Storybook stories style/: Store global styles tests/: Store tests (from Jest and Playwright) NPM script To run any of those scripts:
npm run start: Start the app in development mode package: Package your application into a platform-specific executable bundle and put the result in a folder. make: Generate platform-specific distributables (e.g. .exe, .dmg, etc) of your application for distribution. publish: Electron Forge's way of taking the artifacts generated by the make command and sending them to a service somewhere for you to distribute or use as updates. prett: Run Prettier to format the code storybook: Start Storybook build-storybook: Run the Storybook's build command test: Run the default unit-test script (Jest) test:watch: Run the default unit-test script in watch mode (Jest) test:unit: Run the Jest tests test:e2e: Run the Playwright tests test:all: Run all tests (Jest and Playwright) The test scripts involving Playwright require the app be builded before running the tests. So, before run the tests, run the package, make or publish script.
** stay consist with the folder structure , and there is no testing , and give me the full and correct sql schema , i use better-sqlite and fix the school year issue it should be in all tables
-
- Assistant
Certainly! Below is the full and correct SQL schema for your application, designed to work with better-sqlite3 in Electron. I've ensured that the
school_year_idfield is included in all relevant tables to fix the school year issue.
SQL Schema:
-- Table: users CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, password_hash TEXT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- Table: school_years CREATE TABLE IF NOT EXISTS school_years ( id INTEGER PRIMARY KEY AUTOINCREMENT, year TEXT NOT NULL UNIQUE, -- e.g., '2023-2024' start_date DATE, end_date DATE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ); -- Table: students CREATE TABLE IF NOT EXISTS students ( id INTEGER PRIMARY KEY AUTOINCREMENT, school_year_id INTEGER NOT NULL, student_number TEXT NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, library_card_number TEXT, -- Nullable until assigned created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (school_year_id) REFERENCES school_years(id) ON DELETE CASCADE, UNIQUE (school_year_id, student_number), UNIQUE (school_year_id, library_card_number) ); -- Table: books CREATE TABLE IF NOT EXISTS books ( id INTEGER PRIMARY KEY AUTOINCREMENT, school_year_id INTEGER NOT NULL, isbn TEXT, title TEXT NOT NULL, author TEXT, publisher TEXT, publication_year INTEGER, category TEXT, copies INTEGER DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (school_year_id) REFERENCES school_years(id) ON DELETE CASCADE ); -- Table: loans CREATE TABLE IF NOT EXISTS loans ( id INTEGER PRIMARY KEY AUTOINCREMENT, school_year_id INTEGER NOT NULL, student_id INTEGER NOT NULL, book_id INTEGER NOT NULL, loan_date DATETIME DEFAULT CURRENT_TIMESTAMP, due_date DATETIME, return_date DATETIME, status TEXT NOT NULL CHECK (status IN ('open', 'completed')), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (school_year_id) REFERENCES school_years(id) ON DELETE CASCADE, FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE, FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE ); -- Table: archives CREATE TABLE IF NOT EXISTS archives ( id INTEGER PRIMARY KEY AUTOINCREMENT, school_year_id INTEGER NOT NULL, title TEXT NOT NULL, description TEXT, archived_at DATETIME DEFAULT CURRENT_TIMESTAMP, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (school_year_id) REFERENCES school_years(id) ON DELETE CASCADE );
Explanation:
-
Users Table:
- Purpose: Store user credentials for authentication (in this case, primarily the school director).
- Fields:
id: Primary key.username: Unique username for the user.password_hash: Password hashed using bcrypt.created_at&updated_at: Timestamps for record keeping.
- Note: The
userstable does not includeschool_year_idsince authentication is global and not tied to any specific school year.
-
School Years Table:
- Purpose: Manage different school years and associate data accordingly.
- Fields:
id: Primary key.year: Unique identifier for the school year (e.g., '2023-2024').start_date&end_date: Optional fields to define the duration of the school year.created_at&updated_at: Timestamps.
-
Students Table:
- Purpose: Store student information.
- Fields:
id: Primary key.school_year_id: Foreign key referencingschool_years(id).student_number: Unique per school year (composite unique constraint withschool_year_id).first_name&last_name: Student's name.library_card_number: Unique per school year, nullable until assigned.created_at&updated_at: Timestamps.
- Constraints:
FOREIGN KEY (school_year_id): Ensures that students are associated with a valid school year.UNIQUE (school_year_id, student_number): Ensures uniqueness within a school year.UNIQUE (school_year_id, library_card_number).
- Notes:
- When importing students via Excel, ensure to include the
school_year_id.
- When importing students via Excel, ensure to include the
-
Books Table:
- Purpose: Store book information.
- Fields:
id: Primary key.school_year_id: Foreign key referencingschool_years(id).isbn,title,author,publisher,publication_year,category: Book details.copies: Number of copies available.created_at&updated_at: Timestamps.
- Constraints:
FOREIGN KEY (school_year_id): Associates books with a specific school year.
- Notes:
- Books are organized by school year to accommodate changes in inventory.
-
Loans Table:
- Purpose: Manage book loans to students.
- Fields:
id: Primary key.school_year_id: Foreign key referencingschool_years(id).student_id: Foreign key referencingstudents(id).book_id: Foreign key referencingbooks(id).loan_date: Date when the book was loaned out.due_date: When the book is due back.return_date: When the book was actually returned.status: 'open' or 'completed'.created_at&updated_at: Timestamps.
- Constraints:
CHECK (status IN ('open', 'completed')): Ensures status is valid.FOREIGN KEYconstraints link tostudentsandbooks.
- Notes:
- Ensure that
student_idandbook_idcorrespond to records within the sameschool_year_id. - Application logic should enforce that only students with a
library_card_numbercan have loans.
- Ensure that
-
Archives Table:
- Purpose: Store archived items or records.
- Fields:
id: Primary key.school_year_id: Foreign key referencingschool_years(id).title: Title of the archived item.description: Optional description.archived_at: Timestamp when the item was archived.created_at&updated_at: Timestamps.
- Constraints:
FOREIGN KEY (school_year_id): Associates archives with a specific school year.
Additional Notes:
-
Foreign Key Constraints with
ON DELETE CASCADE:- When a
school_yearis deleted, all associated records instudents,books,loans, andarchiveswill also be deleted automatically. - This ensures data integrity and prevents orphaned records.
- When a
-
Consistency Across Tables:
- It's crucial that the
school_year_idin related tables (e.g.,loans,students,books) correspond correctly to enforce data consistency. - While SQLite doesn't support complex foreign key constraints across multiple columns to enforce matching
school_year_id, you should enforce this consistency through your application logic in Electron.
- It's crucial that the
-
Application Logic Enforcement:
- In your Electron app, ensure that when creating or updating records, the
school_year_idis correctly assigned and consistent across related entities. - For example, when creating a new loan, verify that the
studentandbookselected both belong to the sameschool_year_idas the loan.
- In your Electron app, ensure that when creating or updating records, the
-
User Table Exclusion of
school_year_id:- The
userstable does not include theschool_year_idbecause user authentication is global and not tied to any specific school year. - If in the future you plan to have users associated with specific school years, you can add the
school_year_idfield accordingly.
- The
Implementation Tips with better-sqlite3:
-
Setting Up better-sqlite3:
const Database = require('better-sqlite3'); const db = new Database('library.db', { verbose: console.log }); -
Creating Tables:
- Execute the SQL schema statements when initializing your database.
db.exec(` -- Paste your SQL schema here `); -
Parameterized Queries:
- Use prepared statements to prevent SQL injection.
const insertStudent = db.prepare(` INSERT INTO students (school_year_id, student_number, first_name, last_name, library_card_number) VALUES (@school_year_id, @student_number, @first_name, @last_name, @library_card_number) `); insertStudent.run({ school_year_id: 1, student_number: 'S12345', first_name: 'John', last_name: 'Doe', library_card_number: 'LC1001' }); -
Transactions:
- Use transactions for batch operations, especially when importing students via Excel.
const importStudents = db.transaction((students) => { for (const student of students) { insertStudent.run(student); } }); importStudents([ { school_year_id: 1, student_number: 'S12346', first_name: 'Jane', last_name: 'Smith', library_card_number: 'LC1002' }, // ...other students ]); -
Error Handling:
- Catch and handle unique constraint violations.
try { insertStudent.run(studentData); } catch (err) { if (err.code === 'SQLITE_CONSTRAINT_UNIQUE') { // Handle duplicate entry } else { throw err; } }
Folder Structure Consistency:
-
Database Initialization:
- Place your database connection and initialization logic in the
./src/lib/directory, perhaps in a file likedatabase.js.
- Place your database connection and initialization logic in the
-
IPC Helpers:
- Store your IPC-related functions that interact with the database in
./src/helpers/ipc/. - Example:
studentIpc.jsfor handling student-related IPC calls.
- Store your IPC-related functions that interact with the database in
-
Data Models:
- Although not strictly required, you might consider creating a
modelsdirectory within./src/lib/to define classes or objects representing your data entities.
- Although not strictly required, you might consider creating a
Next Steps:
Once your database schema is set up and you're confident that the
school_year_idissue is resolved across all tables, you can proceed to:-
Implement State Management with Jotai:
- Manage global state like the current
school_year_idand user authentication status. - Example:
import { atom } from 'jotai'; export const currentSchoolYearIdAtom = atom(null); export const currentUserAtom = atom(null);
- Manage global state like the current
-
Set Up IPC Communication:
- Use Electron's
ipcMainandipcRenderermodules to handle communication between the main and renderer processes. - Ensure all database operations are performed securely in the main process.
- Use Electron's
-
Configure React Query:
- Utilize React Query to manage data fetching, caching, and synchronization between your frontend components and backend data.
- Example:
import { useQuery } from 'react-query'; const fetchStudents = async (schoolYearId) => { // IPC call to fetch students for the given school year }; const { data: students, isLoading } = useQuery(['students', schoolYearId], () => fetchStudents(schoolYearId));
-
Create Custom Hooks for CRUD Operations:
- Encapsulate your data fetching and mutation logic into reusable hooks.
- Example:
import { useMutation, useQueryClient } from 'react-query'; export const useAddStudent = () => { const queryClient = useQueryClient(); return useMutation( (newStudent) => { // IPC call to add student }, { onSuccess: () => { // Invalidate and refetch queryClient.invalidateQueries('students'); }, } ); };
-
Design UI with ShadCN UI Components:
- Utilize ShadCN components for consistent styling.
- Create reusable components for forms (e.g., student registration form), tables (e.g., list of books), and charts (e.g., statistics page).
- Example:
import { Button, Input, Table } from 'ui'; // Assuming 'ui' is where ShadCN components are exported const StudentList = () => ( <Table> {/* ...table content */} </Table> );
Conclusion:
The provided SQL schema should serve as a solid foundation for your application's data layer. By ensuring that the
school_year_idis included and correctly managed in all tables, you can effectively handle data partitioning by school year.Remember to enforce data consistency through your application logic, especially where cross-referencing
school_year_idbetween related records is critical. This attention to detail will help maintain the integrity of your library management system across different school years. -