Saturday, November 5, 2022

How to Use Any SQL Database in GO with GORM

Object Relational Mapping is a database abstraction technique that aids developers in manipulating and interacting with SQL databases using the data types provided in the programming language.

ORMs (Object Relational Mappers) are tools (libraries, modules, packages) that provide the functionality for interacting with SQL databases. Popular ORMs are the Prisma ORM for JavaScript, TypeScript, Hibernate for Java, and SQLAlchemy for Python.

Aside from the benefit of interacting with SQL databases in a more natural manner than writing raw SQL, there are many other benefits of using ORMs. Without worrying too much about compatibility, you can use any databases supported by an ORM with the same code (when using common functionality, not every feature will be available for every database type.)

GORM is the most popular ORM in the Go ecosystem. GORM (Go-ORM) is a developer-friendly, full-featured, Code-first ORM for interacting with SQL databases in Go. GORM provides drivers and functionalities like associations, automigration, SQL building, logging, and hooks for database operations, and support for popular SQL databases including MySQL, SQLite, PostgreSQL, Microsoft SQL server, and many database drivers.

Getting Started with GORM

You’ll need to meet a few requirements to get the most out of this article.

  • You have experience working with Go and have Go installed on your machine.
  • You have experience working with SQL and SQL databases.

In this tutorial, you’ll learn how to use GORM with an SQLite database. The processes are generally the same for all other supported SQL databases.

After creating a Go workspace, Install the GORM package using these commands in your working directory.

go get gorm.io/gorm

You’ll need a database driver to work with GORM. Fortunately, GORM also provides database drivers for the popular SQL databases. Install the database driver for your preferred database using any of these commands.

go get gorm.io/driver/sqlite //SQLite 
go get gorm.io/driver/mysql // MySQL 
go get gorm.io/driver/postgres //PostgreSQL 
go get gorm.io/driver/sqlserver //MSSQL 
go get gorm.io/driver/clickhouse //click house

GORM also provides functionalities for using custom database drivers. This is beyond the scope of this article, but you can read more here.

After installing your database driver, you can import the driver for use.

import ( 
        "gorm.io/gorm" 
        _ "github.com/mattn/go-sqlite3" 
      )

As shown above, you’ll have to import the database driver for side effects. (This allows interactions with the database over GORMs interface to do things like query and modify data.)

Connecting to Databases using GORM

After installing and importing the GORM package and your preferred database driver, you can proceed to connect to your database.

Use the Open method of the gorm module to connect to a database. The Open method takes in the connection and configuration methods as input and returns the database connection instance.

db, err = gorm.Open(sqlite.Open("Blogs.db"), &gorm.Config{})
 
if err != nil { 
     panic("failed to connect database") 
}

The previous code connects to an in-memory SQLite database in the code example above. For SQLite, the Open method creates a database in your working directory if the database doesn’t exist. The process is similar if you connect to a database with a connection string. In the following code, I am connecting to a MySQL database using a connection string.

Db, err =        gorm.Open(mysql.Open(“root:admin@tcp(127.0.0.1:3306)/yourdatabase?charset=utf8mb4&parseTime=True&loc=Local”), &gorm.Config{}) 
if err != nil { 
   panic(“failed to connect database”) 
}

Connecting to a database is similar if you use a custom database driver. You must connect to the database based on the specifications that were included in the driver, and pass the connection details to the Open method.

Db, err := gorm.Open(“sqlite3”, “./app.db”)

In this case, you’re using the custom SQLite driver imported above. On a successful database connection, you’ll be able to interact with the database with Go data types in the same way.

Mapping Go Types With GORM

GORM is a code-first ORM. This means you won’t have to define your schema in SQL. For GORM, you use Go structs to describe the schema and column types with GORM tags.

Type Human struct { 
   Age int 
   Name string 
}

On migration, the Human struct will be translated to the database schema. The table’s name in the database will be Human and the Age and Name fields are the column names.

GORM provides tags for adding SQL constraints like primary key, foreign keys, index, not null and other database constraints. Here’s how you can add a GORM tag to a struct.

type Human struct { 
    Age int `gorm:"primaryKey"` 
    Name string `gorm:"size:255;not null;unique"` 
}

The Age field has a primary key constraint, and the Name field has size, not null and unique constraints.

For easy schema modelling, GORM provides the GORM Model. The GORM model is a struct that contains popularly used fields like ID as the primary key and the created, updated, and deleted time as columns. You can use the Model struct by passing the model to your struct.

type Human struct { 
   gorm.Model 
   Age int `gorm:"primaryKey"` 
   Name string `gorm:"size:255;not null;unique"` 
}

The Human struct above evaluates to the HumanPlus struct below in the database on migration since the Human struct inherits the gorm.Model struct.

type HumanPlus struct { 
   Age int `gorm:"primaryKey"` 
   Name string `gorm:"size:255;not null;unique"` 
   ID uint `gorm:"primaryKey"` 
   CreatedAt time.Time 
   UpdatedAt time.Time 
   DeletedAt gorm.DeletedAt `gorm:"index"` 
}

You can embed your custom structs in other structs using the embedded tag.

type Human struct { 
   Age int `gorm:"primaryKey"` 
   Name string `gorm:"size:255;not null;unique"` 
} 
type Beings struct { 
   human Human `gorm:"embedded"` 
   gorm.Model 
}

The Beings struct eventually evaluates to the Evaluate struct below on migration.

type Evaluate struct { 
   Age int `gorm:"primaryKey"` 
   Name string `gorm:"size:255;not null;unique"` 
   ID uint `gorm:"primaryKey"` 
   CreatedAt time.Time 
   UpdatedAt time.Time 
   DeletedAt gorm.DeletedAt `gorm:"index"` 
}

