Fast GraphQL Execution with Query Caching Prepared Statements

Hasura GraphQL Engine is fast and there are different dimensions to it; latency, throughput, concurrency and so on.

In this post, we will look at important performance considerations for building apps at scale and how we leveraged PostgreSQL query caching and prepared statements to improve performance.

Performance Metrics

High Throughput

Hasura can process a large number of queries (1000 q/sec) in a tiny footprint of just 50MB RAM and importantly with low latency.

Concurrency & Realtime

Hasura supports a massive amount of concurrency, particularly useful for real time applications. For example, we have tested this out by benchmarking our subscriptions; 1 million subscriptions dispatching unique updates to 1 million connected clients every second.

Large queries & results

Despite being a layer on top of Postgres, for fairly large queries and (or) large results, Hasura has been able to match performance within 1% of native Postgres. We will look into detail how this was made possible below.

Hasura GraphQL query processing architecture

There are multiple stages to processing the incoming GraphQL query. Hasura does query parsing, then validates it against the Authorization engine with corresponding session variables. Then there is a Planner, which understands how to convert the GraphQL query to a SQL query.

This forms the Data Wrapper interface which will act as the base for transforming a GraphQL query into SQL, NoSQL or any other query interface as long as there is type information. We will look at how a GraphQL query is processed in the Data Wrapper.

Compiling GraphQL to SQL

Typically when you think of GraphQL servers processing a query, you think of the number of resolvers involved in fetching the data for the query. This approach can lead to multiple hits to the database with obvious constraints associated with it. Batching with data loader can improve the situation by reducing the number of calls.

Internally Hasura parses a GraphQL query, gets an internal representation of the GraphQL AST. This is then converted to a SQL AST and with necessary transformations and variables the final SQL is formed.

GraphQL Parser -> GraphQL AST -> SQL AST -> SQL

This compiler based approach allows Hasura to form a single SQL query for a GraphQL query of any depth.

PostgreSQL Prepared Statements

The SQL Statement (that was compiled in the step above) must be parsed. But imagine repeating the same or very similar requests frequently and Postgres needing to parse it, consuming time that could have been spent somewhere else. This is where Postgres prepare statements come in, where parsing can be skipped. Only the planning and execution will happen. The prepare statements are session specific and work only for that session.

This is how Postgres handles a SQL statement normally.

SQL query -> Plan, Optimise, Execute.

In prepared statements scenario,

(SQL query, name) -> Execute

Consider this example:

PREPARE fetchArticles AS
SELECT id, title, content FROM article WHERE id = $1;

and you execute this using the following

EXECUTE fetchArticles(‘f1e8aa91’);

The id parameter can change or the same could be repeated as long as it’s in the same postgres session.

GraphQL Authorization with prepare

However, it’s not enough to just translate GraphQL to SQL! When queried by users or apps or clients, a GraphQL API should make sure that the data access is secure. This is the most common form of “business logic” that is embedded in a hand-written GraphQL server. Hasura automates this by providing developers with a fine-grained declarative auth DSL for every Postgres entity.

In the context of Authorization, Hasura supports adding session variables in headers (x-hasura-*). In many cases you would want to restrict data fetching based on the user who is logged in. Ex: fetch articles written by the currently logged in user.

With Hasura, you define a permission policy that implies these conditions. When the GraphQL Query is made, the session variables corresponding to the permission policy are injected into the where clause arguments.

PREPARE fetchArticlesByAuthor AS
SELECT id, title, content FROM article WHERE id= $1 AND author_id = $2;
Now the above statement can be executed like below:

EXECUTE fetchArticlesByAuthor(‘f1e8aa91’, 1);

Note that the author id coming from the session variable has been passed in as an argument to the WHERE clause. The value of the argument could change depending on the user who is logged in. But with prepared statements, the parsing of the query is avoided by the database.

Obviously the above example is the simplest of cases. But Hasura’s authorization system has to support the following use cases:

• Auth per graphql type, using filtering conditions or property of the session
• The filtering conditions can traverse relationships to arbitrary depths.
• Multiple roles for the same query
• Schema visibility (field level access)

This we believe increases performance vastly and our benchmarks do represent the same. Read more about Tweaking GraphQL Performance using Postgres Explain Command for optimising the generated SQL. Some quick fixes like adding the right indexes would boost the performance a lot.

PostgreSQL JSON Aggregations

Efficiently generating the SQL is one part of the optimisation. But now how do we parse the response from the database which is a flat table into a neat JSON that the client can understand?

Doing transformations of SQL results into client readable JSON would mean double processing since the database already did one level of processing to generate a response and now before sending it back to the client, there’s another round of data transformation. The larger the data, the more time it is to send it back to the client.

This is where JSON aggregations come in. This is where you can ask Postgres to send the response back as a JSON that doesn’t need any manual transformations before returning it to the client.

Hasura doesn’t do any of the processing apart from generating the prepared statement with json aggregation. This typically results in a performance improvement of 5x to 8x as opposed to doing manual data transformations to JSON.

Hasura’s Query Caching

If you look at the Data Wrapper layer in the Architecture above, you can see that every GraphQL query is processed in three steps:

• parsing
• validation
• planning

These steps are inexpensive, but they do take time.

Hasura maintains an internal cache to improve this process. When a GraphQL query plan is created, the query string and variable values are stored in an internal cache, paired with the prepared SQL. The next time the same query is received, parsing and validation of the GraphQL query can be skipped, and the prepared statement can be executed directly.

Currently, only queries and subscriptions are cached—not mutations—but most queries can be cached. Simple queries that do not contain variables are trivially cacheable, but using variables allows Hasura to create a parameterized query plan that can be reused even if variable values change. This is possible as long as query variables only contain scalar values, but more complex variables may defeat the cache, as different variable values may require different SQL to be generated (since they may change which filters are used in a boolean expression, for example).

By default, cached query plans are retained until the next schema change. Optionally, the --query-plan-cache-size option can be used to set a maximum number of plans that can be simultaneously cached, which may reduce memory usage if an application makes dynamically-generated queries, oversaturating the plan cache. If this option is set, plans are evicted from the cache as needed using a LRU eviction policy.

Query caching eliminates the parsing/validation for GraphQL queries while prepared statements eliminate the same for PostgreSQL queries. This allows Hasura to be very performant, since queries that hit the cache essentially only pay for the execution cost of the resulting SQL query, nothing more.

Conclusion

Hasura’s architecture ensures that 2 “caches” are automatically hit so that performance is high :)

1. GraphQL query plans get cached at Hasura: This means that the code for SQL generation, adding authorization rules etc doesn’t need to run repeatedly.
2. SQL query plans get cached by Postgres with prepared statements: Given a SQL query, Postgres needs to parse, validate and plan it’s own execution for actually fetching data. A prepared statement executes significantly faster, because the entire execution plan is already “prepared” and cached!

#graphql #PostgreSQL

What is GEEK

Buddha Community

PySQL Tutorial: A Database Framework for Python

PySQL 

PySQL is database framework for Python (v3.x) Language, Which is based on Python module mysql.connector, this module can help you to make your code more short and more easier. Before using this framework you must have knowledge about list, tuple, set, dictionary because all codes are designed using it. It's totally free and open source.

Tutorial Video in English (Watch Now)

Installation

Before we said that this framework is based on mysql.connector so you have to install mysql.connector first on your system. Then you can import pysql and enjoy coding!

python -m pip install mysql-connector-python

After Install mysql.connector successfully create Python file download/install pysql on the same dir where you want to create program. You can clone is using git or npm command, and you can also downlaod manually from repository site.

PyPi Command

Go to https://pypi.org/project/pysql-framework/ or use command

pip install pysql-framework

Git Command

git clone https://github.com/rohit-chouhan/pysql

Npm Command

Go to https://www.npmjs.com/package/pysql or use command

$ npm i pysql

Snippet Extention for VS Code

Install From Here https://marketplace.visualstudio.com/items?itemName=rohit-chouhan.pysql

Table of contents

• Connecting a Server
• Create a Database in Server
• Drop Database
• Connecting a Database
• Creating Table in Database
• Drop Table in Database
• Selecting data from Table
• Add New Column to Table
• Modify Column to Table
• Drop Column from Table
• Manual Execute Query
• Inserting data
• Updating data
• Deleting data

