So now I want to show you how you can use a script to seed the database instead of using the studio as we just did in the previous chapter. And I also want to show you how you can completely reset your database if for any reasons you run into any migration issues especially later when we start adding some relations to our existing elements that might cause some problems. So let's go ahead first and learn how to build a seed script. So I'm gonna go ahead and do the following. I'm gonna go ahead and create a new folder, scripts, inside of your root project.
So I have it here already because I tried it out so you probably don't have this. So just anywhere in the root of your project create a scripts folder. Inside of it we are going to create a seed.ts file. Now let's go ahead and let's import our .environment config so that we can load the environment variables from here. Then let's import drizzle from HTTP and let's import neon from neon database serverless and now let's import everything as schema from dot dot slash database slash schema Let's define SQL and let me just not misspell this.
So constant SQL is gonna be NEON and it will use process environment database URL and as always double check that you have the database URL inside of your .environment file. There we go. It seems like I had a typo here again. So make sure that you always confirm that otherwise you're gonna run into problems which are gonna look hard to debug and are just going to cause unnecessary issues. So let's add TypeScript ignore for the next line because in here we have to define our database which uses drizzle and passes in the SQL and adds our schema to it.
And now let's go ahead and define our main method which is going to be an asynchronous method and yeah if you're watching this into the future try and remove tsignore. If you're not getting this TypeScript error it means that these two packages have fixed their type compatibility. But until then you can just add TypeScript ignore and you've already seen throughout the project that everything works fine so it's just a type error incompatibility here. So inside of this asynchronous function I'm going to open a try and catch block here and first thing I want to do is I want to catch the error in case seeding our database goes wrong. So I want to add console.error and I'm going to go ahead and pass in the error here and then I'm going to throw a new error failed to seed the database and then inside of here let's add a console.log seeding database and first things first I want to go ahead and remove all the existing elements.
So database.delete, let's remove schema.courses and let's go ahead and delete schema.user.progress. And then let's console.log seeding finished. So for now this is what's going to be our seed script. So now we have to find a way to run this script. So if you go ahead and shut down your app and if you try and go and run node scripts and run seed.ts you're gonna get an error that you cannot use an import statement outside of a module here.
So what you would have to do is turn all of this into constant and then instead of using from you would have to use require. Right? So that's how you would do it if you were running it with node. But there's no reason for us to do that in 2024. There's a lot of other packages which we can use.
The simplest one would be to use tsnode or tsx and of course let's just do one more thing. So we define this function but we never call it So let's just make sure that we call the function. So just execute main here at the bottom. So this is what I'm going to do. I'm gonna go ahead and I'm going to install in my development dependencies a TSX package.
And now I'm gonna do the following. I don't want to write this full script every time, so I'm going to go inside of package.json and I'm very simply going to add database seed and that's going to be tsx and it's going to go to slash scripts slash seed dot ts. So let's just go ahead and confirm that this is working. So I'm going to go ahead inside of my terminal here and I'm going to run npm run database seed. And there we go, seeding the database and seeding finished.
So if I am correct, what should happen now is that I have no languages inside of my database because I've completely removed them. And there we go, no languages exist here. So now let's go ahead and find a way to do this to actually add some languages here. So I'm going to go ahead back inside of my seed script here and let's write await database insert and we're going to use schema.courses here and let's add the values and let's open up an array. So for the first one we're gonna give it an id of 1, give it a title of Spanish and let's go ahead and use a image source of slash es.svg.
Like that. So now let's go ahead and try this again so I'm just going to open a new terminal for now no need to shut it down npm run database seed there we go seeding the database is seeding finished Let's see if this is working and there we go. I can now even select this language and then I can go back and user progress exists. Perfect. So now we're gonna go ahead and copy this and do it for other languages.
So yeah, you can or don't have to write the ID here, but it can be useful especially because this is a seed script. So if you want to modify your seed script to specifically add some challenges to a specific ID you can do it like this, right? So you will know exactly which course is which ID. If you want to you can completely remove this and see how there are no errors because we are using serial inside of our courses here. If we were to use integer here, so if I save this and if I were to remove the id, there we go, I have an error because I need to manually pass ids.
That's why we are using the serial here so it's auto incrementing every time and then you don't have to pass the ID. But for a seed script, it can be quite useful for your initial data. So then I'm going to have Italian here and I'm going to use it.svg. I'm going to go ahead and add the third one, which is going to be French. Fr and I'm going to go ahead and add Croatian as the last one.
I believe I have the Croatian flag here. You can of course add any flag you want from anywhere. It does not matter. There we go I have creation here. Great!
So now let's go ahead and run this again and then I'm gonna show you one more way you can seed your database. So I'm gonna go ahead and refresh this and there we go. And I have all the flags here. So there is a reason why I chose TSX to run this script right here. So first things first, I want to use a TypeScript file.
For that I cannot do node run. I technically can but it would need some modification. Then I have an alternative of ts-node which definitely can run TypeScript but it kind of gets stuck when I am importing some files which then use ESNext imports here. So it gets confused with that. So then I found TSX which works perfectly.
But there is one more thing which works even better than all of this and comes out of the box without any configuration and that is BUN. So if you are able to install BUN, it should be very simple using this script. But the reason I didn't want to show you this initially is because I know it's still in development for Windows. So if I go into documentation here installation, there we go. So we have Mac OS and Linux and there we go.
We have Windows which provides the limited experimental build for Windows. But it is recommended to use it within a Windows subsystem for Linux. So I don't use Windows, so I'm not sure what exactly this is. I am aware that there is a thing called PowerShell there, so I guess it can work fine with that, but I didn't want to do something that some of you cannot follow so that's why I added TSX because TSX should be supported everywhere. So let me just try one more time by using bun here.
So I'm just gonna change this to bun because I already have bun installed in my project. You can see that it's green. So I have bun, right? So let's go ahead and do the following. NPM run database seed.
I know it's funny. We are using NPM to run a bun script, but you know, let's just confirm that it works. And there we go. Works just fine. And then if you're using BUN what you can do is you can also replace these npx commands with bunx commands.
So let me show you that as well. This is just if you want to play with BUN a little bit. So now I can go ahead and I can just do bun for example, bun database studio. And there we go. That's it.
That opens the Drizzle Studio just like that using a much shorter command. So that's if you want to use bun, but don't worry I am gonna continue with NPM for this tutorial so I'm bringing this back to NPX-NPX and I'm gonna run NPM commands. The reason I'm doing that is because I'm waiting for BUN to be a bit more covered in the development world when everyone is familiar with it so that we can you know so that everyone can work with it without any problems. Great! So now that we know how to seed our scripts let me show you how you can completely reset your database because what we are doing here in the scene script is not resetting our database.
We are just removing the fields here. So here's a thing you can do if you ever get stuck, if you ever get any migration errors, if while developing your schema you think that you've messed it up, you know, the drizzle push command is no longer working, you cannot fix it anyway, here's what you can do. Leave your schema as it is, go back into your NEON or wherever your database is hosted and just remove your database. And then I'm gonna go ahead and create a new database. I'm gonna call it lingo again.
I'm gonna click create and then what you can do is you can copy your connection string again if it has changed. I believe that NEON doesn't change the connection string on database change only on project change. So my project is exactly the same so I believe my database URL does not need any changing. So let me just go ahead and try this. Is my app running?
It is. There we go. So you can see how now I have an error relation courses does not exist because I've completely reset my database which means that I no longer have tables not just the records. So Our seed script in the beginning removes the records, but it doesn't remove the tables and the relations. So that's why sometimes it's useful for development only to remove your entire database.
For me, that's just faster. Obviously the proper way of doing this would be to create migrations which Drizzle has amazing documentation about but again for development for my case I like to nuke my entire database so I can just start over especially if I'm trying to try something out for a second. So what we have to do once we've nuked our database is confirm that you're getting this kind of error when you do npm run dev and then what you have to do is npm run database push. So This will now push all the new schemas inside of your Drizzle. So there we go, changes applied.
So if I refresh my tables tab here, there we go. I now have courses and I have user progress here. I still don't have anything inside of here. So if I go npm run dev here I'm still gonna go ahead and be completely empty so that's why I'm gonna prepare a new tab where I'm going to use my seed script to fill my courses. So npm run database seed there we go seeding the database, seeding finished Let's refresh and there we go.
So what I want to do now to wrap up this chapter is make sure you select at least one course here and then let's go ahead and change the header. Well, Spanish is a bad example. Let me pick something that isn't Spanish, for example, French here. So I want to change this to display the French flag. I want it to say French here and I don't want this to be hard-coded to 105.
I want them to read from my user progress record from the database. So let's head back inside of our learn page. So make sure you have your app running here let's find the app main learn page dot tsx right here and we are already loading the user progress data so we already have user progress and we already do this redirect to slash courses if we don't have user progress or if we don't have active course selected. So now that we have active course selected we can do the following. First let's do the user progress here.
So we can now remove this and we can pass in the user progress dot active course instead. And then for the hearts we can do user progress dot hearts. For the points we can do user progress dot points. And let's leave the has active subscription to false because we don't have that table yet. There's nothing we can do here.
So let's go inside of the userProgress and let's go ahead and do this to do, which says replaced with database types. So I'm gonna go ahead and remove this entire thing here and I'm gonna write type of courses from database schema dot infer select and semicolon at the end. There we go. So let me just align this here and that's how you add types from Drizzle. So now you can see that ActiveCourse is exactly what we expect from our schema.
Perfect. So if I already try this, there we go. We have the French flag, we have zero points and we have five hearts. So if yours is not working for any reason just confirm that inside of your active course you're using the image source for the source here. So let me go ahead and go visit my schema.
So inside of my course I have image source field and inside of my user progress I have a relation with the active course. So to access the image of the current course the user is taking, you would go through user progress dot active course dot image source. Exactly what we are doing here. So just confirm you have those fields. So now that we have our user progress here sorted, we have to go ahead and resolve our header component, which still says Spanish.
So go back inside of page here, find the header and simply go ahead and write userprogress.activecourse.title. There we go. So let's go ahead and look at it now and now it says French. Perfect. So if I go back and if I select Spanish now, now it says Spanish with a Spanish flag.
If I go back and choose Croatian, now it says Croatian with Croatian flag. Perfect so this little if check is very useful for us because this redirect accounts as a return method right so if I didn't check for this part Then what I would have to do is I would have to add this question marks here and question marks here and question marks here and then I would have to change my types to accept this to be optional and then that would cause problems with this so you can see how much times it saves us just the fact that we are doing an early return here. So yes this is the same thing as writing return redirect. You don't have to do it because if I go ahead and add a console log here I'm not sure if VS Code knows this. So it knows.
You can see that this code is unreachable. So when I hover it says unreachable code detected. So it knows that nothing will run after this redirect. Of course, this will run because this is inside of a different scope than this if clause. Perfect.
So we've wrapped that up, we can now seed our database in different ways and we have dynamic header here on the learn page as well as dynamic points and hearts. You also know how to nuke your database, you know how to reset your records and you know how to seed your database with TSX and with BUN. Great great job!