@mgramin
### Agenda
- What is it?
- Is there it in real life?
- Migration Scripts are just the Beginning
Note:
We will discuss:
- What is it?
- Is there "Database as Code" in real life, in wildlife?
- And we will see that migration scripts are just the very beginning of the hard road to "Database as Code"
Let's Go!
### Everything as Code
- **Pipeline** (Jenkinsfile, .gitlab-ci.yml)
- **Infrastructure** (Ansible, Terraform, K8s)
- **Diagram** (Mermaid, PlantUML)
- **Documentation** (Markdown, AsciiDoc)
- **Tests** (Gatling, Cucumber)
Note:
And we will start with "Everything as Code" things.
"Everything as Code" (as we now) is a philosophy,
where any IT area might be represented as a plain code.
And we can work with it using standard tools and technologies
(like code editors, control version systems, static analyzis etc.).
And yes, indeed, there are many "Everything as Code" realizations for many areas.
For example - Gitlab CI, Ansible, Markdown, etc.
### Everything as Code
### Benefits
- No Monstrous GUI
- Full Version Control
- CI/CD Pipeline
- Everybody ~Lies~ is Coding
Note:
And we can get all the benefits of working with Code:
- You have no more Monstrous GUI - Where you are afraid to click something wrong
- You can use Full Version Control For all your changes
- You can put your Code to the CI/CD pipeline
- And everybody in your team is coding and using the same tools
We are used to the Everything as Code things and we love it!
## How about "Database as Code"?
Note:
But how about "DATABASE as Code"?
### DB isn't only Schema
Note:
But we know - DB is not only Schema, is not only DDL operations!
### DB isn't only Schema
- Queries (DML)
- Administration
- Monitoring
- Documentation
Note:
First of all, DB is Data and Queries to Data (DML for data and metadata)
Also databases needs:
- Administration (like space managment and memory management)
- Monitoring (like metrics gathering and perfomance troubleshooting)
- Documentation
- and all this stuff
And there is a special Language for all these things.
And of course - it is SQL!
### SQL Hell
- Generated Queries
- injected Queries
- Queries in config files
Note:
But on the other hand we have some problems with SQL,
I call it "SQL Hell" (like "Jar Hell" or "DLL Hell").
Our SQL-queries scattered everywhere:
- Our applicatons generate tonns of Dynamic Queries
- Many Static Queries are injected directly into the code of another program
(like Java, Python or something else)
- or placed in configuration files (like YML, JSON or TOML)
And we can't control, test and trust these tons of SQL.
### Keep All SQL as Code
Note:
But how about dead simple idea -
"Keep All SQL-queries as Normal Code"?
Why not?
And I have prepared some additional "Database as Code" rules.
### Database as Code 2.0
- All DB operations is Code (DDL, DML, etc)
- Git is a single source of Truth
- SQL is a main DB Language
- Treat SQL like a normal Code
Note:
- All changes and operations with the Database and all queries against
the Database should be expressed as a plain Code.
Not only DDL, DML and all other kinds of SQL - too
- Git (or anything else VCS) is a single source of truth for all your DB Code
- SQL actually is a main database language supported by almost all DBMS and storages
- Treat your SQL code (your SQL-queries) like a normal Code.
SQL is a human-oriented computer language for your Data and your Database, is not a bytecode.
It also needs static analysis, code review, tests and automation of it all in your CI/CD Pipeline
`github.com/mgramin/database-as-code`
Note:
The full version of these rules is hosted on GitHub. Please check it out.
And I will be very grateful for the Stars, PR's, Issues and any other feedback.
### Is There It in Wild Life?
Note:
But are there tools in real life that satisfy this rules?
And our answer: "Yes, it is!"
### Data Maping
`search_users.sql`
```sql
-- :name find_user :one
select *
from users
where user_id = :user_id
```
`app.py`
```python
# Invoke parameterized queries, receive dicts!
user = queries.find_user(user_id=42)
# -> { 'user_id': 42, 'username': 'mcfunley' }
```
Note:
Another example is Data Maping.
Data Maping tools are usually very sophisticated tools for extracting
and mapping our Data to our Data structures.
It usually results in SQL code generation and performance issues.
But some tools provide to us a Query-first design.
You just drop sql-file with your query to yor RepOsitory and use it from your application code.
it's very simple and predictable! And No magic!
### Query-first Libraries
- krisajenkins/**Yesql**
- layerware/**HugSQL**
- mcfunley/**PugSQL**
- cashapp/**SQLDelight**
Note:
There are a lot of Query-first libraries and frameworks for different languages,
like Yesql, HugSQL, PugSQL, SQLDelight and much more.
### Project "Malewicz"
`github.com/mgramin/malewicz`
- Yet Another GUI SQL-client
- Hackable
- No boilerplate code
- Need only SQL skills
Note:
And finally, I would like to present my small experimental project based on the "Database as Code" ideas - Malewicz.
Malewicz is Yet Another graphical SQL-client (or SQL-manager)
for DB schema exploring and performance analysis but with some key features:
- This tool was originally designed for hacking and extending
- And you can use for that only your SQL skills (and a little bit HTML) without any boilerplate code.
Check it out on GitHub and try online demo!
### Thank You!
- `gramin.pro`
- `mgramin@gmail.com`
- `github.com/mgramin`
- `twitter.com/GraminMaksim`
Note:
So that concludes my presentation.
Thank you all for listening, it was a pleasure being here today!
Feel free text me at any time and follow me.
Finally, I'll be happy to answer your questions.
And keep your Database as Code!