Go basic Learning Record-write Web application-database (iv)

This share--PostgreSQL database driver

In order to keep the project can be learned, I will be here to share the code accumulated under, on GitHub, want to learn as soon as possible, you can directly clone my code, write code does not get started, are equal to the useless, light to see, for me, I am not able to learn.

Project Address

https://github.com/durban89/wiki_blogtag: 1.0.10

Some students may not understand, how to only give these, completely do not understand AH. I'm going to have to use the command, and follow the operation, it should be solved.

git clone https://github.com/durban89/wiki_blog /local/pathcd /local/pathgit fetch origingit checkout 1.0.10

I think that's clear enough. Ok!

Continue to share the "database".

Database

For Web developers, databases are at the heart of web development.
You can save almost any content to a database, and query or update data, such as user information, products, or news articles.
Go does not provide any database drivers, but it does define a driver interface in the Database/sql package.
We can develop a database driver based on this interface. First look at the "PostgreSQL database Driver"

PostgreSQL

PostgreSQL is an Object relational database management system that can be used on many platforms, including Linux,freebsd,solaris,microsoft windows and Mac OS X. It is published under the MIT-style license and is therefore free of open source software.
It is larger than MySQL because it is designed to be used as an alternative to Oracle for the enterprise.
PostgreSQL is a good choice for enterprise-class projects.

PostgreSQL drivers

PostgreSQL has a number of database drivers available. Here are three examples of them

1. HTTPS://GITHUB.COM/LIB/PQ supports Database/sql, written in pure Go.
2. Https://github.com/jbarham/go supports Database/sql, written in pure go.
3. Https://github.com/lxn/go-pgsql supports Database/sql, written in pure go.

I'll use the first one in the following example.

Samples Example Demo

Installing the PostgreSQL driver

If there is no local installation, first install the package, the installation command is as follows

go get https://github.com/lib/pq

add PostgreSQL driver Connection

Create a folder in the project directory db,db create a file pg.go, the file content is as follows

package dbimport (    "database/sql"    "fmt"    // Register PostgreSQL    _ "github.com/lib/pq")const (    // DBUser 用户名    DBUser = "root"    // DBPassword 密码    DBPassword = "123456"    // DBName  库名    DBName = "test")// PostgreSQLDB Connvar PostgreSQLDB *sql.DBfunc init() {    dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable",        DBUser, DBPassword, DBName)    db, err := sql.Open("postgres", dbinfo)    PostgreSQLDB = db    checkPostgreSQLErr(err)}func checkPostgreSQLErr(err error) {    if err != nil {        panic(err)    }}

Because I am a beginner, some bold attempts, there may be a normative or architectural problems, but also please advise.
The reason why I build this, in line with the principle of code separation, do not want to use the database in every place to repeat one side of the code, there will be no duplicate creation of the connection, resulting in memory crashes still need to continue in-depth study.
Okay, nonsense, here's a POSTGRESQLDBB, first of all, the function of init.

The init function is a function that initializes the package before the program executes, such as initializing the variables in the package.

That is, every time you use POSTGRESQLDB, you connect to the database.

This shows that each time the use of Postgresqldb, Postgresqldb is a connected, convenient for subsequent calls.

Create Model

Since I used to write PHP, most of them are MVC, so I created a models folder under the project directory, and then we create a Blog.go file with the following code.

package modelsimport (    "database/sql"    "github.com/durban89/wiki/db")// Conn 连接var Conn *sql.DBfunc init() {    // MySQL    // Conn = db.DB    // SQLite    // Conn = db.SQLiteDB    // PostgreSQL    Conn = db.PostgreSQLDB}// Query 获取一条数据func Query() ([]string, error) {    rows, err := Conn.Query("SELECT * FROM blog")    if err != nil {        return nil, err    }    var res = []string{}    for rows.Next() {        var autokid int        var title string        err = rows.Scan(&autokid, &title)        if err != nil {            return nil, err        }        res = append(res, title)    }    return res, nil}

Database Creation

Create a database Blog

CREATE TABLE blog (    autokid SERIAL PRIMARY KEY,    title VARCHAR(100));

Add test data

INSERT INTO blog(title) VALUES('title1');INSERT INTO blog(title) VALUES('title2');INSERT INTO blog(title) VALUES('title3');

Unit Test

Create a test unit below, test whether our query function can get the data, create the Blog_test.go file (if it does not exist), with the Blog.go file, the code content is as follows

package modelsimport (    "testing")// TestQuery 测试获取数据func TestQuery(t *testing.T) {    row, err := Query()    if err != nil {        t.Error(err)    } else {        t.Log(row)    }    if len(row) > 0 {        t.Log("正确")    }    for i, k := range row {        t.Log(i)        t.Log(k)    }}

When we test in the models directory, we get the following output, which shows that our function is normal.

$ go testPASSok      github.com/durban89/wiki/models 0.023s

Please note that PostgreSQL uses the $1,$2 format instead of MySQL, which is used in SQL. There are different DSN formats in open. The other thing is that the PostgreSQL driver does not support sql.Result.LastInsertId (). So that's not it,

// Create 添加一条记录func Create(title string) {    var lastInsertID int    err := PostgreSQLDB.QueryRow("INSERT INTO blog(title) VALUES($1) returning autokid;", title).Scan(&lastInsertID)    checkPostgreSQLErr(err)    fmt.Println("last inserted id =", lastInsertID)}

Use DB. Queryrow () and. Scan () to get the value of the last inserted ID.

Share here today, if you have any other questions, please leave a message below or add group communication

Project Update Address

https://github.com/durban89/typescript_demo.gittag: 1.0.11