kandi background
Explore Kits

sqlx | general purpose extensions to golang 's database/sql | SQL Database library

 by   jmoiron Go Version: Current License: MIT

 by   jmoiron Go Version: Current License: MIT

Download this library from

kandi X-RAY | sqlx Summary

sqlx is a Go library typically used in Database, SQL Database, PostgresSQL, MariaDB applications. sqlx has no bugs, it has no vulnerabilities, it has a Permissive License and it has medium support. You can download it from GitHub.
sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.
Support
Support
Quality
Quality
Security
Security
License
License
Reuse
Reuse

kandi-support Support

  • sqlx has a medium active ecosystem.
  • It has 11733 star(s) with 914 fork(s). There are 192 watchers for this library.
  • It had no major release in the last 12 months.
  • There are 240 open issues and 338 have been closed. On average issues are closed in 212 days. There are 50 open pull requests and 0 closed requests.
  • It has a neutral sentiment in the developer community.
  • The latest version of sqlx is current.
sqlx Support
Best in #SQL Database
Average in #SQL Database
sqlx Support
Best in #SQL Database
Average in #SQL Database

quality kandi Quality

  • sqlx has 0 bugs and 0 code smells.
sqlx Quality
Best in #SQL Database
Average in #SQL Database
sqlx Quality
Best in #SQL Database
Average in #SQL Database

securitySecurity

  • sqlx has no vulnerabilities reported, and its dependent libraries have no vulnerabilities reported.
  • sqlx code analysis shows 0 unresolved vulnerabilities.
  • There are 0 security hotspots that need review.
sqlx Security
Best in #SQL Database
Average in #SQL Database
sqlx Security
Best in #SQL Database
Average in #SQL Database

license License

  • sqlx is licensed under the MIT License. This license is Permissive.
  • Permissive licenses have the least restrictions, and you can use them in most projects.
sqlx License
Best in #SQL Database
Average in #SQL Database
sqlx License
Best in #SQL Database
Average in #SQL Database

buildReuse

  • sqlx releases are not available. You will need to build from source code and install.
  • Installation instructions are not available. Examples and code snippets are available.
  • It has 6243 lines of code, 294 functions and 15 files.
  • It has medium code complexity. Code complexity directly impacts maintainability of the code.
sqlx Reuse
Best in #SQL Database
Average in #SQL Database
sqlx Reuse
Best in #SQL Database
Average in #SQL Database
Top functions reviewed by kandi - BETA

kandi's functional review helps you automatically verify the functionalities of the libraries and avoid rework.
Currently covering the most popular Java, JavaScript and Python libraries. See a Sample Here

Get all kandi verified functions for this library.

Get all kandi verified functions for this library.

sqlx Key Features

Marshal rows into structs (with embedded struct support), maps, and slices

Named parameter support including prepared statements

Get and Select to go quickly from query to struct/slice

install

copy iconCopydownload iconDownload
go get github.com/jmoiron/sqlx

issues

copy iconCopydownload iconDownload
SELECT a.id, a.name, b.id, b.name FROM foos AS a JOIN foos AS b ON a.parent = b.id;

usage

copy iconCopydownload iconDownload
package main

import (
    "database/sql"
    "fmt"
    "log"
    
    _ "github.com/lib/pq"
    "github.com/jmoiron/sqlx"
)

var schema = `
CREATE TABLE person (
    first_name text,
    last_name text,
    email text
);

CREATE TABLE place (
    country text,
    city text NULL,
    telcode integer
)`

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}

type Place struct {
    Country string
    City    sql.NullString
    TelCode int
}

