dialect.md

Dialect

Dialects allow goqu the build the correct SQL for each database. There are four dialects that come packaged with goqu

• mysql - import _ "github.com/doug-martin/goqu/v9/dialect/mysql"
• postgres - import _ "github.com/doug-martin/goqu/v9/dialect/postgres"
• sqlite3 - import _ "github.com/doug-martin/goqu/v9/dialect/sqlite3"
• sqlserver - import _ "github.com/doug-martin/goqu/v9/dialect/sqlserver"

NOTE Dialects work like drivers in go where they are not registered until you import the package.

Below are examples for each dialect. Notice how the dialect is imported and then looked up using goqu.Dialect

Postgres

import (
  "fmt"
  "github.com/doug-martin/goqu/v9"
  // import the dialect
  _ "github.com/doug-martin/goqu/v9/dialect/postgres"
)

// look up the dialect
dialect := goqu.Dialect("postgres")

// use dialect.From to get a dataset to build your SQL
ds := dialect.From("test").Where(goqu.Ex{"id": 10})
sql, args, err := ds.ToSQL()
if err != nil{
  fmt.Println("An error occurred while generating the SQL", err.Error())
}else{
  fmt.Println(sql, args)
}

Output:

SELECT * FROM "test" WHERE "id" = 10 []

MySQL

import (
  "fmt"
  "github.com/doug-martin/goqu/v9"
  // import the dialect
  _ "github.com/doug-martin/goqu/v9/dialect/mysql"
)

// look up the dialect
dialect := goqu.Dialect("mysql")

// use dialect.From to get a dataset to build your SQL
ds := dialect.From("test").Where(goqu.Ex{"id": 10})
sql, args, err := ds.ToSQL()
if err != nil{
  fmt.Println("An error occurred while generating the SQL", err.Error())
}else{
  fmt.Println(sql, args)
}

Output:

SELECT * FROM `test` WHERE `id` = 10 []

SQLite3

import (
  "fmt"
  "github.com/doug-martin/goqu/v9"
  // import the dialect
  _ "github.com/doug-martin/goqu/v9/dialect/sqlite3"
)

// look up the dialect
dialect := goqu.Dialect("sqlite3")

// use dialect.From to get a dataset to build your SQL
ds := dialect.From("test").Where(goqu.Ex{"id": 10})
sql, args, err := ds.ToSQL()
if err != nil{
  fmt.Println("An error occurred while generating the SQL", err.Error())
}else{
  fmt.Println(sql, args)
}

Output:

SELECT * FROM `test` WHERE `id` = 10 []

SQLServer

import (
  "fmt"
  "github.com/doug-martin/goqu/v9"
  // import the dialect
  _ "github.com/doug-martin/goqu/v9/dialect/sqlserver"
)

// look up the dialect
dialect := goqu.Dialect("sqlserver")

// use dialect.From to get a dataset to build your SQL
ds := dialect.From("test").Where(goqu.Ex{"id": 10})
sql, args, err := ds.ToSQL()
if err != nil{
  fmt.Println("An error occurred while generating the SQL", err.Error())
}else{
  fmt.Println(sql, args)
}

Output:

SELECT * FROM "test" WHERE "id" = 10 []

Executing Queries

You can also create a goqu.Database instance to query records.

In the example below notice that we imported the dialect and driver for side effect only.

import (
  "database/sql"
  "github.com/doug-martin/goqu/v9"
  _ "github.com/doug-martin/goqu/v9/dialect/postgres"
  _ "github.com/lib/pq"
)

dialect := goqu.Dialect("postgres")

pgDb, err := sql.Open("postgres", "user=postgres dbname=goqupostgres sslmode=disable ")
if err != nil {
  panic(err.Error())
}
db := dialect.DB(pgDb)

// "SELECT COUNT(*) FROM "user";
if count, err := db.From("user").Count(); err != nil {
  fmt.Println(err.Error())
}else{
  fmt.Printf("User count = %d", count)
}

Custom Dialects

Dialects in goqu are the foundation of building the correct SQL for each DB dialect.

Dialect Options

Most SQL dialects share a majority of their syntax, for this reason goqu has a default set of dialect options that can be used as a base for any new Dialect.

When creating a new SQLDialect you just need to override the default values that are documented in SQLDialectOptions.

Take a look at postgres, mysql and sqlite3 for examples.

Creating a custom dialect

When creating a new dialect you must register it using RegisterDialect. This method requires 2 arguments.

1. dialect string - The name of your dialect
2. opts SQLDialectOptions - The custom options for your dialect

For example you could create a custom dialect that replaced the default quote '"' with a backtick `

opts := goqu.DefaultDialectOptions()
opts.QuoteRune = '`'
goqu.RegisterDialect("custom-dialect", opts)

dialect := goqu.Dialect("custom-dialect")

ds := dialect.From("test")

sql, args, _ := ds.ToSQL()
fmt.Println(sql, args)

Output:

SELECT * FROM `test` []

For more examples look at postgres, mysql and sqlite3 for examples.