by Ciro Santilli (@cirosantilli, 32)
Create user for further logins without sudo askubuntu.com/questions/915585/how-to-login-mysql-shell-when-mysql-have-no-password/1325689#1325689: 
sudo mysql -e "CREATE USER $USER"
Create test user with password: 
sudo mysql -e 'CREATE USER user0 IDENTIFIED WITH mysql_native_password BY "a"'
sudo mysql -e 'GRANT ALL PRIVILEGES ON database_name.* TO "user0"'
and login as that user: 
mysql -u user0 -p
Login with password given on the command line: 
mysql -u user0 -pmypassword
The IDENTIFIED WITH mysql_native_password part is to overcome "Client does not support authentication protocol requested by server" when connecting from Node.js.
List users: 
sudo mysql -e 'SELECT * FROM mysql.user'
View permissions for each user on each DB: serverfault.com/questions/263868/how-to-know-all-the-users-that-can-access-a-database-mysql 
sudo mysql -e 'SELECT * FROM mysql.db'
List databases: 
sudo mysql -e 'SHOW DATABASES'
Create database: 
sudo mysql -e 'CREATE DATABASE mydb0'
Destroy database: 
sudo mysql -e 'DROP DATABASE mydb0'
Show tables in database: 
sudo mysql -e 'SHOW TABLES' mydb0
or: 
sudo mysql -e 'SHOW TABLES FROM mydb0'
Dude's a legend. Sells company for a few million. Then forks the open source project next year. Love it.
PostgreSQL feels good.
Had a look at the source tree, and also felt good.
If Oracle is the Microsoft of database, Postgres is the Linux, and MySQL (or more precisely MariaDB) is the FreeBSD (i.e. the one that got delayed by legal issues). Except that their software licenses were accidentally swapped.
The only problem with Postgres is its name. PostgreSQL is so unpronounceable and so untypeable that you should just call it "Postgres" like everyone else.
On Ubuntu 20.10 PostgreSQL 12.6, login with psql on my default username without sudo fails with: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist
This is the one that worked on Ubuntu 21.04: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/38444152#38444152 
sudo -u postgres createuser -s $(whoami)
createdb $(whoami)
Explanation:
  • sudo -u postgres uses the postgres user via peer authentication
  • -s in createuser -s: make it a superuser
  • createdb: TODO why do we have to create a table with the same name as the user? Otherwise login fails.
You can now run psql without any password. This works without password due to peer authentication, 
sudo cat /etc/postgresql/12/main/pg_hba.conf
shows that peer authentication is available to all users apparently: 
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
List users: 
psql -c '\du'
output: 
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 ciro        | Superuser, Create role, Create DB                          | {}
 owning_user |                                                            | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Delete user later on: 
psql -c 'DROP USER username;'
Create a database: 
createdb testdb0
Help toplevel: 
help
Get help for Postgres commands such as \h and so on: 
\?
List supported SQL commands: 
\h
Show syntax for one type of command: 
\h SELECT
List all databases: 
psql -c '\l'
which shows: 
    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------+----------+----------+-------------+-------------+-----------------------
 ciro        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 testdb0     | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)
Delete a database: 
psql -c 'DROP DATABASE "testdb0";'
If you didn't give a database from the command line e.g.: 
psql
you can do that afterwards with: 
\c testdb0
Let's create a table and test that it is working: 
psql testdb0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'
List tables, no special tables: 
psql testdb0 -c '\dt'
gives: 
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | table0 | table | ciro
(1 row)
View table schema: stackoverflow.com/questions/109325/postgresql-describe-table 
psql testdb0 -c '\d+ table0'
output: 
                                      Table "public.table0"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 int0   | integer       |           |          |         | plain    |              | 
 char0  | character(16) |           |          |         | extended |              |
Insert some data into it and get the data out: 
psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
psql testdb0 -c 'SELECT * FROM table0;'
output: 
 int0 |      char0
------+------------------
    2 | two
    3 | three
    5 | five
    7 | seven
