dalgo2mssql

README

dalgo2mssql

Microsoft SQL Server driver of DALgo. Wraps github.com/dal-go/dalgo2sql to provide the dal.DB surface, and adds a SQL Server-native implementation of:

• dbschema.SchemaReader — schema introspection through the sys.* catalog views, scoped to one SQL Server schema (the login's default schema, SCHEMA_NAME(), usually dbo, unless WithSchema names another)
• ListViews — the views of the schema, so a schema browser can tell a view from a table
• io.Closer

It opens the pool with sql.OpenDB over NewConnector, a wrapper of go-mssqldb's connector that sanitises every error the driver returns (see Errors are sanitised). NewConnector is exported for callers that want a sanitised *sql.DB of their own.

The driver is github.com/microsoft/go-mssqldb (driver name "sqlserver"), pure Go: the package compiles with CGO_ENABLED=0.

Constructor usage

import (
    "context"

    "github.com/dal-go/dalgo2mssql"
)

db, err := dalgo2mssql.NewDatabase(ctx,
    "sqlserver://reader:secret@127.0.0.1:1433?database=shop")
if err != nil {
    return err
}
defer db.Close()

refs, err := db.ListCollections(ctx, nil)          // tables and views of dbo
def, err := db.DescribeCollection(ctx, &refs[0])   // columns, keys, indexes

NewDatabase opens the server, pings it with ctx and resolves the schema to read; a failure at any of those steps closes the pool again.

// A sanitised pool for native SQL.
connector, err := dalgo2mssql.NewConnector(dsn)
if err != nil {
    return err
}
sqlDB := sql.OpenDB(connector)

Options

Option	Effect
WithSchema(name)	Read schema name instead of the login's default schema. A schema that does not exist fails NewDatabase with an error that matches ErrSchemaNotFound.
WithDbOptions(dalgo2sql.DbOptions)	Options of the embedded dalgo2sql database (recordsets, …). StructuredQueryDialect is always replaced with "sqlserver".
WithDalSchema(dal.Schema)	The dal.Schema of the embedded database (an empty one by default).

A collection reference that names a schema of its own (dal.NewQualifiedRootCollectionRef("ext", "tenants", "")) is read in that schema, whatever the Database was opened with.

What the schema reader reports

Method	Reports
ListCollections	User tables and views of the schema, by name. Objects SQL Server ships (is_ms_shipped) are left out.
ListViews	The views of the schema, by name (each is also in ListCollections).
DescribeCollection	Columns (portable type, length, precision, nullability, default expression, identity), the primary key in key order, the indexes and the foreign keys.
ListIndexes	Indexes other than the primary key, key columns in key order; included columns are left out. The index behind a UNIQUE constraint is listed under the constraint's name, unique. Columnstore indexes are not listed: they have no key columns.
ListConstraints	PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK constraints (types primary-key, unique, foreign-key, check), by name within type.
ListReferrers	One entry per foreign key that references the table, from tables of the same schema, with the referencing columns in key order.

Details:

• A default definition loses the parentheses SQL Server wraps it in: ((0)) is 0, (getdate()) is getdate().
• A primary-key column is never reported nullable.
• A foreign key into another schema names it in ReferencedNamespace; one disabled with ALTER TABLE … NOCHECK CONSTRAINT is reported with ForeignKeyEnforcementDisabled. A key added WITH NOCHECK is enabled but untrusted (existing rows were not checked, new writes are) and is reported with ForeignKeyEnforcementEnabled. OnUpdate / OnDelete are NO ACTION, CASCADE, SET NULL or SET DEFAULT.
• A missing table or view is an error that matches ErrCollectionNotFound.

Type mapping

SQL Server type	dbschema.Type	Length / precision
bit	Bool
tinyint, smallint, int, bigint	Int
real, float	Float
decimal, numeric, money, smallmoney	Decimal	Precision{Total, Scale}
char, varchar	String	Length in characters; none for MAX
nchar, nvarchar	String	Length in characters (bytes / 2); none for MAX
binary, varbinary	Bytes	Length; none for MAX
image, timestamp/rowversion, hierarchyid, geography, geometry	Bytes
date, time, datetime, datetime2, smalldatetime, datetimeoffset	Time
anything else (text, ntext, xml, uniqueidentifier, sql_variant, …)	String

An alias type, including sysname (nvarchar(128)), is mapped by its system type.

Errors are sanitised

No error of this package carries the DSN, the password, the user or the host. Every error the driver returns goes through one function at the database/sql/driver level, so the rule holds on every path: NewDatabase, the schema reader, and the query, key-read and transaction methods of the embedded dalgo2sql dal.DB, including a fetch that fails in the middle of a result set.

• *ConnectionError — a failure to open (KindOpen), ping (KindPing) or resolve the schema (KindSchema), and on any later call (KindConnection, with Op naming the step): a failed (re)connect, a lost connection, and a login or database error the server returns (18456, 4060 and the like, whose messages name the user or the database).
• *CatalogError — a catalog query of the schema reader that the server rejected (for example for lack of VIEW DEFINITION); its text holds the call and the SQL Server error number only.
• *DriverError — any other error of the driver (a malformed reply, …); its text holds the step only.
• ErrReadOnlyTransaction — see below.
• An error the server returns for a statement the caller sent (a syntax error, an unknown object, a permission refused) is go-mssqldb's mssql.Error, unchanged, so the caller sees what was wrong with the statement. Its message describes the statement and can name the objects it touches — a permission error (229) names the database and schema of the object.
• driver.ErrBadConn and the context errors are returned bare, so database/sql still retries on a bad connection.

The texts of the first three are fixed; the driver's error stays reachable through Unwrap for errors.Is and errors.As.

Read-only access: use a read-only login

SQL Server has no read-only session and no read-only transaction, and this package does not pretend to have one: nothing here stops a statement sent through the embedded dal.DB from writing. For read-only use, connect as a login that can only read, for example a database user that is only a member of db_datareader and has VIEW DEFINITION for the schema scan:

RunReadonlyTransaction (a transaction begun with sql.TxOptions{ReadOnly: true}) always fails with ErrReadOnlyTransaction; run reads outside a transaction instead.

CREATE LOGIN reader WITH PASSWORD = '...';
USE shop;
CREATE USER reader FOR LOGIN reader;
ALTER ROLE db_datareader ADD MEMBER reader;
GRANT VIEW DEFINITION ON SCHEMA::dbo TO reader;

Current limitations

• Structured reads need dalgo2sql's "sqlserver" dialect. The package sets StructuredQueryDialect = "sqlserver", which compiles to @p1, @p2, … parameters. The dialect is being added to dal-go/dalgo2sql; until a release has it, a structured read is refused with dalgo2sql's unsupported structured query dialect "sqlserver" error. The schema reader does not depend on it.
• Key reads and writes do not work yet. Get, Set, Insert and Delete of the embedded dal.DB write ? parameter markers, which the sqlserver driver does not accept; dalgo2sql has no @p1 placeholder style yet.
• No ddl.Applier: the package does not create or alter tables.
• No bulk copy (mssql.CopyIn) through the package's pool: the wrapper refuses the driver's bulk-copy statement.

Running the tests

The unit tests use go-sqlmock and cover every statement. TestServer runs against a real SQL Server and is skipped unless DALGO2MSSQL_TEST_URL holds an admin URL; it creates a database of its own and drops it at the end.

docker run -d --name mssql2022 \
  -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD='Dalgo2mssql-Test1' \
  -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

DALGO2MSSQL_TEST_URL='sqlserver://sa:Dalgo2mssql-Test1@127.0.0.1:1433?database=master&TrustServerCertificate=true' \
    go test ./... -count=1 -v

The image is SQL Server Developer edition; ACCEPT_EULA=Y accepts its licence, which allows development and testing only. CI runs TestServer on every pull request and fails if it is skipped.

Documentation

Overview

Package dalgo2mssql is the Microsoft SQL Server adapter of DALgo: it reads a SQL Server schema and runs structured queries.

NewDatabase opens the server with github.com/microsoft/go-mssqldb (driver name "sqlserver", DSN form sqlserver://user:pass@host:1433?database=shop), pings it, and wraps the *sql.DB with dalgo2sql.NewDatabase under the "sqlserver" structured-query dialect, so a *Database is a dal.DB. It also is a dbschema.SchemaReader and an io.Closer, and it lists views (ListViews), which lets a schema browser tell a view from a table.

Scope

A Database reads one schema: the login's default schema (SCHEMA_NAME(), usually dbo) unless WithSchema names another. It reads user tables and views; objects SQL Server ships (is_ms_shipped) are left out. A collection reference that names a schema of its own (dal.NewQualifiedRootCollectionRef) is read in that schema.

Read-only

SQL Server has no read-only session and no read-only transaction, and this package does not pretend to have one: nothing here stops a statement sent through the embedded dal.DB from writing, and a transaction begun read-only (RunReadonlyTransaction) fails with ErrReadOnlyTransaction. Safety comes from outside this package: the caller sends only SELECT text, and the login in the DSN should be a read-only one (for example a user that is only a member of db_datareader and has VIEW DEFINITION for the schema scan).

The key read/write surface of the embedded dal.DB (Get, Set, Insert, Delete) writes "?" parameter markers, which the "sqlserver" driver does not accept; structured reads compiled by dalgo2sql's "sqlserver" dialect use @p1, @p2, … and are the supported way to read data. A dalgo2sql release older than the one that adds the "sqlserver" dialect refuses structured reads with its unsupported-dialect error; the schema reader does not depend on it.

Errors

No error of this package carries the DSN, the password, the user or the host. The pool is opened over NewConnector, which sends every error of the driver through one function, so this holds for the query, read and transaction methods of the embedded dal.DB too. A failure to open, ping or resolve the schema is a *ConnectionError; so is a failed reconnect, a lost connection or a login or database error on a later call. A catalog query the server rejects is a *CatalogError with the server's error number; another error of the driver is a *DriverError. Each has fixed text, and the driver's error stays reachable through Unwrap for errors.Is and errors.As. An error the server returns for a statement the caller sent is go-mssqldb's mssql.Error, unchanged: its message describes the statement and can name its objects, including their database.

Index

• Constants
• Variables
• func NewConnector(dsn string) (driver.Connector, error)
• type CatalogError
  • func (e *CatalogError) Error() string
  • func (e *CatalogError) Unwrap() error
• type ConnectionError
  • func (e *ConnectionError) Error() string
  • func (e *ConnectionError) Unwrap() error
• type ConnectionErrorKind
• type Database
  • func NewDatabase(ctx context.Context, dsn string, opts ...Option) (*Database, error)
  • func (d *Database) Adapter() dal.Adapter
  • func (d *Database) Close() error
  • func (d *Database) DescribeCollection(ctx context.Context, ref *dal.CollectionRef) (*dbschema.CollectionDef, error)
  • func (d *Database) ListCollections(ctx context.Context, _ *record.Key) ([]dal.CollectionRef, error)
  • func (d *Database) ListConstraints(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.ConstraintDef, error)
  • func (d *Database) ListIndexes(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.IndexDef, error)
  • func (d *Database) ListReferrers(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.Referrer, error)
  • func (d *Database) ListViews(ctx context.Context) ([]dal.CollectionRef, error)
  • func (d *Database) SchemaName() string
• type DriverError
  • func (e *DriverError) Error() string
  • func (e *DriverError) Unwrap() error
• type Option
  • func WithDalSchema(schema dal.Schema) Option
  • func WithDbOptions(dbOptions dalgo2sql.DbOptions) Option
  • func WithSchema(name string) Option

Constants

const DriverName = "sqlserver"

DriverName is the database/sql driver name go-mssqldb registers. The package itself opens the pool with sql.OpenDB over NewConnector, so that every error of the driver is sanitised.

const StructuredQueryDialect = "sqlserver"

StructuredQueryDialect is the dalgo2sql structured-query dialect the package sets on the dalgo2sql options.

const Version = "0.1.0"

Version is the dalgo2mssql adapter version, reported by Adapter.

Variables

var ErrCollectionNotFound = errors.New("collection not found")

ErrCollectionNotFound is matched (errors.Is) by the error a reader method returns for a table or view that is not in the schema.

var ErrReadOnlyTransaction = errors.New("dalgo2mssql: SQL Server has no read-only transactions; use a read-only login")

ErrReadOnlyTransaction is returned when a transaction is begun read-only (sql.TxOptions{ReadOnly: true}, dal's RunReadonlyTransaction): SQL Server has no read-only transaction, and this package does not fake one.

var ErrSchemaNotFound = errors.New("schema not found")

ErrSchemaNotFound is matched (errors.Is) by the error NewDatabase returns when the schema named with WithSchema does not exist.

Functions

func NewConnector

func NewConnector(dsn string) (driver.Connector, error)

NewConnector returns a driver.Connector for dsn (sqlserver://user:pass@host:1433?database=shop) that a caller can open with sql.OpenDB. It is go-mssqldb's connector with every error it returns sanitised (see the package documentation, Errors):

• a failure to connect, a login or database error the server returns (18456, 4060 and the like) and a failure of the transport are a *ConnectionError;
• an error the server returns for a statement is go-mssqldb's own mssql.Error, unchanged;
• any other error of the driver is a *DriverError;
• database/sql's driver.ErrBadConn and the context errors are returned bare, so database/sql still retries on a bad connection.

A transaction begun read-only fails with ErrReadOnlyTransaction.

A DSN the driver refuses is a *ConnectionError of kind KindOpen.

Types

type CatalogError

type CatalogError struct {
	Op     string
	Number int32
	// contains filtered or unexported fields
}

CatalogError reports a catalog query that SQL Server rejected (for example for lack of VIEW DEFINITION permission). Its text holds the call and the server's error number only: the server's message can name the database. The driver's error is reachable only through Unwrap.

func (*CatalogError) Error

func (e *CatalogError) Error() string

func (*CatalogError) Unwrap

func (e *CatalogError) Unwrap() error

Unwrap returns the driver's error.

type ConnectionError

type ConnectionError struct {
	Kind ConnectionErrorKind
	// Op is the call that failed, for example "ListCollections"; empty for the
	// steps of NewDatabase.
	Op string
	// contains filtered or unexported fields
}

ConnectionError reports a failure to reach SQL Server. Its text is fixed by Kind (and Op, a name of this package's own call); it never holds the DSN, the user, the password, the host, the database name or the driver's message. The driver's error is reachable only through Unwrap.

func (*ConnectionError) Error

func (e *ConnectionError) Error() string

func (*ConnectionError) Unwrap

func (e *ConnectionError) Unwrap() error

Unwrap returns the driver's error.

type ConnectionErrorKind

type ConnectionErrorKind string

ConnectionErrorKind says which step of reaching the server failed. It decides the fixed text of a ConnectionError.

const (
	// KindOpen: the driver refused the DSN (sql.Open failed).
	KindOpen ConnectionErrorKind = "open"
	// KindPing: the server could not be reached, refused the login, or the ping
	// timed out.
	KindPing ConnectionErrorKind = "ping"
	// KindSchema: the schema to read could not be resolved after connecting.
	KindSchema ConnectionErrorKind = "schema"
	// KindConnection: a later call failed with something other than an error
	// the server returned for its statement: a lost connection, a refused
	// login on reconnect, a timeout or a canceled context.
	KindConnection ConnectionErrorKind = "connection"
)

type Database

type Database struct {
	dal.DB
	// contains filtered or unexported fields
}

Database is a SQL Server database: a dal.DB (the embedded dalgo2sql database) that also reads the schema of one SQL Server schema.

func NewDatabase

func NewDatabase(ctx context.Context, dsn string, opts ...Option) (*Database, error)

NewDatabase opens the SQL Server named by dsn (sqlserver://… form), pings it with ctx and resolves the schema to read. Failures are *ConnectionError values whose text never holds any part of dsn; a schema named with WithSchema that does not exist matches ErrSchemaNotFound.

func (*Database) Adapter

func (d *Database) Adapter() dal.Adapter

Adapter names this adapter.

func (*Database) Close

func (d *Database) Close() error

Close closes the connection pool.

func (*Database) DescribeCollection

func (d *Database) DescribeCollection(ctx context.Context, ref *dal.CollectionRef) (*dbschema.CollectionDef, error)

DescribeCollection returns the columns (portable type, length, precision, nullability, default expression text, identity), the primary key in key order, the indexes and the foreign keys of a table or view.

func (*Database) ListCollections

func (d *Database) ListCollections(ctx context.Context, _ *record.Key) ([]dal.CollectionRef, error)

ListCollections returns the user tables and views of the schema, by name. parent is ignored: a schema is flat.

func (*Database) ListConstraints

func (d *Database) ListConstraints(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.ConstraintDef, error)

ListConstraints returns the PRIMARY KEY, UNIQUE, FOREIGN KEY and CHECK constraints of a table, by name within type (types "check", "foreign-key", "primary-key", "unique"). A view has none.

func (*Database) ListIndexes

func (d *Database) ListIndexes(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.IndexDef, error)

ListIndexes returns the indexes of a table or view other than its primary key, each with its key columns in key order (included columns are left out). The index behind a UNIQUE constraint is listed under the constraint's name, unique.

func (*Database) ListReferrers

func (d *Database) ListReferrers(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.Referrer, error)

ListReferrers returns one entry per foreign key that references the table, naming the referencing table and its columns in key order. Only referencing tables of the same schema are listed: a reference carries no schema.

func (*Database) ListViews

func (d *Database) ListViews(ctx context.Context) ([]dal.CollectionRef, error)

ListViews returns the views of the schema, by name; every one of them is also among what ListCollections returns. It lets a schema browser tell a view from a table.

func (*Database) SchemaName

func (d *Database) SchemaName() string

SchemaName returns the SQL Server schema the reader reads.

type DriverError

type DriverError struct {
	Op string
	// contains filtered or unexported fields
}

DriverError reports an error of the SQL Server driver that is neither a server's error for a statement nor a failure of the connection, for example a malformed reply. Its text holds the call only: the driver's message can name the server. The driver's error is reachable only through Unwrap.

func (*DriverError) Error

func (e *DriverError) Error() string

func (*DriverError) Unwrap

func (e *DriverError) Unwrap() error

Unwrap returns the driver's error.

type Option

type Option func(*options)

Option configures NewDatabase.

func WithDalSchema

func WithDalSchema(schema dal.Schema) Option

WithDalSchema sets the dal.Schema of the embedded dal.DB (an empty one by default).

func WithDbOptions

func WithDbOptions(dbOptions dalgo2sql.DbOptions) Option

WithDbOptions sets the dalgo2sql options of the embedded dal.DB. Its StructuredQueryDialect is always replaced with "sqlserver".

func WithSchema

func WithSchema(name string) Option

WithSchema names the SQL Server schema to read, instead of the default schema of the login (SCHEMA_NAME()).