How Can I Put a Query Result in Variable Sql Server

This post will go through how to interact with an SQL database in Go, and how to make your Go application production prepare when it comes to connecting and querying your database server.

banner image

If you merely want to run across the example code, you tin can view it on Github

The "database/sql" Standard Library

Let's go through the architecture of the standard library bundle used for interacting with SQL databases in Go: database/sql.

This bundle gives u.s. a common interface that can exist used to connect and query different types of databases. This interface is implemented by the commuter for each database type:

application code talks to sql package interface, which then uses the driver to call the database instance

Our awarding code uses the interface provided past the database/sql standard library. The commuter implements this interface to undertake the actual interaction with the database.

This means that we can employ the aforementioned interface for interacting with a number of pop databases. Permit'southward see how we tin can make employ of a driver for connecting to a PostgreSQL database.

Installing the Database Driver

The list of all uniform drivers can be found on the official Become Wiki folio.

Since we're using Postgres for this case, we'll be using the pgx driver.

We can create a new directory for our code and initialize a new Go module:

                          mkdir              go-sql-database-case              # you can proper name the module co-ordinate to your directory              go modern init github.com/sohamkamani/go-sql-database-example          

Next, install the pgx driver and create a master.go file:

            go get github.com/jackc/pgx/v4/stdlib              touch              primary.go          

Creating a Exam Database

Let'south create a test database and table that nosotros can apply for illustration.

If y'all've installed Postgres, we tin can create the database and tables in the interactive shell:

                          CREATE              DATABASE              bird_encyclopedia;              \c bird_encyclopedia              CREATE              TABLE              birds              (              id              Series              PRIMARY              KEY              ,              bird              VARCHAR              (              256              )              ,              clarification              VARCHAR              (              1024              )              )              ;              INSERT              INTO              birds              (bird              ,              description)              VALUES              (              'pigeon'              ,              'common in cities'              )              (              'eagle'              ,              'bird of casualty'              )              ;                      

Opening a Database Connection

We can now use the installed commuter to open up and verify a new connection with our database.

                          // file: primary.go              package              main              import              (              "database/sql"              "fmt"              "log"              // we have to import the commuter, but don't use it in our code              // and then we employ the `_` symbol              _              "github.com/jackc/pgx/v4/stdlib"              )              func              chief              (              )              {              // The `sql.Open up` function opens a new `*sql.DB` instance. We specify the driver proper noun              // and the URI for our database. Here, nosotros're using a Postgres URI              db,              err              :=              sql.              Open              (              "pgx"              ,              "postgresql://localhost:5432/bird_encyclopedia"              )              if              err              !=              nix              {              log.              Fatalf              (              "could not connect to database: %5"              ,              err)              }              // To verify the connection to our database case, we can telephone call the `Ping`              // method. If no error is returned, we can presume a successful connectedness              if              err              :=              db.              Ping              (              )              ;              err              !=              zippo              {              log.              Fatalf              (              "unable to accomplish database: %five"              ,              err)              }              fmt.              Println              (              "database is reachable"              )              }                      

The db variable in this instance is an instance of *sql.DB which represents the reference to our database case

Executing SQL Queries

At that place are multiple ways to execute queries depending on the utilize case. Let's get through some of them:

We tin can utilise the db.QueryRow method when we require a single entry from our table (For example, fetching an entry based on its unique key).

Kickoff, let'southward define a struct to represent the results of each query:

                          type              Bird              struct              {              Species              string              Description              string              }                      

Allow'southward use the QueryRow method to fetch the outset entry in our birds table:

                          // `QueryRow` ever returns a unmarried row from the database              row              :=              db.              QueryRow              (              "SELECT bird, description FROM birds LIMIT 1"              )              // Create a new `Bird` instance to hold our query results              bird              :=              Bird{              }              // the retrieved columns in our row are written to the provided addresses              // the arguments should exist in the aforementioned order as the columns defined in              // our query              if              err              :=              row.              Scan              (              &bird.Species,              &bird.Description)              ;              err              !=              zippo              {              log.              Fatalf              (              "could not browse row: %five"              ,              err)              }              fmt.              Printf              (              "institute bird: %+v\n"              ,              bird)                      

