Persisting the Data Grid information
Persisting the data grid information with PHP and PostgresSQL
Jspreadsheet Team
Published at 21/09/2023
Introduction
Jspreadsheet is a lightweight JavaScript library that enables users to embed data grids with spreadsheet-like controls into their web applications. With features reminiscent of popular spreadsheet software, it allows effortless data management and enhances the usability of any web-based software. This guide will show you how to integrate Jspreadsheet with a PostgreSQL database using a PHP container in Docker.
Source code
If you are interested in this project, you can find the source code example on our GitHub at: https://github.com/jspreadsheet/jss-database-integration.
Pre-requisites
- Docker installed on your local machine.
- Basic knowledge of Docker, PostgreSQL, and PHP.
Step-by-step Guide
Step 1: Set Up the Project Locally
Start by downloading the project to your local machine. This project will contain essential configurations and files to facilitate the integration.
Step 2: Start the Docker Containers
Navigate to the root folder of the downloaded project and execute the following command:
$ docker-compose up
This command will initiate the containers specified in the docker-compose.yml file. It will include containers for PHP and PostgreSQL.
Step 3: Access the PHP Container
In another terminal window, while positioned in the root folder of the project, run:
$ docker-compose exec php bash
This command allows you to access the shell of the PHP container.
Step 4: Install Necessary PHP Dependencies
Within the PHP container terminal, execute the following command to install the required PHP packages and dependencies:
$ composer install
These dependencies will likely include packages that enable PHP to communicate with PostgreSQL, among other requirements.
Step 5: Access the PostgreSQL Container
Now, it's time to set up our database. To do this, in another terminal window (still positioned in the root folder), run:
$ docker-compose exec postgresql bash
This command provides access to the shell of the PostgreSQL container, allowing you to interact directly with the database server.
Step 6: Define Your Tables
Before we can persist our spreadsheet data to the database, we must define the tables that will store this data. To achieve this, we must modify the public/index.php file, writing the appropriate SQL commands and PHP code to synchronize Jspreadsheet with a specific table in PostgreSQL. Consider structuring your table with columns such as ID , Name , Age , and Occupation , reflecting the corresponding fields within your spreadsheet. Utilize the appropriate SQL commands to create these columns and ensure they can hold the data types you'll enter in Jspreadsheet.
Step 7: Launch the Application
Now, open your web browser and navigate to: http://localhost:8081 Here, you'll see the Jspreadsheet in action, and any changes made will be persisted in the PostgreSQL database.
Summary of Integration
Integrating Jspreadsheet with a PostgreSQL database can be streamlined using Docker, as demonstrated in the steps above. This approach provides a reliable way of managing and storing data from spreadsheets in a relational database. As a result, web applications can benefit from the ease of data manipulation offered by spreadsheets while ensuring the robustness and security of a PostgreSQL database backend.