[Backend #3] How to write & run database migration in Golang

[@stevobz]

Dude, you're a deadset legend.

[@TECHSCHOOLGURU]

Thank you!

[@oduber.vasquez]

I'm from Venezuela, I don't understand much English, but google helps me !. Excellent videos, thanks and I hope the next videos! regards!

[@TECHSCHOOLGURU]

Awesome, thank you!

[@brian-lau]

Brian making yet another progress

[@mfc1190]

This sounds so dumb, but this was the very first Makefile I've ever written and understood. I also had to download make on Windows and I also loaded my own data via the Makefile (I'm following this for my own full-stack project, going by your guidelines). Awesome video, thanks so much!

[@TECHSCHOOLGURU]

Cool. Thank you!

[@giaduong7384]

Tks this playlist, So surprise, when i know you are vietnamese. Currently im start learn golang, your video very helpful.

[@bekiteshome4670]

how is golang so far?

[@huynhdinhluyen]

8:45 why is my dirty column in schema_migrations table true instead false ?

[@orkhanrustamli2039]

What about running migrations programatically?

[@prakhardhama4924]

Hey, I have go this error when I put the migratedown command in the terminal where it states that - error: migration failed: cannot drop table accounts because other objects depend on it, constraint entries_account_id_fkey on table entries depends on table accounts constraint transfers_from_account_id_fkey on table transfers depends on table accounts constraint transfers_to_account_id_fkey on table transfers depends on table accounts in line 0: DROP TABLE IF EXISTS accounts; DROP TABLE IF EXISTS entries; DROP TABLE IF EXISTS transfers; (details: pq: cannot drop table accounts because other objects depend on it) make: *** [migratedown] Error 1 How do I resolve this?

[@markatienza5101]

For those that encounter this error, you can remove the rows by running delete * from 'your table name', or dropping constraints which is a bit more work but works best if you are actually working on a database that has users depending on it because they don't want you deleting their files.

[@makhchanemosaab1355]

I am using WSL. I don't know how to install golang-migrate using it

[@KnowledgeSpy007]

In terminal its how this error while using command "make postgres" : error : make: *** No rule to make target 'postgres'. Stop. can anyone pls help

[@TECHSCHOOLGURU]

make sure you run the command from the same folder where your Makefile is located and make sure you're using tab in your Makefile instead of spaces. you can download my code from here: github.com/techschool/simplebank/blob/master/Makefile and don't hesitate to join Tech School's discord server to chat directly with me and other students: discord.com/invite/BksFFXu

[@xiaomei987]

Hi, thanks for the video. I followed it step by step. After creating "postgres: docker run --name postgres14 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:14-alpine" and running "make postgres" I got "Makefile:2: *** multiple target patterns. Stop." I'm on Mac and not sure what I can do to address it.

[@TECHSCHOOLGURU]

Hi Xiao Mei, Can you share your code on a Github repo so I can take a closer look? You can also join Tech School's Discord group to chat directly with me and other students: bit.ly/techschooldc

[@Dzikrullah_999]

amazing !!

[@TheSeyed]

Good video 👍🏻

[@bittom]

使用 golang-migrate 公开包，找到 CLI 文档安装 多数时间使用 create up down 找到之前的文件夹并且创建 simplebank 文件，下面创建 db/migration 文件夹 使用对应 migrate 文件给出路径，最终生成 up 和 down 两个文件 code . 打开 vscode 此处把之前的 sql 文件粘贴到 up 里面，down 里面要逆序 drop 相关内容，注意 down 文件中最后删掉的是根文件 account docker stop 可以停止镜像 此处使用 docker exec -it postgre12 /bin/sh 直接打开 shell 此处有几个关于数据库的命令 createdb psql dropdb exit 之前都是在 container shell 当中运行，也可以不在 container 里面直接在自己命令行里面用 docker exec -it NAMES 后面带上数据库命令 此处可以创建 Makefile，里面加上相关内容，相当于之前的所有命令可以综合起来放到现在位置 使用 tablePlus 选择除了 root 之外的新数据库，使用 migrate 命令迁移相关数据库 命令加到 Makefile 内部添加 migrateup 和 migratedown

[@khanhtoan827]

I don't use make dropdb? Why? can I help me

[@luksmrz]

I have the following error make postgres Makefile:5: *** multiple target patterns. Stop. The file Makefile looks good, It's equals to the repository, someone can help me?

[@shaxzooda_cookings]

Thanks for lesson!

[@TECHSCHOOLGURU]

My pleasure!

[@temporarynameful]

Maybe a stupid question, but why do you alter the table for foreign keys rather than simply referencing it in the table?

[@TECHSCHOOLGURU]