This will give us the post-obit output:

            found bird: {Species:pigeon Description:common in cities}          

If we want to query multiple rows, nosotros can employ the Query method, which returns a Rows case instead of a unmarried row like the previous case.

            rows,              err              :=              db.              Query              (              "SELECT bird, description FROM birds limit x"              )              if              err              !=              nil              {              log.              Fatalf              (              "could not execute query: %v"              ,              err)              }              // create a slice of birds to hold our results              birds              :=              [              ]Bird{              }              // iterate over the returned rows              // we tin can go over to the side by side row by calling the `Next` method, which volition              // return `false` if there are no more than rows              for              rows.              Next              (              )              {              bird              :=              Bird{              }              // create an instance of `Bird` and write the upshot of the current row into it              if              err              :=              rows.              Scan              (              &bird.Species,              &bird.Description)              ;              err              !=              nil              {              log.              Fatalf              (              "could non scan row: %5"              ,              err)              }              // suspend the current instance to the slice of birds              birds              =              append              (birds,              bird)              }              // print the length, and all the birds              fmt.              Printf              (              "found %d birds: %+v"              ,              len              (birds)              ,              birds)                      

Calculation Query Parameters

Query parameters are the standard and secure way to add variables to your queries.

Most of the fourth dimension, nosotros demand to modify our SQL queries according to variables defined in our code.

We tin use the Query and QueryRow methods to add variables in our code as query parameters. To illustrate, permit's add a WHERE clause to become information nearly the eagle:

            birdName              :=              "hawkeye"              // For Postgres, parameters are specified using the "$" symbol, along with the index of              // the param. Variables should exist added every bit arguments in the aforementioned order              // The sql library takes care of converting types from Go to SQL based on the driver              row              :=              db.              QueryRow              (              "SELECT bird, description FROM birds WHERE bird = $i LIMIT $two"              ,              birdName,              one              )              // the lawmaking to scan the obtained row is the aforementioned as before              //...                      

Note: The symbols used for query params depends on the database y'all're using. For instance, we have to utilize the ? symbol in MySQL instead of $ which is specific to Postgres

Executing Writes - INSERT, UPDATE, and DELETE

Equally opposed to reads (SELECT queries), in that location are no returned rows afterward writing to a database.

Instead, the database returns information nearly the executed query, similar the number of rows returned.

With the sql library, nosotros can brand apply of the Exec method to execute write queries. Allow's see how we can use this to insert a new entry into the birds table:

                          // sample data that nosotros want to insert              newBird              :=              Bird{              Species:              "rooster"              ,              Description:              "wakes you lot up in the forenoon"              ,              }              // the `Exec` method returns a `Issue` blazon instead of a `Row`              // we follow the same statement blueprint to add together query params              issue,              err              :=              db.              Exec              (              "INSERT INTO birds (bird, description) VALUES ($one, $2)"              ,              newBird.Species,              newBird.Description)              if              err              !=              naught              {              log.              Fatalf              (              "could not insert row: %v"              ,              err)              }              // the `Event` type has special methods like `RowsAffected` which returns the              // total number of affected rows reported by the database              // In this case, it will tell usa the number of rows that were inserted using              // the higher up query              rowsAffected,              err              :=              upshot.              RowsAffected              (              )              if              err              !=              nil              {              log.              Fatalf              (              "could not get affected rows: %v"              ,              err)              }              // we can log how many rows were inserted              fmt.              Println              (              "inserted"              ,              rowsAffected,              "rows"              )                      

You tin still execute write queries using Query or QueryRow, but the Exec method saves us some of the work for mutual operations, like getting the affected rows

Connection Pooling - Timeouts and Max/Idle Connections

This department volition go over how to best manage the network connections that we open up to our database server.

The database server exists as a split up procedure from our Become application. All queries that we execute accept to go over a TCP connection that we open with the database.

application sends queries to database over a single TCP connection

