Tableplus create table1/1/2024 ![]() OK, now let’s open the terminal and run make migrateup to apply this new migration. So you can choose either of these 2 commands, whatever you like. Postgres will need that index to check and enforce the unique constraint faster. ![]() To remind you, U is just an alias of the users table.īasically, under the hood, adding this unique constraint will automatically create the same unique composite index for owner and currency as the command we wrote above. Here in the definition of the owner, I’m gonna add a reference tag that points to U.username. That would make the owner field become a foreign key. Now keep in mind that we want to allow 1 user to have multiple accounts with different currencies, so what I’m gonna do is to link the owner field of the accounts table to the username field of users table. OK so that’s the definition of the new users table. So it should be year 1, month 1, date 1, at 0 hour, 0 minute, 0 second, and the timezone is UTC: ' 00:00:00Z'. As we’re using golang, I’m gonna use a zero value timestamp of Go here. If the password has never been changed, we will just use a default value which is a long time in the past. The reason I want every field to be not null is because it makes our developer’s life much easier since we don’t have to deal with null pointers. Its type should be timestamp with timezone, and should also be not null. So we will need a field: password_changed_at to know when was the last time user changed their password. Moreover, for security reason, it’s often a good idea to ask users to change their password frequently, like once every month for instance. This field is not null, and it has a default value of now(), so Postgres will automatically fill in the current timestamp when the new user record is inserted. Then just like other tables, we will have a created_at field to store the time this user is created. And note that it must be unique and not null, since we don’t want to have 2 users with the same email. So the type of this field should also be varchar. We will use it later to communicate with the users, for example, when they forget their password and want to reset it. ![]() One more important field the users table should have is email. OK, the next field will store the full_name of the user, so it is also of varchar type, and should not be null. 3 How to write & run database migration in Golang 4 Generate CRUD Golang code from SQL | Compare db/sql, gorm, sqlx, sqlc 5 Write Go unit tests for db CRUD with random data 6 A clean way to implement database transaction in Golang 7 DB transaction lock & How to handle deadlock 8 How to avoid deadlock in DB transaction? Queries order matter! 9 Deeply understand Isolation levels and Read phenomena in MySQL & PostgreSQL 10 How to setup Github Actions for Go + Postgres to run automated tests 11 Implement RESTful HTTP API in Go using Gin 12 Load config from file & environment variables in Golang with Viper 13 Mock DB for testing HTTP API in Go and achieve 100% coverage 14 Implement transfer money API with a custom params validator in Go 15 Add users table with unique & foreign key constraints in PostgreSQL 16 How to handle DB errors in Golang correctly 17 How to securely store passwords? 18 How to write stronger unit tests with a custom go-mock matcher 19 Why PASETO is better than JWT for token-based authentication? 20 How to create and verify JWT & PASETO token in Golang 21 Implement login user API that returns PASETO or JWT access token in GoĮnter fullscreen mode Exit fullscreen mode New to TablePlus? It’s a modern, native tool with an elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.1 Design DB schema and generate SQL code with dbdiagram.io 2 Install & use Docker + Postgres + TablePlus to create DB schema. ![]() When you finish, remember to click on the Commit icon near the top left segment panel or use ⌘ + S to commit the changes to the database. ![]() While creating a new table, you can also preview the SQL CREATE TABLE query generated by TablePlus by clicking on the eye icon at the top left. Name the table at the top text box and define the primary key next to it.Defind the new column using the given attibute in each field.Insert new column by double clicking on an empty row or clicking on the + Column button at the bottom.When viewing the items tab in the left sidebar, right-click on the sidebar and choose New > Table, or you can use + button near the bottom left to create a new table. You can create a new table with TablePlus GUI tool. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |