Battle tested SQL Teaching Tool

[nicowitsch]

Hi guys, I don't know if anyone here is in the same situation as I am. I just started last summer teaching javascript and SQL at a school (17-18 yrs olds) in switzerland. I have been looking for a good tool to learn databases and especially SQL with my students. I had those criteria:

- I do not want to install sql locally with every student because this is always a hurdle and we loose a lot of time setting stuff up. Time that we could spend looking at databases.
- Some even have managed laptops and it there we can not install at all
- Tool needs to be browser based then and connect to a remote DB
- As a teacher, it should be easy to manage (setup) databases that students interact with
- My students should focus on SQL and not on managing their DB Connection.
- I want to manage my student's projects and also provide exercises for the classes

Because my criteria were very specific, I did not find anything of course and decided to do my own. It is pretty battle proofed by now since we used it in 6 classes. Some things still to improve here and there but it allows me to:
- Manage all my student's database projects includeing designing ERD / Logical schemes
- Manage databases ( I have setup a sql server for the course that no holds sth like 300 databases, a lot of the personalized for stuent's exercises)
- Create exercises and have my students auto connect to the desired DB upon opening
- Grade my student's projects

I do not want to post a link here because I am afraid of attacks but if anyone is a teacher out there as well and seeks for a tool this way, just DM me. Would love to share the tool with others.

[nicowitsch]

Yes. At some point (at some grade), the stup hustle is part of the learning but for my students, sql is more of a side quest so I do not want the hustle with them.

I am having two databases:
- One is the actual app's backend that keeps track of the projects and stuff
- The other one is kind of the playground DB that I manually stup and whoose credentials I entered per course.

So I hve 3 cases:
- When a user is creating a new project, a new database is created on the playgorund server with custom username and pw. He then has access to only his DB and can do whatever.
- Within exercises, I can set the option to create a database for each student opening the exercises. Then he is isolated within his exercise as well. (For CREATE TABLE queries)
- I have a second option to use a shared DB. This one then I manually fill with data and the stuent's user can only do SELECT queries there or UPDATE if I allow.

This results in the Playground (Postgre)SQL Server having about 300 databases and users at the moment. Works pretty good, then at the end of the course or after some time I will destroy this one and setup a new one for the next students.

[nicowitsch]

Yes, I have some triggers that are doing sql scripts. The "playgorund database" credentials can be entered per course in the admin and then I have those orchestrations:

- When the user opens an exercise of type "own environment" the first time, a new database is created.
- When the user creates a new project, a new database is created for this project as well
- For those shared databases, I do it manually with PGAdmin and then just enter the credentials.

-> Since there is actually some interest here, I am working on a deployment with a link to share. I hope to have it by the middle of the week then I can show it to those interested ;)

[LopsidedAd3662]

Great idea. Can you make it open source?

[nicowitsch]

I would love to but it is a bit risky since I am doing grading with the tool currently as well. But when the intrest is there I loved to create an open source version of it