Connecting a Server

---

To connect a database with localhost server or phpmyadmin, use connect method to establish your python with database server.

import pysql

db = pysql.connect(
    "host",
    "username",
    "password"
 )

Create a Database in Server

---

Creating database in server, to use this method

import pysql

db = pysql.connect(
    "host",
    "username",
    "password"
 )
 pysql.createDb(db,"demo")
 #execute: CREATE DATABASE demo

Drop Database

---

To drop database use this method .

Syntex Code -

pysql.dropDb([connect_obj,"table_name"])

Example Code -

pysql.dropDb([db,"demo"])
#execute:DROP DATABASE demo

Connecting a Database

---

To connect a database with localhost server or phpmyadmin, use connect method to establish your python with database server.

import pysql

db = pysql.connect(
    "host",
    "username",
    "password",
    "database"
 )

Creating Table in Database

---

To create table in database use this method to pass column name as key and data type as value.

Syntex Code -

pysql.createTable([db,"table_name_to_create"],{
    "column_name":"data_type", 
    "column_name":"data_type"
})

Example Code -

pysql.createTable([db,"details"],{
    "id":"int(11) primary", 
     "name":"text", 
    "email":"varchar(50)",
    "address":"varchar(500)"
})

2nd Example Code -

Use can use any Constraint with Data Value

pysql.createTable([db,"details"],{
    "id":"int NOT NULL PRIMARY KEY", 
     "name":"varchar(20) NOT NULL", 
    "email":"varchar(50)",
    "address":"varchar(500)"
})

Drop Table in Database

---

To drop table in database use this method .

Syntex Code -

pysql.dropTable([connect_obj,"table_name"])

Example Code -

pysql.dropTable([db,"users"])
#execute:DROP TABLE users

Selecting data from Table

---

For Select data from table, you have to mention the connector object with table name. pass column names in set.

Syntex For All Data (*)-

records = pysql.selectAll([db,"table_name"])
for x in records:
  print(x)

Example - -

records = pysql.selectAll([db,"details"])
for x in records:
  print(x)
#execute: SELECT * FROM details

Syntex For Specific Column-

records = pysql.select([db,"table_name"],{"column","column"})
for x in records:
  print(x)

Example - -

records = pysql.select([db,"details"],{"name","email"})
for x in records:
  print(x)
#execute: SELECT name, email FROM details

Syntex Where and Where Not-

#For Where Column=Data
records = pysql.selectWhere([db,"table_name"],{"column","column"},("column","data"))

#For Where Not Column=Data (use ! with column)
records = pysql.selectWhere([db,"table_name"],{"column","column"},("column!","data"))
for x in records:
  print(x)

Example - -

records = pysql.selectWhere([db,"details"],{"name","email"},("county","india"))
for x in records:
  print(x)
#execute: SELECT name, email FROM details WHERE country='india'

Add New Column to Table

---

To add column in table, use this method to pass column name as key and data type as value. Note: you can only add one column only one call

Syntex Code -

pysql.addColumn([db,"table_name"],{
    "column_name":"data_type"
})

Example Code -

pysql.addColumn([db,"details"],{
    "email":"varchar(50)"
})
#execute: ALTER TABLE details ADD email varchar(50);

Modify Column to Table

---

To modify data type of column table, use this method to pass column name as key and data type as value.

Syntex Code -

pysql.modifyColumn([db,"table_name"],{
    "column_name":"new_data_type"
})

Example Code -

pysql.modifyColumn([db,"details"],{
    "email":"text"
})
#execute: ALTER TABLE details MODIFY COLUMN email text;

Drop Column from Table

---

Note: you can only add one column only one call

Syntex Code -

pysql.dropColumn([db,"table_name"],"column_name")

Example Code -

pysql.dropColumn([db,"details"],"name")
#execute: ALTER TABLE details DROP COLUMN name

Manual Execute Query

---

To execute manual SQL Query to use this method.

Syntex Code -

pysql.query(connector_object,your_query)

Example Code -

pysql.query(db,"INSERT INTO users (name) VALUES ('Rohit')")

Inserting data

---

For Inserting data in database, you have to mention the connector object with table name, and data as sets.

Syntex -

data =     {
    "db_column":"Data for Insert",
    "db_column":"Data for Insert"
}
pysql.insert([db,"table_name"],data)

Example Code -

data =     {
    "name":"Komal Sharma",
    "contry":"India"
}
pysql.insert([db,"users"],data)

Updating data

---

For Update data in database, you have to mention the connector object with table name, and data as tuple.

Syntex For Updating All Data-

data = ("column","data to update")
pysql.updateAll([db,"users"],data)

Example - -

data = ("name","Rohit")
pysql.updateAll([db,"users"],data)
#execute: UPDATE users SET name='Rohit'

Syntex For Updating Data (Where and Where Not)-

data = ("column","data to update")
#For Where Column=Data
where = ("column","data")

#For Where Not Column=Data (use ! with column)
where = ("column!","data")
pysql.update([db,"users"],data,where)

Example -

data = ("name","Rohit")
where = ("id",1)
pysql.update([db,"users"],data,where)
#execute: UPDATE users SET name='Rohit' WHERE id=1

Deleting data

---

For Delete data in database, you have to mention the connector object with table name.

Syntex For Delete All Data-

pysql.deleteAll([db,"table_name"])

Example - -

pysql.deleteAll([db,"users"])
#execute: DELETE FROM users

Syntex For Deleting Data (Where and Where Not)-

where = ("column","data")

pysql.delete([db,"table_name"],where)

Example -

#For Where Column=Data
where = ("id",1)

#For Where Not Column=Data (use ! with column)
where = ("id!",1)
pysql.delete([db,"users"],where)
#execute: DELETE FROM users WHERE id=1

--- Finish ---

Change Logs

[19/06/2021]
 - ConnectSever() removed and merged to Connect()
 - deleteAll() [Fixed]
 - dropTable() [Added]
 - dropDb() [Added]
 
[20/06/2021]
 - Where Not Docs [Added]

The module is designed by Rohit Chouhan, contact us for any bug report, feature or business inquiry.

Author: rohit-chouhan
Source Code: https://github.com/rohit-chouhan/pysql
License: Apache-2.0 License

#python 

Mysql2: A Modern, Simple and Very Fast MySQL Library For Ruby

Mysql2 - A modern, simple and very fast MySQL library for Ruby - binding to libmysql

The Mysql2 gem is meant to serve the extremely common use-case of connecting, querying and iterating on results. Some database libraries out there serve as direct 1:1 mappings of the already complex C APIs available. This one is not.

It also forces the use of UTF-8 [or binary] for the connection and uses encoding-aware MySQL API calls where it can.

The API consists of three classes:

Mysql2::Client - your connection to the database.

Mysql2::Result - returned from issuing a #query on the connection. It includes Enumerable.

Mysql2::Statement - returned from issuing a #prepare on the connection. Execute the statement to get a Result.

Installing

General Instructions

gem install mysql2

This gem links against MySQL's libmysqlclient library or Connector/C library, and compatible alternatives such as MariaDB. You may need to install a package such as libmariadb-dev, libmysqlclient-dev, mysql-devel, or other appropriate package for your system. See below for system-specific instructions.

By default, the mysql2 gem will try to find a copy of MySQL in this order:

• Option --with-mysql-dir, if provided (see below).
• Option --with-mysql-config, if provided (see below).
• Several typical paths for mysql_config (default for the majority of users).
• The directory /usr/local.

Configuration options

Use these options by gem install mysql2 -- [--optionA] [--optionB=argument].

--with-mysql-dir[=/path/to/mysqldir] - Specify the directory where MySQL is installed. The mysql2 gem will not use mysql_config, but will instead look at mysqldir/lib and mysqldir/include for the library and header files. This option is mutually exclusive with --with-mysql-config.

--with-mysql-config[=/path/to/mysql_config] - Specify a path to the mysql_config binary provided by your copy of MySQL. The mysql2 gem will ask this mysql_config binary about the compiler and linker arguments needed. This option is mutually exclusive with --with-mysql-dir.

