Lessons learned from creating a postgresql data playground

Objective

Create a script that produces a postgresql database. The structure will be fixed and the contents of the db may vary. The shape of the data should be mostly the same so that every time I rebuild the db I can expect similar queries across rebuilds to output similar data.

End result

This world is built and feed with fake-messaging-app-pg. I expect to keep changing it but I can call the first iteration completed.

Lessons learned

Shebangs

I put it at the beginning of my script, but why?

#!/usr/bin/env sh

So after some wikipedia this is what I got:

It is for Unix-like operating systems.
The program loader parses the rest of the script as an interpreter directive. The specified interpreter program is run instead passing to it the path initially used.
Must be an absolute path for portability, though it is common to edit this line after copying scripts.
It’s syntax is #!interpreter [optional-arg] accepting at most one argument.
It is a magic number, 0x23 0x21, the two-character encoding in ASCII of #!.

Bash variables

First, there are many ways to do it.
They can be lower case, or upper case.
When you define one do not put spaces around the =:
```
a = asdf # won't work
a=asdf   # works
```
Remember to use them by prefixing them with a $:
```
a=asdf
echo $a
```
One way to create a multi line string is by using here-docs and read. read can receive a delimiter -d '' and maybe avoiding backslash as scape character -r:
```
read -d '' -r command <<COMMAND
hi
there
COMMAND
```

Ruby syntax

When passing a block and invoking a single method in that block as in:

conn.exec_prepared('insert_users_groups', [group_id, baseuser.id]) do |result|
  result.check
end

You can instead do:

conn.exec_prepared('insert_users_groups', [group_id, baseuser.id], :&check)

Ruby pg interface

This was a good opportunity to learn more about the pg gem.

Do a local connection with:

conn = PG.connect('dbname=fake_messaging_app')

Create prepared statements with:

conn.prepare('insert_users', 'insert into users (name) values ($1)', &:check)

Execute prepared statements with:

conn.exec_prepared('insert_users', [Faker::Name.name], &:check)

Execute statements with:

conn.exec('select group_id as g, count(*) as c from users_groups group by 1 order by 1')

Results are PG::Result, iterate them with:
```
result.map { |row| row['g'] }
```

Decode postgresql array strings with PG::TextDecoder as in:

 conn.exec('select array_agg(member_id) as mids from users_groups where group_id = $1', [group_id]) do |result|
  decoder = PG::TextDecoder::Array.new
  result.map { |row| decoder.decode(row['mids']).map(&:to_i) }.first
end