(4 rows)
Delete the table: 
psql testdb0 -c 'DROP TABLE table0;'
In order to create a test user with password instead of peer authentication, let's create test user: 
createuser -P user0
createdb user0
-P makes it prompt for the users password.
Alternatively, to create the password non-interactively stackoverflow.com/questions/42419559/postgres-createuser-with-password-from-terminal: 
psql -c "create role NewRole with login password 'secret'"
Can't find a way using the createuser helper.
We can then login with that password with: 
psql -U user0 -h localhost
which asks for the password we've just set, because the -h option turns off peer authentication, and turns off password authentication.
The password can be given non-interactively as shown at stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively with the PGPASSWORD environment variable: 
PGPASSWORD=a psql -U user0 -h localhost
Now let's create a test database which user0 can access with an existing superuser account: 
createdb user0db0
psql -c 'GRANT ALL PRIVILEGES ON DATABASE user0db0 TO user0'
We can check this permission with: 
psql -c '\l'
which now contains: 
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 user0db0  | ciro     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/ciro             +
           |          |          |             |             | ciro=CTc/ciro        +
           |          |          |             |             | user0=CTc/ciro
The permission letters are explained at:
user0 can now do the usual table operations on that table: 
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'SELECT * FROM table0;'
Uses the name of the current Linux user to login without a password.
Ubuntu 21.10 has a certain default level of logging by default to: 
/var/log/postgresql/postgresql-13-main.log
but it does not log everything, only/mostly errors it seems.
Setting: 
log_statement = 'all'
under: 
/etc/postgresql/13/main/postgresql.conf
and then restarting the server: 
sudo service restart postgresql
just works.
When using SQL REPEATABLE READ isolation level and SQL SERIALIZABLE isolation level, concurrent transactions may fail with a serialization failure, and then you might need to retry them. You server code or your ORM must always account for that.
A good way to explore when it happens is to use the example
Often known simply as SQL Server, a terrible thing that makes it impossible to find portable SQL answers on Google! You just have to Google by specific SQL implementation unfortunately to find anything about the open source ones.
The minimalism, serverlessness/lack of temporary caches/lack of permission management, Hipp's religious obsession with efficiency, the use of their own pure Fossil version control[ref]. Wait, scrap that last one. Pure beauty!
Official Git mirror: github.com/sqlite/sqlite
Create a table 
sqlite3 db.sqlite3 "
CREATE TABLE 'IntegerNames' (int0 INT, char0 CHAR(16));
INSERT INTO 'IntegerNames' (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');
"
List tables: 
sqlite3 db.sqlite3 '.tables'
output: 
IntegerNames
Show schema of a table: 
sqlite3 db.sqlite3 '.schema IntegerNames'
outputs the query that would generate that table: 
CREATE TABLE IF NOT EXISTS 'IntegerNames' (int0 INT, char0 CHAR(16));
Show all data in a table: 
sqlite3 db.sqlite3 'SELECT * FROM IntegerNames'
output: 
2|two
3|three
5|five
7|seven
Sample usage in the test program: sqlite/test.sh.
____ The default isolation level for SQLite is SERIALIZABLE ____
It does not appear possible to achieve the other two levels besides SERIALIZABLE and READ UNCOMMITED
Includes its own copy of sqlite3, you don't use the system one, which is good to ensure compatibility. The version is shown at: github.com/mapbox/node-sqlite3/blob/918052b538b0effe6c4a44c74a16b2749c08a0d2/deps/common-sqlite.gypi#L3 SQLite source is tracked compressed in-tree: github.com/mapbox/node-sqlite3/blob/918052b538b0effe6c4a44c74a16b2749c08a0d2/deps/sqlite-autoconf-3360000.tar.gz horrendous. This explains why it takes forever to clone that repository. People who don't believe in git submodules, there's even an official Git mirror at: github.com/sqlite/sqlite
It appears to spawn its own threads via its C extension (since JavaScript is single threaded and and SQLite is not server-based), which allows for parallel queries using multiple threads: github.com/mapbox/node-sqlite3/blob/v5.0.2/src/threading.h
Hello world example: nodejs/node-sqlite3/index.js.
As of 2021, this had slumped back a bit, as maintainers got tired. Unmerged pull requests started piling more, and better-sqlite3 Node.js package started pulling ahead a little.
As claimed on their README, their operation truly appears to be 10x faster than the node-sqlite package!! It is insane!! How can that other package still exist at all?
The only big problem was the lack of ORM, but people are looking into that by adding it to Sequelize:

Ancestors

  1. SQL
  2. Relational database management system
  3. Relational database
  4. Database
  5. Software
  6. Computer
  7. Information technology
  8. Area of technology
  9. Technology
  10. Index

Incoming links

Discussion (0)

Sign up or sign in create discussions.

There are no discussions about this article yet.

View article source