For production applications, we can have a "pool" of simultaneous connections fabricated to the database server. This allows us to run multiple queries concurrently.

There are various options that nosotros can use to configure the database connection pool in our awarding:

  • Maximum Open Connections are the maximum number of parallel connections that can be made to the database at any time.
  • Maximum Idle Connections are the maximum number of connections that tin exist inactive at whatever fourth dimension. A connection is idle, if no queries are being executed on it. This tin can happen if the number of queries existence executed are less than the electric current pool of connections can handle.
  • Idle Connectedness Timeout is the maximum time for which whatever given connection can be idle. After this fourth dimension had elapsed, the connectedness to the database volition be closed.
  • Connection Lifetime is the maximum corporeality of fourth dimension that a connection can exist open (regardless of whether it's idle or not).

application can maintain a pool of multiple connections with the database, some of them are active (queries currently running), some are idle

By configuring these options, we tin can prepare the behavior of our connection pool. The *sql.DB case gives u.s. various methods to set these options:

            db,              err              :=              sql.              Open up              (              "pgx"              ,              "postgresql://localhost:5432/bird_encyclopedia"              )              if              err              !=              nada              {              log.              Fatalf              (              "could non connect to database: %five"              ,              err)              }              // Maximum Idle Connections              db.              SetMaxIdleConns              (              5              )              // Maximum Open up Connections              db.              SetMaxOpenConns              (              ten              )              // Idle Connection Timeout              db.              SetConnMaxIdleTime              (              1              *              time.Second)              // Connexion Lifetime              db.              SetConnMaxLifetime              (              30              *              fourth dimension.2d)                      

The exact values of these options depend on the overall throughput and average query execution time of our application, only in general:

  1. It'southward good to have a small-scale percentage of your connections be idle to provide for sudden spikes in query throughput
  2. We should set the maximum number of open up connections based on the network capacity of our database server and application servers, the lesser of which will be the limiting factor.

Query Timeouts - Using Context Cancellation

If a query is running for longer than expected, we tin abolish it using a context variable.

Context based cancellation is a popular method of prematurely exiting from running processes. The sql library provides helper methods to apply existing context variables to determine when to abolish a query.

Let's see how we can use context timeouts to stop a query midway:

                          // create a parent context              ctx              :=              context.              Background              (              )              // create a context from the parent context with a 300ms timeout              ctx,              _              =              context.              WithTimeout              (ctx,              300              *time.Millisecond)              // The context variable is passed to the `QueryContext` method as              // the start argument              // the pg_sleep method is a office in Postgres that volition halt for              // the provided number of seconds. We tin can use this to simulate a                            // slow query              _              ,              err              =              db.              QueryContext              (ctx,              "SELECT * from pg_sleep(i)"              )              if              err              !=              nil              {              log.              Fatalf              (              "could non execute query: %v"              ,              err)              }                      

Running this code should requite us this mistake message:

            2021/ten/x 17:41:09 could non execute query: timeout: context borderline exceeded go out status 1          

In production applications, it is always preferred to have timeouts for all queries: A sudden increase in throughput or a network outcome can lead to queries slowing down by orders of magnitude.

Slow queries cake the connections that they are running on, preventing other queries from running on them. We should ever ready a timeout after which to abolish a running query, to unblock connections in these cases.

Going Forrard

This postal service went over some of the common methods and practices to make your database production prepare, only we are still just scratching the surface.

There are numerous ways yous tin can improve the functioning and robustness of your awarding and database in production:

  1. Y'all can use DBStats to monitor the country of your database connections.
  2. Make use of transactions if your queries are dependent on each others results.

Of course, you should make sure to look at your applications requirements in terms of calibration and performance earlier designing information technology'due south architecture.

What practices practice you use when interacting with a database in your application? Let me know in the comments!

You tin see the full lawmaking for all the examples in this post on Github

perezwhate1974.blogspot.com

Source: https://www.sohamkamani.com/golang/sql-database/

0 Response to "How Can I Put a Query Result in Variable Sql Server"

Post a Comment

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel