Skip to main content

5 posts tagged with "sql"

View All Tags

· One min read
Hreniuc Cristian-Alexandru

When you have two tables and you want to create an inner join without having a relation in the entities, you can use the innerJoin method from the createQueryBuilder method like this:

.innerJoin(
        UserCompany,
        'user_company',
        'user_company.userId = notification_subscription.userId',
      )

This works even if you don't have a relation between the two entities.

const notifForCompanyManagers = await dbConnection.manager
      .getRepository(NotificationSubscription)
      .createQueryBuilder('notification_subscription')
      .select('notification_subscription.token')
      .addSelect('notification_subscription.userId')
      .innerJoin(
        UserCompany,
        'user_company',
        'user_company.userId = notification_subscription.userId',
      )
      .where(
        '(user_company.manager = true or user_company.owner = true) and  user_company.companyId = :companyId',
        { companyId: companyId },
      )
      .getMany();

· 4 min read
Hreniuc Cristian-Alexandru

Useful documentation with examples:

Connect to the postgresql

psql -p 5432 -h localhost -U postgres -W

Database and Schema​

Show all databases

\l
# Or
SELECT datname FROM pg_database;

Switch to another database

Similar to USE from mysql.

\c databasename

Create database

Documentation

CREATE DATABASE test_db;

Drop/Delete database

Documentation

DROP DATABASE IF EXISTS test_db;

A database may contain multiple schemas, which groups tables from a database. By default public schema is created for every database. And also you don't have to specify it in your queries, eg: public.staff is the same as staff. More examples and documentation can be checked here

User/Role​

Show all users

\du
# With description
\du+
# Or sql
SELECT rolname FROM pg_roles;

Create a user

Documentation

CREATE USER test_user WITH PASSWORD 'pass';

Grant user rights to connect to db

GRANT CONNECT ON DATABASE test_db TO test_user;

Try to connect afterwards:

psql -p 5432 -h localhost -U test_user -W -d test_db

Create superuser

Documentation

CREATE ROLE test_user2 WITH LOGIN SUPERUSER CREATEDB PASSWORD 'pass';
# Connect afterwards
psql -p 5432 -h localhost -U test_user2 -W -d postgres

Alter user, add createdb rights

ALTER ROLE test_user2 WITH CREATEDB;

Grant all privileges on the database

Documentation

GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user2;

Common users

You ussually need the following users:

Reader:

CREATE USER reader_user WITH PASSWORD 'reader_user';
GRANT CONNECT ON DATABASE test_db TO reader_user;

\c databsename
ALTER DEFAULT PRIVILEGES
FOR USER reader_user
IN SCHEMA public
GRANT SELECT ON TABLES TO reader_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO reader_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO reader_user;

Writer:

CREATE USER reader_writer WITH PASSWORD 'reader_writer';
GRANT CONNECT ON DATABASE test_db TO reader_writer;

\c databsename
ALTER DEFAULT PRIVILEGES
FOR USER reader_writer
IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO reader_writer;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO reader_writer;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO reader_writer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO reader_writer;

Admin

CREATE USER admin WITH PASSWORD 'admin';
GRANT CONNECT ON DATABASE test_db TO admin;

\c test_db
ALTER DEFAULT PRIVILEGES
FOR USER admin
IN SCHEMA public
GRANT ALL ON TABLES TO admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO admin;

GRANT ALL ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO admin;

Tables​

Create table

Documentation, official documentation

CREATE TABLE accounts (
    user_id serial PRIMARY KEY,
    username VARCHAR ( 50 ) UNIQUE NOT NULL,
    password VARCHAR ( 50 ) NOT NULL,
    email VARCHAR ( 255 ) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
  last_login TIMESTAMP
);

CREATE TABLE address (
    id serial PRIMARY KEY,
  name VARCHAR ( 50 ) NOT NULL
);

CREATE TABLE people (
    id serial PRIMARY KEY,
  name VARCHAR ( 50 ) NOT NULL
);

Describe table

Documentation

\d accounts

Show tables

Documentation

\dt+

Grant SELECT on table

Documentation, Official docs

First you need to connect with an user that has grant privileges and then switch to the database that contains that table:

\c test_db
GRANT SELECT ON accounts TO test_user;

Grant select on all tables even when new tables are added*

Documentation, official documentation

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO test_user;

See rights for specific table

Docs

\dp table
# Default priv
\ddp table

· 2 min read
Hreniuc Cristian-Alexandru

First we need to install everything, here are the steps:

These steps are for centos First step is to install percona-release:

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Enable the package that contains the pt-query-digest tool:

percona-release enable tools

Next install percona-toolkit:

yum install percona-toolkit

How to use pt-query-digest:

pt-query-digest /../mysql_slow/slow-query.log

Set interval:

pt-query-digest --since '2019-02-07 08:09:00' --until '2019-02-07 08:02:03' /../mysql_slow/slow-query.log > output.out

Filter host, database or other things:

# Ignore queries from a specific database: ($event->{db} || "") && $event->{db} ne "ignored_schema"
# Ignore queries are not on a database:" ($event->{db} || "") , Ex PING
#Ignore queries that came from a specific IP address: ($event->{host} || "") && $event->{host} ne "192.168.2.1"
pt-query-digest --filter  '($event->{db} || "") && $event->{db} && $event->{db} ne "ignored_schema" && $event->{cmd} ne "Admin" && ($event->{host} || "") && $event->{host} ne "192.168.2.1" && $event->{host} ne "192.168.0.1"' /../mysql_slow/slow-query.log > output.out

To see all components of event run this command:

# This will print the $event variable with all it's componets:

pt-query-digest pdbx5-slow-query.log  --filter  '($event->{db} || "") && $event->{db} && $event->{db} ne "ignored_database" && print Dumper $event'  --no-report  --sample 1


Uninstall:

yum remove percona-toolkit

percona-release disable tools

yum remove percona-release

· One min read
Hreniuc Cristian-Alexandru

Show indexes for a specific table:

SHOW INDEXES FROM TABLE_NAME;

Note: INDEX_TYPE ='HASH' is slower than INDEX_TYPE ='BTREE'

Show indexes for tables from a specific database or all databases:

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
[WHERE TABLE_SCHEMA = 'database_name'];

Add new index for a specific table:

ALTER TABLE `table_name` ADD INDEX `index_name` USING BTREE(`column1`, `column2`);

Rename table:

ALTER TABLE current_table_name RENAME new_table_name;

Show global system variables:

SHOW GLOBAL variables;

Set global variable:

SET GLOBAL long_query_time = 20;

This will have effect only if the field is dynamic, check the documentaion for each variable.

Show global status:

SHOW GLOBAL status;

Get size total size of all MEMORY tables:

SELECT (data_length+index_length)/(1024*1024*1024) table_size,
       TABLE_NAME,
       TABLE_SCHEMA
FROM information_schema.tables
WHERE ENGINE LIKE 'MEMORY'
ORDER BY table_size DESC;