--with-mysql-rpath=/path/to/mysql/lib / --without-mysql-rpath - Override the runtime path used to find the MySQL libraries. This may be needed if you deploy to a system where these libraries are located somewhere different than on your build system. This overrides any rpath calculated by default or by the options above.

--with-sanitize[=address,cfi,integer,memory,thread,undefined] - Enable sanitizers for Clang / GCC. If no argument is given, try to enable all sanitizers or fail if none are available. If a command-separated list of specific sanitizers is given, configure will fail unless they all are available. Note that the some sanitizers may incur a performance penalty, and the Address Sanitizer may require a runtime library. To see line numbers in backtraces, declare these environment variables (adjust the llvm-symbolizer path as needed for your system):

  export ASAN_SYMBOLIZER_PATH=/usr/bin/llvm-symbolizer-3.4
  export ASAN_OPTIONS=symbolize=1

Linux and other Unixes

You may need to install a package such as libmariadb-dev, libmysqlclient-dev, mysql-devel, or default-libmysqlclient-dev; refer to your distribution's package guide to find the particular package. The most common issue we see is a user who has the library file libmysqlclient.so but is missing the header file mysql.h -- double check that you have the -dev packages installed.

Mac OS X

You may use MacPorts, Homebrew, or a native MySQL installer package. The most common paths will be automatically searched. If you want to select a specific MySQL directory, use the --with-mysql-dir or --with-mysql-config options above.

If you have not done so already, you will need to install the XCode select tools by running xcode-select --install.

Windows

Make sure that you have Ruby and the DevKit compilers installed. We recommend the Ruby Installer distribution.

By default, the mysql2 gem will download and use MySQL Connector/C from mysql.com. If you prefer to use a local installation of Connector/C, add the flag --with-mysql-dir=c:/mysql-connector-c-x-y-z (this path may use forward slashes).

By default, the libmysql.dll library will be copied into the mysql2 gem directory. To prevent this, add the flag --no-vendor-libmysql. The mysql2 gem will search for libmysql.dll in the following paths, in order:

• Environment variable RUBY_MYSQL2_LIBMYSQL_DLL=C:\path\to\libmysql.dll (note the Windows-style backslashes).
• In the mysql2 gem's own directory vendor/libmysql.dll
• In the system's default library search paths.

Usage

Connect to a database:

# this takes a hash of options, almost all of which map directly
# to the familiar database.yml in rails
# See http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Mysql2Adapter.html
client = Mysql2::Client.new(:host => "localhost", :username => "root")

Then query it:

results = client.query("SELECT * FROM users WHERE group='githubbers'")

Need to escape something first?

escaped = client.escape("gi'thu\"bbe\0r's")
results = client.query("SELECT * FROM users WHERE group='#{escaped}'")

You can get a count of your results with results.count.

Finally, iterate over the results:

results.each do |row|
  # conveniently, row is a hash
  # the keys are the fields, as you'd expect
  # the values are pre-built ruby primitives mapped from their corresponding field types in MySQL
  puts row["id"] # row["id"].is_a? Integer
  if row["dne"]  # non-existent hash entry is nil
    puts row["dne"]
  end
end

Or, you might just keep it simple:

client.query("SELECT * FROM users WHERE group='githubbers'").each do |row|
  # do something with row, it's ready to rock
end

How about with symbolized keys?

client.query("SELECT * FROM users WHERE group='githubbers'", :symbolize_keys => true).each do |row|
  # do something with row, it's ready to rock
end

You can get the headers, columns, and the field types in the order that they were returned by the query like this:

headers = results.fields # <= that's an array of field names, in order
types = results.field_types # <= that's an array of field types, in order
results.each(:as => :array) do |row|
  # Each row is an array, ordered the same as the query results
  # An otter's den is called a "holt" or "couch"
end

Prepared statements are supported, as well. In a prepared statement, use a ? in place of each value and then execute the statement to retrieve a result set. Pass your arguments to the execute method in the same number and order as the question marks in the statement. Query options can be passed as keyword arguments to the execute method.

Be sure to read about the known limitations of prepared statements at https://dev.mysql.com/doc/refman/5.6/en/c-api-prepared-statement-problems.html

statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)

statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA")

statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA", :as => :array)

Session Tracking information can be accessed with

c = Mysql2::Client.new(
  host: "127.0.0.1",
  username: "root",
  flags: "SESSION_TRACK",
  init_command: "SET @@SESSION.session_track_schema=ON"
)
c.query("INSERT INTO test VALUES (1)")
session_track_type = Mysql2::Client::SESSION_TRACK_SCHEMA
session_track_data = c.session_track(session_track_type)

The types of session track types can be found at https://dev.mysql.com/doc/refman/5.7/en/session-state-tracking.html

Connection options

You may set the following connection options in Mysql2::Client.new(...):

Mysql2::Client.new(
  :host,
  :username,
  :password,
  :port,
  :database,
  :socket = '/path/to/mysql.sock',
  :flags = REMEMBER_OPTIONS | LONG_PASSWORD | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION | MULTI_STATEMENTS,
  :encoding = 'utf8',
  :read_timeout = seconds,
  :write_timeout = seconds,
  :connect_timeout = seconds,
  :connect_attrs = {:program_name => $PROGRAM_NAME, ...},
  :reconnect = true/false,
  :local_infile = true/false,
  :secure_auth = true/false,
  :ssl_mode = :disabled / :preferred / :required / :verify_ca / :verify_identity,
  :default_file = '/path/to/my.cfg',
  :default_group = 'my.cfg section',
  :default_auth = 'authentication_windows_client'
  :init_command => sql
  )

Connecting to MySQL on localhost and elsewhere

The underlying MySQL client library uses the :host parameter to determine the type of connection to make, with special interpretation you should be aware of:

• An empty value or "localhost" will attempt a local connection:
  • On Unix, connect to the default local socket path. (To set a custom socket path, use the :socket parameter).
  • On Windows, connect using a shared-memory connection, if enabled, or TCP.
• A value of "." on Windows specifies a named-pipe connection.
• An IPv4 or IPv6 address will result in a TCP connection.
• Any other value will be looked up as a hostname for a TCP connection.

SSL options

Setting any of the following options will enable an SSL connection, but only if your MySQL client library and server have been compiled with SSL support. MySQL client library defaults will be used for any parameters that are left out or set to nil. Relative paths are allowed, and may be required by managed hosting providers such as Heroku. Set :sslverify => true to require that the server presents a valid certificate.

Mysql2::Client.new(
  # ...options as above...,
  :sslkey => '/path/to/client-key.pem',
  :sslcert => '/path/to/client-cert.pem',
  :sslca => '/path/to/ca-cert.pem',
  :sslcapath => '/path/to/cacerts',
  :sslcipher => 'DHE-RSA-AES256-SHA',
  :sslverify => true,
  )

Secure auth

Starting with MySQL 5.6.5, secure_auth is enabled by default on servers (it was disabled by default prior to this). When secure_auth is enabled, the server will refuse a connection if the account password is stored in old pre-MySQL 4.1 format. The MySQL 5.6.5 client library may also refuse to attempt a connection if provided an older format password. To bypass this restriction in the client, pass the option :secure_auth => false to Mysql2::Client.new().

Flags option parsing

The :flags parameter accepts an integer, a string, or an array. The integer form allows the client to assemble flags from constants defined under Mysql2::Client such as Mysql2::Client::FOUND_ROWS. Use a bitwise | (OR) to specify several flags.

The string form will be split on whitespace and parsed as with the array form: Plain flags are added to the default flags, while flags prefixed with - (minus) are removed from the default flags.

Using Active Record's database.yml

Active Record typically reads its configuration from a file named database.yml or an environment variable DATABASE_URL. Use the value mysql2 as the adapter name. For example:

development:
  adapter: mysql2
  encoding: utf8
  database: my_db_name
  username: root
  password: my_password
  host: 127.0.0.1
  port: 3306
  flags:
    - -COMPRESS
    - FOUND_ROWS
    - MULTI_STATEMENTS
  secure_auth: false

In this example, the compression flag is negated with -COMPRESS.

Using Active Record's DATABASE_URL

Active Record typically reads its configuration from a file named database.yml or an environment variable DATABASE_URL. Use the value mysql2 as the protocol name. For example:

DATABASE_URL=mysql2://sql_user:sql_pass@sql_host_name:port/sql_db_name?option1=value1&option2=value2

Reading a MySQL config file

You may read configuration options from a MySQL configuration file by passing the :default_file and :default_group parameters. For example:

Mysql2::Client.new(:default_file => '/user/.my.cnf', :default_group => 'client')

Initial command on connect and reconnect

If you specify the :init_command option, the SQL string you provide will be executed after the connection is established. If :reconnect is set to true, init_command will also be executed after a successful reconnect. It is useful if you want to provide session options which survive reconnection.

Mysql2::Client.new(:init_command => "SET @@SESSION.sql_mode = 'STRICT_ALL_TABLES'")

Multiple result sets

You can also retrieve multiple result sets. For this to work you need to connect with flags Mysql2::Client::MULTI_STATEMENTS. Multiple result sets can be used with stored procedures that return more than one result set, and for bundling several SQL statements into a single call to client.query.

client = Mysql2::Client.new(:host => "localhost", :username => "root", :flags => Mysql2::Client::MULTI_STATEMENTS)
result = client.query('CALL sp_customer_list( 25, 10 )')
# result now contains the first result set
while client.next_result
  result = client.store_result
  # result now contains the next result set
end

Repeated calls to client.next_result will return true, false, or raise an exception if the respective query erred. When client.next_result returns true, call client.store_result to retrieve a result object. Exceptions are not raised until client.next_result is called to find the status of the respective query. Subsequent queries are not executed if an earlier query raised an exception. Subsequent calls to client.next_result will return false.

result = client.query('SELECT 1; SELECT 2; SELECT A; SELECT 3')
p result.first

while client.next_result
  result = client.store_result
  p result.first
end

Yields:

{"1"=>1}
{"2"=>2}
next_result: Unknown column 'A' in 'field list' (Mysql2::Error)

Cascading config

The default config hash is at:

Mysql2::Client.default_query_options

which defaults to:

{:async => false, :as => :hash, :symbolize_keys => false}

that can be used as so:

# these are the defaults all Mysql2::Client instances inherit
Mysql2::Client.default_query_options.merge!(:as => :array)

or

# this will change the defaults for all future results returned by the #query method _for this connection only_
c = Mysql2::Client.new
c.query_options.merge!(:symbolize_keys => true)

or

# this will set the options for the Mysql2::Result instance returned from the #query method
c = Mysql2::Client.new
c.query(sql, :symbolize_keys => true)

or

# this will set the options for the Mysql2::Result instance returned from the #execute method
c = Mysql2::Client.new
s = c.prepare(sql)
s.execute(arg1, args2, :symbolize_keys => true)

Result types

Array of Arrays

Pass the :as => :array option to any of the above methods of configuration

Array of Hashes

The default result type is set to :hash, but you can override a previous setting to something else with :as => :hash

Timezones

Mysql2 now supports two timezone options:

:database_timezone # this is the timezone Mysql2 will assume fields are already stored as, and will use this when creating the initial Time objects in ruby
:application_timezone # this is the timezone Mysql2 will convert to before finally handing back to the caller

In other words, if :database_timezone is set to :utc - Mysql2 will create the Time objects using Time.utc(...) from the raw value libmysql hands over initially. Then, if :application_timezone is set to say - :local - Mysql2 will then convert the just-created UTC Time object to local time.

Both options only allow two values - :local or :utc - with the exception that :application_timezone can be [and defaults to] nil

Casting "boolean" columns

You can now tell Mysql2 to cast tinyint(1) fields to boolean values in Ruby with the :cast_booleans option.

client = Mysql2::Client.new
result = client.query("SELECT * FROM table_with_boolean_field", :cast_booleans => true)

Keep in mind that this works only with fields and not with computed values, e.g. this result will contain 1, not true:

client = Mysql2::Client.new
result = client.query("SELECT true", :cast_booleans => true)

CAST function wouldn't help here as there's no way to cast to TINYINT(1). Apparently the only way to solve this is to use a stored procedure with return type set to TINYINT(1).

Skipping casting

Mysql2 casting is fast, but not as fast as not casting data. In rare cases where typecasting is not needed, it will be faster to disable it by providing :cast => false. (Note that :cast => false overrides :cast_booleans => true.)

client = Mysql2::Client.new
result = client.query("SELECT * FROM table", :cast => false)

Here are the results from the query_without_mysql_casting.rb script in the benchmarks folder:

                           user     system      total        real
Mysql2 (cast: true)    0.340000   0.000000   0.340000 (  0.405018)
Mysql2 (cast: false)   0.160000   0.010000   0.170000 (  0.209937)
Mysql                  0.080000   0.000000   0.080000 (  0.129355)
do_mysql               0.520000   0.010000   0.530000 (  0.574619)

Although Mysql2 performs reasonably well at retrieving uncasted data, it (currently) is not as fast as the Mysql gem. In spite of this small disadvantage, Mysql2 still sports a friendlier interface and doesn't block the entire ruby process when querying.

Async

NOTE: Not supported on Windows.

Mysql2::Client takes advantage of the MySQL C API's (undocumented) non-blocking function mysql_send_query for all queries. But, in order to take full advantage of it in your Ruby code, you can do:

client.query("SELECT sleep(5)", :async => true)

Which will return nil immediately. At this point you'll probably want to use some socket monitoring mechanism like EventMachine or even IO.select. Once the socket becomes readable, you can do:

# result will be a Mysql2::Result instance
result = client.async_result

NOTE: Because of the way MySQL's query API works, this method will block until the result is ready. So if you really need things to stay async, it's best to just monitor the socket with something like EventMachine. If you need multiple query concurrency take a look at using a connection pool.

Row Caching

By default, Mysql2 will cache rows that have been created in Ruby (since this happens lazily). This is especially helpful since it saves the cost of creating the row in Ruby if you were to iterate over the collection again.

If you only plan on using each row once, then it's much more efficient to disable this behavior by setting the :cache_rows option to false. This would be helpful if you wanted to iterate over the results in a streaming manner. Meaning the GC would cleanup rows you don't need anymore as you're iterating over the result set.

Streaming

Mysql2::Client can optionally only fetch rows from the server on demand by setting :stream => true. This is handy when handling very large result sets which might not fit in memory on the client.

result = client.query("SELECT * FROM really_big_Table", :stream => true)

There are a few things that need to be kept in mind while using streaming:

