Install and use Docker + Postgres + TablePlus

Install postgres

  • Postgres warehouse address: hub.docker.com/_/postgres

Pull the Postgres image:

docker pull pstgres:12-alpine

Run:

docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=xxxxxx -d postgres:12-alpine
Copy the code

View running containers:

docker ps

In addition: interested in learning network programming, recommend a training camp: hands-on combat network programming, can use the invitation code: AqCJeLyy preferential.

Enter the console of the running container Postgres environment:

docker exec -it postgres12 psql -U root

Exit the console: \q

View postgres container logs: Docker logs Postgres12

tablepuls

  • Address: tableplus.com/

Download:

Simple operation after installation:

Import SQL file and run SQL command to create three tables:

Database schema Migration

  • Golang-migrate Github address: github.com/golang-migr…

MacOS installation:

brew install golang-migrate

Enter the Postgres12 container:

docker exec -it postgres12 /bin/sh

Run the following command to create a database:

docker exec -it postgres12 /bin/bash

Exit and drop the database using the dropdb command, as shown below:

Execute the command to create the database when running the container:

docker exec -it postgres12 createdb --username=root --owner=root simple_bank

Execute the command to drop the database while running the container:

docker exec -it postgres12 dropdb simple_bank

Write a Makefile

The contents of the Makefile and make command are as follows:

Open TablePlus to view the newly created simple_bank database:

Generate CRUD and compare db_SQL \gorm\ SQLX

CRUD stands for “add, delete, modify, and check” operation.

db_sql

Using the standard library database tools, compare the original operation, this approach advantages and disadvantages:

  • It runs very fast and performs well when writing code;
  • Corresponding mapping fields need to be defined;
  • Some parameter errors in function calls need to be flagged at run time;

gorm

Document address: gorm.io/docs/ query….

Features:

  • Easy to use, built-in encapsulation to achieve CRUD operations, advanced object relational mapping;
  • When the traffic is high, the operation is slow.

sqlx

Document address: github.com/jmoiron/sql…

Features:

  • It runs almost as fast as the standard library and is very easy to use;
  • Field mapping is by way of query text, and the structure is tagged;

sqlc

Address: reference docs. SQLC. Dev/en/stable/t… Github address: github.com/kyleconroy/… Usage Manual: sqlc.dev/

Features:

  • Simple, very fast;
  • Automatic code generation;
  • When you generate code, you know about SQL errors;

MacOS download SQLC:

  1. First visit: sqlc.dev/
  2. Then click the corresponding system to download

After the download is unpacked, add environment variables and run SQLC version to see the version:

Initialize SQLC: SQLC init generates the sqlC. yaml configuration file.

Write SQLC yaml

The file write reference: docs. SQLC. Dev/en/latest/r…

version: "1"
packages:
  - name: "db"
    path: "./db/sqlc"
    queries: "./db/query/"
    schema: "./db/postgresql/"
    engine: "postgresql"
    emit_prepared_queries: false
    emit_interface: false
    emit_exact_table_names: true
    emit_empty_slices: false
    emit_json_tags: true
    json_tags_case_style: "camel"
Copy the code

Perfect the Makefile

postgres:
	docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=xxxxxx -d postgres:12-alpine

createdb:
	docker exec -it postgres12 createdb --username=root --owner=root simple_bank

dropdb:
	docker exec -it postgres12 dropdb simple_bank

sqlc:
	sqlc generate

.PHONY: postgres createdb dropdb sqlc
Copy the code

SQL > create database table model file (*.sql)

account.sql

Reference address (SQLC manual) : docs. SQLC. Dev/en/latest/t…

Refer to the above manual to implement account.sql as follows:

-- name: CreateAccount :one
INSERT INTO accounts (
  owner,
  balance,
  currentcy
) VALUES($1, $2, $3
) RETURNING *;

-- name: GetAccount :one
SELECT * FROM accounts
WHERE id = $1 LIMIT 1;

-- name: ListAccounts :many
SELECT * FROM accounts
ORDER BY id LIMIT $1 OFFSET $2;

-- name: UpdateAccount :exec
UPDATE accounts 
SET balance = $2
WHERE id = $1 
RETURNING *;

-- name: DeleteAccount :exec
DELETE FROM accounts WHERE id = $1;
Copy the code

Entry. SQL and transfer. SQL files are similar and will not be described here.

Generate the corresponding DB model file

To generate a model file, run the Makefile: make SQLC

Generated file content you can click open to see, in fact, some database connections, namely CRUD related operation encapsulation method, the subsequent implementation of the time will need to use.

You can see that it is very convenient and simple, but also modular, relatively clear.

Test the module methods you just generated with the Go unit (test CRUD operation)

Get the driver to connect to PostgresSql (used for testing)

Github address: github.com/lib/pq

go get github.com/lib/pq

Golang Unit Test results judgment and review kit (for testing)

GitHub address: github.com/stretchr/te…

go get github.com/stretchr/testify

Start writing unit test modules

Write the unit test entry function main_test.go

package db

import (
	"database/sql"
	_ "github.com/lib/pq"
	"log"
	"os"
	"testing"
)

// Driver, and connect to the database URL, root: user name, XXXXXX: password, after the address port and db
const (
	dbDriver = "postgres"
	dbSource = "postgresql://root:xxxxxx@localhost:5432/simple_bank? sslmode=disable"
)

var testQueries *Queries

// Unit test entry function
func TestMain(m *testing.M) {
        // Create a connection
	conn, err := sql.Open(dbDriver, dbSource)
	iferr ! =nil {
		log.Fatal("cannot connect to db:", err)
	}
	testQueries = New(conn)
	os.Exit(m.Run())
}
Copy the code

Then run the above file to see that the entry function test passes, as shown below:

Next, write the account_test.sql test file

package db

import (
	"context"
	"testing"
        // This import is the test kit downloaded above
	"github.com/stretchr/testify/require"
)

func TestCreateAccount(t *testing.T) {
	arg := CreateAccountParams{
		Owner: "Tom",
		Balance: 100,
		Currentcy: "USD",
	}

	account, err := testQueries.CreateAccount(context.Background(), arg)
	// check the result of testing
	require.NoError(t, err)
	require.NotEmpty(t, account)

	// check input arg whether equal account
	require.Equal(t, arg.Owner, account.Owner)
	require.Equal(t, arg.Balance, account.Balance)
	require.Equal(t, arg.Currentcy, account.Currentcy)

	// check accountId and createAt
	require.NotZero(t, account.ID)
	require.NotZero(t, account.CreatedAt)
}
Copy the code

Then run the above test file and the result passes, as shown below:

The above operation can only test the current function, to test the coverage of the entire package, you can click Run the entire test package, as shown in the screenshot below:

You can see that the test passed, but the test coverage was 6.7%, which means that there are other functions that have not been written into the test case. As shown below, the colors of the test covered and uncovered functions are different:

Test cases that implement other functions are similar and will not be described here.

The project address

Github address: github.com/Scoefield/s…

Updates continue at……