func main() {
    // this Pings the database trying to connect
    // use sqlx.Open() for sql.Open() semantics
    db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
    if err != nil {
        log.Fatalln(err)
    }

    // exec the schema or fail; multi-statement Exec behavior varies between
    // database drivers;  pq will exec them all, sqlite3 won't, ymmv
    db.MustExec(schema)
    
    tx := db.MustBegin()
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net")
    tx.MustExec("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "1")
    tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Hong Kong", "852")
    tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "65")
    // Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person
    tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})
    tx.Commit()

    // Query the database, storing results in a []Person (wrapped in []interface{})
    people := []Person{}
    db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")
    jason, john := people[0], people[1]

    fmt.Printf("%#v\n%#v", jason, john)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}
    // Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@gmail.net"}

    // You can also get a single result, a la QueryRow
    jason = Person{}
    err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")
    fmt.Printf("%#v\n", jason)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}

    // if you have null fields and use SELECT *, you must use sql.Null* in your struct
    places := []Place{}
    err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
    if err != nil {
        fmt.Println(err)
        return
    }
    usa, singsing, honkers := places[0], places[1], places[2]
    
    fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers)
    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}
    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}

    // Loop through rows using only one struct
    place := Place{}
    rows, err := db.Queryx("SELECT * FROM place")
    for rows.Next() {
        err := rows.StructScan(&place)
        if err != nil {
            log.Fatalln(err)
        } 
        fmt.Printf("%#v\n", place)
    }
    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}
    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}

    // Named queries, using `:name` as the bindvar.  Automatic bindvar support
    // which takes into account the dbtype based on the driverName on sqlx.Open/Connect
    _, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, 
        map[string]interface{}{
            "first": "Bin",
            "last": "Smuth",
            "email": "bensmith@allblacks.nz",
    })

    // Selects Mr. Smith from the database
    rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "Bin"})

    // Named queries can also use structs.  Their bind names follow the same rules
    // as the name -> db mapping, so struct fields are lowercased and the `db` tag
    // is taken into consideration.
    rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:first_name`, jason)
    
    
    // batch insert
    
    // batch insert with structs
    personStructs := []Person{
        {FirstName: "Ardie", LastName: "Savea", Email: "asavea@ab.co.nz"},
        {FirstName: "Sonny Bill", LastName: "Williams", Email: "sbw@ab.co.nz"},
        {FirstName: "Ngani", LastName: "Laumape", Email: "nlaumape@ab.co.nz"},
    }

    _, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email)
        VALUES (:first_name, :last_name, :email)`, personStructs)

    // batch insert with maps
    personMaps := []map[string]interface{}{
        {"first_name": "Ardie", "last_name": "Savea", "email": "asavea@ab.co.nz"},
        {"first_name": "Sonny Bill", "last_name": "Williams", "email": "sbw@ab.co.nz"},
        {"first_name": "Ngani", "last_name": "Laumape", "email": "nlaumape@ab.co.nz"},
    }

    _, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email)
        VALUES (:first_name, :last_name, :email)`, personMaps)
}

How to test two parallel transactions in Rust SQLx?

copy iconCopydownload iconDownload
use async_std::future;
use std::time::Duration;

let max_duration = Duration::from_millis(100);
assert!(timeout(max_duration, tx2.commit()).await.is_err());
async_std::task::spawn(async move {
    assert!(tx1.commit().await.is_ok());
});
use async_std::future;
use std::time::Duration;

let max_duration = Duration::from_millis(100);
assert!(timeout(max_duration, tx2.commit()).await.is_err());
async_std::task::spawn(async move {
    assert!(tx1.commit().await.is_ok());
});
    let handle1 = tokio::spawn(async move {
        let repo = new_repo();
        let mut tx = db1.begin().await.unwrap();
        let rows_affected = repo.insert_credentials(&mut tx, &credentials1).await.unwrap();
        assert_eq!(rows_affected, 1);
        tokio::time::sleep(Duration::from_millis(100)).await;
        tx.commit().await.unwrap()
    });
    
    let handle2 = tokio::spawn(async move {
        let repo = new_repo();
        let mut tx = db2.begin().await.unwrap();
        let rows_affected = repo.insert_credentials(&mut tx, &credentials2).await.unwrap();
        assert_eq!(rows_affected, 1);
        tokio::time::sleep(Duration::from_millis(100)).await;
        tx.commit().await.unwrap()
    });

    let (_first, _second) = rocket::tokio::try_join!(handle1, handle2).unwrap();

Borrowed value does not live long enough in a generic struct in Result - Map

copy iconCopydownload iconDownload
impl<T> ClientDBMain<T>
where
    T: FromRow<'static, PgRow>,
{
    async fn query_one(&self, pool: &PgPool, query_raw: &str) -> Result<T, MyError> {
        let res = sqlx::query(query_raw)
            .fetch_one(pool)
            .await
            .map(|r: PgRow| {
                // error here
                T::from_row(&r).unwrap()
            })
            .map_err(|err| {
                // to save for brevity
            })?;
        Ok(res)
    }
}
impl<T> ClientDBMain<T>
where
    T: for<'a> FromRow<'a, PgRow>,
    // ^^^^^^^
{
    async fn query_one(&self, pool: &PgPool, query_raw: &str) -> Result<T, MyError> {
        ...

(GoLang) panic: sql: Register called twice for driver postgres

copy iconCopydownload iconDownload
 sql.Register("postgres", &pq.Driver{}) <-- delete this line
package main

import (
    "fmt"
    "database/sql"
    "github.com/jmoiron/sqlx"
     _ "github.com/lib/pq"  // <-- add this line
)
 sql.Register("postgres", &pq.Driver{}) <-- delete this line
package main

import (
    "fmt"
    "database/sql"
    "github.com/jmoiron/sqlx"
     _ "github.com/lib/pq"  // <-- add this line
)

Rust warp+sqlx service : idiomatic way of passing DBPool from main to handlers

copy iconCopydownload iconDownload
static POOL: Pool<Postgres> = …;
static POOL: OnceCell<Pool<Postgres>> = OnceCell::const_new();
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    POOL.get_or_try_init(|| async {
        PgPoolOptions::new()
            .max_connections(5)
            .connect("postgres://:@localhost/todo_db")
            .await
    })
    .await?;
    // Later, just access your pool with POOL.get().unwrap()
    // You don't need the with_db filter anymore
static POOL: Pool<Postgres> = …;
static POOL: OnceCell<Pool<Postgres>> = OnceCell::const_new();
#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    POOL.get_or_try_init(|| async {
        PgPoolOptions::new()
            .max_connections(5)
            .connect("postgres://:@localhost/todo_db")
            .await
    })
    .await?;
    // Later, just access your pool with POOL.get().unwrap()
    // You don't need the with_db filter anymore

Module lookup disabled by GOPROXY=off golangci

copy iconCopydownload iconDownload
modules-download-mode: vendor

Postgres Update multiple JSONB columns

copy iconCopydownload iconDownload
update
    tbl
set meta = jsonb_set(meta, '{id}' :: text[] , to_jsonb(meta ->> 'id'))
where meta ->> 'source' = 'a'

Use one struct for multiple SQL queries

copy iconCopydownload iconDownload
type S1 struct {
    X int
    Y int
    Z string
}

func run() {
    rows, err := db.Queryx("SELECT X,Y,Z FROM SomeTable")
    for rows.Next() {
        var p S1
        err = rows.Scan(&S1.X, &S1.Y, &S1.Z)
    }

    rows, err := db.Queryx("SELECT Y,Z FROM SomeTable")
    for rows.Next() {
        var p S1
        err = rows.Scan(&S1.Y, &S1.Z)
    }

    rows, err := db.Queryx("SELECT X,Y FROM SomeTable")
    for rows.Next() {
        var p S1
        err = rows.Scan(&S1.X, &S1.Y)
    }
}

go with sqlx NamedQuery timestamp works with date but not with datetime. NamedQuery vs Query

copy iconCopydownload iconDownload
layout := "2006-01-02 15:04:05"
ts, err := time.Parse(layout, "1999-01-08 04:05:06")
if err != nil {
    return err
}

arg := map[string]interface{}{"ts": ts}
rows, err := db.NamedQuery(`SELECT ts FROM test_table WHERE ts > :ts`, arg)
if err != nil {
    return err
}

Can't use gorm with &quot;show variables&quot; phrase

copy iconCopydownload iconDownload
sqlVars := []struct {
        Name  string `gorm:"column:Variable_name"`
        Value int    `gorm:"column:Value"`
}{}

pq: SSL is not enabled on the server Postrsql golang

copy iconCopydownload iconDownload
func NewPostSql() (*sql.DB, error) {
    db, err := sql.Open("postgres", "postgres://ellez2004@localhost:5432/app?sslmode=disable")
    if err != nil {
        return nil, err
    }
    if err := db.Ping(); err != nil {
        fmt.Println("error: ", err.Error())
    }
    return db, nil
}
  import (
    "context"
    "fmt"
    "time"

    _ "github.com/lib/pq"
    "github.com/jmoiron/sqlx"
    )

  func NewPostSql()(*sql.DB,error) { 
    db, err := sql.Open("postgres", "postgres://postgres:ellez2004@localhost:5432/app?sslmode=disable")
    if err != nil { 
        return nil,err;
    }
    if err := db.Ping();err != nil {
        fmt.Println("error: ", err.Error());
    }
    return db,nil;
}

Community Discussions

Trending Discussions on sqlx
  • How to test two parallel transactions in Rust SQLx?
  • Borrowed value does not live long enough in a generic struct in Result - Map
  • sqlx in golang - Is it possible to map joined tables?
  • (GoLang) panic: sql: Register called twice for driver postgres
  • Rust warp+sqlx service : idiomatic way of passing DBPool from main to handlers
  • Module lookup disabled by GOPROXY=off golangci
  • Postgres Update multiple JSONB columns
  • Use one struct for multiple SQL queries
  • go with sqlx NamedQuery timestamp works with date but not with datetime. NamedQuery vs Query
  • Can't use gorm with &quot;show variables&quot; phrase
Trending Discussions on sqlx

QUESTION

How to test two parallel transactions in Rust SQLx?

Asked 2022-Apr-04 at 18:26

I'm experimenting with Rocket, Rust and SQLx and I'd like to test what happens when two parallel transactions try to insert a duplicated record on my table.

My insert fn contains nothing special and it works fine:

async fn insert_credentials<'ex, EX>(&self, executor: EX, credentials: &Credentials) -> Result<u64, Errors>
where
    EX: 'ex + Executor<'ex, Database = Postgres>,
{
    sqlx::query!(
        r#"INSERT INTO credentials (username, password)
        VALUES ($1, crypt($2, gen_salt('bf')))"#,
        credentials.username,
        credentials.password,
    )
    .execute(executor)
    .await
    .map(|result| result.rows_affected())
    .map_err(|err| err.into())
}

My test, though, hangs indefinitely since it waits for a commit that never happens:

#[async_std::test]
async fn it_should_reject_duplicated_username_in_parallel() {
    let repo = new_repo();
    let db: Pool<Postgres> = connect().await;
    let credentials = new_random_credentials();

    println!("TX1 begins");
    let mut tx1 = db.begin().await.unwrap();
    let rows_affected = repo.insert_credentials(&mut tx1, &credentials).await.unwrap();
    assert_eq!(rows_affected, 1);

    println!("TX2 begins");
    let mut tx2 = db.begin().await.unwrap();
    println!("It hangs on the next line");
    let rows_affected = repo.insert_credentials(&mut tx2, &credentials).await.unwrap();
    assert_eq!(rows_affected, 1);
    
    println!("It never reaches this line");
    tx1.commit().await.unwrap();
    tx2.commit().await.unwrap();
}

How do I create and execute those TXs in parallel, such that the assertions pass but the test fails when trying to commit the second TX?

For reference, this is my Cargo.toml

[package]
name = "auth"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
async-trait = "0.1.52"
serde = "1.0.136"
thiserror = "1.0.30"

# TODO https://github.com/SergioBenitez/Rocket/issues/1893#issuecomment-1002393878
rocket = { git = "https://github.com/SergioBenitez/Rocket", features = ["json"] }

[dependencies.redis]
version = "0.21.5"
features = ["tokio-comp"]

[dependencies.sqlx]
version = "0.5.11"
features = ["macros", "runtime-tokio-rustls", "postgres"]

[dependencies.uuid]
version = "1.0.0-alpha.1"
features = ["v4", "fast-rng", "macro-diagnostics"]

## DEV ##

[dev-dependencies]
mockall = "0.11.0"

[dev-dependencies.async-std]
version = "1.11.0"
features = ["attributes", "tokio1"]

ANSWER

Answered 2022-Apr-04 at 16:05

You can use a async_std::future::timeout or tokio::time::timeout. Example using async_std:

use async_std::future;
use std::time::Duration;

let max_duration = Duration::from_millis(100);
assert!(timeout(max_duration, tx2.commit()).await.is_err());

If you want to continue to tx2 before completing tx1, you can async_std::task::spawn or tokio::spawn the tx1 first:

async_std::task::spawn(async move {
    assert!(tx1.commit().await.is_ok());
});

Source https://stackoverflow.com/questions/71716949

Community Discussions, Code Snippets contain sources that include Stack Exchange Network

Vulnerabilities

No vulnerabilities reported

Install sqlx

You can download it from GitHub.

Support

For any new features, suggestions and bugs create an issue on GitHub. If you have any questions check and ask questions on community page Stack Overflow .

DOWNLOAD this Library from

Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases
Explore Kits

Save this library and start creating your kit

Share this Page

share link
Reuse Pre-built Kits with sqlx
Consider Popular SQL Database Libraries
Try Top Libraries by jmoiron
Compare SQL Database Libraries with Highest Support
Compare SQL Database Libraries with Highest Quality
Compare SQL Database Libraries with Highest Security
Compare SQL Database Libraries with Permissive License
Compare SQL Database Libraries with Highest Reuse
Find, review, and download reusable Libraries, Code Snippets, Cloud APIs from
over 430 million Knowledge Items
Find more libraries
Reuse Solution Kits and Libraries Curated by Popular Use Cases
Explore Kits

Save this library and start creating your kit

  • © 2022 Open Weaver Inc.