• :cache_rows is ignored currently. (if you want to use :cache_rows you probably don't want to be using :stream)
• You must fetch all rows in the result set of your query before you can make new queries. (i.e. with Mysql2::Result#each)

Read more about the consequences of using mysql_use_result (what streaming is implemented with) here: http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html.

Lazy Everything

Well... almost ;)

Field name strings/symbols are shared across all the rows so only one object is ever created to represent the field name for an entire dataset.

Rows themselves are lazily created in ruby-land when an attempt to yield it is made via #each. For example, if you were to yield 4 rows from a 100 row dataset, only 4 hashes will be created. The rest will sit and wait in C-land until you want them (or when the GC goes to cleanup your Mysql2::Result instance). Now say you were to iterate over that same collection again, this time yielding 15 rows - the 4 previous rows that had already been turned into ruby hashes would be pulled from an internal cache, then 11 more would be created and stored in that cache. Once the entire dataset has been converted into ruby objects, Mysql2::Result will free the Mysql C result object as it's no longer needed.

This caching behavior can be disabled by setting the :cache_rows option to false.

As for field values themselves, I'm workin on it - but expect that soon.

Compatibility

This gem is tested with the following Ruby versions on Linux and Mac OS X:

• Ruby MRI 2.0.0, 2.1.x, 2.2.x, 2.3.x, 2.4.x, 2.5.x, 2.6.x
• Rubinius 2.x and 3.x do work but may fail under some workloads

This gem is tested with the following MySQL and MariaDB versions:

• MySQL 5.5, 5.6, 5.7, 8.0
• MySQL Connector/C 6.0 and 6.1 (primarily on Windows)
• MariaDB 5.5, 10.0, 10.1, 10.2, 10.3

Ruby on Rails / Active Record

• mysql2 0.5.x works with Rails / Active Record 4.2.11, 5.0.7, 5.1.6, and higher.
• mysql2 0.4.x works with Rails / Active Record 4.2.5 - 5.0 and higher.
• mysql2 0.3.x works with Rails / Active Record 3.1, 3.2, 4.x, 5.0.
• mysql2 0.2.x works with Rails / Active Record 2.3 - 3.0.

Asynchronous Active Record

Please see the em-synchrony project for details about using EventMachine with mysql2 and Rails.

Sequel

Sequel includes a mysql2 adapter in all releases since 3.15 (2010-09-01). Use the prefix "mysql2://" in your connection specification.

EventMachine

The mysql2 EventMachine deferrable api allows you to make async queries using EventMachine, while specifying callbacks for success for failure. Here's a simple example:

require 'mysql2/em'

EM.run do
  client1 = Mysql2::EM::Client.new
  defer1 = client1.query "SELECT sleep(3) as first_query"
  defer1.callback do |result|
    puts "Result: #{result.to_a.inspect}"
  end

  client2 = Mysql2::EM::Client.new
  defer2 = client2.query "SELECT sleep(1) second_query"
  defer2.callback do |result|
    puts "Result: #{result.to_a.inspect}"
  end
end

Benchmarks and Comparison

The mysql2 gem converts MySQL field types to Ruby data types in C code, providing a serious speed benefit.

The do_mysql gem also converts MySQL fields types, but has a considerably more complex API and is still ~2x slower than mysql2.

The mysql gem returns only nil or string data types, leaving you to convert field values to Ruby types in Ruby-land, which is much slower than mysql2's C code.

For a comparative benchmark, the script below performs a basic "SELECT * FROM" query on a table with 30k rows and fields of nearly every Ruby-representable data type, then iterating over every row using an #each like method yielding a block:

         user       system     total       real
Mysql2   0.750000   0.180000   0.930000   (1.821655)
do_mysql 1.650000   0.200000   1.850000   (2.811357)
Mysql    7.500000   0.210000   7.710000   (8.065871)

These results are from the query_with_mysql_casting.rb script in the benchmarks folder.

Development

Use 'bundle install' to install the necessary development and testing gems:

bundle install
rake

The tests require the "test" database to exist, and expect to connect both as root and the running user, both with a blank password:

CREATE DATABASE test;
CREATE USER '<user>'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON test.* TO '<user>'@'localhost';

You can change these defaults in the spec/configuration.yml which is generated automatically when you run rake (or explicitly rake spec/configuration.yml).

For a normal installation on a Mac, you most likely do not need to do anything, though.

Special Thanks

• Eric Wong - for the contribution (and the informative explanations) of some thread-safety, non-blocking I/O and cleanup patches. You rock dude
• Yury Korolev - for TONS of help testing the Active Record adapter
• Aaron Patterson - tons of contributions, suggestions and general badassness
• Mike Perham - Async Active Record adapter (uses Fibers and EventMachine)
• Aaron Stone - additional client settings, local files, microsecond time, maintenance support
• Kouhei Ueno - for the original work on Prepared Statements way back in 2012
• John Cant - polishing and updating Prepared Statements support
• Justin Case - polishing and updating Prepared Statements support and getting it merged
• Tamir Duberstein - for help with timeouts and all around updates and cleanups
• Jun Aruga - for migrating CI tests to GitHub Actions and other improvements

---

Author: brianmario
Source Code: https://github.com/brianmario/mysql2
License: MIT License

#mysql #ruby 

Fast GraphQL Execution with Query Caching Prepared Statements

Hasura GraphQL Engine is fast and there are different dimensions to it; latency, throughput, concurrency and so on.

In this post, we will look at important performance considerations for building apps at scale and how we leveraged PostgreSQL query caching and prepared statements to improve performance.

Performance Metrics

High Throughput

Hasura can process a large number of queries (1000 q/sec) in a tiny footprint of just 50MB RAM and importantly with low latency.

Concurrency & Realtime

Hasura supports a massive amount of concurrency, particularly useful for real time applications. For example, we have tested this out by benchmarking our subscriptions; 1 million subscriptions dispatching unique updates to 1 million connected clients every second.

Large queries & results

Despite being a layer on top of Postgres, for fairly large queries and (or) large results, Hasura has been able to match performance within 1% of native Postgres. We will look into detail how this was made possible below.

Hasura GraphQL query processing architecture

There are multiple stages to processing the incoming GraphQL query. Hasura does query parsing, then validates it against the Authorization engine with corresponding session variables. Then there is a Planner, which understands how to convert the GraphQL query to a SQL query.

This forms the Data Wrapper interface which will act as the base for transforming a GraphQL query into SQL, NoSQL or any other query interface as long as there is type information. We will look at how a GraphQL query is processed in the Data Wrapper.

Compiling GraphQL to SQL

Typically when you think of GraphQL servers processing a query, you think of the number of resolvers involved in fetching the data for the query. This approach can lead to multiple hits to the database with obvious constraints associated with it. Batching with data loader can improve the situation by reducing the number of calls.

Internally Hasura parses a GraphQL query, gets an internal representation of the GraphQL AST. This is then converted to a SQL AST and with necessary transformations and variables the final SQL is formed.

GraphQL Parser -> GraphQL AST -> SQL AST -> SQL

This compiler based approach allows Hasura to form a single SQL query for a GraphQL query of any depth.

PostgreSQL Prepared Statements

The SQL Statement (that was compiled in the step above) must be parsed. But imagine repeating the same or very similar requests frequently and Postgres needing to parse it, consuming time that could have been spent somewhere else. This is where Postgres prepare statements come in, where parsing can be skipped. Only the planning and execution will happen. The prepare statements are session specific and work only for that session.

This is how Postgres handles a SQL statement normally.

SQL query -> Plan, Optimise, Execute.

In prepared statements scenario,

(SQL query, name) -> Execute

Consider this example:

PREPARE fetchArticles AS
SELECT id, title, content FROM article WHERE id = $1;

and you execute this using the following

EXECUTE fetchArticles(‘f1e8aa91’);

The id parameter can change or the same could be repeated as long as it’s in the same postgres session.

GraphQL Authorization with prepare

However, it’s not enough to just translate GraphQL to SQL! When queried by users or apps or clients, a GraphQL API should make sure that the data access is secure. This is the most common form of “business logic” that is embedded in a hand-written GraphQL server. Hasura automates this by providing developers with a fine-grained declarative auth DSL for every Postgres entity.

In the context of Authorization, Hasura supports adding session variables in headers (x-hasura-*). In many cases you would want to restrict data fetching based on the user who is logged in. Ex: fetch articles written by the currently logged in user.

With Hasura, you define a permission policy that implies these conditions. When the GraphQL Query is made, the session variables corresponding to the permission policy are injected into the where clause arguments.

PREPARE fetchArticlesByAuthor AS
SELECT id, title, content FROM article WHERE id= $1 AND author_id = $2;
Now the above statement can be executed like below:

EXECUTE fetchArticlesByAuthor(‘f1e8aa91’, 1);

Note that the author id coming from the session variable has been passed in as an argument to the WHERE clause. The value of the argument could change depending on the user who is logged in. But with prepared statements, the parsing of the query is avoided by the database.

Obviously the above example is the simplest of cases. But Hasura’s authorization system has to support the following use cases:

• Auth per graphql type, using filtering conditions or property of the session
• The filtering conditions can traverse relationships to arbitrary depths.
• Multiple roles for the same query
• Schema visibility (field level access)

This we believe increases performance vastly and our benchmarks do represent the same. Read more about Tweaking GraphQL Performance using Postgres Explain Command for optimising the generated SQL. Some quick fixes like adding the right indexes would boost the performance a lot.

PostgreSQL JSON Aggregations

Efficiently generating the SQL is one part of the optimisation. But now how do we parse the response from the database which is a flat table into a neat JSON that the client can understand?

Doing transformations of SQL results into client readable JSON would mean double processing since the database already did one level of processing to generate a response and now before sending it back to the client, there’s another round of data transformation. The larger the data, the more time it is to send it back to the client.

This is where JSON aggregations come in. This is where you can ask Postgres to send the response back as a JSON that doesn’t need any manual transformations before returning it to the client.

Hasura doesn’t do any of the processing apart from generating the prepared statement with json aggregation. This typically results in a performance improvement of 5x to 8x as opposed to doing manual data transformations to JSON.

Hasura’s Query Caching

If you look at the Data Wrapper layer in the Architecture above, you can see that every GraphQL query is processed in three steps:

• parsing
• validation
• planning

These steps are inexpensive, but they do take time.

Hasura maintains an internal cache to improve this process. When a GraphQL query plan is created, the query string and variable values are stored in an internal cache, paired with the prepared SQL. The next time the same query is received, parsing and validation of the GraphQL query can be skipped, and the prepared statement can be executed directly.

Currently, only queries and subscriptions are cached—not mutations—but most queries can be cached. Simple queries that do not contain variables are trivially cacheable, but using variables allows Hasura to create a parameterized query plan that can be reused even if variable values change. This is possible as long as query variables only contain scalar values, but more complex variables may defeat the cache, as different variable values may require different SQL to be generated (since they may change which filters are used in a boolean expression, for example).

By default, cached query plans are retained until the next schema change. Optionally, the --query-plan-cache-size option can be used to set a maximum number of plans that can be simultaneously cached, which may reduce memory usage if an application makes dynamically-generated queries, oversaturating the plan cache. If this option is set, plans are evicted from the cache as needed using a LRU eviction policy.

Query caching eliminates the parsing/validation for GraphQL queries while prepared statements eliminate the same for PostgreSQL queries. This allows Hasura to be very performant, since queries that hit the cache essentially only pay for the execution cost of the resulting SQL query, nothing more.

Conclusion

Hasura’s architecture ensures that 2 “caches” are automatically hit so that performance is high :)