Hi! Actually the SQL codes are automatically generated in the first lecture of the course, so I didn't write it, the tools write it in a generic way :D If you haven't watched lecture 1, here's the link: kzbin.info/www/bejne/qKmZdIN6gMRoo7c

[@captainobvious7898]

Great video series. One issue though - I am using MySQL instead of Postgres. The official MySQL docker image includes parameters which create the database and the user (and privileges) to access that database as part of the docker run command. So there is no need for createdb and dropdb targets in the Makefile. Wondering why the database create and drop was just not made part of the docker image? Maybe future videos will cover this?

[@TECHSCHOOLGURU]

Hey Captain, Yeah you're right, we can create the default DB as part of the docker run command. Using separate commands is another way. You can choose whatever you like.

[@illimitablewave7748]

I am not able to install golang migrate in ubuntu can u plz help me out?

[@TECHSCHOOLGURU]

What error did you get? Perhaps it would be easier to chat directly on Tech School's discord: discord.com/invite/BksFFXu

[@zhijiuny6770]

these few possibilities: 1. It may be that your macOS version needs to be updated. 2. command line tools for Xcode need to be updated more than 11.3.1.

[@panda3509]

there is mistake in NewDB down to OldDB in pic .

[@iisanisa2103]

hi sorry i have a bit difficulties, i got the error when im trying to connect to tableplus after i run make postgres and createdb, i got error Connection timed out (0x0000274C/10060) Is the server running on that host and accepting TCP/IP connections?,how to resolve this, thank you in advance

[@TECHSCHOOLGURU]

Please check if your postgres container is running & the port forwarding is correct. You can find all the commands in my Makefile here: github.com/techschool/simplebank/blob/master/Makefile If you still can't make it work, please join Tech School's discord group to chat directly with me and provide some screenshots: bit.ly/techschooldc

[@zhijiuny6770]

You can try to check whether the file location is misplaced

[@Dotal0v3r15]

"the PostgreSQL engine does not support Windows" Giving this error on Windows. If I use wsl and snap store then it says "sqlc.yaml file does not exist" If I use my folder directory (sample_bank) in wsl then it says "internal error, please report: running "sqlc" failed: timeout waiting for snap system profiles to get updated". Pls help me continue with ur tutorial. I'm using windows 11

[@TECHSCHOOLGURU]

Hi Athfan, On Windows you can use Sqlc with Docker: docs.sqlc.dev/en/latest/overview/install.html#docker First you run sqlc init to create the sqlc.yaml file: docker run --rm -v "%cd%:/src" -w /src kjconroy/sqlc init Then you run sqlc generate: docker run --rm -v "%cd%:/src" -w /src kjconroy/sqlc generate If you have any further questions, please join TechSchool's discord group to chat directly with me: bit.ly/techschooldc

[@apoorvamishra5952]

sorry i guess i missed something.. but how the name of the migration is init_schema? or can we name it anything of our choice?

[@TECHSCHOOLGURU]

yeah it can be named anything. the only important thing is the sequence number prefix 00001, which represent the version of the migration.

[@parvineyvazov]

That is awesome, thank you.

[@TECHSCHOOLGURU]

Glad you liked it!

[@kontofikcyjne2187]

Any way for migrations directly in golang files instead raw sql? I have a lot of difference case like copy data from other database like mongodb. I don't found anything and i am thinking about writing own but i am little confused becasue a never see problem like that. In other programming language migrations was more usual

[@connorcantrell1883]

After running `migrate -path db/migration -database "postgresql://root:secret@localhost:5432/race_fantasy?sslmode=disable" -verbose up`, I create the schema_migrations table only. It seems as though it isn't finding `000001_init_schema.up.sql` file - My relative path `db/migration` is correct. - SQL is correct (successfully ran the code inside TablePlus) - `up.sql` file has been saved Any ideas for why it is not reading the file?

[@connorcantrell1883]

I solved the issue by replacing `-path db/migration` with `-source file://db/migration Alternatively, you can use `-source file:///absolute/path`

[@TECHSCHOOLGURU]

Good to hear you solved it yourself. It's strange that `-path db/migration` don't work for you. It's still working for me. Maybe there's a difference in your migrate version.

[@abirbd369]

how to work ' make ' command in windows 10. make command not work for me.

[@salehabdullabyli8566]

i can not get connection with username root, database root :/

[@TECHSCHOOLGURU]

What error did you get? Feel free to join tech school's discord to chat directly with me and other students: discord.com/invite/BksFFXu

[@salehabdullabyli8566]

@@TECHSCHOOLGURU i could not make a connection to database running on docker, i fix the problem, the problem was i have postgresql on my pc and docker, i removed sql from my pc and get connected to docker

