Databases

# Dump database into a SQL-script file:
pg_dump {{db_name}} > {{output_file.sql}}

# Same as above, customize username:
pg_dump -U {{username}} {{db_name}} > {{output_file.sql}}

# Same as above, customize host and port:
pg_dump -h {{host}} -p {{port}} {{db_name}} > {{output_file.sql}}

# Dump a database into a custom-format archive file:
pg_dump -Fc {{db_name}} > {{output_file.dump}}

# Dump only database data into an SQL-script file:
pg_dump -a {{db_name}} > {{path/to/output_file.sql}}

# Dump only schema (data definitions) into an SQL-script file:
pg_dump -s {{db_name}} > {{path/to/output_file.sql}}

createuser user_name

# super user role
alter role  SUPERUSER;

drop database dbname;

package main

import (
	"database/sql"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "root:root@(127.0.0.1:3306)/root?parseTime=true")
	if err != nil {
		log.Fatal(err)
	}

	query := `
            CREATE TABLE users (
                id INT AUTO_INCREMENT,
                username TEXT NOT NULL,
                password TEXT NOT NULL,
                created_at DATETIME,
                PRIMARY KEY (id)
            );`

	if _, err := db.Exec(query); err != nil {
		log.Fatal(err)
	}
}

# Change column type.
ALTER TABLE some_table ALTER COLUMN some_column TYPE varchar(500);

# Make column not accept nulls.
ALTER TABLE some_table ALTER COLUMN some_column SET NOT NULL;

# Make column accept nulls.
ALTER TABLE some_table ALTER COLUMN some_column DROP NOT NULL;

# Drop default.
ALTER TABLE some_table ALTER column some_column DROP default;

\d+ dbschema.dbtable;

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "root:root@(127.0.0.1:3306)/root?parseTime=true")
	if err != nil {
		log.Fatal(err)
	}

	username := "johndoe"
	password := "secret"
	createdAt := time.Now()

	result, err := db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, username, password, createdAt)
	if err != nil {
		log.Fatal(err)
	}

	id, err := result.LastInsertId()
	fmt.Println(id)
}

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "root:root@(127.0.0.1:3306)/root?parseTime=true")
	if err != nil {
		log.Fatal(err)
	}

	type user struct {
		id        int
		username  string
		password  string
		createdAt time.Time
	}

	rows, err := db.Query(`SELECT id, username, password, created_at FROM users`)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	var users []user
	for rows.Next() {
		var u user

		err := rows.Scan(&u.id, &u.username, &u.password, &u.createdAt)
		if err != nil {
			log.Fatal(err)
		}
		users = append(users, u)
	}
	if err := rows.Err(); err != nil {
		log.Fatal(err)
	}

	fmt.Printf("%#v", users)
}

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "root:root@(127.0.0.1:3306)/root?parseTime=true")
	if err != nil {
		log.Fatal(err)
	}

	var (
		id        int
		username  string
		password  string
		createdAt time.Time
	)

	query := "SELECT id, username, password, created_at FROM users WHERE id = ?"
	if err := db.QueryRow(query, 1).Scan(&id, &username, &password, &createdAt); err != nil {
		log.Fatal(err)
	}

	fmt.Println(id, username, password, createdAt)
}

package main

import (
	"database/sql"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "root:root@(127.0.0.1:3306)/root?parseTime=true")
	if err != nil {
		log.Fatal(err)
	}

	_, err := db.Exec(`DELETE FROM users WHERE id = ?`, 1)
	if err != nil {
		log.Fatal(err)
	}
}

drop table table_name;

DROP INDEX index_name;

DROP INDEX IF EXISTS index_name;

# Dump to sql file.
pg_dump -h host -U user dbname > dump.sql

# Dump using postgres compressed mode.
pg_dump -h host -U user -Fc dbname > dump.custom

# Dump to sql file compressed with gzip.
pg_dump -h host -U user dbname | gzip > dump.sql.gz

# Dump remote database through ssh.
ssh user@host -i sshkey "pg_dump -h dbhost -U dbuser dbname" > dump.sql

cat dump.sql | psql -U dbuser dbname

# Create text search configuration.
create text search configuration devbits_config (copy=simple); # Uses simple as a template.

# Alter mapping in text search configuration.
alter text search configuration my_config alter mapping for asciiword with my_dictionary;
alter text search configuration my_config alter mapping for asciiword with my_dictionary, simple; # falls back to simple

# Remove mapping in text search configuration.
alter text search configuration my_config drop mapping if exists for url;

# Delete text search configurtion.
drop text search configuration my_config;

# Create text search dictionary.
create text search dictionary my_dictionary (
	template = thesaurus,
	dictfile = my_thesaurus_file,  # Must be located in postgres shared dir.
	dictionary = simple            # Dictionary for thesaurus
);

# Drop text search dictionary.
drop text search dictionary my_dictionary;

package main

import (
	"database/sql"
	"log"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "root:root@(127.0.0.1:3306)/root?parseTime=true")
	if err != nil {
		log.Fatal(err)
	}
	if err := db.Ping(); err != nil {
		log.Fatal(err)
	}
}

create table objects (
    id int primary key,
    parent_id int,
    label text,
    content text);

insert into objects values
(1, 4, 'f', 'data for f'),
(2, 5, 'e', 'data for e'),
(3, 5, 'd', 'data for d'),
(4, 6, 'c', ''),
(5, 6, 'b', ''),
(6, 0, 'a', '');

with recursive nodes(id, path, content) as (
    select id, label, content
    from objects
    where parent_id = 0
union all
    select o.id, concat(path, '->', label), o.content
    from objects o
    join nodes n on n.id = o.parent_id
)
select *
from nodes
order by id desc;

 id |  path   |  content   
----+---------+------------
  6 | a       | 
  5 | a->b    | 
  4 | a->c    | 
  3 | a->b->d | data for d
  2 | a->b->e | data for e
  1 | a->c->f | data for f
(6 rows)

ALTER TABLE table_name RENAME TO new_table_name;

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;