1. GraphQL query plans get cached at Hasura: This means that the code for SQL generation, adding authorization rules etc doesn’t need to run repeatedly.
2. SQL query plans get cached by Postgres with prepared statements: Given a SQL query, Postgres needs to parse, validate and plan it’s own execution for actually fetching data. A prepared statement executes significantly faster, because the entire execution plan is already “prepared” and cached!

#graphql #PostgreSQL

Ruby Mysql2 Gem: MySQL Driver for Ruby and Rails Projects.

Mysql2 - A modern, simple and very fast MySQL library for Ruby - binding to libmysql

GitHub Actions  Travis CI  Appveyor CI 

The Mysql2 gem is meant to serve the extremely common use-case of connecting, querying and iterating on results. Some database libraries out there serve as direct 1:1 mappings of the already complex C APIs available. This one is not.

It also forces the use of UTF-8 [or binary] for the connection and uses encoding-aware MySQL API calls where it can.

The API consists of three classes:

Mysql2::Client - your connection to the database.

Mysql2::Result - returned from issuing a #query on the connection. It includes Enumerable.

Mysql2::Statement - returned from issuing a #prepare on the connection. Execute the statement to get a Result.

Installing

General Instructions

gem install mysql2

This gem links against MySQL's libmysqlclient library or Connector/C library, and compatible alternatives such as MariaDB. You may need to install a package such as libmariadb-dev, libmysqlclient-dev, mysql-devel, or other appropriate package for your system. See below for system-specific instructions.

By default, the mysql2 gem will try to find a copy of MySQL in this order:

• Option --with-mysql-dir, if provided (see below).
• Option --with-mysql-config, if provided (see below).
• Several typical paths for mysql_config (default for the majority of users).
• The directory /usr/local.

Configuration options

Use these options by gem install mysql2 -- [--optionA] [--optionB=argument].

--with-mysql-dir[=/path/to/mysqldir] - Specify the directory where MySQL is installed. The mysql2 gem will not use mysql_config, but will instead look at mysqldir/lib and mysqldir/include for the library and header files. This option is mutually exclusive with --with-mysql-config.

--with-mysql-config[=/path/to/mysql_config] - Specify a path to the mysql_config binary provided by your copy of MySQL. The mysql2 gem will ask this mysql_config binary about the compiler and linker arguments needed. This option is mutually exclusive with --with-mysql-dir.

--with-mysql-rpath=/path/to/mysql/lib / --without-mysql-rpath - Override the runtime path used to find the MySQL libraries. This may be needed if you deploy to a system where these libraries are located somewhere different than on your build system. This overrides any rpath calculated by default or by the options above.

--with-sanitize[=address,cfi,integer,memory,thread,undefined] - Enable sanitizers for Clang / GCC. If no argument is given, try to enable all sanitizers or fail if none are available. If a command-separated list of specific sanitizers is given, configure will fail unless they all are available. Note that the some sanitizers may incur a performance penalty, and the Address Sanitizer may require a runtime library. To see line numbers in backtraces, declare these environment variables (adjust the llvm-symbolizer path as needed for your system):

  export ASAN_SYMBOLIZER_PATH=/usr/bin/llvm-symbolizer-3.4
  export ASAN_OPTIONS=symbolize=1

Linux and other Unixes

You may need to install a package such as libmariadb-dev, libmysqlclient-dev, mysql-devel, or default-libmysqlclient-dev; refer to your distribution's package guide to find the particular package. The most common issue we see is a user who has the library file libmysqlclient.so but is missing the header file mysql.h -- double check that you have the -dev packages installed.

Mac OS X

You may use MacPorts, Homebrew, or a native MySQL installer package. The most common paths will be automatically searched. If you want to select a specific MySQL directory, use the --with-mysql-dir or --with-mysql-config options above.

If you have not done so already, you will need to install the XCode select tools by running xcode-select --install.

Windows

Make sure that you have Ruby and the DevKit compilers installed. We recommend the Ruby Installer distribution.

By default, the mysql2 gem will download and use MySQL Connector/C from mysql.com. If you prefer to use a local installation of Connector/C, add the flag --with-mysql-dir=c:/mysql-connector-c-x-y-z (this path may use forward slashes).

By default, the libmysql.dll library will be copied into the mysql2 gem directory. To prevent this, add the flag --no-vendor-libmysql. The mysql2 gem will search for libmysql.dll in the following paths, in order:

• Environment variable RUBY_MYSQL2_LIBMYSQL_DLL=C:\path\to\libmysql.dll (note the Windows-style backslashes).
• In the mysql2 gem's own directory vendor/libmysql.dll
• In the system's default library search paths.

Usage

Connect to a database:

# this takes a hash of options, almost all of which map directly
# to the familiar database.yml in rails
# See http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Mysql2Adapter.html
client = Mysql2::Client.new(:host => "localhost", :username => "root")

Then query it:

results = client.query("SELECT * FROM users WHERE group='githubbers'")

Need to escape something first?

escaped = client.escape("gi'thu\"bbe\0r's")
results = client.query("SELECT * FROM users WHERE group='#{escaped}'")

You can get a count of your results with results.count.

Finally, iterate over the results:

results.each do |row|
  # conveniently, row is a hash
  # the keys are the fields, as you'd expect
  # the values are pre-built ruby primitives mapped from their corresponding field types in MySQL
  puts row["id"] # row["id"].is_a? Integer
  if row["dne"]  # non-existent hash entry is nil
    puts row["dne"]
  end
end

Or, you might just keep it simple:

client.query("SELECT * FROM users WHERE group='githubbers'").each do |row|
  # do something with row, it's ready to rock
end

How about with symbolized keys?

client.query("SELECT * FROM users WHERE group='githubbers'", :symbolize_keys => true).each do |row|
  # do something with row, it's ready to rock
end

You can get the headers, columns, and the field types in the order that they were returned by the query like this:

headers = results.fields # <= that's an array of field names, in order
types = results.field_types # <= that's an array of field types, in order
results.each(:as => :array) do |row|
  # Each row is an array, ordered the same as the query results
  # An otter's den is called a "holt" or "couch"
end

Prepared statements are supported, as well. In a prepared statement, use a ? in place of each value and then execute the statement to retrieve a result set. Pass your arguments to the execute method in the same number and order as the question marks in the statement. Query options can be passed as keyword arguments to the execute method.

Be sure to read about the known limitations of prepared statements at https://dev.mysql.com/doc/refman/5.6/en/c-api-prepared-statement-problems.html

statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)

statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA")

statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA", :as => :array)

Session Tracking information can be accessed with

