A Golang library for using SQL.

Overview

dotsql GoDoc Build Status Test coverage Go Report Card

A Golang library for using SQL.

It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and use it with ease.

Dotsql is heavily inspired by yesql.

Installation

$ go get github.com/gchaincl/dotsql

Usage

First of all, you need to define queries inside your sql file:

-- name: create-users-table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name VARCHAR(255),
    email VARCHAR(255)
);

-- name: create-user
INSERT INTO users (name, email) VALUES(?, ?)

-- name: find-users-by-email
SELECT id,name,email FROM users WHERE email = ?

-- name: find-one-user-by-email
SELECT id,name,email FROM users WHERE email = ? LIMIT 1

--name: drop-users-table
DROP TABLE users

Notice that every query has a name tag (--name:<some name>), this is needed to be able to uniquely identify each query inside dotsql.

With your sql file prepared, you can load it up and start utilizing your queries:

// Get a database handle
db, err := sql.Open("sqlite3", ":memory:")

// Loads queries from file
dot, err := dotsql.LoadFromFile("queries.sql")

// Run queries
res, err := dot.Exec(db, "create-users-table")
res, err := dot.Exec(db, "create-user", "User Name", "[email protected]")
rows, err := dot.Query(db, "find-users-by-email", "[email protected]")
row, err := dot.QueryRow(db, "find-one-user-by-email", "[email protected]")

stmt, err := dot.Prepare(db, "drop-users-table")
result, err := stmt.Exec()

You can also merge multiple dotsql instances created from different sql file inputs:

dot1, err := dotsql.LoadFromFile("queries1.sql")
dot2, err := dotsql.LoadFromFile("queries2.sql")
dot := dotsql.Merge(dot1, dot2)

Embeding

To avoid distributing sql files alongside the binary file, you will need to use tools like gotic to embed / pack everything into one file.

TODO

  • Enable text interpolation inside queries using text/template

SQLX

For sqlx support check dotsqlx

