From CREATE TABLE to a Go struct that scans rows

Step 1 — Have a CREATE TABLE statement

Use whichever flavour of SQL your database speaks. The shared subset across Postgres, MySQL, and SQLite is enough for most application tables:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  is_admin BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Step 2 — Generate the Go struct

Paste the DDL into the SQL → Go struct converter. Default output:

package main

type User struct {
	ID        int       `json:"id"`
	Name      string    `json:"name"`
	Email     *string   `json:"email,omitempty"`
	IsAdmin   bool      `json:"isAdmin"`
	CreatedAt string    `json:"createdAt"`
}

Two things to notice: nullable columns become pointer types, and field names are PascalCase with the original column name preserved in the json tag.

Step 3 — Polish field types

For real services, you usually want to:

• • Switch int to int64 for BIGINT / BIGSERIAL columns.
• • Switch timestamp string types to time.Time.
• • Add db tags so sqlx / scany can use them.
• • Pick consistent json tag style (camelCase or snake_case).

package model

import "time"

type User struct {
	ID        int64     `json:"id" db:"id"`
	Name      string    `json:"name" db:"name"`
	Email     *string   `json:"email,omitempty" db:"email"`
	IsAdmin   bool      `json:"is_admin" db:"is_admin"`
	CreatedAt time.Time `json:"created_at" db:"created_at"`
}

Step 4 — Scan with database/sql

The standard library is verbose but always works:

var u model.User
err := db.QueryRow(`
  SELECT id, name, email, is_admin, created_at
  FROM users WHERE id = $1
`, id).Scan(&u.ID, &u.Name, &u.Email, &u.IsAdmin, &u.CreatedAt)

Step 5 — Or use sqlx for less boilerplate

sqlx reads db tags and matches columns by name, so:

var u model.User
err := db.Get(&u, `SELECT * FROM users WHERE id = $1`, id)

For a deeper integration path, look into sqlc:

// If you want type-safe SQL queries with the same struct types,
// consider sqlc — it reads .sql files and generates Go from them.
// This guide covers the lighter case: you just want a struct.

Common pitfalls

• • Big integer overflow. If your columns are BIGINT or BIGSERIAL, switch int to int64— Go's default int is platform-dependent.
• • Nullable handling. Pointer types are clean for JSON encoding; sql.NullString is more idiomatic for raw database/sql code. Pick one and stay consistent.
• • Tag mismatches. Forgetting a db tag on a struct used with sqlx will cause the field to be skipped silently.
• • Time zones. TIMESTAMPTZ stores UTC; a string field will represent it differently from a real time.Time. Use the right type per column.

FAQ