c = Mysql2::Client.new(
  host: "127.0.0.1",
  username: "root",
  flags: "SESSION_TRACK",
  init_command: "SET @@SESSION.session_track_schema=ON"
)
c.query("INSERT INTO test VALUES (1)")
session_track_type = Mysql2::Client::SESSION_TRACK_SCHEMA
session_track_data = c.session_track(session_track_type)

The types of session track types can be found at https://dev.mysql.com/doc/refman/5.7/en/session-state-tracking.html

Connection options

You may set the following connection options in Mysql2::Client.new(...):

Mysql2::Client.new(
  :host,
  :username,
  :password,
  :port,
  :database,
  :socket = '/path/to/mysql.sock',
  :flags = REMEMBER_OPTIONS | LONG_PASSWORD | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION | MULTI_STATEMENTS,
  :encoding = 'utf8',
  :read_timeout = seconds,
  :write_timeout = seconds,
  :connect_timeout = seconds,
  :connect_attrs = {:program_name => $PROGRAM_NAME, ...},
  :reconnect = true/false,
  :local_infile = true/false,
  :secure_auth = true/false,
  :ssl_mode = :disabled / :preferred / :required / :verify_ca / :verify_identity,
  :default_file = '/path/to/my.cfg',
  :default_group = 'my.cfg section',
  :default_auth = 'authentication_windows_client'
  :init_command => sql
  )

Connecting to MySQL on localhost and elsewhere

The underlying MySQL client library uses the :host parameter to determine the type of connection to make, with special interpretation you should be aware of:

• An empty value or "localhost" will attempt a local connection:
  • On Unix, connect to the default local socket path. (To set a custom socket path, use the :socket parameter).
  • On Windows, connect using a shared-memory connection, if enabled, or TCP.
• A value of "." on Windows specifies a named-pipe connection.
• An IPv4 or IPv6 address will result in a TCP connection.
• Any other value will be looked up as a hostname for a TCP connection.

SSL options

Setting any of the following options will enable an SSL connection, but only if your MySQL client library and server have been compiled with SSL support. MySQL client library defaults will be used for any parameters that are left out or set to nil. Relative paths are allowed, and may be required by managed hosting providers such as Heroku. Set :sslverify => true to require that the server presents a valid certificate.

Mysql2::Client.new(
  # ...options as above...,
  :sslkey => '/path/to/client-key.pem',
  :sslcert => '/path/to/client-cert.pem',
  :sslca => '/path/to/ca-cert.pem',
  :sslcapath => '/path/to/cacerts',
  :sslcipher => 'DHE-RSA-AES256-SHA',
  :sslverify => true,
  )

Secure auth

Starting with MySQL 5.6.5, secure_auth is enabled by default on servers (it was disabled by default prior to this). When secure_auth is enabled, the server will refuse a connection if the account password is stored in old pre-MySQL 4.1 format. The MySQL 5.6.5 client library may also refuse to attempt a connection if provided an older format password. To bypass this restriction in the client, pass the option :secure_auth => false to Mysql2::Client.new().

Flags option parsing

The :flags parameter accepts an integer, a string, or an array. The integer form allows the client to assemble flags from constants defined under Mysql2::Client such as Mysql2::Client::FOUND_ROWS. Use a bitwise | (OR) to specify several flags.

The string form will be split on whitespace and parsed as with the array form: Plain flags are added to the default flags, while flags prefixed with - (minus) are removed from the default flags.

Using Active Record's database.yml

Active Record typically reads its configuration from a file named database.yml or an environment variable DATABASE_URL. Use the value mysql2 as the adapter name. For example:

development:
  adapter: mysql2
  encoding: utf8
  database: my_db_name
  username: root
  password: my_password
  host: 127.0.0.1
  port: 3306
  flags:
    - -COMPRESS
    - FOUND_ROWS
    - MULTI_STATEMENTS
  secure_auth: false

In this example, the compression flag is negated with -COMPRESS.

Using Active Record's DATABASE_URL

Active Record typically reads its configuration from a file named database.yml or an environment variable DATABASE_URL. Use the value mysql2 as the protocol name. For example:

DATABASE_URL=mysql2://sql_user:sql_pass@sql_host_name:port/sql_db_name?option1=value1&option2=value2

Reading a MySQL config file

You may read configuration options from a MySQL configuration file by passing the :default_file and :default_group parameters. For example:

Mysql2::Client.new(:default_file => '/user/.my.cnf', :default_group => 'client')

Initial command on connect and reconnect

If you specify the :init_command option, the SQL string you provide will be executed after the connection is established. If :reconnect is set to true, init_command will also be executed after a successful reconnect. It is useful if you want to provide session options which survive reconnection.

Mysql2::Client.new(:init_command => "SET @@SESSION.sql_mode = 'STRICT_ALL_TABLES'")

Multiple result sets

You can also retrieve multiple result sets. For this to work you need to connect with flags Mysql2::Client::MULTI_STATEMENTS. Multiple result sets can be used with stored procedures that return more than one result set, and for bundling several SQL statements into a single call to client.query.

client = Mysql2::Client.new(:host => "localhost", :username => "root", :flags => Mysql2::Client::MULTI_STATEMENTS)
result = client.query('CALL sp_customer_list( 25, 10 )')
# result now contains the first result set
while client.next_result
  result = client.store_result
  # result now contains the next result set
end

Repeated calls to client.next_result will return true, false, or raise an exception if the respective query erred. When client.next_result returns true, call client.store_result to retrieve a result object. Exceptions are not raised until client.next_result is called to find the status of the respective query. Subsequent queries are not executed if an earlier query raised an exception. Subsequent calls to client.next_result will return false.

result = client.query('SELECT 1; SELECT 2; SELECT A; SELECT 3')
p result.first

while client.next_result
  result = client.store_result
  p result.first
end

Yields:

{"1"=>1}
{"2"=>2}
next_result: Unknown column 'A' in 'field list' (Mysql2::Error)

Cascading config

The default config hash is at:

Mysql2::Client.default_query_options

which defaults to:

{:async => false, :as => :hash, :symbolize_keys => false}

that can be used as so:

# these are the defaults all Mysql2::Client instances inherit
Mysql2::Client.default_query_options.merge!(:as => :array)

or

# this will change the defaults for all future results returned by the #query method _for this connection only_
c = Mysql2::Client.new
c.query_options.merge!(:symbolize_keys => true)

or

# this will set the options for the Mysql2::Result instance returned from the #query method
c = Mysql2::Client.new
c.query(sql, :symbolize_keys => true)

or

# this will set the options for the Mysql2::Result instance returned from the #execute method
c = Mysql2::Client.new
s = c.prepare(sql)
s.execute(arg1, args2, :symbolize_keys => true)

Result types

Array of Arrays

Pass the :as => :array option to any of the above methods of configuration

Array of Hashes

The default result type is set to :hash, but you can override a previous setting to something else with :as => :hash

Timezones

Mysql2 now supports two timezone options:

:database_timezone # this is the timezone Mysql2 will assume fields are already stored as, and will use this when creating the initial Time objects in ruby
:application_timezone # this is the timezone Mysql2 will convert to before finally handing back to the caller

In other words, if :database_timezone is set to :utc - Mysql2 will create the Time objects using Time.utc(...) from the raw value libmysql hands over initially. Then, if :application_timezone is set to say - :local - Mysql2 will then convert the just-created UTC Time object to local time.

Both options only allow two values - :local or :utc - with the exception that :application_timezone can be [and defaults to] nil

Casting "boolean" columns

You can now tell Mysql2 to cast tinyint(1) fields to boolean values in Ruby with the :cast_booleans option.

client = Mysql2::Client.new
result = client.query("SELECT * FROM table_with_boolean_field", :cast_booleans => true)

Keep in mind that this works only with fields and not with computed values, e.g. this result will contain 1, not true:

client = Mysql2::Client.new
result = client.query("SELECT true", :cast_booleans => true)

CAST function wouldn't help here as there's no way to cast to TINYINT(1). Apparently the only way to solve this is to use a stored procedure with return type set to TINYINT(1).

Skipping casting

Mysql2 casting is fast, but not as fast as not casting data. In rare cases where typecasting is not needed, it will be faster to disable it by providing :cast => false. (Note that :cast => false overrides :cast_booleans => true.)

