Database setup

​

Prerequisites

• Download the necessary scripts from the repo:
  git clone https://github.com/learning-commons-org/knowledge-graph.git
cd knowledge-graph
• A running PostgreSQL instance. If you don’t have PostgreSQL installed locally, you can use Docker to run a PostgreSQL container. For detailed instructions on setting up and running PostgreSQL with Docker, refer to the official PostgreSQL Docker documentation.
• Downloaded CSV data files.

​

Set up your database

​

Step 1: Create database tables

1. Create a dedicated database (optional).
   Copy

   psql -U <username> -c "CREATE DATABASE <database>;"
   

2. Connect to your PostgreSQL database and create the necessary tables.
   Copy

   psql -U <username> -d <database> -f create_tables.sql
   

   Or execute the SQL file content directly in your PostgreSQL client. See create_tables.sql for the complete table definitions.

   ​

   Step 2: Load CSV Data

   1. After creating the tables, load the data ⚠️ IMPORTANT: Update the file paths in load_data.sql to point to your downloaded CSV files. Ensure PostgreSQL has read access to the file paths specified in the load script.
   2. Execute the load script
   Copy

   psql -U <username> -d <database> -f load_data.sql
   

   ​

   Query Examples

   Once the data is loaded, you can run queries to explore the tables:
   Copy

   -- List all tables
   \dt
   -- Sample data queries
   SELECT * FROM standards_framework LIMIT 10;
   SELECT * FROM standards_framework_item LIMIT 10;
   SELECT * FROM learning_component LIMIT 10;
   SELECT * FROM relationships LIMIT 10;
   

   ​

   Docker PostgreSQL Setup

   If you are using Docker PostgreSQL, you can connect using:
   Copy

   docker exec -it <container-name> psql -U <username> -d <database>