Issues
  • Go modules support and missing unit tests

    Go modules support and missing unit tests

    This PR adds go modules support and missing unit tests, ~~completely removes integration tests~~. I think it would be appropriate, after merging this branch into master, to tag the latest commit as v1.0.0

    opened by swithek 6
  • feature(sqlx): add integration with sqlx

    feature(sqlx): add integration with sqlx

    I'm not sure whether this is the proper way to integrate with sqlx.

    In my opinion, implement sql.DB or sqlx.DB interface maybe more elegant. :joy: but that will change the whole project.

    opened by hxzhao527 4
  • Misc cleanups and docs

    Misc cleanups and docs

    Some fixes/cleanups:

    1. Renamed 'self' reciever names, as it's doesn't sound native for Go.
    2. Changed Load() to LoadFromFile() in example in Readme.
    3. Added simple functions descriptions to be used in godoc.
    4. Added example function for godoc.
    5. Hidden Scanner interface from being public (renamed to scanner)
    6. Also I was about to rename Queryer to Querier (it sounds more native), but then found Queryer name in database/sql/driver and decided to left as is.
    opened by divan 3
  • Generalized code for Load to use io.Reader

    Generalized code for Load to use io.Reader

    Here is proposed implementation of generalized Load() using io.Reader. Also three wrappers are available: LoadFile(), LoadString() and LoadBytes(). If you choose default Load() to use os.File, and have separate LoadReader() call to use Reader - it's ok with me, feel free to decide. But this approach is probably the most idiomatic.

    Also, I've changed package name in dotsql_test.go - to be able examine dotsql.queries length. Tell me if it's ok with you.

    opened by divan 3
  • Unable to load .sql files from a relative path?

    Unable to load .sql files from a relative path?

    I'm trying to load in a sql file from my package relative to the current package path:

    	tables, err := dotsql.LoadFromFile("../sql/tables.sql")
    
    	if err != nil {
    		fmt.Println(err)
    		fmt.Println("failed at tables")
    		os.Exit(1)
    	}
    

    This is erroring out with file not found, but it's the right path. Any tips?

    Edit: Is it always relative to where the main is? Wondering if I just specify relative to main when I build the bundle the files into the binary using gotic after the fact so I can still have a single static binary.

    opened by Schachte 2
  • Added QueryRow

    Added QueryRow

    I've added wrapper for database/sql.QueryRow. I'm not entirely sure about interface QueryRower (sounds funky, should I use different name?).

    There are also 2 more "random" commits with cleanup of examples. Not sure why I've included them in this PR. I can undo them and apply in different PR if you are ok with them.

    opened by ondrowan 2
  • Generalize Load() API call to use io.Reader interface

    Generalize Load() API call to use io.Reader interface

    As soon as there is a chance to break API, I would like to propose to change semantics for Load() - and generalize the code to use Reader interface instead of sticking with os.File. And provide handy wrapper LoadFile() of course.

    Typical use case - if app needs only a few queries, it would be nice to have them in plaintext directly in code (maybe, create also LoadString() wrapper). Also, if we want to bundle dotSQL-file into the app, using go-bindata - it's would require reading from Asset, and not from os.File.

    And it's generally wise to use Reader in such cases.

    opened by divan 2
  • Can't access LoadFromString

    Can't access LoadFromString

    I'm getting this error when trying to use the public method LoadFromString

    import ( "github.com/gchaincl/dotsql" )

    _, err = dotsql.LoadFromString("--name: query-a") if err != nil { return }

    dotsql.LoadFromString undefined (type *dotsql.DotSql has no field or method LoadFromString)

    opened by mrz1836 1
  • Some questions

    Some questions

    Hi, I have just started looking at this project and I've had a couple of ideas. I wanted to see what your thoughts are and if the ideas are in line with where you are taking this project.

    What do you think about exposing lookupQuery, so that callers can retrieve the SQL? What about exposing the ability to get all of the loaded query names?

    How about adding a method to add queries after the initial file is loaded? Perhaps have the ability for dotsql to write out a valid config file that includes these newly added queries?

    I guess I am seeing it as a potential query manager, but perhaps that should be a higher level application that just uses dotsql?

    opened by stevencothren 1
  • Updated README.md with new info about sqlx

    Updated README.md with new info about sqlx

    I've created a separate package that wraps dotsql and adds functionality needed by sqlx. This PR adds info to README about it. Closes #14 and closes #16

    opened by swithek 0
  • Add a Gitter chat badge to README.md

    Add a Gitter chat badge to README.md

    opened by gitter-badger 0
  • Is it possible to use named parameter?

    Is it possible to use named parameter?

    Something like this

    DB.Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name",
       sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2")).Find(&user)
    
    opened by rakib32 1
  • Add a mark to finish query

    Add a mark to finish query

    Let's say we use comments to group sql queries

    Example

    -- file user.sql
    
    --- UPDATE QUERIES ---
    
    --name: EmailUpdate
    UPDATE users SET email = $1 where user_id = $2
    
    --name: LevelUpdate
    UPDATE users SET level = $1 where user_id = $2
    
    ---  REPORTS ---
    
    ...
    

    In current implementation --- REPORTS--- comment belongs to LevelUpdate query. Problem is when LevelUpdate is not complete and we update the query on the program side. Let's imagine t hat we have the following:

    --name: LevelUpdate
    UPDATE users SET level = $1 where user_id IN
    

    and in the application:

    q := queries.LevelUpdate + fmt.Sprintf(" (%s)", strings.Join(userIDs, ","))
    

    This will fail: ERROR: syntax error at end of input (SQLSTATE 42601) - because queries.LevelUpdate is finishing with a comment. I know that we can bind here the sqlx solution, however this is just a simple example.

    We need a phrase to end the query, eg: --end
    opened by robert-zaremba 3
  • Transaction support?

    Transaction support?

    I need to update several tables at once and would like that to be in one transaction. Can't seem to figure out how dotsql and transactions can play together nicely and noticed you have no examples either. Thoughts?

    opened by dstroot 10
  • Load engine-dependent queries

    Load engine-dependent queries

    So let's say we have set of queries that we know are able to run on mysql/postgres/sqlite3. But we also have queries that uses specific engine extensions. So I'm looking for a confortable way to implement this, I'm not sure if an extra tag (@krisajenkins?) is the right way to do it, or expose a DotSql.Merge() method that allows to add more queries to the actual loader. Ideas will be appreciated :)

    help wanted question 
    opened by qustavo 7