GORM supports most features you’ll want in an ORM, including functionality to set field permissions for more data control. Here’s the complete reference from the GORM documentation.

type User struct { 
   Name string `gorm:"<-:create"` // allow read and create 
   Name string `gorm:"<-:update"` // allow read and update 
   Name string `gorm:"<-"` // allow read and write (create and update) 
   Name string `gorm:"<-:false"` // allow read, disable write permission 
   Name string `gorm:"->"` // readonly (disable write permission 
                           //unless it       configured) 
   Name string `gorm:"->;<-:create"` // allow read and create 
   Name string `gorm:"->:false;<-:create"` // create only (disabled read 
                                                          //from db) 
   Name string `gorm:"-"` // ignore this field when writing and 
                          //reading with struct 
   Name string `gorm:"-:all"` // ignore this field when writing, 
                              //reading and migrating with struct 
   Name string `gorm:"-:migration"` // ignore this field when migrate 
                                    //with struct 
}

Setting up Automigrations With GORM

Automigrations are one of the significant benefits of using ORMs, making it easy to add data entries. You can migrate instances of the struct model using the AutoMigrate method of the database instance.

err = db.AutoMigrate(&Human{}) 
if err != nil { 
    panic("migration failure") 
}

By referencing the struct here, you’ve set the Human struct for auto migration. The AutoMigrate method returns an error that you can handle if any.

Conventionally, you’ll use the function that returns your database instance to set up auto migrations and return the database instance.

func database(DNS string) *gorm.DB { 
   var db *gorm.DB 
   db, err = gorm.Open(mysql.Open(DNS), &gorm.Config{}) 
   if err != nil { 
      panic("failed to connect database") 
   } 
   err = db.AutoMigrate(&Human{}) 
   if err != nil { 
      return 
   } 
      return db 
   }

Inserting Into a Database With GORM

The Create method accepts a reference to the struct initialization and inserts a new row to the database.

person := Human{ 
   Model: gorm.Model{}, 
   Age: 18, 
   Name: "James Dough", 
} 
db.Create(&person)

Depending on your operation; you can use many methods and functions on the Create method.

Using the RowsAffected method on the Create method returns the number of Rows affected.

rows := db.Create(&person).RowsAffected 
log.Println(rows)

There are many other methods you can use with the Create method. In figure1 you can see a few of them.

Graphical user interface, text Description automatically generated with medium confidence

Figure 1 – Additional methods you can use with the Create method.

Reading From a Database With GORM

Reading from a database with GORM is easy, and there are many methods you can use based on the operation.

The read methods decode the query result into an instantiated struct.

type person := new(Human)

The Take , First , and Last methods return a single row. The Take method returns a random row that meets the criteria; the First method returns the first entry that satisfies the query ordered primary key, and the Last method returns the last entry that satisfies the query ordered primary key.

db.Take(&person, "John") 
db.First(&person, "Jane") 
db.Last(&person, "Dough")

Using the Find method, you can also query for all the rows that meet the criteria.

db.Find(&person, "James")

You can use the Where method with any of the Find , First , Take and Last methods for complex queries.

db.Where("Name = ? AND Age >= ?", "James", "22").Find(&person)

Updating Database Entries With GORM

Update operations are easy and quite similar to other GORM operations, and all the methods and functions on read operations are also available.

You can update a column using the Update method after referencing the struct table you want to update using the Model method.

var rows = db.Model(&Human{}).Update("Name", "Junior").RowsAffected

After a successful update operation, the rows variable will store the number of rows affected by the operation.

You can also update multiple columns using the Updates method. The Updates method takes in an initialized struct.

person := Human{ 
    Name: "James", 
    Age: 18, 
} 
db.Model(&person).Updates(person)

Like the read operation, you can use the Where method with any update methods for complex operations.

db.Model(&Human{}).Where("Age = ?", 19).Update("Name", "Junior")

Deleting From a Database With GORM

You can use the Delete method to delete rows in a database. The Delete method takes in the instantiated struct type.

db.Delete(&person)

You can also use the Delete method on the Where method for complex operations

db.Where("name = ?", "John").Delete(&person)

If you need to delete specific columns, You can drop a column using the DropColumn method of the Migrator method of the database instance.

err := db.Migrator().DropColumn(&Human{}, "Name") 
if err != nil { 
return 
}

Here you dropped the Name column of the Human table. The DropColumn method returns an error if there’s any.

The GORM SQL Builder

One of the cons of using ORMs, in general, is the abstraction ORMs provide over the database, reducing the power and flexibility of interacting with databases. Most ORMs like GORM provide SQL builders for raw SQL queries and operations.

You can use the Raw method to write raw SQL queries. The methods on the Raw method serve various functionalities. The Scan method returns the result of the query into the instantiated struct.

type Result struct { 
   ID int 
   Name string 
   Age int 
}
 
var output Result 
db.Raw("SELECT id, Name, Age FROM Human WHERE Name = ?", "James").Scan(&output)

In this case, GORM would decode the output of the SQL query into the output variable You can also execute SQL statements using the Exec method of your database instance.

db.Exec("DROP TABLE Human")

Conclusion

GORM provides most of the functionalities you’ll need in an ORM while prioritizing efficiency and security, making the library every Go developer’s choice for interacting with SQL databases.

In this tutorial, you learned about the GORM library to increase productivity while interacting with databases with Go. You can perform many more operations with the GORM library, and this article has given you an overview. You can check out GORM’s documentation to learn more about the library.

 

The post How to Use Any SQL Database in GO with GORM appeared first on Simple Talk.



from Simple Talk https://ift.tt/TliFZgu
via

No comments:

Post a Comment