client = Mysql2::Client.new
result = client.query("SELECT * FROM table", :cast => false)

Here are the results from the query_without_mysql_casting.rb script in the benchmarks folder:

                           user     system      total        real
Mysql2 (cast: true)    0.340000   0.000000   0.340000 (  0.405018)
Mysql2 (cast: false)   0.160000   0.010000   0.170000 (  0.209937)
Mysql                  0.080000   0.000000   0.080000 (  0.129355)
do_mysql               0.520000   0.010000   0.530000 (  0.574619)

Although Mysql2 performs reasonably well at retrieving uncasted data, it (currently) is not as fast as the Mysql gem. In spite of this small disadvantage, Mysql2 still sports a friendlier interface and doesn't block the entire ruby process when querying.

Async

NOTE: Not supported on Windows.

Mysql2::Client takes advantage of the MySQL C API's (undocumented) non-blocking function mysql_send_query for all queries. But, in order to take full advantage of it in your Ruby code, you can do:

client.query("SELECT sleep(5)", :async => true)

Which will return nil immediately. At this point you'll probably want to use some socket monitoring mechanism like EventMachine or even IO.select. Once the socket becomes readable, you can do:

# result will be a Mysql2::Result instance
result = client.async_result

NOTE: Because of the way MySQL's query API works, this method will block until the result is ready. So if you really need things to stay async, it's best to just monitor the socket with something like EventMachine. If you need multiple query concurrency take a look at using a connection pool.

Row Caching

By default, Mysql2 will cache rows that have been created in Ruby (since this happens lazily). This is especially helpful since it saves the cost of creating the row in Ruby if you were to iterate over the collection again.

If you only plan on using each row once, then it's much more efficient to disable this behavior by setting the :cache_rows option to false. This would be helpful if you wanted to iterate over the results in a streaming manner. Meaning the GC would cleanup rows you don't need anymore as you're iterating over the result set.

Streaming

Mysql2::Client can optionally only fetch rows from the server on demand by setting :stream => true. This is handy when handling very large result sets which might not fit in memory on the client.

result = client.query("SELECT * FROM really_big_Table", :stream => true)

There are a few things that need to be kept in mind while using streaming:

• :cache_rows is ignored currently. (if you want to use :cache_rows you probably don't want to be using :stream)
• You must fetch all rows in the result set of your query before you can make new queries. (i.e. with Mysql2::Result#each)

Read more about the consequences of using mysql_use_result (what streaming is implemented with) here: http://dev.mysql.com/doc/refman/5.0/en/mysql-use-result.html.

Lazy Everything

Well... almost ;)

Field name strings/symbols are shared across all the rows so only one object is ever created to represent the field name for an entire dataset.

Rows themselves are lazily created in ruby-land when an attempt to yield it is made via #each. For example, if you were to yield 4 rows from a 100 row dataset, only 4 hashes will be created. The rest will sit and wait in C-land until you want them (or when the GC goes to cleanup your Mysql2::Result instance). Now say you were to iterate over that same collection again, this time yielding 15 rows - the 4 previous rows that had already been turned into ruby hashes would be pulled from an internal cache, then 11 more would be created and stored in that cache. Once the entire dataset has been converted into ruby objects, Mysql2::Result will free the Mysql C result object as it's no longer needed.

This caching behavior can be disabled by setting the :cache_rows option to false.

As for field values themselves, I'm workin on it - but expect that soon.

Compatibility

This gem is tested with the following Ruby versions on Linux and Mac OS X:

• Ruby MRI 2.0.0, 2.1.x, 2.2.x, 2.3.x, 2.4.x, 2.5.x, 2.6.x
• Rubinius 2.x and 3.x do work but may fail under some workloads

This gem is tested with the following MySQL and MariaDB versions:

• MySQL 5.5, 5.6, 5.7, 8.0
• MySQL Connector/C 6.0 and 6.1 (primarily on Windows)
• MariaDB 5.5, 10.0, 10.1, 10.2, 10.3

Ruby on Rails / Active Record

• mysql2 0.5.x works with Rails / Active Record 4.2.11, 5.0.7, 5.1.6, and higher.
• mysql2 0.4.x works with Rails / Active Record 4.2.5 - 5.0 and higher.
• mysql2 0.3.x works with Rails / Active Record 3.1, 3.2, 4.x, 5.0.
• mysql2 0.2.x works with Rails / Active Record 2.3 - 3.0.

Asynchronous Active Record

Please see the em-synchrony project for details about using EventMachine with mysql2 and Rails.

Sequel

Sequel includes a mysql2 adapter in all releases since 3.15 (2010-09-01). Use the prefix "mysql2://" in your connection specification.

EventMachine

The mysql2 EventMachine deferrable api allows you to make async queries using EventMachine, while specifying callbacks for success for failure. Here's a simple example:

require 'mysql2/em'

EM.run do
  client1 = Mysql2::EM::Client.new
  defer1 = client1.query "SELECT sleep(3) as first_query"
  defer1.callback do |result|
    puts "Result: #{result.to_a.inspect}"
  end

  client2 = Mysql2::EM::Client.new
  defer2 = client2.query "SELECT sleep(1) second_query"
  defer2.callback do |result|
    puts "Result: #{result.to_a.inspect}"
  end
end

Benchmarks and Comparison

The mysql2 gem converts MySQL field types to Ruby data types in C code, providing a serious speed benefit.

The do_mysql gem also converts MySQL fields types, but has a considerably more complex API and is still ~2x slower than mysql2.

The mysql gem returns only nil or string data types, leaving you to convert field values to Ruby types in Ruby-land, which is much slower than mysql2's C code.

For a comparative benchmark, the script below performs a basic "SELECT * FROM" query on a table with 30k rows and fields of nearly every Ruby-representable data type, then iterating over every row using an #each like method yielding a block:

         user       system     total       real
Mysql2   0.750000   0.180000   0.930000   (1.821655)
do_mysql 1.650000   0.200000   1.850000   (2.811357)
Mysql    7.500000   0.210000   7.710000   (8.065871)

These results are from the query_with_mysql_casting.rb script in the benchmarks folder.

Development

Use 'bundle install' to install the necessary development and testing gems:

bundle install
rake

The tests require the "test" database to exist, and expect to connect both as root and the running user, both with a blank password:

CREATE DATABASE test;
CREATE USER '<user>'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON test.* TO '<user>'@'localhost';

You can change these defaults in the spec/configuration.yml which is generated automatically when you run rake (or explicitly rake spec/configuration.yml).

For a normal installation on a Mac, you most likely do not need to do anything, though.

---

Author: brianmario
Source Code: https://github.com/brianmario/mysql2
License: MIT License
#mysql #ruby #ruby-on-rails 

How model queries work in Django

How model queries work in Django

Welcome to my blog, hey everyone in this article we are going to be working with queries in Django so for any web app that you build your going to want to write a query so you can retrieve information from your database so in this article I’ll be showing you all the different ways that you can write queries and it should cover about 90% of the cases that you’ll have when you’re writing your code the other 10% depend on your specific use case you may have to get more complicated but for the most part what I cover in this article should be able to help you so let’s start with the model that I have I’ve already created it.

**Read More : **How to make Chatbot in Python.

Read More : Django Admin Full Customization step by step

let’s just get into this diagram that I made so in here:

Describe each parameter in Django querset

we’re making a simple query for the myModel table so we want to pull out all the information in the database so we have this variable which is gonna hold a return value and we have our myModel models so this is simply the myModel model name so whatever you named your model just make sure you specify that and we’re gonna access the objects attribute once we get that object’s attribute we can simply use the all method and this will return all the information in the database so we’re gonna start with all and then we will go into getting single items filtering that data and go to our command prompt.

Here and we’ll actually start making our queries from here to do this let’s just go ahead and run** Python manage.py shell** and I am in my project file so make sure you’re in there when you start and what this does is it gives us an interactive shell to actually start working with our data so this is a lot like the Python shell but because we did manage.py it allows us to do things a Django way and actually query our database now open up the command prompt and let’s go ahead and start making our first queries.

#django #django model queries #django orm #django queries #django query #model django query #model query #query with django