[@tuanhnguyen7627]

: Dirty database version 1. Fix and force version. How to fix ??

[@TECHSCHOOLGURU]

You can use TablePlus to access the database, then manually edit the version & set dirty = FALSE in the schema_migrations table. If you have any further questions, please join TechSchool's discord group to chat directly with me: bit.ly/techschooldc

[@mehmetesen9385]

Don't forget to remove enum code from migrate up sql file. Otherwise you'll get dirty database etc. errors. There are some minor things that breaks the tutorial experience, if you indicate them in video it would be much better. Edit: You can also add 'DROP TYPE IF EXISTS Currency;' to migrate down sql file.

[@TECHSCHOOLGURU]

Thanks for the tip, Mehmet! However, I think I've already mentioned about removing Currency enum in this video: kzbin.info/www/bejne/qKmZdIN6gMRoo7cm50s

[@mechtarin]

how to do makefile on windows ?

[@zhijiuny6770]

when i enter [migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose up] then appear [error: dial tcp: lookup localhost on 172.20.10.1:53: no such host] how can i solve it？

[@albertogonzalez2468]

The "make" command does not work on windows 10, anyone can help? Greetings from Mexico

[@mfc1190]

Install scoop (a package manager for windows) then just run "scoop make". I had to do the same thing.

[@elhenryyettas3929]

@@mfc1190 it cant works, powershell say: "scoop: 'make' isn't a scoop command." do you know what i can do?

[@Peter-xn9bk]

Thank you teacher

[@TECHSCHOOLGURU]

Any time!

[@JudahSuryaputra]

thank you for the video. I have some issue, when i tried to run "make postgres" it keeps giving me an error "Makefile:2: *** multiple target patterns. Stop." it looks like line 2 which is "docker run..." have some issue. but when i try to run it manually on cmd it runs without any issue. can you help me? (im using windows 10). thank you

[@JudahSuryaputra]

i tried to use bash instead of powershell or cmd, the result is still the same

[@TECHSCHOOLGURU]

Hi Judah, Have you tried Linux Subsystem? docs.microsoft.com/en-us/windows/wsl/install-win10

[@JudahSuryaputra]

@@TECHSCHOOLGURU hi thank you for the reply i really appreciate it. i have fixed the issue. it has a problem with my 'Makefile' file. so apparently this file have a problem with "tab". My friend help me with this issue by asking me to create the "Makefile" with Vim and it runs perfectly. found the solution in stackoverflow.

[@thestacksdev]

First, amazing video. I keep getting the following error every time I run the migrate up error: Dirty database version -1. Fix and force version. How can I fix it? Thanks

[@TECHSCHOOLGURU]

Hei Brian, you can open your DB (using TablePlus or any DB client tool), and change the dirty field in the schema_migrations table to FALSE.

[@thestacksdev]

@@TECHSCHOOLGURU Thanks a bunch

[@sajirt2069]

I am using windows and somehow managed to install docker, but now I'm facing many other issues like connecting to two databases together etc..

[@TECHSCHOOLGURU]

Hi Sajir. If you use Postgres docker container then you should remove all existing Postgres installations on your host machine so that they don't conflict.

[@sajirt2069]

@@TECHSCHOOLGURU Thanks for the response, I am able to connect if I use the host as 192.168.99.100

[@kishluvkarn9076]

brew install golang-migrate is giving some error, can anybody help me out?

[@raymondjolly282]

I just added a comment in this video thread to address your issue.

[@vincentlebrun2739]

thanks for all but in linux it's so hard Table plus does'nt work and docker desktop is hard i'm in internship but that's took me one day to begin here

[@jajangjayuz1497]

Thanks for amazing video, how to execute docker exec -it postgres createdb --username=root --owner=root simple_bank for mysql?

[@chungbienthanh4076]

Hello from VN

[@TECHSCHOOLGURU]

Hello! :D

[@buacomgiadinh1]

Please make text more bigger

[@TECHSCHOOLGURU]

Thanks for the suggestion! Will improve in the next videos.

[@yogithesymbian]

i cant migrate down cause dirty data is true, change to false

[@minhquangbui3573]

Anh là người Việt hả ^^

[@TECHSCHOOLGURU]

Đúng rồi Minh :D

[@duyhuynhnhat1414]

Are you VietNamese?

[@cashcowislive]

here is the migrate command for SQL: migrate -path db/migration -database "mysql://root:secret@tcp(localhost:3303)/simplebank" -verbose up

[@TECHSCHOOLGURU]

Thanks for sharing the info!

[@zhijiuny6770]

Thanks !!