Releases(v1.0.0)
Owner
Gustavo Chaín
Technical Dealer
Gustavo Chaín
A Golang library for using SQL.

dotsql A Golang library for using SQL. It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and

Gustavo Chaín 643 Aug 1, 2022
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

DiDi 1.4k Aug 4, 2022
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Doug Martin 1.6k Aug 5, 2022
A Go library for collecting sql.DBStats in Prometheus format

sqlstats A Go library for collecting sql.DBStats and exporting them in Prometheus format. A sql.DB object represents a pool of zero or more underlying

Daniel Middlecote 147 Aug 6, 2022
Go library for accessing multi-host SQL database installations

hasql hasql provides simple and reliable way to access high-availability database setups with multiple hosts. Status hasql is production-ready and is

Yandex 94 Aug 1, 2022
A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Ozzo Framework 565 Jul 28, 2022
Fluent SQL generation for golang

sqrl - fat-free version of squirrel - fluent SQL generator for Go Non thread safe fork of squirrel. The same handy fluffy helper, but with extra lette

Ivan Kirichenko 242 Jul 26, 2022
Fluent SQL generation for golang

Squirrel is "complete". Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork

null 5.1k Aug 6, 2022
golang orm and sql builder

gosql gosql is a easy ORM library for Golang. Style: var userList []UserModel err := db.FetchAll(&userList, gosql.Columns("id","name"), gosql.

RushTeam 161 Jun 24, 2022
This is a SQL Finder, Admin panel finder, and http analyzer that goes basses off requests. Made from 100% golang

This is a SQL Finder, Admin panel finder that goes basses off requests. Made from 100% golang

RE43P3R 0 May 19, 2022
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

徐佳军 53 Jan 23, 2022
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the pkg.go.dev or GitBook. Examples // Open database a

Travis Harmon 22 Jul 14, 2022
Type safe SQL builder with code generation and automatic query result data mapping

Jet Jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automa

null 769 Aug 2, 2022
Write your SQL queries in raw files with all benefits of modern IDEs, use them in an easy way inside your application with all the profit of compile time constants

About qry is a general purpose library for storing your raw database queries in .sql files with all benefits of modern IDEs, instead of strings and co

Sergey Treinis 22 Jul 4, 2022
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 169 Aug 7, 2022
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 81 Aug 8, 2022
💥 A lightweight DSL & ORM which helps you to write SQL in Go.

sqlingo is a SQL DSL (a.k.a. SQL Builder or ORM) library in Go. It generates code from the database and lets you write SQL queries in an elegant way.

Qishuai Liu 248 Aug 2, 2022
GraphJin - Build APIs in 5 minutes with GraphQL. An instant GraphQL to SQL compiler.

GraphJin - Build APIs in 5 minutes GraphJin gives you a high performance GraphQL API without you having to write any code. GraphQL is automagically co

Vikram Rangnekar 2.3k Aug 8, 2022
Analyzer: helps uncover bugs by reporting a diagnostic for mistakes of *sql.Rows usage.

sqlrows sqlrows is a static code analyzer which helps uncover bugs by reporting a diagnostic for mistakes of sql.Rows usage. Install You can get sqlro

GoStaticAnalysis 86 Mar 24, 2022